Re: execute immediate 'insert into ...'
- From: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx
- To: joe_dba@xxxxxxxxxxx
- Date: Fri, 9 Sep 2005 16:33:55 +0100
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/
>
> --
> //www.freelists.org/webpage/oracle-l
>
::This message sent using the free Web Mail service from http://TheName.co.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
