BULK Collect
Hi,
I am trying to load some transactional data from flat file(Excel for example) into Oracle
Application and am following the below process :
1) Import Excel Data into custom table.
2) BULK COLLECT Data into PL/SQL Table which is having rowtype of my custom table.
3) Validate Data
4) Derive Data
5) Load Error Recs in other pl/sql table which is having rowtype of my error table
6) Inserting Validated Recs to my base table calling API
7) Inserting Error Recs into error table using BULK Insert.
Now in my custom table there are two fields say record_id(behaves as primary key) and
record_status(used to identify status of record).
I do not have these fields in my flat file(Excel).
Hence I am calling a procedure to populate these two fields.
Record ID via a sequence( using SELECT sequence.nextval)
Status with constant value ( assignment ).
My first query is :
1) Can I do a BULK UPDATE to populate both these fields?
2) Currently I am doing the processing record by record basis meaning after bulk collect of all
records into my pl/sql table :
For Each Record
a) Populate record id and record status
b) Validate Input Data
c) Derive some columns used for API processing using
SQLs and assigning them to pl/sql table cols.
d) Error Logging into error pl/sql table
e) Calling API to insert validated records
f) Updating Custom Table with derived cols value(stored in pl/sql table)
End ;
BULK INSERT error records into error table.
I want to do all at one shot. I mean :
a) Update record id and record status for all records in custom table.
b) Validate Input Data for all records
c) Derive cols for all records
d) Error Logging for all records
e) API call for all records
f) Synochronising for all records.
How can i acheive this?