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

You rock !!!
 
Should have reviews my code closer!!
Thanks.Jacques Kilchoer <[EMAIL PROTECTED]> wrote: Sorry about that. Looking at your example more closely it seems like you maybe doing it right.In your package body I see this:IF ( in_whereclause IS NOT NULL ) THEN v_sqlbeg := v_sqlbeg || in_orderbyclause ||' '; END IF;shouldn't that be "in_whereclause" in the second line?> -----Original Message-----> From: Jacques Kilchoer > Sent: mardi, 10. juin 2003 14:55> To: [EMAIL PROTECTED]> Cc: [EMAIL PROTECTED]> Subject: RE: How to pass string with a ' to PL/SQL> > > 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&gtt; > 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 twoo 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-----> From: laura pena [mailto:[EMAIL PROTECTED]> > 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" wrote:> Show us the package body ... not the spec.> > -----Original Message-----> From: laura pena [mailto:[EMAIL PROTECTED]> Sent: Tuesday, June 10, 2003 2: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 Reporrts 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 KilchoerINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free online calendar with sync to Outlook(TM).