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

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Tue, 10 Jun 2003 13:53:55 -0700
Message-ID: <F001.005AE944.20030610131453@fatcity.com>

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 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to pass string with a ' to PL/SQL This does not work when executing my stored procedure:
SQL> var a refcursorSQL> 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::= . ( @ % ; immediateThe 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 refcursorSQL> 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).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_addresses. Return summary information
about sales analysis for period of time for NetOne Reports from
address table.
Input: customerinfoid, addressType
Output: reference cursor FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addressType IN NUMBER) RETURN pkg_cursors.ref_cursor;
Function fu_sales_analysis_wtn. Return summary information
about sales analysis for period of time for NetOne Reports from
wtn table.
Input: customerinfoid
Output: reference cursor FUNCTION fu_sales_analysis_wtn (in_customerinfoid IN NUMBER) RETURN pkg_cursors.ref_cursor; END pkg_reports_dynamiccti; /

CREATE OR REPLACE PACKAGE BODY pkg_reports_dynamiccti IS 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 IS rt_custinfo pkg_cursors.ref_cursor;

 v_sqlstatement VARCHAR2(5000);
 v_sqlbeg VARCHAR2(2000);
 v_sqlend VARCHAR2(2000);

BEGIN
v_sqlbeg := 'SELECT TO_CHAR(CALLDATE,''MM/DD/YYYY'') as p_date, '||

       'TO_CHAR(CALLDATE,''MM/DD/YYYY HH24:MI'') as d_date, '|| 
       'DECODE(OPERATORID,NULL,'' '',OPERATORID) as dxc_rep_id, '||
       'CASE WHEN VERIFIED = ''Y'' THEN ''good sale'' ELSE ''no sale'' END as 
status_txt, '|| 
       'SALESPERSONID as tsr_id, SALESPERSONNAME as tsr_name, '|| 
       'DECODE(SPARE6,NULL,'' '',SPARE6) as call_card, '||
       'DECODE(SPARE7,NULL,'' '',SPARE7) as center_id, '|| 
       'DECODE(SPARE11,NULL,'' '',SPARE11) as campaign, '||
       'DECODE(SPARE3,NULL,'' '',SPARE3) as serv_code, '|| 
       'CASE WHEN BTN IS NULL THEN ''0000000000'' ELSE BTN END as btn, '||
       'DECODE(FULLNAME,'' '',FULLNAME) as auth_name, '||
       'CUSTOMERINFOID as custinfoid, '||
       'CASE WHEN fu_getntid(CALL_ID) = ''NF'' THEN ''NOT FOUND'' ELSE 
fu_getntid(CALL_ID) END as ver_code '|| 
       'FROM CUSTOMERINFO '||
       'WHERE CALLDATE between TO_DATE(:a,''YYYY-MM-DD HH24:MI:SS'') AND '||
       ' TO_DATE(:b ,''YYYY-MM-DD HH24:MI:SS'') ';
   IF ( in_whereclause IS NOT NULL ) THEN 
      v_sqlbeg := v_sqlbeg || in_orderbyclause ||' ';

END IF;
v_sqlend :=

       'AND SPARE7 = :c AND ACCOUNT_NO = :d '||
       'AND CALL_ID <> ''0'' OR CALL_ID IN ( ' ||
       ' SELECT CALL_ID FROM AUDIT_TABLE WHERE EVENT_TYPE=3 '||
       '  AND EVENT_ID between TO_DATE(:e,''YYYY-MM-DD HH24:MI:SS'') AND '||
       '  TO_DATE(:f,''YYYY-MM-DD HH24:MI:SS'')  AND CALL_ID IN ( '|| 
       '   SELECT CALL_ID FROM CUSTOMERINFO WHERE '||
       '    CALLDATE BETWEEN TO_DATE(:g,''YYYY-MM-DD HH24:MI:SS'') AND '||
       '     TO_DATE(:h,''YYYY-MM-DD HH24:MI:SS'') AND SPARE7=:i AND '||
       '     ACCOUNT_NO=:j AND CALL_ID <> ''0'') '||
       ')';
   IF ( in_orderbyclause IS NOT NULL ) THEN 
      v_sqlend := v_sqlend || in_orderbyclause ||' ';

END IF;
v_sqlstatement := v_sqlbeg || v_sqlend;

OPEN rt_custinfo FOR v_sqlstatement USING in_begdate,in_enddate,in_center,in_acct, in_begdate,in_enddate,in_begdate,in_enddate, in_center,in_acct; RETURN rt_custinfo;

EXCEPTION
WHEN OTHERS THEN return rt_custinfo; END fu_sales_analysis;

FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addressType IN NUMBER)
RETURN pkg_cursors.ref_cursor IS rt_addressinfo pkg_cursors.ref_cursor; v_sqlstatement VARCHAR2(3000);
BEGIN
v_sqlstatement := 'SELECT FULLNAME as bill_name, ' ||

      'ADDRESS1 ||'' '' || ADDRESS2 as street, '|| 
      'DECODE(CITY,NULL,'' '',CITY) as city, '||
      'DECODE(POSTALCODE,NULL,'' '',POSTALCODE) as zip, '||
      'DECODE(REGION,null,'' '',REGION) as state FROM ADDRESSES '||
      'WHERE CUSTOMERINFOID = :a AND ADDRESSTYPE= :b ';
   OPEN rt_addressinfo FOR v_sqlstatement USING in_customerinfoid, in_addressType;

RETURN rt_addressinfo;

EXCEPTION
WHEN OTHERS THEN return rt_addressinfo; END fu_sales_analysis_address;

FUNCTION fu_sales_analysis_wtn (in_customerinfoid IN NUMBER) RETURN pkg_cursors.ref_cursor IS rt_wtn pkg_cursors.ref_cursor; v_sqlstatement VARCHAR2(500);
BEGIN
v_sqlstatement := 'SELECT DECODE(wtn,''0000000000'','' '',WTN) AS WTN ' ||

      'FROM WTNS WHERE CUSTOMERINFOID= :a';
   OPEN rt_wtn FOR v_sqlstatement USING in_customerinfoid;

RETURN rt_wtn;

EXCEPTION
WHEN OTHERS THEN return rt_wtn;
END fu_sales_analysis_wtn;

END pkg_reports_dynamiccti;
/

Sampele test runs var a refcursor set serveroutput on size 30000

execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103',null,null); 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',null); 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',null);

var a refcursor
var b varchar2(100);
begin

:b := 'and sub_account_no=' || CHR(39) || '864240103’ || CHR(39); 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); end;
/

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','and sub_account_no=''864240103'' ');

execute :a := pkg_reports_Dynamiccti.fu_sales_analysis_address(114,2); execute :a := pkg_reports_Dynamiccti.fu_sales_analysis_wtn(600); --print a Received on Tue Jun 10 2003 - 15:53:55 CDT