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

[Mercadante, Thomas F]

Lizz,

 

Remove the 'execute' from your PL/SQL block.  it is not needed - in fact - it is causing the error.

 

"Execute" is only needed within sqlplus when not using a PL/SQL block.  You are using an anonymous PL/SQL block to call your procedure.

 

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: laura pena [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 5:15 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to pass string with a ' to PL/SQL

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.

 

Thanks ....

-Lizz

 

"Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:

Show us the package body ... not the spec.

 

Raj

--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

-----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'

 

*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1

---

Do you Yahoo!?
Free online calendar with sync to Outlook(TM).