Home » Infrastructure » Unix » Passing String Arguments to SQL*Plus from a Unix Shell
Passing String Arguments to SQL*Plus from a Unix Shell [message #227569] Wed, 28 March 2007 12:16 Go to next message
ukuechle
Messages: 9
Registered: June 2005
Junior Member
Hi all,

I've scanned the FAQ and Forums but haven't found a satisfying answer so far:
I need to pass arguments containing the SQL string delimiter "'" to sqlplus from a shell script, e.g.:

sqlplus user/pass @my.sql $1


where $1 should be like "'1','2','3'", so that it can be resolved to

...
WHERE mycol NOT IN ( '1','2','3' );


I know how to pass arguments/parameters without the "'", but when I use "'" it gets erased by the shell. I've tried to escape the "'" but with no success so far.
It looks so simple but I can't find a solution. *help*

Regards,
Uwe
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227575 is a reply to message #227569] Wed, 28 March 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I've tried to escape the "'" but with no success so far.
Keep trying & it can be made to work.
However, IMO, you are using the wrong tool for the job.
If it were the "right tool" you would not be having such problems.
You won't like this, but I'd use PERL & avoid all the pain.
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227619 is a reply to message #227575] Wed, 28 March 2007 15:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can always avoid the pesky quotes until runtime. Substitute them with some other "non-special" character (that also won't occur in your data).

dev>>cat t.sql                           
set echo on
set verify on
select replace('&1', '^', '''') from dual;

dev>>export X=^1^,^2^,^3^                
dev>>sqlplus tst/tst1@dev1 @t.sql $X

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Mar 28 13:51:19 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> set verify on
SQL> select replace('&1', '^', '''') from dual;
old   1: select replace('&1', '^', '''') from dual
new   1: select replace('^1^,^2^,^3^', '^', '''') from dual

REPLACE('^1
-----------
'1','2','3'

SQL> 

Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227730 is a reply to message #227619] Thu, 29 March 2007 03:44 Go to previous messageGo to next message
ukuechle
Messages: 9
Registered: June 2005
Junior Member
Thanks for your suggestion, andrew.
Though your example works, it is not fully suitable to my problem: I do not select with a possibility to replace the strings, instead I want to modify the where clause in an existing statement.

It works fine as long as I am not in need of quote characters:

define inlist=&1
...
SELECT something FROM mytable
 WHERE something NOT IN ( &inlist );


Now I could pass "1,2,3" as an argument but not "'1','2','3'". This is the crucial point.

Regards,
Uwe
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227756 is a reply to message #227569] Thu, 29 March 2007 05:15 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Why dont you modifie the inparamter with *nix shell scripting
before sending it to sqlplus ?
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227762 is a reply to message #227756] Thu, 29 March 2007 05:49 Go to previous messageGo to next message
ukuechle
Messages: 9
Registered: June 2005
Junior Member
Quote:
Why dont you modifie the inparamter


Like... ?
Please be a little more helpful. It's nice to see you know an answer, but I'd like to be enlightened, too.

Regards,
Uwe
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227828 is a reply to message #227762] Thu, 29 March 2007 10:04 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Just an simple example to clarify

vi test.sh
#!/bin/sh
echo 'enter input:'
read inpar

l_var=`echo ${inpar} | sed "s/'//g" `
echo 'new value:' $l_var


run test.sh

[/] . test.sh
enter input:
"1,2,3"
new value: "1,2,3"

[/] . test.sh
enter input:
"'1','2','3'"
new value: "1,2,3"


So if you put in either "1,2,3" or "'1','2','3'"
you get the same result back, and then pass it on
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227832 is a reply to message #227828] Thu, 29 March 2007 11:11 Go to previous messageGo to next message
ukuechle
Messages: 9
Registered: June 2005
Junior Member
tahpush,

thanks for the example. Unfortunately, SQL*Plus filters quotes as well, which leads to the now passed characters to be stripped anyway.

But I was provided with another idea to split up the script in a definition part and an execution part and do the definition within the shell script like this:
$ cat test.sql
select sysdate from dual
 where dummy not in( &list1 );
exit

$ sqlplus -s user/pass <<END
> define list1="'1','2'"
> @test
> exit
> END

SQL> SQL> old   2:  where dummy not in( &list1)
new   2:  where dummy not in( '1','2' )

Kudos to Nigel Thomas!
Regards,
--==/ Uwe \==--
Re: Passing String Arguments to SQL*Plus from a Unix Shell [message #227841 is a reply to message #227832] Thu, 29 March 2007 13:24 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
just escape the " and ' at Unix level.

dev>>cat t1.sql                             
set echo on
set verify on
select * from ABC where col1 in (&1);

dev>>export X=\"\'1\',\'2\',\'3\'\" 
dev>>echo $X                                
"'1','2','3'"
dev>>sqlplus tst/tst1@dev1 @t1.sql $X  

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Mar 29 11:20:39 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> set verify on
SQL> select * from ABC where col1 in (&1);
old   1: select * from ABC where col1 in (&1)
new   1: select * from ABC where col1 in ('1','2','3')

      COL1 COL2
---------- ----------
         1 hello
         2 hello2
         3 hello3

SQL> 


Previous Topic: how to give runtime input to oracle stored proc with in Shell scripts
Next Topic: ORA-01031 for Oracle User
Goto Forum:
  


Current Time: Thu Mar 28 03:46:27 CDT 2024