Re: dynamic sql and dates

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 25 Jan 2007 11:53:44 -0700
Message-Id: <20070125185244.143535B476B@turing.freelists.org>

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

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