Special Variables

[ Home / SQLBatch Home / Documentation / Basics ]


 Predefined Variables Overview

There are a few special predefined variables.

SQLCODE, SQLERRM variables

These variables are set after each execution of an explicit or implicit SQL command.

SQLCODE and SQLERRM example

SQL> select * from nonexistingtable;
ORA-00942  table or view does not exist
SQL>? SQLCODE={sqlcode}
SQLCODE=942
SQL>? SQLERRM={sqlerrm}
SQLERRM=ORA-00942  table or view does not exist
SQL>{#if sqlcode != 0} exit {#endif}
Disconnected
Finished

ARG variable

In fact, this is a function that takes one argument or nothing. It is used to access command line arguments supplied to the current script by START, @, @@ commands

SyntaxDescriptionExample
ARG ARG without arguments returns the first command line parameter and removes it. If there is no any parameter then an empty string is returned prompt {arg} -- print the first parameter
ARG [(] positive_integer [)] Returns the positive_integerth parameter but does not remove it. If the parameter does not exist then an empty string is returned. Use &1 .. &9 (like in SQLPlus) if you want the value to be read from console when the parameter does not exist instead of returning an empty string. prompt {arg 1}:{arg 2} -- print the first and the second parameters

ARG example 1

{#if !arg 1 || !arg(2)} -- if arg1 or arg2 are not supplied
 ?{script_name}: Invalid parameters!  
 RETURN -- return back to the calling script
{#endif}
....

ARG example 2

{
	par1 = arg; -- get the first parameter
	par2 = arg; -- get the next parameter
}
?'{par1}':'{par2}'  -- print them
....

DATE, DATETIME variables

These variables contain current date or date and time.

Use DATEFORMAT and DATETIMEFORMAT to change display format.
Format modelDescriptionExample (November 23, 18:34:01 2003)
DDNumeric day of month23
MMNumeric month of year11
MON3-letter month of yearNov
YY2-digit year03
YYYY4-digit year2003
HHhours (0-23) 18
MIminutes34
SSseconds 01

Default formats:
DATEFORMATYYYY-MM-DD
DATETIMEFORMATYYYY-MM-DD HH:MI:SS

DATE, DATETIME example

SQL>{dateformat = 'yymmdd';}
SQL>spool >>/logs/{date}.log  -- switch log daily
SQL>....
SQL>spool off
SQL>

SCRIPT_NAME, SCRIPT_FILE, SCRIPT_DIR, HOME_DIR, BIN_DIR variables

VariableDescription Sample value (for "c:\oracle\o817\rdbms\admin\catalog.sql")
SCRIPT_NAME Current script name without extension and path catalog
SCRIPT_FILE Current script full namec:\oracle\o817\rdbms\admin\catalog.sql
SCRIPT_DIR Current script directoryc:\oracle\o817\rdbms\admin
HOME_DIR SB home directory c:/Program files/Sb
BIN_DIR SB bin directory c:/Program files/Sb/Bin

ENV variable

ENV is a structure that contains OS environment in its fields. Modifying ENV fields changes actual corresponding environment variables for current and child processes.

ENV example 1

This code prints TEMP environment variable.
SQL> prompt {env->temp}

C:\Temp

ENV example 2

This code adds the directory to PATH. SB will use PATH to run programs and scripts if their full path is not specified. Note double backslashes in the string literal.
SQL> {env->path .= ';c:\\user\\app';}

ENV example 3

This code prints all environment variables' names.
SQL>  ?{join ' ', keys %{env}}

PROMPT PWD PROCESSOR_IDENTIFIER TMP OS2LIBPATH USERNAME TEMP USERPROFILE
USERDOMAIN HOMEDRIVE OS PATH PATHEXT COMPUTERNAME COMMONPROGRAMFILES
LOGONSERVER LIB PROGRAMFILES PROCESSOR_ARCHITECTURE SYSTEMROOT 
PROCESSOR_REVISION WINDOWS_LOGIN ALLUSERSPROFILE COMSPEC SYSTEMDRIVE 
WINDIR INCLUDE PROCESSOR_LEVEL NUMBER_OF_PROCESSORS HOMEPATH 
The keys function is applicable to any structure variable. It returns a list of structure's fields. The join function joins the strings from the list with the separator given.

DDL variable

DDL is a structure that controls output of the X-DDL command.

It has the following nested fields and structures (the format is: field=>default value or field=>{nested structure}):

DDL=>
{
	STORAGE_SCALE => 1000, 
	STORAGE_MIN_UNIT => 'K',
	STORAGE_MIN => 1024, 
	QUOTE_NAMES => 0,
	NAME_CASE => 'L', /*Lower, Upper*/
	OWNER => 0,
	SCHEMA =>
	{
		SUBST =>
		{
			
		}
	},
	TABLE => 
	{
  		SUBSTSOURCE =>
  		{
  		},
		SUBST => 
		{
				PCT_FREE => '',
				PCT_USED => '',
				PCT_INCREASE => '',
				TABLESPACE => ''	
		},
		CONSTRAINTS => 0,
		INDEXES => 0,
		USE_ATTRIBUTES =>1,
		ATTRIBUTES =>
		{
			PHYSICAL_ATTRIBUTES =>
			{
	   		PCT_FREE=>1,
	   		PCT_USED=>1,
	   		INI_TRANS=>0,
	   		MAX_TRANS=>0,
				STORAGE =>
 				{
			 		INITIAL_EXTENT=>1,
					NEXT_EXTENT =>1,
					MIN_EXTENTS=>0,
					MAX_EXTENTS=>0,
	   			PCT_INCREASE=>1,
	   			FREELISTS=>0,
	   			FREELIST_GROUPS=>0
				}
			},
			TABLESPACE=>1,
			CLUSTER=>1
		}
	},
	INDEX => 
	{
		SUBST => 
		{
				PCT_FREE => '',
				PCT_INCREASE => '',
				TABLESPACE => ''	
		},
		USE_ATTRIBUTES =>1,
		ATTRIBUTES =>
		{
			PHYSICAL_ATTRIBUTES =>
			{
	   		PCT_FREE=>1,
	   		INI_TRANS=>0,
	   		MAX_TRANS=>0,
				STORAGE =>
 				{
			 		INITIAL_EXTENT=>1,
					NEXT_EXTENT =>1,
					MIN_EXTENTS=>0,
					MAX_EXTENTS=>0,
	   			PCT_INCREASE=>1,
	   			FREELISTS=>0,
	   			FREELIST_GROUPS=>0
				}
			},
			TABLESPACE=>1
		}
		
	},
	SEQUENCE =>
	{
		PRESERVE_LAST => 0
	},
	VIEW =>
	{
		CREATE_FORCE => 0
	}		
}
See Also
Language Basics
Variables


Copyright (C) 1999 - 2024 Anatoly Moskovsky Report a site problem