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

Igor Neyman Tue, 10 Jun 2003 12:19:49 -0700

Laura,

I can’t count to 4 -J

that’s why I’m using CHR(39), see my previous message.

Igor Neyman, OCP DBA

[EMAIL PROTECTED]

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

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

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

Reply via email to