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 ??

danish · Accepted Answer · 2014-03-12 12:53:44Z

0

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.

answered Mar 12, 2014 at 12:53

danish

5,6002 gold badges27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vkreddy Over a year ago

Thank you:) One more thing, In between copying, If few more records added to the process_state table then will this procedure copy new records which are being added recently??

danish Over a year ago

Your procedure will archive everything that the table had till the moment where insert statement is called.

Collectives™ on Stack Overflow

How to Handle Exceptions like DB down In Stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related