RE: execute immediate 'insert into ...'

From: Kennedy, Jim <jim_kennedy_at_mentor.com>
Date: Fri, 9 Sep 2005 08:00:09 -0700
Message-ID: <EF25DB6D87DD1A469C80A312C63C3B4C34F075@SVR-ORW-EXC-07.mgc.mentorg.com>

Why not just do insert not execute immediate?
I believe you could use bind variables in the execute immediate. Which would be better than this.
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_at_freelists.org on behalf of Mercadante, Thomas F (LABOR) Sent: Fri 9/9/2005 7:49 AM
To: joe_dba_at_hotmail.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Friday, September 09, 2005 10:31 AM To: oracle-l_at_freelists.org
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/

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 09 2005 - 10:05:01 CDT