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

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 10 Jun 2003 12:19:49 -0700
Message-ID: <F001.005AE816.20030610113946@fatcity.com>

<span style='font-size:

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

<span style='font-size:

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

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>I can&#8217;t count to 4 -<font size=2 color=navy face=Wingdings><span style='font-size:10.0pt;font-family: Wingdings;color:navy'>J

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>that&#8217;s why I&#8217;m using CHR(39), see my previous message.

<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 1:44
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: How to pass string
with a ' to PL/SQL

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>This does not work when executing my stored procedure:

<span

style='font-size:12.0pt'> 

<span

style='font-size:12.0pt'>SQL> var a refcursor SQL> var b varchar2(100);
SQL> begin
  2     :b :='and sub_account_no=' || '''' || '864240103' || '''';
  3     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);

  4  end;
  5  /
   execute :a :=

pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-2100:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);            *
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'>The package body is defined as:

<span

style='font-size:12.0pt'>CREATE OR REPLACE PACKAGE pkg_reports_dynamiccti AS 

 -- Function fu_sales_analysis. Return summary information 
 -- about sales analysis for period of time for NetOne Reports from 
 -- customerinfo table. 
 -- Input: begin Date, end date, center_id, account
 -- Output: reference cursor
 FUNCTION fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN

VARCHAR2,

in_center IN VARCHAR2, in_acct IN VARCHAR2,

in_orderbyClause IN VARCHAR2,

in_whereclause IN VARCHAR2)
    RETURN pkg_cursors.ref_cursor;
 FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addressType IN NUMBER)
    RETURN pkg_cursors.ref_cursor;
END pkg_reports_dynamiccti;
/

<span

style='font-size:12.0pt'>this works:

<span

style='font-size:12.0pt'>SQL> var a refcursor SQL> var b varchar2(100);
SQL> begin
  2     :b :='and sub_account_no=' || '''' || '864240103' || '''';
  3  end;
  4  /

<span

style='font-size:12.0pt'>PL/SQL procedure successfully completed.

<span

style='font-size:12.0pt'>SQL> print b

<span

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

---

<span

style='font-size:12.0pt'>and sub_account_no='864240103'

<span

style='font-size:12.0pt'>

"Mercadante, Thomas
F" <[EMAIL PROTECTED]> wrote:

<blockquote style='border:none;border-left:solid #1010FF 1.5pt;padding:0in 0in 0in 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-bottom:5.0pt'>

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Laura,

<span

style='font-size:12.0pt'> 

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>try it this way:

<span

style='font-size:12.0pt'> 

<font size=3 color=black

face="Times New Roman">  
:b :='and sub_account_no=' || '''' || '864240103' || '''';

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>remember - 4 quotes gets you one.

<span style='font-size:

10.0pt;font-family:Arial'>Tom Mercadante Oracle
Certified Professional

<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'>-----Original
Message-----
From: laura pena
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 1:25
PM
To: Multiple recipients of list
ORACLE-L
Subject: How to pass string with a
' to PL/SQL

<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).

<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 - 14:19:49 CDT