Pass parameter to spool file

Question

Say I have a sql file which is executed from command prompt (SQL Plus), which is working fine.

sqlplus dbusername/dbpassword@DBInstance @sqlfilename.sql

Now I need to pass a parameter to this sql spool file and according to the value of parameter I should be able to determine the functionality in the sql file. In the below sql code, I need to store the value that I am passing as parameter and in the variable parameter_Value

SET SERVEROUTPUT ON
SET DEFINE OFF
SPOOL "Test.log"
var parameter_Value VARCHAR2(20);
BEGIN
 IF(parameter_Value='A')
 THEN
 --do something
 ELSE
 --do something
 END IF;
END;
/
SPOOL OFF;
SET DEFINE ON
SET SERVEROUTPUT OFF

Can anyone please help how to perform this task?
Thanks in advance!

Answer 1

Your usage of the word spoolfile is not correct. sqlfilename.sql is the sql file or sqlplus file. The output file Test.log is the spool file.

you call

sqlplus dbusername/dbpassword@DBInstance @sqlfilename.sql A

to call the following file

SPOOL "Test.log"
DEFINE MY_VALUE=&1
SET SERVEROUTPUT ON
var parameter_Value VARCHAR2(20);
EXEC :parameter_Value:='&MY_VALUE'
SET DEFINE OFF
BEGIN
 IF(:parameter_Value='A')
 THEN
 --do something
 ELSE
 --do something
 END IF;
END;
/
SET DEFINE ON
SET SERVEROUTPUT OFF
SPOOL OFF;

• the substitution of the & variables is a simple text substitution. It can be turned off by executing SET DEFINE OFF. The substituion raises information messages by sqlplus that can be turned off by executing SET VERIFY OFF

• you should start with the SPOOL command, you won't see errors thrown by commands executed before you start spooling into the logfile. This can make debugging tedious. Also you should execute all commands before SPOOL OFF

• the parameters in the command line are referend by their position number. I prefer assigning them to a named variable and use the named variable later. This is not necessary. You can use the positional parameter later, too, instead of using a named parameter. But using named parameter makes it easier to change the file if the position of a parameter changes. Also it can document the purpose of the parameter if you choose an appropriate name. I didn't in this example.

• You cannot disable the special property of the & character with SET DEFINE OFF before the parameter substitution. You have to postpone it until you have done all your substitutions. Otherwise the substitution will not work.

• a bind variable is definded with the VAR ... statement in sqlplus. In an SQL text it must be referenced with a preceding colon (:).

• EXEC statement is an abbreviation for BEGIN statement; END

• be aware that the --do something is in a PL/SQL block. So it must be replaced by PL/SQL statements and not by SQL statements or SQL*Plus statements. So it cannot be replaced by a CREATE TABLE ... statement (this is SQL but not PL/SQL) and it cannot be replaced by a SPOOL OFF statement, which is SQL*Plus but not PL/SQL.

• Sarath Subramanian: Thats great Miracle. Wll explained :) @miracle173