DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003

    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

  2. #2
    Join Date
    Aug 2000
    Straham NH
    When I use & in a string I normally convert it to ascii values as follows:

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

  3. #3
    Join Date
    Aug 2002
    Colorado Springs
    that's just a sql*plus default setting. use "set define off" and ampersand is just another character. no need for chr(38)'s
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width