 |
|
SQL*Plus Script
Parameters
Oracle Tips by
Burleson
|
When you call a script, you can pass parameters to the script in
the command line, as shown in the following example:
sqlplus username/password @set_grade 999999999 2103 'A'
This is what the set_grade.sql script looks like:
UPDATE ENROLLED_COURSES
SET course_grade = upper ('&&3')
WHERE ssn = &&1
AND course_number = &&2;
In this example, 999999999 is referenced in the script as
&&1, and 2103 is referenced in the script as &&2.
The student’s grade for course 2103 is referenced as &&3.
Parameters are referenced by integer values in the order in which
they are passed (one of the reasons why it’s important to document
what parameters are used and in what order they should be passed).
References to parameters are typically made using the ampersand (&)
character; this character can be altered using the set define
command. A single ampersand instructs SQL to prompt the user for the
value of the parameter. Double ampersands (&&) instruct SQL to prompt
the user for a value if there is no value already stored for the
parameter; variables defined with a single & are undefined immediately
after the variable is used. Consequently, if a script is to run
without interaction, you should use double ampersands to reference
your parameters.
Spooling Output To
Files
The spool command is used to control the direction of output
to a file. The syntax for the spool command is:
spool filename[.sql];
If no extension is given for the spool file, SQL*Plus assumes that
you want the output file to have an extension of .LST.
To stop spooling to a file, use the following command:
spool off;
Substitution
Variables
A substitution variable is a variable name preceded by one
or two ampersands, like the variable used in the following example:
SELECT count (*)
FROM STUDENTS
WHERE last_name = upper (&LastName);
When this script is run, SQL*Plus will prompt the user for each
undefined substitution variable it encounters.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
