Exception handling for insert statements in plsql code

Question

if i have 3 insert statements in my plsql code. lets assume them as insert_1, insert_2, insert_3. my conditions be like : if insert_1 statement successfully executed then automatically insert_3 should be executed as well as if insert_3 executed successfully then insert_1 also should be executed automatically.

if both insert_1 and insert_3 fails then only insert_2 should be executed. please write plsql code for this requirement.

smells like an interview question or a homework – cha Apr 06 '18 at 01:53 — cha, Apr 06 '18 at 01:53
yah.. i had faced in a interview – Ganesh galla Apr 06 '18 at 02:18 — Ganesh galla, Apr 06 '18 at 02:18
please share your efforts ! – Gilles Gouaillardet Apr 06 '18 at 02:42 — Gilles Gouaillardet, Apr 06 '18 at 02:42

score 0 · Answer 1 · answered Apr 06 '18 at 05:12

You can create a package for this:

create a package and inside package body you try something like this.

create or replace PACKAGE BODY PKG_INSERT_QUERY AS
 P_ERR_CODE number;  

procedure finalPro
is 
begin 
    P_ERR_CODE := 0;

    insert into a values('a');
    commit;
    EXCEPTION WHEN OTHERS THEN ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('ERROR ' ||SQLERRM);
      P_ERR_CODE := 1;

   if(P_ERR_CODE < 1 )then
      secondQ;
      IF(P_ERR_CODE < 1)  THEN
        thirdQ;
      END IF;
    END IF;  
end;

procedure secondQ
as 
begin
    insert into B values('B');
    commit;
    EXCEPTION WHEN OTHERS THEN ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('ERROR ' ||SQLERRM);
      P_ERR_CODE := 2;
end;

procedure thirdQ
as 
begin
    insert into C values('C');
    commit;
    EXCEPTION WHEN OTHERS THEN ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('ERROR ' ||SQLERRM);
      P_ERR_CODE := 3;
end;
end;

Exception handling for insert statements in plsql code

1 Answers1