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_at_hotmail.com>:

> 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