How to Handle Exceptions like DB down In Stored procedure

Question

I am archiving data from one table to another table using below procedure.

create or replace PROCEDURE CHECK_TWO AS
  v_insert_count number;
  v_delete_count number;
  v_initial_count number;
BEGIN

   SELECT count(*) into v_initial_count from process_state where tstamp BETWEEN       ADD_MONTHS(trunc(SYSDATE,'MONTH'), -12) AND LAST_DAY(TRUNC(SYSDATE, 'MONTH') - 4);
   dbms_output.put_line(v_initial_count);

   insert into process_state_archive select * from process_state where tstamp BETWEEN ADD_MONTHS(trunc(SYSDATE,'MONTH'), -12) AND LAST_DAY(TRUNC(SYSDATE,  'MONTH') - 4);
   v_insert_count := sql%rowcount;
   dbms_output.put_line(v_insert_count);

   delete process_state where  tstamp BETWEEN ADD_MONTHS(trunc(SYSDATE,'MONTH'), -12) AND LAST_DAY(TRUNC(SYSDATE,  'MONTH') - 4);
   v_delete_count := sql%rowcount;
   dbms_output.put_line(v_delete_count);

   if v_insert_count = v_delete_count AND v_initial_count= v_insert_count then
     commit;
   else
     rollback;
   end if;
END CHECK_TWO;

In this process, I want to handle exceptions like Server Unavailable or Database Down or any other Environmental related issues. How to handle these scenarios in my stored procedure. Can anyone please tell me ??

Answer 1

Looks like you are dealing within same database. so, if database is down, your stored procedure will not be executed either. However if you are dealing with another database, there can be plenty of reasons why a database is down. There is predefined list of exceptions that comes with PL SQL. You can take a look at that. However, if your case is not included in it, you could use something like this:

DECLARE
  myExceptionName EXCEPTION;
  PRAGMA EXCEPTION_INIT(myExceptionName, <exceptionNumberHere>);
BEGIN
  <your code here>
EXCEPTION
  WHEN myExceptionName THEN
    <handle exception here>
END; 

You can search for exception numbers that are defined in Oracle and add cases to your procedure accordingly. Here is the list of error messages and numbers for Oracle 11g R2.