I have a table called DUD which is pretty much Static (which means once the data is inserted it never changes). I query data from DUD and populate a staging table CAR from which Webmethods polls everyday.
Usually it is 10 records for every transaction. There are two transactions per day.
I have written a Cursor to do this and I am happy with the logic.
The output will look like:
TRANSID A B C cnt
------ --- --- -- ---
A123 JIM NY ACT 1
A123 BOB CA ACT 2
A123 PIN GA ACT 3
--------------------------
A124 MIK CA ACT 1
A124 JON MA ACT 2
A124 CON MY ACT 3
A124 JIB CA ACT 4
What really concerns me and question is:
-
If the insert in the loop fails, it should rollback all the inserts made in this transaction and do not end up with partially inserted records or orphaned records for a transaction. I commit only after the loop is completed no exception was raised.
-
When exception happens, I also want to know which record failed to insert. I hope to catch this in my exception and call a function in the exception handler that will insert this information in to an Error table for further investigation.
-
The auto commit is disabled in the DB. But will oracle consider ALL the insert through a loop as one transaction or independent transactions and insert it immediately?
Code
DECLARE TYPE message_info
IS
RECORD
(
message_code INTEGER,
message VARCHAR2(500));
msg MESSAGE_INFO;
tranid NUMBER;
p_error EXCEPTION;
CURSOR b1 IS
SELECT *
FROM dud
WHERE dud.DATE = SYSDATE
AND dud.status='ACTIVE';
BEGIN
IF *CHECK SOME condition*
BEGIN
tranid = seq_transid.NEXTVAL;
--- Transaction id is unique per transaction.
--- All 10 records will have same transaction id.
FOR b1 IN c1
LOOP
i=b1%rowcount;
INSERT INTO car
(
transid,
a,
b,
c,
cnt
)
VALUES
(
tranid,
b1.a,
b1.b,
b1.c,
i
);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
msg.message := 'Unable to insert into CAR Table';
RAISE p_error;
END;
COMMIT;
EXCEPTION
WHEN p_error THEN
error.post_msg (msg.message, SQLCODE,SQLERRM,USER);
END IF;
END;
Best Answer
You can use FORALL statement also in this situation....
you are using cursor and in loop you are inserting into tables.. you can directly insert all the transactions in one shot. this will increase the performance of your code as well and this will give you surety also that all transaction inserted or none of them have inserted...