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.
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.
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.
DECLARE x NUMBER; BEGIN dbms_output.put_line('Starting...'); x := 1 / 0; dbms_output.put_line('...Ending'); END;
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.
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.
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;
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.
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.
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.
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;
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.
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.
DECLARE x NUMBER; BEGIN x := 1 / 0; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Some exception caused this to display.'); END;
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.
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;
The divide-by-zero error code and the first one hundred characters of the error message are displayed when this code is executed.
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.
DECLARE my_new_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Starting...'); DBMS_OUTPUT.PUT_LINE('...Ending'); END;
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.
DECLARE my_new_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Starting'); RAISE my_new_exception; END;
When you want to cause a user defined exception to occur in code, you use the RAISE keyword.
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;
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.
BEGIN RAISE_APPLICATION_ERROR(-20000, 'A user defined error occurred.'); END;
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.
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;
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;
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;