Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Many insertions at one shot

Re: Many insertions at one shot

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 30 Apr 2005 07:18:22 -0700
Message-ID: <1114870474.183310@yasure>


Patrice wrote:

> Hi,
> I am facing the following problem.
> I'm trying to make a high number of insertions into a remoteOracle database
> from an EAI.
> My problem is: for 1000 records it takes around 10mns. That is too long for
> me.
> Could you tell if it's possible by calling a stored procedure to send a 'n'
> number of records at one shot instead of inserting each record one after
> one.
> Thanks in advance for you help.

If it is taking 10 minutes you have a problem that has nothing to do with it being a remote database. Get a network admin to check it out.

Follow Jim Kennedy's advice first but yes you can use a stored procedure. Essentially you package up your insert statements in the form of an anonymous block:

BEGIN

   INSERT INTO ....
   INSERT INTO ....
   INSERT INTO ....
   INSERT INTO ....

END;
/

and pass it through as a VARCHAR2 (under 32K) or as a CLOB and execute it on the other side using EXECUTE IMMEDIATE (under 32K) or DBMS_SQL (more than 32K).

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Apr 30 2005 - 09:18:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US