Passing string parameters to a SQL*Plus script
With SQL*Plus, it is possible to use parameters in the form &1, &2, … inside a script to be executed through the sqlplus command and pass the parameter values from the command line. Consider this script, contained in a file called sample.sql:
SELECT '&1' FROM DUAL;
This script can be executed from a unix shell using sqlplus as follows:
$ sqlplus user/pass@instance @sample.sql Hello ... old 1: SELECT '&1' FROM DUAL new 1: SELECT 'Hello' FROM DUAL 'HELL ----- Hello
Now, it is also possible that the parameter is not quoted inside the script:
SELECT &1 FROM DUAL;
If we execute this script in the same way as before, SQL*Plus simply inserts the parameter into the SELECT statement and it is finally treated as identifier:
$ sqlplus user/pass@instance @sample.sql Hello ... old 1: SELECT &1 FROM DUAL new 1: SELECT Hello FROM DUAL SELECT Hello FROM DUAL * ERROR at line 1: ORA-00904: "HELLO": invalid identifier
To really pass the parameter as String, we need to properly escape it on the command line, using double quotes, single quotes and both of them need to be escaped:
$ sqlplus user/pass@instance @sample.sql \"\'Hello\'\" ... old 1: SELECT &1 FROM DUAL new 1: SELECT 'Hello' FROM DUAL 'HELL ----- Hello
Note that using parameters this way should always be taken with care, so that no sql injection security issues are introduced!