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