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 Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
-- //www.freelists.org/webpage/oracle-l
