Bulk inserts to DB through PL/SQL

[sumeshnb]

Hi,
I have to solve a problem relating to insertion/updation of millions of
rows to DB (Oracle).
The current implementation of the problem is described as follows:
A C++ application receives data from many sources and after processing
it inserts this data to DB. There are various procedures to handle the insert
requests corresponding to various tables. The problem is that for each
row's insertion , the C++ application has to call the procedure. I can buffer
multiple insertion requests and send to the DB server as a bulk. But I dont know how to process this bulk data in DB . Data which I sent from C++ to
DB is either "insertion" or "updation" data.

Please advise me in the following matters:

1) When we want to insert huge data to DB from a back end applicaton , what is the best way?
2) Is there a way to accept a buffer(which contains multiple rows) into a procedure and process each row one by one?

etc ..etc.

[sumeshnb]

Please give some hint...thats all needed. I dont have time to go throgh a full text book to search for a solution. Also done lot of googling...please help.

[OraMaster]

Hi,

Why don't you generate CSV files from within C++ application and then try loading those CSV files into Oracle tables. Using the bulk loader that comes with Oracle. This is designed to load CSV data very rapidly into oracle. You could perhaps spawn this program from your C++ Builder program using WinExec(), ShellExecute() or CreateProcess().

Best Of Lucks,
Bhushan

[sumeshnb]

Hi Bhushan,
thanks for the reply. I will do some analysis in that way.

-sumesh.

[sumeshnb]

Is it possible to use sqlloader to give input to a pl/sql procedure?
ie, not a direct insertion/updation to the DB. instead a pl/sql procedure to be called with each row of the csv file.
any help would be greately appreciated.

[sumeshnb]

please give me some info...
thanks in advance.

[amitpatel66]

No, SQLLOADER doesnt work that way through a procedure. You will need to put the data in to a file (CSV, any other delimiter) and then call the SQLLOADER.

Prerequisites for INSERT:
-------------------------------------

1. Control File for SQL Loader
2. Data file to be created everytime you need to insert the data in to the oracle database

UPDATE:
---------------

If you are looking at updating the database column then you will need to create a file for the data to be updated, and call a procedure that will read the data using UTL_FILE package from the file and does the update.