Calling PL/SQL from Unix Shell

[ajayarora]

Hi,

I'm calling a PL/SQL procedure from a unix shell script. Shell gets its params from a file and passes them (enclosing the params in single quotes) to PL/SQL.It works fine for most of the cases but whenever a parameter contains single quote(') in it, procedure fails with the error message :
'quoted string not properly terminated'

Can anyone suggest anything to overcome this problem.

Regards
Ajay.

 

[jad]

put the plsql script into a file and @<file> at the sqlplus prompt, or on the sqlplus command-line

 

[sampsonr]

If jad's sugesstion doesn't work for you, try sending every argument through sed before passing it to SQLPlus, placing a backslash before any embedded single-quotes, as in:

my_argument=`echo $my_argument | sed &quot;s/'/\\'/g&quot;`

This way, the SQLPlus command will pass the variables intact.

 

[ajayarora]

Hi Sampsonr,

I'm sending two single quotes using sed like

my_argument=`echo $my_argument | sed &quot;s/'/\'\'/g&quot;`

Its working fine now. Thanks for your suggestion and time.

Regards
Ajay.