Home » SQL & PL/SQL » Client Tools » how to pass ambersand ( & ) as input parameter to a stored procedure ? (oracle 10g)
how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480713] Tue, 26 October 2010 11:42 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Hi,

i have a stored procedure whose input parameter is a varchar2 datatype.

i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.

my problem is when there is a input string with & (ambersand) then its not working.

even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.

look at the sample code below which i wrote for testing purpose:

procedure is:

create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;


i pass parameter as given below:

begin
testproc('abc & def');
end;


if i run above script, it asks me to input some string value as it sees & in the string.

attached is the image that shows up in TOAD.

if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.

begin
testproc('abc &'||'def');
end;


thanks
jillu
  • Attachment: ambersand.jpg
    (Size: 26.59KB, Downloaded 332 times)
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480715 is a reply to message #480713] Tue, 26 October 2010 11:49 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

http://www.orafaq.com/forum/t/97816/2/
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480717 is a reply to message #480715] Tue, 26 October 2010 11:59 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
it asks me to use CHR(38). but how can TIBCO send CHR(38) instead of & ? because it doesnt know what the input parameter is going to be. and also i cant use REPLACE function to convert & to CHR(38). while calling the procedure parametr only we are having trouble.
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480718 is a reply to message #480717] Tue, 26 October 2010 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
SQL> SET DEFINE OFF
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480721 is a reply to message #480718] Tue, 26 October 2010 12:25 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i cant use set define off. its not me who is running script to call procedure.
Its TIBCO which would be calling procedure. it cannot set define off
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480722 is a reply to message #480721] Tue, 26 October 2010 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
either have client ignore the ampersand or do not include ampersand.
I do not see any other alternative.
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480724 is a reply to message #480722] Tue, 26 October 2010 13:02 Go to previous messageGo to next message
Littlefoot
Messages: 21593
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP
... and tibco would be calling my procedure

What is "tibco"?

Quote:
i tried to pass the parameter with & in TOAD, it asks me to enter value for string

This is most probably because you don't know how to properly use a tool you possess.
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480744 is a reply to message #480724] Tue, 26 October 2010 16:59 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i know how to use the tool. but i was testing it in toad with the same data as i was receiving. thats how we know whats wrong,
right ??

hey, if some application is calling procedure and passing '&', it doesnt fail. actually there was other issue with the record that i got. hence it was failing. '&' was not the issue.

thanks for your response
jillu
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480747 is a reply to message #480744] Tue, 26 October 2010 17:09 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
The tool Littlefoot was refering to is TOAD. And you obviously don't know how to get it to ignore &.

& is not a special character as far as oracle databases are concerned. It is a special character to sqlplus and some oracle GUIs like TOAD.
Re: how to pass ambersand ( & ) as input parameter to a stored procedure ? [message #480750 is a reply to message #480747] Tue, 26 October 2010 17:46 Go to previous message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
set scan off or set define off will help if i run the script in toad or sqlplus
Previous Topic: Running CodeXpert through command Line using Toad 9.0
Next Topic: Problems in queries with CLOB fields
Goto Forum:
  


Current Time: Wed Sep 23 12:16:28 CDT 2020