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

When you have a problem like this, assign the string to a variable and the print it to the screen using DBMS_Output. That will show you the exact SQL that is being submitted. — David Aldridge, Jun 30 '15 at 10:49

score 2 · Accepted Answer · answered Jun 29 '15 at 15:48

2

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

answered Jun 29 '15 at 15:48

Allan

17,141
4
52
69

score 2 · Answer 2 · edited May 23 '17 at 11:58

There are two things in your code which seems odd:

to_date(''t2.time_id'',''dd-mon-yyyy'')

You don't need any single quotation-marks around the column, you only need single-quotation marks around a string literal. Change it to:

to_date(t2.time_id,''dd-mon-yyyy'')

If you are struggling with single quotation marks at multiple places, then I would suggest Quoting string literal technique to avoid errors due to single-quotation marks in the string. For example, see https://stackoverflow.com/a/27373394/3989608

COMMIT;

You don't need COMMIT for DDL statements, you need commit only for DML statements. Remove it.

Jeet · Answer 3 · 2015-06-29T16:13:10.810

0

BEGIN
execute immediate ('CREATE TABLE mytable1 AS 
(select to_date(/*remove single quotes here*/t2.time_id/*and here*/,''dd-mon-yyyy'') as mis_date 
from (select current_date time_id from dual) t2)');
end;

And in the last line of the select query. Because it is getting treated as string

edited Jun 29 '15 at 16:13

answered Jun 29 '15 at 16:05

Jeet

1,006
1
14
25

Formatting execute immediate with to_date ORACLE

3 Answers3