passing string with &

I have got into this silly problem.

I am trying to pass a VARCHAR2 string to a procedure with & and ; in it. i.e. I have to pass : ‘send & now’ to a procedure but now able to do it. I tried with single quote, double quote, \ Escape but nothing solved the problem.

Test Code:
to_pro is having one input string which get printed on screen
I am called to_pro from a procedure from_pro
See what happens when I execute

SQL> create or replace procedure to_pro(now_string varchar2) as
2 begin
3 DBMS_OUTPUT.PUT_LINE(now_string);
4 end;
5 /
Procedure created.

SQL> create or replace procedure from_pro as
2 begin
3 to_pro('send & now');
4 end;
5 /
Enter value for amp:
old 3: to_pro('send & now');
new 3: to_pro('send ; now');
Procedure created.

-- i pressed enter key so &amp is replaced by null


1 create or replace procedure from_pro as
2 begin
3 to_pro('send "&"amp";" now');
4* end;
SQL> /

Procedure created.

SQL> exec from_pro
send "&"amp";" now

PL/SQL procedure successfully completed.

here it doesn’t give any compilation error but it pass double quote - " also

Single quote is also not working

i tried putting \& and \Escape but it pass string with \ and Escape key word. What to do?
Sahil Patel

When I use & in a string I normally convert it to ascii values as follows:


to_pro('send '|| chr(38) || ' now')

that's just a sql*plus default setting. use "set define off" and ampersand is just another character. no need for chr(38)'s