Month: April 2014

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!