Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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:

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 (*)
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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter