Exception handling and rollbacks in PL/SQL

Question

Currently I have one table FIRST_ENTRY_TABLE with PLAN_A and other 11 tables have respective customer_ID with respect to PLAN_A.I have a temp table with new Key which will replace PLAN_A and add PLAN_B for respective customer_is's. Below are 12 PL/SQL Procedure which is inserting/updating new PLAN_B into all required tables with respect to customer_id. As there is around 100 000 PLANS which will be replaced by NEW PLAN KEY for customer_ids.

Firstly I am creating cursor and reading all new KEYS to be updated in 12 tables.

Then respectively inserting/updating query in 12 tables through 12 Procedures.

DECLARE
var1
CURSOR C1
select query fetching PLAN_B key from temp table
PROCEDURE procedure1(
P_key IN OFFTABLE%TYPE;
IS
BEGIN
INSERT into FIRST_ENTRY_TABLE( 
mandator_key,OFFER_TYPE_KEY,offer_key)VALUES('abc','FIXED',P_key)
RETURNING ID
INTO var1;
 END procedure1;
 )
PROCEDIRE procedure2(
 P_key IN OFFTABLE%TYPE;
 oldid IN FIRST_ENTRY_TABLE%TYPE;
 )
IS
 BEGIN
 INSERT into SECOND_ENTRY_TABLE(col1,col2...custid)
 select (col1,col2...var1) FROM sometable WHERE offer_key= P_key;
 UPDATE SECOND_ENTRY_TABLE set custid=var1 where custid=oldid;
 END procedure2;

 simplarly procedure3

 procedure4..  procedure5

 --------procedure12
 COMMIT;

My question is how to do exception handling here as I have all insert and update statements. I know to use NO_DATA_FOUND,what all other exceptions could come and how can we handle it here. How to handle exception, In the end after all procedures execute and COMMIT or inside every procedure?

How can I take rollback from all 12 tables? there would be delete script for new inserted keys and update query for PLAN getting updated in few tables.but there would be over 100k records. Is there any way to write procedure to take rollback before execution of procedures?

Answer 1

As long as all the updates, etc are performed during a single transaction, a single ROLLBACK will roll back all the changes made in the transaction. A transaction is automatically started when you perform a DML operation (SELECT, INSERT, UPDATE, MERGE, DELETE) when no transaction is in effect; you can also start one using the SET TRANSACTION statement.

Answer 2

your code seems very messy.. To practice exception handling in PL/SQL- try to write your sql query within begin, exception and end block. you can do it within loop as well according to the need of your data.

DECLARE
   lv_qry varchar2(100):=NULL;
   lv_param1 varchar2(100):=NULL;
   lv_param2 varchar2(100):=NULL;
BEGIN
   lv_qry:=''; --your or update query 
   EXECUTE IMMEDIATE lv_qry into lv_param1 using lv_param2; --Bind the variable 'optional'
   COMMIT;
 EXCEPTION
    WHEN others THEN
      DBMS_OUTPUT.PUT_LINE(sqlerrm);
      ROLLBACK;
 END;