Re: Allocating memory for Bulk Inserts

"oidba" <oidba_at_yahoo.com> schrieb im Newsbeitrag news:e61db2dc.0401072138.7294c1d0_at_posting.google.com...
> > > SQL*LOADER is not an option
> >
> > I am sorry: Why not ?
>
> We're talking zero room for error in this situation. This is a
> telecomm call charging application. Unless Oracle provides a C/C++
> SQL*LOADER API,
the loader can be invoked from the shell (UNIX) or from a batch file (Windows), no C++ needed for this.
You would just need a control file, a batchfile (both are rather small and easy to configure) and the data file.

>I would not bet too much on SQL*LOADER to provide the
> appropriate error handling and transaction control needed. How do you
> suppose I rollback a transaction once SQL*LOADER has loaded the table.

The question is loading _which_ table, that is why I suggested to load the raw data into
some _staging tables_ as a first step, and then as a second step to migrate these data via a PL/SQL script
to the tables you need. And that script should do all the error handling and logging,
and finally the transaction (depends of the type, quality and size of Your data: all as 1 transaction
- only good for few data, or a cursor loop committing every 100th record maybe).
And in that script You could decide what to do in error cases (rollback, logging, whatever).

> I would not want to depend on the return value from a "system()" or
> "popen()" to handle the errors. Should one file be incorrectly
> processed, a tedious and time consuming task of batch cleaning would
> have to take place to manually rollback.

That would be true.

But I would not want to depend on C++ in this case. From what You said above,
You seem to have "zero room for errors"
but still a possibility for errors (as always) and the way to deal with those eventual errors is still unclear in Your case.
This is a classic situation for using Loader and a PL Script.

If You have the opportunity to write some PL/SQL for this, You could do tests before doing the real thing, that is migrating from the loaded table FLAT_RAWDATA1 to some REAL_TABLE_test1 or such
and You could run simply it in SQLPlus or as a stored procedure, and transaction control should be far easier than C++, as PL is the first choice as programming language for Oracle.

hth, Jan Received on Thu Jan 08 2004 - 08:57:04 CET