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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 10 Jun 2003 16:17:13 -0700
Message-ID: <F001.005AEB11.20030610154005@fatcity.com>

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

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

Received on Tue Jun 10 2003 - 18:17:13 CDT