Import/processing large volume of data (Oracle 9i)

androknego
androknego used Ask the Experts™
on
Hi,

I need advice on the method to be used for the following requirement:
- A large flat file (circa 1 million) needs to be imported into an Oracle 9i database, then several queries needs to be run against the existing data which would validate the newly imported data, and then the new data needs to be inserted into production tables, and some other production tables need to be updated (for the records that have been validated).

As far as I know, the most efficient way to do this is to import the data from the flat file into a NOLOGGING table using SQL Loader. After that, the hard part comes - how do you efficiently scan 1 million records, and validate/insert/merge the data into the production tables. The best thing I could think of is to take chunks of 1000-5000 records, insert them into a temporary table and then do the rest of processing. However, I'm not sure how to actually scan the data from the large NOLOGGING table and then delete the data from that table. I could declare a cursor on the whole table and then loop through 1000-5000 rows, insert them into a temporary table, delete them (using DELETE WHERE CURRENT OF) and then process the temporary table. However, I'm not sure if this would take a lot of CPU/memory resources.

Any help would be greatly appreciated,

Regards

Andrew
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Lookup the 'BULK COLLECT' and 'FORALL' options/statements in your PL/SQL manual.


Commented:
Bulk collect and for all will defently solve yr problem .refer manual

Author

Commented:
Hi again,

Thanks for the tip - but I've looked at the docs and I tried to run a test script and it's always returning an error. Here is what I got - two tables with identical structure called test_trader and test_trader1:

TRADER_ID                           NOT NULL NUMBER(16)
PROGRAM_ID                          NOT NULL NUMBER(16)
NAME                                NOT NULL NVARCHAR2(200)

test_trader1 has got some data in it, and I want to insert 5 rows of them into test_trader. Here is the script I have (I ran it on Oracle 9.2):

DECLARE
   TYPE trader_table IS TABLE OF Test_TRADER1%ROWTYPE;
   trader_rec trader_table;
   
   CURSOR curs IS
      SELECT *
      FROM Test_TRADER1;
BEGIN
   OPEN curs;
   FETCH curs
      BULK COLLECT INTO trader_rec
       LIMIT 5;
   FORALL i IN trader_rec.FIRST..trader_rec.LAST
  --INSERT INTO Test_Trader VALUES (trader_rec(i).trader_id, trader_rec(i).program_id, trader_rec(i).name);
   INSERT INTO Test_Trader VALUES (trader_rec(i));
   
   CLOSE curs;
END;

When I try to use the commented out INSERT INTO Test_Trader statement I get error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. I guess what that means is that I can refer to specific columns of Row which is contained in a Table container. Fair enough...

However when I use the statement just as I've shown it here I get error PL/SQL: ORA-00947: not enough values. From the official Oracle docs I saw that you can do this, and I've checked many times that the structures of the two tables are identical (I've created them using CREATE TABLE AS SELECT...).

Regards

Andrew
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Just a small correction: " I guess what that means is that I CAN'T refer to specific columns of Row which is contained in a Table container."
You cannot use an array of 'record' or 'rowtype', you need to define single arrays as:

type num_type is table of number;
type nvchr_type is table of varchar2(200);

v_TRADER_ID num_type;
v_PROGRAM_ID num_type;
v_NAME nvchr_type;

And then do:

FETCH curs
 BULK COLLECT INTO v_TRADER_ID, v_PROGRAM_ID, v_NAME
..etc...



Author

Commented:
Hi Mike,

Sorry, but my idea was OK. I got it working by simply removing two brackets, it should be
INSERT INTO Test_Trader VALUES trader_rec(i);

I'll give you the pts anyway, you did point me in the right direction.

Regards

Andrew

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial