Formatting execute immediate with to_date ORACLE

Question

Am stuck on a formatting procedure within a package..... script works ok however integrating it with a package is proving difficult!!

Am not used to oracle...I have script running but not in package...well not all of it...Drop Table worked

CREATE OR REPLACE PACKAGE BODY Testing is
    PROCEDURE DropTable1 IS         
        BEGIN
                execute immediate ('DROP TABLE mytable1');                           
        END;

    PROCEDURE PopulateTable1 IS
        BEGIN
            execute immediate ('CREATE TABLE mytable1
            AS (   
                select
                substr(t1.genarea,3,3) as M_Class,
                substr(t1.genarea,6,30) as M_Description,
                substr(t1.genarea,36,3) as M_Class,
                substr(t1.genarea,39,30) as M_Description,
                substr(t1.itemitem,1,3) as product_code,
                t3.CHANNEL_NUM as SALES_CHANNEL,
                to_date(''t2.time_id'',''dd-mon-yyyy'') as mis_date,
                sum(t2.ap_cw_cfi_irp+t2.ap_cw_issues_irp) as ap_gross,
                sum(t2.Ap_Cw_Cfi_Irp+t2.Ap_Revivals_Irp) as ap_net,
                sum(t2.sp_inc_irp+t2.sp_issues_irp) as sp_gross,
                sum(t2.sp_dec_irp+t2.sp_fs_irp) as sp_net
              from
                d_pr t1, act t2, age_map t3
              where
                t1.pfx=''IT'' and t1.coy=''1'' and t1.tabl=''T81'' and substr(t1.itemitem,1,3) = t2.product_id and t3.AGE_NUM = t2.age_id
              group by
                substr(t1.genarea,3,3),
                substr(t1.genarea,6,30),
                substr(t1.genarea,36,3),
                substr(t1.genarea,39,30),
                substr(t1.itemitem,1,3),
                t3.CHANNEL_NUM,
                to_date(''t2.time_id'',''dd-mon-yyyy'')
            )');
            COMMIT;
        END PopulateTable1;
END Testing;
/

thanks

Answer 1

You shouldn't have quotes around t2.time_id. Single quotes are used to indicate a string literal; that's clearly a column reference.