RE: dynamic sql and dates

From: Pabba, Chandra <Chandra.Pabba_at_fmr.com>
Date: Thu, 25 Jan 2007 12:22:23 -0600
Message-ID: <6D2375ACD32D9B48A9B433AA0527B084054A496E@MSGDCCCLA2WIN.DMN1.FMR.COM>

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 - 12:22:23 CST