2.9. Command Line Arguments

You can use positional parameters with the symbols: &1, &2, &3, and so forth. Here is an example of a SQL script that takes two parameters. The intent here is for these arguments to be passed on the command line; first, the source code of the script (a total of five lines including an initial comment line), then the session that runs it.

					-- filename MY_QUERY.sql             my_query.sql line 1
set verify off
SELECT * FROM students
WHERE student_name = '&1'
AND college_major = '&2';            my_query.sql line 5
					SQL> @my_query John Business         Run with 2 parameters
					STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS
					---------- ------- -------------------- ------
					A101       John    Biology              Degree
					A102       Mary    Math/Science         Degree
					SQL>
				
  • The command SET VERIFY OFF is ...

Get Programming Oracle® Triggers and Stored Procedures, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.