Re: dynamic sql and dates

Violently shaking my head in disbelief. Even if you don't know already, a quick trip to the Oracle documentation gives the right solution:


create or replace procedure ins_date
is
   l_date  date := sysdate;
   l_number number := 99;
   l_sql   varchar2(1000);
begin
   l_sql := 'insert into kk select :dt from dual';
   dbms_output.put_line(l_sql);
   dbms_output.put_line(sysdate);
   execute immediate l_sql using l_date;
end;
/


At 09:12 AM 1/25/2007, LS Cheng wrote:
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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l


Other related posts: