PL/SQL - Exceptions

Syntax for Exception Handling

The General Syntax for exception handling is as follows. Here you can list down as many as exceptions you want to handle. The default exception will be handled using WHEN others THEN:
DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling goes here >
   WHEN exception1 THEN 
       exception1-handling-statements 
   WHEN exception2  THEN 
      exception2-handling-statements 
   WHEN exception3 THEN 
      exception3-handling-statements
   ........
   WHEN others THEN
      exception3-handling-statements
END;

Example

Let us write some simple code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters:
DECLARE
   c_id customers.id%type := 8;
   c_name  customers.name%type;
   c_addr customers.address%type;
BEGIN
   SELECT  name, address INTO  c_name, c_addr
   FROM customers
   WHERE id = c_id;

   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/
When the above code is executed at SQL prompt, it produces the following result:
No such customer!

PL/SQL procedure successfully completed.
The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exceptionNO_DATA_FOUND which is captured in EXCEPTION block.

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database errors, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax of raising an exception:
DECLARE
   exception_name EXCEPTION;
BEGIN
   IF condition THEN
      RAISE exception_name;
   END IF;
EXCEPTION
   WHEN exception_name THEN
   statement;
END;
You can use above syntax in raising Oracle standard exception or any user-defined exception. Next section will give you an example on raising user-defined exception, similar way you can raise Oracle standard exceptions as well.

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is:
DECLARE
   my-exception EXCEPTION;

Example:

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.
DECLARE
   c_id customers.id%type := &cc_id;
   c_name  customers.name%type;
   c_addr customers.address%type;

   -- user defined exception
   ex_invalid_id  EXCEPTION;
BEGIN
   IF c_id <= 0 THEN
      RAISE ex_invalid_id;
   ELSE
      SELECT  name, address INTO  c_name, c_addr
      FROM customers
      WHERE id = c_id;
   
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
   END IF;
EXCEPTION
   WHEN ex_invalid_id THEN
      dbms_output.put_line('ID must be greater than zero!');
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!'); 
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Enter value for cc_id: -6 (let's enter a value -6)
old  2: c_id customers.id%type := &cc_id;
new  2: c_id customers.id%type := -6;
ID must be greater than zero!

PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions:
ExceptionOracle ErrorSQLCODEDescription
ACCESS_INTO_NULL06530-6530It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND06592-6592It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL06531-6531It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX00001-1It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR01001-1001It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER01722-1722It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED01017-1017It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND01403+100It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON01012-1012It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR06501-6501It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH06504-6504It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL30625-30625It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR06500-6500It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS01422-1422It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR06502-6502It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE014761476It is raised when an attempt is made to divide a number by zero.

1 comments:

Kamal Negi said...

Evosys, the leading Oracle Platinum Partner, offers customers with cross-industry capabilities spanning enterprise resource planning, customer relationship management, and supply chain planning, Our E-Business Suite applications supports and aids customers to manage the complexities of global business environments. With industry expertise in ERP, Evosys has completed on time implementation with the help of their Expert Consultants.

About Evosys:Evosys is an Oracle Partner providing consulting services for implementation and maintenance of Oracle Applications. We are a market-focused, process-centric organization that develops and delivers innovative solutions to our customers, consistently outshining other market players. http://evosys.co.in/EnterpriseResourcePlanning

For any Oracle Service, Business Partnerships, Sales Referrals, Collaborations & Tieups, Please contact business@evosys.co.in & we shall contact you immediately.

Contact Business@evosys.co.in

Post a Comment

Related Posts Plugin for WordPress, Blogger...