Sorry about that. Looking at your example more closely it seems like you may
be 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
>
> 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-----
> 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" <[EMAIL PROTECTED]> 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 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).
