Re: Passing String Arguments to SQL*Plus from a Unix Shell

Uwe

It looks like SQL*Plus itself is being (too) clever and parses away unescaped single quotes; it sometimes also treats '1','2' as two parameters rather than one. Probably a legacy of porting it to some legacy platform where comma separation is significant.

$ cat show_inputs.sql
prompt P1=&1
prompt p2=&2
exit

# test 1
$ sqlplus -s nthomas/nthomas @show_inputs '1','2' '3','4' P1=1,2
p2=3,4
(the single quotes are lost)

# test 2
$ sqlplus -s nthomas/nthomas @show_inputs "'1','2'" "'3','4'" P1=1
p2=,'2'
(sqlplus goes mad and breaks one unix parameter into two SQL*Plus - in a wierd way)

# test 3
$ sqlplus -s nthomas/nthomas @show_inputs \"'1','2'\" \"'3','4'\" P1=1,2
p2=3,4
(escaping the double quotes gets you back to the same as test 1 - all quotes stripped)

# test 4
$ sqlplus -s nthomas/nthomas @show_inputs "\'1\',\'2\'" "\'3\',\'4\'" P1=\'1\',\'2\'
p2=\'3\',\'4\'
(so you have everything you want - but the escape characters come through too)

# test 5
$ sqlplus -s nthomas/nthomas @show_inputs \'1\',\'2\' \'3\',\'4\' P1=1
p2=,'2'
(same as test 2)

# test 6 - do your own variable substitution in a HEREIS document:

$ sqlplus -s nthomas/nthomas <<END
> define 1="'1','2'"
> define 2="'3','4'"
> @show_inputs
> exit
> END
P1='1','2'
p2='3','4'

That should do the trick... and of course you can turn this into a general purpose shell function if you use it a lot...

Regards Nigel

--
http://www.freelists.org/webpage/oracle-l