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