Home » SQL & PL/SQL » SQL & PL/SQL » sql passing arguments special characters problem (Oracle 9)
sql passing arguments special characters problem [message #442082] Thu, 04 February 2010 11:25 Go to next message
amicon007
Messages: 3
Registered: February 2010
Junior Member
I need to pass a string variable to my.sql file. The string can contain any of the special characeters

Dummy my.sql:
select 'abc' from dual where 'name' ='&1'

If the string contains the scan character ('&' in this case), it prompts for another value to substitute (bar), which is not expected.
I can't change the scan character as the string can contain any of them:

SQL> @ my.sql "foo&bar";
Enter value for bar:
old 1: select 'abc' from dual where 'name' ='&1'
new 1: select 'abc' from dual where 'name' ='foo'

no rows selected

P.S. This works fine when user passes the string manually:
SQL> @ my.sql;
Enter value for 1: foo&bar
old 1: select 'abc' from dual where 'name' ='&1'
new 1: select 'abc' from dual where 'name' ='foo&bar'


I will be executing my.sql through unix shell script calling sqlplus. Thanks in advance.

Re: sql passing arguments special characters problem [message #442088 is a reply to message #442082] Thu, 04 February 2010 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
SQL> SET DEFINE OFF
Re: sql passing arguments special characters problem [message #442089 is a reply to message #442082] Thu, 04 February 2010 11:37 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you are relying on & in your script then you have no option other than to make sure you don't pass a string containing &.
Re: sql passing arguments special characters problem [message #442099 is a reply to message #442089] Thu, 04 February 2010 12:12 Go to previous messageGo to next message
amicon007
Messages: 3
Registered: February 2010
Junior Member
Quote:
SET DEFINE OFF

-- wont substitute the argument then.

Quote:
If you are relying on & in your script then you have no option other than to make sure you don't pass a string containing &.


So, I have to strip/replace &'s from the string before passing it to sql. Is that true??
Re: sql passing arguments special characters problem [message #442100 is a reply to message #442082] Thu, 04 February 2010 12:16 Go to previous messageGo to next message
amicon007
Messages: 3
Registered: February 2010
Junior Member
But this way, the stripped out string won't match the database if I query it.
Re: sql passing arguments special characters problem [message #442102 is a reply to message #442099] Thu, 04 February 2010 12:35 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>wont substitute the argument then.

A workaround would be the following

http://en.wikipedia.org/wiki/Here_script

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Pls Help!
Next Topic: Inserting a blank line to a clob
Goto Forum:
  


Current Time: Mon Sep 26 04:19:54 CDT 2016

Total time taken to generate the page: 0.24338 seconds