From oracle-l-bounce@freelists.org Fri Sep 9 10:05:01 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89F51Nx025382 for ; Fri, 9 Sep 2005 10:05:01 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j89F4uIP025369 for ; Fri, 9 Sep 2005 10:04:56 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F3591E9DD3; Fri, 9 Sep 2005 10:04:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19591-02; Fri, 9 Sep 2005 10:04:49 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9A2CE1E9675; Fri, 9 Sep 2005 10:04:48 -0500 (EST) x-mimeole: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5B54F.9022B5F4" Subject: RE: execute immediate 'insert into ...' Date: Fri, 9 Sep 2005 08:00:09 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: execute immediate 'insert into ...' Thread-Index: AcW1S2pCPZXk6RYsQSmHyKCmsDHp3gAAd6gAAAB4wwM= From: "Kennedy, Jim" To: , , X-OriginalArrivalTime: 09 Sep 2005 15:02:57.0833 (UTC) FILETIME=[90530190:01C5B54F] X-archive-position: 25211 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jim_kennedy@mentor.com Precedence: normal Reply-To: jim_kennedy@mentor.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.4 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------_=_NextPart_001_01C5B54F.9022B5F4 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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@freelists.org on behalf of Mercadante, Thomas F = (LABOR) Sent: Fri 9/9/2005 7:49 AM To: joe_dba@hotmail.com; oracle-l@freelists.org Subject: RE: execute immediate 'insert into ...' =20 Joe, Try this: execute immediate 'insert into test values (' ||=20 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 (' ||=20 2 || '''' || ',' || '''' || 'joe' || '''' || ',' || '''' || '07-JUN-05' || ')'=20 from dual; If the quotes look correct, then try executing it. Tom -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Joe Smith Sent: Friday, September 09, 2005 10:31 AM To: oracle-l@freelists.org Subject: execute immediate 'insert into ...' I am trying to insert records into a table through execute immediate. The=20 number datatypes go in with no problem. It is the varchar2 and date formats=20 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 ||', '||=20 07-JUN-05 ||')'; I have tried different ways to format the varchar2 and date datatype, but=20 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!=20 http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l ------_=_NextPart_001_01C5B54F.9022B5F4 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: execute immediate 'insert into ...'

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@freelists.org on behalf of Mercadante, Thomas F = (LABOR)
Sent: Fri 9/9/2005 7:49 AM
To: joe_dba@hotmail.com; oracle-l@freelists.org
Subject: RE: execute immediate 'insert into ...'

Joe,

Try this:

execute immediate 'insert into test values (' ||
            &= nbsp;      2 || '''' || ',' || '''' ||
            &= nbsp;      'joe' || '''' || ',' || '''' ||
            &= nbsp;      '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 (' ||
            &= nbsp;      2 || '''' || ',' || '''' ||
            &= nbsp;      'joe' || '''' || ',' || '''' ||
            &= nbsp;      '07-JUN-05' || ')'
from dual;

If the quotes look correct, then try executing it.

Tom



-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@free= lists.org] On Behalf Of Joe Smith
Sent: Friday, September 09, 2005 10:31 AM
To: oracle-l@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.o= rg/webpage/oracle-l
--
http://www.freelists.o= rg/webpage/oracle-l

------_=_NextPart_001_01C5B54F.9022B5F4-- -- http://www.freelists.org/webpage/oracle-l