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

The means of quoting doesn't seem right for the error because it is complaining about not finding anything if I understand what the "" in the error message means. Therefore, as an off the wall idea try changing :b to b and see what happens.

             Pat

laura pena wrote:
>
> --0-724909865-1055276223=:79494
> Content-Type: multipart/alternative; boundary="0-1690356993-1055276223=:79494"
>
> --0-1690356993-1055276223=:79494
> Content-Type: text/plain; charset=us-ascii
>
> 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-----
> 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'
>
>
> CREATE OR REPLACE PACKAGE pkg_reports_dynamiccti AS=09
> -- Function fu_sales_analysis. Return summary information=20
> -- about sales analysis for period of time for NetOne Reports from=20
> -- customerinfo table.=20
> -- Input: begin Date, end date, center_id, account
> -- Output: reference cursor
> FUNCTION fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN VARCHA=
> R2,=20
> in_center IN VARCHAR2, in_acct IN VARCHAR2,
> in_orderbyClause IN VARCHAR2,=20
> in_whereclause IN VARCHAR2)
> RETURN pkg_cursors.ref_cursor;
>
> -- Function fu_sales_analysis_addresses. Return summary information=20
> -- about sales analysis for period of time for NetOne Reports from=20
> -- address table.=20
> -- Input: customerinfoid, addressType=20
> -- Output: reference cursor
> FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addr=
> essType IN NUMBER)
> RETURN pkg_cursors.ref_cursor;
>
> -- Function fu_sales_analysis_wtn. Return summary information=20
> -- about sales analysis for period of time for NetOne Reports from=20
> -- wtn table.=20
> -- Input: customerinfoid=20
> -- 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=20
> FUNCTION fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN VARCHA=
> R2,=20
> in_center IN VARCHAR2, in_acct IN VARCHAR2,
> in_orderbyClause IN VARCHAR2,=20
> 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 :=3D 'SELECT TO_CHAR(CALLDATE,''MM/DD/YYYY'') as p_date, '||
> 'TO_CHAR(CALLDATE,''MM/DD/YYYY HH24:MI'') as d_date, '||=20
> 'DECODE(OPERATORID,NULL,'' '',OPERATORID) as dxc_rep_id, '||
> 'CASE WHEN VERIFIED =3D ''Y'' THEN ''good sale'' ELSE ''no sale'' =
> END as status_txt, '||=20
> 'SALESPERSONID as tsr_id, SALESPERSONNAME as tsr_name, '||=20
> 'DECODE(SPARE6,NULL,'' '',SPARE6) as call_card, '||
> 'DECODE(SPARE7,NULL,'' '',SPARE7) as center_id, '||=20
> 'DECODE(SPARE11,NULL,'' '',SPARE11) as campaign, '||
> 'DECODE(SPARE3,NULL,'' '',SPARE3) as serv_code, '||=20
> '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) =3D ''NF'' THEN ''NOT FOUND'' ELSE =
> fu_getntid(CALL_ID) END as ver_code '||=20
> '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=20
> v_sqlbeg :=3D v_sqlbeg || in_orderbyclause ||' ';=20
> END IF;
> v_sqlend :=3D
> 'AND SPARE7 =3D :c AND ACCOUNT_NO =3D :d '||
> 'AND CALL_ID <> ''0'' OR CALL_ID IN ( ' ||
> ' SELECT CALL_ID FROM AUDIT_TABLE WHERE EVENT_TYPE=3D3 '||
> ' 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 ( '||=20
> ' 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=3D:i AND '|=
> |
> ' ACCOUNT_NO=3D:j AND CALL_ID <> ''0'') '||
> ')';
> IF ( in_orderbyclause IS NOT NULL ) THEN=20
> v_sqlend :=3D v_sqlend || in_orderbyclause ||' ';=20
> END IF;
> v_sqlstatement :=3D v_sqlbeg || v_sqlend;=20
> =20
> OPEN rt_custinfo FOR v_sqlstatement USING in_begdate,in_enddate,in_cent=
> er,in_acct, in_begdate,in_enddate,in_begdate,in_enddate, in_center,in_acc=
> t; =20
> RETURN rt_custinfo;
>
> EXCEPTION=20
> WHEN OTHERS THEN return rt_custinfo;
> END fu_sales_analysis;
>
>
> FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addr=
> essType IN NUMBER)
> RETURN pkg_cursors.ref_cursor IS rt_addressinfo pkg_cursors.ref_cursor;
> v_sqlstatement VARCHAR2(3000);
> BEGIN
> v_sqlstatement :=3D 'SELECT FULLNAME as bill_name, ' ||
> 'ADDRESS1 ||'' '' || ADDRESS2 as street, '||=20
> 'DECODE(CITY,NULL,'' '',CITY) as city, '||
> 'DECODE(POSTALCODE,NULL,'' '',POSTALCODE) as zip, '||
> 'DECODE(REGION,null,'' '',REGION) as state FROM ADDRESSES '||
> 'WHERE CUSTOMERINFOID =3D :a AND ADDRESSTYPE=3D :b ';
> OPEN rt_addressinfo FOR v_sqlstatement USING in_customerinfoid, in_add=
> ressType; =20
> RETURN rt_addressinfo;
>
> EXCEPTION=20
> 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=20
> v_sqlstatement :=3D 'SELECT DECODE(wtn,''0000000000'','' '',WTN) AS WT=
> N ' ||
> 'FROM WTNS WHERE CUSTOMERINFOID=3D :a';
> OPEN rt_wtn FOR v_sqlstatement USING in_customerinfoid; =20
> RETURN rt_wtn;
>
> EXCEPTION=20
> 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 :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
> 0:00','2003-06-02 00:00:00','20','864240103',null,null);
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
> 0:00','2003-06-02 00:00:00','20','864240103','order by calldate',null);
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
> 0:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',nul=
> l);
>
>
> var a refcursor
> var b varchar2(100);
> begin
> :b :=3D 'and sub_account_no=3D' || CHR(39) || '864240103=92 || CHR(39)=
> ;
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 0=
> 0:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',=
> :b);
> end;
> /
>
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:0=
> 0:00','2003-06-02 00:00:00','20','864240103','order by calldate desc','an=
> d sub_account_no=3D''864240103'' ');
>
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis_address(114,2);
> execute :a :=3D pkg_reports_Dynamiccti.fu_sales_analysis_wtn(600);
> --print a
>
> --0-724909865-1055276223=:79494--
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pat Hildebrand
  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).