RE: dynamic sql and dates

Hi Cheng,
 
I am not sure if I can explain this behavior, but I do have a work
around.  You just need to have a single-tick for l_date in the dynamic
sql.
 
create or replace procedure ins_date
is
   l_date  date := sysdate;
   l_sql   varchar2(1000); 
begin
   l_sql :=        'insert into kk ' || chr(10) ||
                    'select '''|| l_date ||''' from dual';
   dbms_output.put_line(l_sql);
   dbms_output.put_line(sysdate);
execute immediate l_sql;
end;
/

I think, what is happening here is when you are assigning the sysdate
value to l_date, it is getting assigned the literal value, as you will
notice in your output (select 20070125 17:11:47 from dual ). But when
you use sysdate it is not getting translated into a literal value and it
is just inserting the SYSDATE.  But, one caveat, if you use the above
approach, you will lose the time factor in the date, it will be some
thing like 25-01-07 00:00:00.  If you use the SYSDATE, it will have the
time component as well some thing like - 25-01-07 11:52:56.
 
HTH
 

Thanks 
Chandra Pabba 
972-584-2289 

 


  _____  

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LS Cheng
        Sent: Thursday, January 25, 2007 10:13 AM
        To: oracle-l
        Subject: dynamic sql and dates
        
        
        Hi
        
        I am using dynamic sql in a stored procedure which inserts dates
into a table.
        
        The procedure is as follows:
        
        create or replace procedure ins_date
        is
           l_date  date := sysdate;
           l_sql   varchar2(1000); 
        begin
           l_sql :=        'insert into kk ' || chr(10) ||
                            'select '|| l_date ||' from dual';
           dbms_output.put_line(l_sql);
           execute immediate l_sql;
        end;
        /
        
        kk is a table with a column (datatype date)
        
        whenever I execute I get this error
        
        SQL> exec ins_date
        insert into kk 
        select 20070125 17:11:47 from dual
        BEGIN ins_date; END;
        
        *
        ERROR at line 1: 
        ORA-00923: FROM keyword not found where expected
        ORA-06512: at "LSC.INS_DATE", line 9
        ORA-06512: at line 1
        
        If I change the procedure to 
        
        create or replace procedure ins_date
        is
           l_date  date := sysdate; 
           l_sql   varchar2(1000);
        begin
           l_sql :=        'insert into kk ' || chr(10) ||
                            'select sysdate from dual';
           dbms_output.put_line(l_sql);
           execute immediate l_sql; 
        end;
        /
        
        it runs perfectly
        
        exec ins_date
        insert into kk 
        select sysdate from dual
        
        PL/SQL procedure successfully completed.
        
        Anyone know what can be wrong?
        
        TIA
        
        
        --
        LSC

Other related posts: