dynamic sql and dates

From: LS Cheng <exriscer_at_gmail.com>
Date: Thu, 25 Jan 2007 17:12:53 +0100
Message-ID: <6e9345580701250812q4b5b3d6emf97763a62535a62c@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 25 2007 - 10:12:53 CST