I have tables x,y,z .
My procedure deletes all the tables and then populates each of them as follows:
Create or Replace procedure Ins as
Delete from x;
Insert into x
select * from tmp;
Delete from y;
Insert into y
select * from tmp2;
Delete from z;
Insert into z
select * from tmp3;
Could you please help me in answering the following questions.
1) How should I error handle if I get errors at each insert stmt. Would the procedure at the error or just give me an error and proceed to the other inserts.
2) How should incorporate the Exception handling, what kind of exceptions should I take care in this code.
3) Should I put Commit statements after each Insert?
a). The procedure will exit, when an error occurs, therefore the process will not move on to the next statement.
b). To find out if an exception occur and what the error is play the following exception of your code.
WHEN OTHERS THEN
c). I would recommend placing a commit after each statment. Depends on the number of rows inserted and the size of your rollback segs also. You may want to consider a cursor insert and commiting after ever n records.
OCP 8i, 9i DBA
Can I make use of hte following:
When Others Then
Also when I run into an error on hte 3rd insert the above 2 inserts are made successfully.
I have added a commit after each insert.
I don't see why not, as long as you declare ErrMsg.
b). If the first and second complete and third fails, but if the first failed and there was no exception handling, then the process would exit, and the second and third wouldn't complete.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width