RE: execute immediate 'insert into ...'
- From: "Kennedy, Jim" <jim_kennedy@xxxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <joe_dba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Sep 2005 08:00:09 -0700
1. Why not just do insert not execute immediate?
2. I believe you could use bind variables in the execute immediate. Which
would be better than this.
3. Use to_date on the date string. You are assuming the date format and that
has a high probability of biting you in the backside in the future. Also use 4
digit years.(backside bit also) eg to_date('07-JUN-2005','DD-MMM-YYYY')
Jim
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Mercadante, Thomas F (LABOR)
Sent: Fri 9/9/2005 7:49 AM
To: joe_dba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: execute immediate 'insert into ...'
Joe,
Try this:
execute immediate 'insert into test values (' ||
2 || '''' || ',' || '''' ||
'joe' || '''' || ',' || '''' ||
'07-JUN-05' || ')';
The nest way to test this is to select the above string from dula to see
if it is formatted correctly:
Select 'insert into test values (' ||
2 || '''' || ',' || '''' ||
'joe' || '''' || ',' || '''' ||
'07-JUN-05' || ')'
from dual;
If the quotes look correct, then try executing it.
Tom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joe Smith
Sent: Friday, September 09, 2005 10:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: execute immediate 'insert into ...'
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
