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
