CODEWITHSUNDEEP
oracle-databaseplsql
Lucas Rezende
Lucas Rezende

Reputation: 2686

How to handle general exceptions in PL/SQL

I looked at Google and here but couldn't find an answer for this.

I don't want to treat all exceptions in Oracle.

What I want is just get the return code of the last statement and check if it is 0 (zero). If it is, the command was executed ok. If not, there was some error and then I want the procedure to exit and raise a message saying only that there was an error and the command that caused that error.

Thanks in advance!

Upvotes: 0

Views: 3098

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

If I'm interpreting your question correctly, it sounds like you want to ignore all exceptions and manually check the SQLCODE after every statement. If my understanding is correct, I'm afraid there's not really a way to do that in PL/SQL. When errors occur the system WILL raise an exception - there's no way around it. What you CAN do is to record a description of where the program is in its execution and print that from a generic exception handler, similar to the following:

PROCEDURE SOME_PROCEDURE IS
  strCheckpoint  VARCHAR2(2000);
  rowSome_table  SOME_TABLE%ROWTYPE;
BEGIN
  strCheckpoint := 'SELECT FROM SOME_TABLE';
  SELECT *
    INTO rowSome_table 
    FROM SOME_TABLE
    WHERE SOME_COLUMN = 42;

  strCheckpoint := 'UPDATE SOME_OTHER_TABLE';
  UPDATE SOME_OTHER_TABLE
    SET A_COLUMN = rowSome_table.A_COLUMN
    WHERE KEY_COLUMN = rowSome_table.KEY_COLUMN;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Something happened at checkpoint ''' ||
                         strCheckpoint ||
                         ''' SQLCODE=' || SQLCODE ||
                         ' SQLERRM=' || SQLERRM);
    RETURN;
END;  -- SOME_PROCEDURE

Share and enjoy.

Upvotes: 2

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

Not sure I got your problem right but does this look like you need ?

SQL> create function get_number return number
  2  is
  3  begin
  4   return 1;
  5  end;
  6  /


SQL> begin
  2   if get_number != 0 then
  3  raise_application_error(-20000,'Error raised !');
  4  end if;
  5  end;
  6  /
begin
*
Error in line 1:
ORA-20000: Error raised ! 
ORA-06512: на  line 3

or

SQL> declare
  2   user_def_exc exception;
  3  begin
  4   if get_number!=0 then
  5     raise user_def_exc;
  6   end if;
  7  exception
  8   when user_def_exc then
  9     dbms_output.put_line('We ave got a problem');
 10  end;
 11  /
We ave got a problem

Upvotes: 1

Rene
Rene

Reputation: 10541

You can use the following in SQLPLus:

SQL>whenever sqlerror exit 1

This will terminate the execution of your script upon an error with exit value 1

Upvotes: 0

Related Questions