facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Exception Clause

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

It is important to distinguish the various types of error conditions that might exist in PL/SQL. A compile time error occurs when there is a problem with PL/SQL code at the time it is saved to the database. A run time error occurs when a syntactically valid program encounters a problem during program execution. Many run time errors can be dealt with in the EXCEPTION section of a PL/SQL block. An exception handler is the special code implemented to handle a given exception.

Lesson Goals

  • Learn about handling exceptions
  • Learn how to use the exception clause
  • Learn how to raise exceptions
  • Learn how to use SQLCODE and SQLERRM

Overview of Exceptions

The term exception in PL/SQL can be used to refer to a system generated error (which typically has a code that starts with ORA- or PLS-) or a user defined error condition. The term used to describe the point at which an error occurs is raised. When an exception is raised, normal program execution ceases and the exception is propagated to the nearest available exception handler in scope (or out of the enclosing block). A system generated error will be raised automatically, while you must explicitly raise a user defined error using the RAISE keyword or RAISE_APPLICATION_ERROR procedure.

Causing System Generated Exceptions

To gain a better understanding of how exceptions behave, you can explicitly cause an exception to occur by creating code that will compile but raise an exception at runtime.

Code Sample:

Exception-Clause/Demos/exception_caused.sql
DECLARE
  x NUMBER;
BEGIN
  dbms_output.put_line('Starting...');
  x := 1 / 0;
  dbms_output.put_line('...Ending');
END;

Code Explanation

In this example an error occurs because of an attempt to divide by zero:

ORA-01476: divisor is equal to zero

Oracle includes object names, numbers, and character strings in messages. When dealing with PL/SQL objects, a message stack includes additional information, such as the line number where the error occurred. In order to quickly track down errors, make sure to have line numbers displayed in your editor.

Handling System Generated Exceptions

Now that you know how to cause a system generated exception, you can learn how to handle it. In this example, a handler that is specifically designed to address divide-by-zero exceptions will simply output a message.

Code Sample:

Exception-Clause/Demos/exception_handled.sql
DECLARE
  x NUMBER;
BEGIN
  dbms_output.put_line('Starting...');
  x := 1 / 0;
  dbms_output.put_line('...Ending');
EXCEPTION
 WHEN ZERO_DIVIDE
  THEN DBMS_OUTPUT.PUT_LINE('Divide by zero error.');
END;

Code Explanation

Each PL/SQL block can optionally include an EXCEPTION section where specific exceptions and errors are captured and dealt with programmatically. Specific error types can be handled by indicating the error in a specific WHEN clause.

Identifying System Generated Exceptions

You might be wondering how to determine the name of a given exception to be handled. Predefined PL/SQL exceptions are listed in the Oracle Documentation. In addition, the following query shows the names of predefined exceptions declared globally in package STANDARD. It is not important to understand all of the details of this query. It is sufficient to understand that many Oracle exceptions that you will need to handle can be identified by name by consulting the documentation as needed.

select regexp_substr(e, '[^,]+', 1, 1) as error_name,
			regexp_substr(e, '[^,]+', 1, 2) as error_code
