From Jacques.Kilchoer@quest.com Tue, 10 Jun 2003 16:17:13 -0700 From: Jacques Kilchoer Date: Tue, 10 Jun 2003 16:17:13 -0700 Subject: RE: How to pass string with a ' to PL/SQL Message-ID: MIME-Version: 1.0 Content-Type: text/plain I think the problem is that once you pass the string to a PL/SQL procedure, and you want to use it in dynamic SQL, you have to "escape" the single quote again. Perhaps the example below will help. SQL> select * from emp ; ID LAST_NAME FIRST_NAME --------- ------------------------------ ------------------------------ 1 MILLER ARTHUR 2 O'NEILL EUGENE SQL> create function get_id (in_where_clause varchar2) return number 2 is 3 the_id number ; 4 begin 5 execute immediate 'select id from emp where ' || in_where_clause 6 into the_id ; 7 return the_id ; 8 end ; 9 / Fonction créée. SQL> variable return_id number SQL> -- in this example, I want the function to receive the string SQL> -- (last_name = 'MILLER') so I surround it with single quotes SQL> -- and change each of the single quotes to two single quotes SQL> execute :return_id := get_id ('last_name = ''MILLER''') Procédure PL/SQL terminée avec succès. SQL> print RETURN_ID --------- 1 SQL> -- in this example, I want the function to receive the string SQL> -- (last_name = 'O''NEILL') so I surround it with single quotes SQL> -- and change each of the single quotes to two single quotes SQL> execute :return_id := get_id ('last_name = ''O''''NEILL''') Procédure PL/SQL terminée avec succès. SQL> print RETURN_ID --------- 2 SQL> -----Original Message----- Here you go... So far I have not gotten it to work... I have tried all suggestions so far. Seems to work if I just do it to a variable but once I call in the stored proc. It fails to work. -----Original Message----- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: Show us the package body ... not the spec. -----Original Message----- Sent: Tuesday, June 10, 2003 2:44 PM To: Multiple recipients of list ORACLE-L 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' -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).