Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article contains information about Oracle PL/SQL Exception and Types such as system defined, user defined.
What is Exception in Oracle PL/SQL?
These are the structures used for the management of errors that occur during the execution of commands.
Oracle PL/SQL Exception Types
- System-defined
- User-defined
The use of the PL / SQL Exception structure is as follows.
1 2 3 4 5 6 7 8 9 10 | DECLARE -- definitions BEGIN -- commands EXCEPTION WHEN HATA-TURU THEN -- commands WHEN OTHERS THEN -- commands END; |
Sample Exception Usage is as follows;
1 2 3 4 5 6 7 8 | BEGIN DBMS_OUTPUT.put_line(3/0); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.put_line('Divide by zero error.'); WHEN OTHERS THEN DBMS_OUTPUT.put_line('unknown error.'); END; |
System Defined Exceptions in Oracle PL/SQL
The Exception type previously created by Oracle PL / SQL is called System defined.
System-defined exception types are listed below.
- ACCESS_INTO_NULL
- ASE_NOT_FOUND
- COLLECTION_IS_NULL
- DUP_VAL_ON_INDEX
- INVALID_CURSOR
- INVALID_NUMBER, LOGIN_DENIED
- NO_DATA_FOUND
- NOT_LOGGED_ON
- PROGRAM_ERROR
- ROWTYPE_MISMATCH
- SELF_IS_NULL
- STORAGE_ERROR
- TOO_MANY_ROWS
- VALUE_ERROR
- ZERO_DIVIDE
User Defined Exceptions in Oracle PL/SQL
Oracle also allows custom exception definition. These Exception types are called user defined exceptions. You can create User Defined Exception as follows.
1 2 3 4 5 | DECLARE MY_CUSTOM_ERROR EXCEPTION; BEGIN NULL; END; |
We can set a special error code for the exception as follows.
1 2 3 4 5 6 | DECLARE CUSTOM_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453); BEGIN NULL; END; |
The occured error can be triggered by RAISE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SET SERVEROUTPUT ON; DECLARE CUSTOM_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453); v_sayi PLS_INTEGER := '0'; BEGIN IF v_sayi = 0 THEN RAISE CUSTOM_ERROR; -- RAISE_APPLICATION_ERROR(-1453, 'Another Error.'); END IF; EXCEPTION WHEN CUSTOM_ERROR THEN DBMS_OUTPUT.put_line('Special Error Occured.'); DBMS_OUTPUT.put_line(SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.put_line('Unknown Error Occured.'); END; |
The RAISE_APPLICATION_ERROR function takes the “error code”, “error message”, and “whether the error should be replaced with existing errors” as parameters to create a custom error.
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about exception at docs.oracle.com
