|
|
|
|
 |
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
| Syntax | Description | Example |
|---|
| 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 model | Description | Example (November 23, 18:34:01 2003) |
|---|
| DD | Numeric day of month | 23 |
| MM | Numeric month of year | 11 |
| MON | 3-letter month of year | Nov |
| YY | 2-digit year | 03 |
| YYYY | 4-digit year | 2003 |
| HH | hours (0-23) | 18 |
| MI | minutes | 34 |
| SS | seconds | 01 |
Default formats:
| DATEFORMAT | YYYY-MM-DD |
| DATETIMEFORMAT | YYYY-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
| Variable | Description |
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 name | c:\oracle\o817\rdbms\admin\catalog.sql |
| SCRIPT_DIR | Current script directory | c:\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
}
}
|
|
|
|
