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


 

HTML Text

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:

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.

  
 

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