PL/SQL Exceptions - Built in Exceptions
PL/SQL exceptions are predefined and raised automatically into oracle engine when any error occur during a program.
Each and every error has defined a unique number and message. When warning/error occur in program it's called an exception to contains information about the error.
In PL/SQL built in exceptions or you make user define exception. Examples of built-in type (internally) defined exceptions division by zero, out of memory. Some common built-in exceptions have predefined names such as ZERO_DIVIDE and STORAGE_ERROR.
Normally when exception is fire, execution stops and control transfers to the exception-handling part of your PL/SQL block. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which are also raise predefined exceptions.
PL/SQL exceptions consist following three,
- Exception Type
- Error Code
- Error Message
DECLARE declaration statement(s); BEGIN statement(s); EXCEPTION WHEN built-in_exception_name_1 THEN User defined statement (action) will be taken; WHEN built-in_exception_name_2 THEN User defined statement (action) will be taken; END;
SQL>edit buitin_exp DECLARE temp enum%rowtype; BEGIN SELECT * INTO temp FROM enum WHERE eno=3; EXCEPTION WHEN no_data_found THEN dbms_output.put_line("Table haven't data"); END; /
Table haven't data
PL/SQL procedure successfully operation.
PL/SQL built in exceptions
Following are built in type exception,
|ACCESS_INTO_NULL||ORA-06530||Exception raised when assign uninitialized (NULL) object.|
|CASE_NOT_FOUND||ORA-06592||Exception raised when no any choice case found in CASE statement as well as no ELSE clause in CASE statement.|
|CURSOR_ALREADY_OPEN||ORA-06511||Exception raised when you open a cursor that is already opened.|
|DUP_VAL_ON_INDEX||ORA-00001||Exception raised when you store duplicate value in unique constraint column.|
|INVALID_CURSOR||ORA-01001||Exception raised when you perform operation on cursor and cursor is not really opened.|
|INVALID_NUMBER||ORA-01722||Exception raised when you try to explicitly conversion from string to a number fail.|
|LOGIN_DENIED||ORA-01017||Exception raised when log in into oracle with wrong username or password.|
|NO_DATA_FOUND||ORA-01403||Exception raised when SELECT ... INTO statement doesn't fetch any row from a database table.|
|NOT_LOGGED_ON||ORA-01012||Exception raised when your program try to get data from database and actually user not connected to Oracle.|
|PROGRAM_ERROR||ORA-06501||Exception raised when your program is error prone (internal error).|
|STORAGE_ERROR||ORA-06500||Exception raised when PL/SQL program runs out of memory or may be memory is dumped/corrupted.|
|SYS_INVALID_ROWID||ORA-01410||Exception raised when you try to explicitly conversion from string character string to a universal rowid (uid) fail.|
|TIMEOUT_ON_RESOURCE||ORA-00051||Exception raised when database is locked or ORACLE is waiting for a resource.|
|TOO_MANY_ROWS||ORA-01422||Exception raised when SELECT ... INTO statement returns more than one row.|
|VALUE_ERROR||ORA-06502||Exception raised when arithmetic, conversion, defined size constraint error occurs.|
|ZERO_DIVIDE||ORA-01476||Exception raised when you program try to attempt divide by zero number.|