from
	(select	replace(
			replace(
			replace(upper(trim(text)),'PRAGMA EXCEPTION_INIT(', ''),
			')', ''),
			'''', '') as e
	from all_source
	where name = 'STANDARD'
		and type='PACKAGE'
		and text like '%EXCEPTION_INIT%')
 order by error_name;

The query lists lines from the STANDARD package as they are found in the all_source view. Only lines that include the substring EXCEPTION_INIT are included. The remaining parts of the query are function calls that strip out the error names and codes for final display.

Predefined exceptions in the standard package

You are not limited to handling the exceptions named above. You can use the pragma EXCEPTION_INIT to associate an exception name with any Oracle Database error code that does not have a predefined exception name.

Code Sample:

Exception-Clause/Demos/pragma_exception_init.sql
DECLARE
  null_not_allowed EXCEPTION;
  
  PRAGMA EXCEPTION_INIT(null_not_allowed, -1400);
  
BEGIN
  insert into employees
  (employee_id, first_name, last_name, email, job_id, hire_date)
  values
  (2000, 'Larry', null, 'lellison', 'AD_PRES', current_date);

EXCEPTION
  WHEN null_not_allowed THEN 

    DBMS_OUTPUT.PUT('You attempted to enter a null value');
    DBMS_OUTPUT.PUT_LINE(' in a required column.');
END;

Code Explanation

The previous query identified exceptions as named in the STANDARD package. This example shows that you can associate your own names with exceptions. The pragma EXCEPTION_INIT directive associates an exception name with the error raised when division by zero causes an exception to be raised. This shows that an internal exception can be referenced by name by a specific handler as long as you know the error code that does not have a predefined exception name.

Typically you do not use the pragma for Oracle error messages that already have a predefined exception name.

OTHERS Exception Handler

The exception handlers specified up to this point were targeted for specific exceptions. An optional OTHERS exception handler can be specified to handle all exceptions not named specifically. The WHEN OTHERS clause must be the last handler specified in a block.

Code Sample:

Exception-Clause/Demos/exception_when_others.sql
DECLARE
  x NUMBER;
BEGIN
  x := 1 / 0;
EXCEPTION
  WHEN OTHERS THEN 
  
    DBMS_OUTPUT.PUT_LINE('Some exception caused this to display.');
END;

Code Explanation

All errors not specifically handled can be caught by using the WHEN OTHERS clause.

If you need to use a WHEN OTHERS exception handler, you might be interested in identifying what exception occurred and is being handled. The SQLCODE and SQLERM functions can be used to return the error code and message text.

Code Sample:

Exception-Clause/Demos/error_code_and_message.sql
DECLARE
   x NUMBER;
BEGIN
   x:= 1 / 0;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line('Err number:   ' ||
                           SQLCODE);
      dbms_output.put_line('Error Message:' ||
                           SUBSTR(SQLERRM, 1, 100));
END;

Code Explanation

The divide-by-zero error code and the first one hundred characters of the error message are displayed when this code is executed.

User Defined Exceptions

A user can define exceptions that are specifically associated with the domain of the program and not defined by Oracle. Such an exception must be declared (to effectively create and initialize it as a type). Later it must be raised within the context of the program when the conditions occur that constitute an exception. It can be handled in the same manner as a system generated exception. A user defined error can also be raised using the Oracle supplied RAISE_APPLICATION_ERROR stored procedure.

Code Sample:

Exception-Clause/Demos/user_defined_exception_declaration.sql
DECLARE 

  my_new_exception EXCEPTION; 
  
BEGIN

  DBMS_OUTPUT.PUT_LINE('Starting...');
  
  DBMS_OUTPUT.PUT_LINE('...Ending');

END;

Code Explanation

You can declare your own exceptions for use later in a program. A declaration does not cause the exception to be raised, it simply declares it in a manner similar to any other variable type.

Code Sample:

Exception-Clause/Demos/user_defined_exception_raise.sql
DECLARE 
  my_new_exception EXCEPTION; 
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting');
  RAISE my_new_exception;
END;

Code Explanation

When you want to cause a user defined exception to occur in code, you use the RAISE keyword.

Code Sample:

Exception-Clause/Demos/user_defined_exception_handle.sql
DECLARE
  my_new_exception EXCEPTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting');

  -- Normally we'd have an IF or CASE here
  RAISE my_new_exception;
  -- Normally we'd close the IF or CASE here

  DBMS_OUTPUT.PUT_LINE('This will not display.');

EXCEPTION
  WHEN my_new_exception
    THEN DBMS_OUTPUT.PUT_LINE('Our rule is broken!');
END;

Code Explanation

A user defined exception that has been raised can be handled like any other exception. This example also includes a line of code below the raised exception that illustrates the control flow. When an exception is raised, normal processing ceases and the relevant exception handler in the current block is invoked.

Code Sample:

Exception-Clause/Demos/user_defined_raise_application_error.sql
BEGIN
  RAISE_APPLICATION_ERROR(-20000,
                          'A user defined error occurred.');
END;

Code Explanation

The RAISE_APPLICATION_ERROR procedure allows you to raise user defined errors from stored subprograms that will display messages in the same format as Oracle error messages. In this case, an exception does not need to be previously declared. The error number specified must be a negative integer between -20000 and -20999.

Exception Handling for Invalid Salary

Duration: 10 to 15 minutes.

Code Sample:

Exception-Clause/Exercises/exception_clause_exercise.sql
DECLARE
  v_salary            VARCHAR(50) := '1000.00';  
  v_salary_number     NUMBER;
BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);  
  
  v_salary_number := to_number(v_salary, '9999.00');
  
  v_salary_number := v_salary_number * 1.05;
  
  v_salary := to_char(v_salary_number, '$999,999.00');
  
  dbms_output.put_line('Final formatted salary:  ' || v_salary);  
     
END;

Code Explanation

  1. Open the exception_clause_exercise.sql file.
  2. Modify the code so that v_salary is assigned the value of $1000.00 (including a dollar sign).
  3. Run the program and note the exception that occurs.
  4. Declare an exception called invalid_salary_exception.
  5. Using a pragma, associate the exception with the exception code thrown during the previous run.
  6. Handle the exception so that the following is printed out: Invalid salary format: $1000.00. Only digits and a decimal point permitted.

Solution:

Exception-Clause/Solutions/exception_clause_solution.sql
DECLARE
  v_salary            VARCHAR(50) := '$1000.00';
  v_salary_number     NUMBER;
  invalid_salary_format exception;
  PRAGMA EXCEPTION_INIT(invalid_salary_format, -06502);
BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);

  v_salary_number := to_number(v_salary, '9999.00');

  v_salary_number := v_salary_number * 1.05;

  v_salary := to_char(v_salary_number, '$999,999.00');

  dbms_output.put_line('Final formatted salary: ' || v_salary);

exception
  when invalid_salary_format then
    dbms_output.put_line('Invalid salary format: ' ||
                    v_salary ||
                    '. Only digits and a decimal point permitted.');
END;

Code Explanation

Challenge

  1. Add conditional logic to determine whether v_salary contains a dollar sign symbol.
  2. Modify the program so that RAISE_APPLICATION_ERROR is used to raise an error that says: ERROR: No dollar signs allowed in v_salary.

Challenge Solution:

Exception-Clause/Solutions/exception_clause_challenge.sql
DECLARE
  v_salary            VARCHAR(50) := '$1000.00';
  v_salary_number     NUMBER;

BEGIN

  dbms_output.put_line(rpad('Initial salary: ',29) || v_salary);

  IF INSTR(v_salary,'$') != 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
                   'ERROR:  No dollar signs allowed in v_salary.');
  ELSE
   v_salary_number := to_number(v_salary, '9999.00');
  END IF;

  v_salary_number := v_salary_number * 1.05;

  v_salary := to_char(v_salary_number, '$999,999.00');

  dbms_output.put_line('Final formatted salary: ' || v_salary);

exception
  when OTHERS then
    dbms_output.put_line('Invalid salary format: ' ||
                    v_salary ||
                    '. Only digits and a decimal point permitted.');
END;

Code Explanation