RE: How to pass string with a ' to PL/SQL

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 10 Jun 2003 10:38:16 -0700
Message-ID: <F001.005AE6A3.20030610101959@fatcity.com>

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>var a refcursor var b varchar2(100);
begin
   :b :='and sub_account_no=' || CHR(39) || '864240103&#8217; || CHR(39);
   execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);
end;
/

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>39 is ASCII code for &#8216;.

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span

style='font-size:10.0pt;color:navy'>Igor Neyman, OCP DBA

<span

 style='font-size:10.0pt;color:navy'>[EMAIL PROTECTED]

<span

style='font-size:10.0pt;color:navy'> 

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span

style='font-size:10.0pt;font-family:Tahoma'>-----Original Message----- From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of laura pena
Sent: Tuesday, June
 10, 2003<span style='font-size:10.0pt;
font-family:Tahoma'> <span
 style='font-size:10.0pt;font-family:Tahoma'>12:25 PM<font size=2 face=Tahoma>
To: Multiple recipients of list
ORACLE-L
Subject: How to pass string with a
' to PL/SQL

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>I have procedure that needs to pass a string with a ' .

<span

style='font-size:12.0pt'>Anyone have any idea how to do this in PL/SQL

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>Here is my example:

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>var a refcursor var b varchar2(100);
begin
   :b :='and sub_account_no=''864240103''';    execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);
end;
/

<span

style='font-size:12.0pt'>This is giving me an error.

<span

style='font-size:12.0pt'>ERROR at line 3:

ORA-06550: line 3, column 12:
PLS-00103: Encountered the symbol "" when expecting one of the
following:

:= . ( @ % ; immediate
The symbol ";" was substituted for "" to continue.

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>Thanks in advance.

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>-Lizz

<font

size=3 face="Times New Roman">

<span

style='font-size:12.0pt'>Do you Yahoo!?
Free online
calendar with sync to Outlook(TM). Received on Tue Jun 10 2003 - 12:38:16 CDT