Re: execute immediate 'insert into ...'

Joe,

Here are a few suggestion:

1. Where-ever possible you should use bind variables so you don't shoot your 
shared pool to pieces, so do it like this:

execute immediate 'insert into test values (:1, :2, :3)' USING 2, 'joe', TO_DATE
('07-JUN-2005','DD-MON-YYYY')


2.  To simplify your SQL and improve error handling, always put your dymanic 
SQL into a variable, then run the variable:

DECLARE
  w_sql  VARCHAR2(100) := 'insert into test values (:1, :2, :3)';
BEGIN
  execute immediate w_sql USING 2, 'joe', TO_DATE('07-JUN-2005','DD-MON-YYYY');
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error running sql: '||w_sql);
    RAISE;
END;


Scott Hutchinson
Interact Analysis Ltd.


Quoting Joe Smith <joe_dba@xxxxxxxxxxx>:

> I am trying to insert records into a table through execute immediate.  The 
> number datatypes go in with no problem.  It is the varchar2 and date formats
> 
> that are giving me a problem.
> 
> 
> examples:
> 
> desc tables test
> col1 number,
> col2 varchar2(30)
> col3 date
> 
> execute immediate 'insert into test values ('|| 2 ||', '|| joe ||', '|| 
> 07-JUN-05 ||')';
> 
> 
> I have tried different ways to format the varchar2 and date datatype, but 
> keeps giving me an error.
> 
> How do I format these datatypes within an execute immediate?
> 
> thanks.
> 
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today - it's FREE! 
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 




::This message sent using the free Web Mail service from http://TheName.co.uk
--
http://www.freelists.org/webpage/oracle-l

Other related posts: