Re: Passing String Arguments to SQL*Plus from a Unix Shell
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: uwe@xxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Mar 2007 04:30:18 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
