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

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Tue, 10 Jun 2003 11:09:35 -0700
Message-ID: <F001.005AE74B.20030610104420@fatcity.com>

This does not work when executing my stored procedure:
 

SQL> var a refcursorSQL> 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::= . ( @ % ; immediateThe 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 refcursorSQL> 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 PMTo: Multiple recipients of list ORACLE-LSubject: 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 refcursorvar 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::= . ( @ % ; immediateThe 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). Received on Tue Jun 10 2003 - 13:09:35 CDT