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

[laura pena]

This does not work when executing my stored procedure:

 

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.

 

The package body is defined as:

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;
/

this works:

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

PL/SQL procedure successfully completed.

SQL> print b

B
-------------------------------------------------------------------------

and sub_account_no='864240103'

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

Laura,

 

try it this way:

 

   :b :='and sub_account_no=' || '''' || '864240103' || '''';

remember - 4 quotes gets you one.

Tom Mercadante
Oracle Certified Professional

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

I have procedure that needs to pass a string with a ' .

Anyone have any idea how to do this in PL/SQL

 

Here is my example:

 

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;
/

This is giving me an error.

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.

 

Thanks in advance.

 

-Lizz

---

Do you Yahoo!?
Free online calendar with sync to Outlook(TM).

---

Do you Yahoo!?
Free online calendar with sync to Outlook(TM).