Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: dynamic sql and dates

Re: dynamic sql and dates

From: LS Cheng <exriscer_at_gmail.com>
Date: Thu, 25 Jan 2007 20:20:22 +0100
Message-ID: <6e9345580701251120g34578232id5991a26a435c22f@mail.gmail.com>


Yea I noticed quotes were missing. Jesus Christ I explicitly used dbms_output to debug the code and could not get it right! :-P

Thanks all

--
LSC

On 1/25/07, Pabba, Chandra <Chandra.Pabba_at_fmr.com> wrote:

>
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 13:20:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US