Loading data with PL/SQL script

[Guest_imported]

I have this code:
DECLARE
CURSOR c1 IS SELECT * FROM golf_denorm;
BEGIN
FOR rec IN c1
LOOP
-- Populate master table
INSERT INTO golf_norm VALUES (rec.game_num, rec.player_id);

-- Populate child table (1 row for each hole in denorm tab)
INSERT INTO golf_norm_holes VALUES (rec.game_num, 1, rec.hole1_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 2, rec.hole2_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 3, rec.hole3_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 4, rec.hole4_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 5, rec.hole5_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 6, rec.hole6_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 7, rec.hole7_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 8, rec.hole8_score);
INSERT INTO golf_norm_holes VALUES (rec.game_num, 9, rec.hole9_score);
END LOOP;
END;
The problem with is I am told that when I try doing this
insert, data becomes too large and insert fails.
I need to do ocassional commits.
Looking at this code, can anyone tell me how to modify this
code to make it commit after say 10 inserts.
Any new code would be greatly appreciated.
Snead.

 

[THoey]

Since a commit takes almost no time at all, why not just put a commit statement in right before your END LOOP statement? Or else, can you nest this current loop inside another loop based on a counter that breaks after 10 writes, does a commit, resets the counter and then continues on? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MikeJones]

I'd look into doing a couple of things...

Firstly stick a commit in could do some harm. What if the SQL fails at some point through the processing, you will have some data commited, which will need to be backed out before you can run the program again. If your data model is as above or as simple then this wouldn't be a problem as you have only 2 simple tables involved, but if your processing is more complex then I would be wary of commiting.

If you processing a lot of rows then for the purposes of overall speed I'd look into Bulk operations. This will allow you to very quickly select all the information into a series of PL/SQL tables and is normaly much, much quicker than doing a normal for loop.

Lastly rather than commiting every few inserts perhaps you could get your DBA to create you a bigger rollback segement to use for the transaction. Your script could even

1. Swirtch the big rollback segment online
2. Use it for the transaction
3. Switch it back off.

If you really don't mind just commiting a bit at a time then I would atleast update a flag on the drving table (golf_denorm) to say that you have processed it and then only select rows that don't have that flag set in the cursor. Atleast it would be easily restartable.

HTH,

Mike.

 

[THoey]

Mike, good reply, I forgot to think about the fatal error side of things. The larger rollback segment is probably the best way to go... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.