Re: Is an Array-Insert the same as using SQL Loader?

Ok thanks for the help, I wasn't sure they were the same thing.

Tha data comes from an in-memory container after a calculation. The data (500-2000 records) right now is written to a global temp table and then merged into the main data table.

The problem is twofold - I have to create a huge string of UNIONs so that I can pass the calculated data to oracle to insert. And I have to do this in several steps of the string size approaches around 64K, which is the biggest query length I can pass using ADO.

It seems that Loader is the right choice.

Thanks

Dean

Mark D Powell wrote:
> dean wrote:
> > Or are they two separate processes?
> >
> > Here's my problem, any help would be appreciated. I need to be able to
> > post around 500 to 2000 records into an output table fast, once the
> > user scrolls off a master record (or, in other words, it has to be
> > responsive). I have tried all kinds of tricks, such as the MERGE
> > statement, multi-row inserts using select bla from bla union all select
> > bla2 from bla union all ...., etc.
> >
> > What are some of the alterntive methods I could try here? I am using
> > Oracle 9.2i and 10g on a single processor windows environment. The
> > output table has generally 2 million records with around 20 field.
> >
> > Thanks for any help,
> >
> > Dean
>
> Dean, depending on what tool you are using on the front-end the bulk
> load feature of pl/sql might be an option. Basically you have to load
> the data into a pl/sql table (array) and then Oracle uses array inserts
> to load the data into the table. See the PL/SQL manual.
>
> The insert/select statement might be an option depending on where your
> data source is.
>
> More detail of where the data comes from and where it has to go would
> probably allow someone to suggest viable approaches to the problem.
>
> HTH -- Mark D Powell --
Received on Fri Jun 09 2006 - 10:17:03 CDT