Wednesday, January 9, 2013

Exception Handling

Named System Exceptions

What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Oracle has a standard set of exceptions already named as follows:
Oracle Exception NameOracle ErrorExplanation
DUP_VAL_ON_INDEXORA-00001You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCEORA-00051You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUTORA-00061The remote portion of a transaction has rolled back.
INVALID_CURSORORA-01001You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ONORA-01012You tried to execute a call to Oracle before logging in.
LOGIN_DENIEDORA-01017You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUNDORA-01403You tried one of the following:
  1. You executed a SELECT INTO statement and no rows were returned.
  2. You referenced an uninitialized row in a table.
  3. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWSORA-01422You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDEORA-01476You tried to divide a number by zero.
INVALID_NUMBERORA-01722You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERRORORA-06500You ran out of memory or memory was corrupted.
PROGRAM_ERRORORA-06501This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERRORORA-06502You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPENORA-06511You tried to open a cursor that is already open.
The syntax for the Named System Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]
   
BEGIN
   executable_section

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];
The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
IS | AS
   [declaration_section]

BEGIN
   executable_section



EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [function_name];
Here is an example of a procedure that uses a Named System Exception:
CREATE OR REPLACE PROCEDURE add_new_supplier
   (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS

BEGIN
   INSERT INTO suppliers (supplier_id, supplier_name )
   VALUES ( supplier_id_in, supplier_name_in );

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');

   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting a supplier.');

END;
In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.



Named Programmer-Defined Exceptions



What is a named programmer-defined exception?

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.
The syntax for the Named Programmer-Defined Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]

   exception_name EXCEPTION;
   
BEGIN
   executable_section
   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]
      
   WHEN OTHERS THEN
      [statements]

END [procedure_name];
The syntax for the Named Programmer-Defined Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype

IS | AS
   [declaration_section]

   exception_name EXCEPTION;

BEGIN
   executable_section

   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]

   WHEN OTHERS THEN
      [statements]
      
END [function_name];
Here is an example of a procedure that uses a Named Programmer-Defined Exception:
CREATE OR REPLACE PROCEDURE add_new_order
   (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
   no_sales EXCEPTION;

BEGIN
   IF sales_in = 0 THEN
      RAISE no_sales;

   ELSE
      INSERT INTO orders (order_id, total_sales )
      VALUES ( order_id_in, sales_in );
   END IF;

EXCEPTION
   WHEN no_sales THEN
      raise_application_error (-20001,'You must have sales in order to submit the order.');

   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting an order.');

END;
In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:
no_sales EXCEPTION;
We've then raised the exception in the executable section of the code:
IF sales_in = 0 THEN
   RAISE no_sales;
Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.
Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:
WHEN no_sales THEN
   raise_application_error (-20001,'You must have sales in order to submit the order.');
We are also using the WHEN OTHERS clause to trap all remaining exceptions:
WHEN OTHERS THEN
   raise_application_error (-20002,'An error has occurred inserting an order.');

WHEN OTHERS Clause


What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.
The syntax for the WHEN OTHERS clause in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]

BEGIN
   executable_section

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];
The syntax for the WHEN OTHERS clause in a function is:
CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
IS | AS
   [declaration_section]

BEGIN
   executable_section

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [function_name];
Here is an example of a procedure that uses a WHEN OTHERS clause:
CREATE OR REPLACE PROCEDURE add_new_order
   (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
   no_sales EXCEPTION;

BEGIN
   IF sales_in = 0 THEN
      RAISE no_sales;

   ELSE
      INSERT INTO orders (order_id, total_sales )
      VALUES ( order_id_in, sales_in );
   END IF;

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      raise_application_error (-20001,'You have tried to insert a duplicate order_id.');

   WHEN no_sales THEN
      raise_application_error (-20001,'You must have sales in order to submit the order.');

   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting an order.');

END;
In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.

Frequently Asked Questions


Question: Is there any way to get the ORA error number (and/or description) for the errors that will fall into OTHERS?
Something like:
WHEN OTHERS THEN
   'Error number ' & Err.Number & ' has happened.'
Answer: Yes, you can use SQLCODE function to retrieve the error number and SQLERRM function to retrieve the error message.
For example, you could raise the error as follows:
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
Or you could log the error to a table as follows:
EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;




SQLCODE Function



What does the SQLCODE Function do?

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];
You could use the SQLCODE function to raise an error as follows:
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;
Learn more about the SQLERRM Function.


SQLERRM Function



What does the SQLERRM Function do?

The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];
You could use the SQLERRM function to raise an error as follows:
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;
Learn more about the SQLCODE Function.



Oracle Error Messages



http://www.techonthenet.com/oracle/errors/index.php