Generally, there are two types of exceptions:
1. System-defined Exceptions
System-defined Exceptions
These are pre-defined exceptions situated in Oracle SQL. It is involuntarily raised or executed on encountering a program that violates any Oracle SQL rules.
System-defined exceptions normally are of two types:
1. Named Exceptions
These are system defined exceptions which are pre-defined in the Oracle SQL and normally have a name for reference intention. Few of the named exceptions are as follows:
a. ZERO_DIVIDE: This error occurs when a user tries to divide a number by zero.
b. NO_DATA_FOUND: This error occurs when a user fires a query that doesn’t return anything.
c. CURSOR_ALREADY_OPEN: This error occurs when you try to access a Cursor which is already open or which is already being used by any other program.
d. TOO_MANY_ROWS: This error is raised when you try to fetch more than a single row into a variable or a record.
e. LOGIN_DENIED: This error is raised when a user tries to log into the Oracle database with a wrong username or a password.
f. INVALID_CURSOR: This error occurs when you perform an invalid task on a cursor like fetching data from a cursor that is closed.
g. STORAGE_ERROR: This error occurs when PL/SQL database runs out of memory or memory gets malfunctioned.
2. Unnamed Exceptions
These are the system defined exceptions that the Oracle provides to its users. These exceptions don’t have a naming system to its block structure. However, such exceptions do have an error code and an error message associated to it.
Such exceptions are handled either by associating the exception code to a name and using it as a named exception or by using the WHEN other THEN exception handler mechanism.
However, Oracle provides us a feature to assign a name to an unnamed System exception which includes the usage of a Pragma which is also known as an Exception_Init. This mechanism is used to link the Oracle system error code to user defined exception name.
Syntax using Exception_Init
DECLARE Exception_Name Exception; Pragma Exception_Init (Exception_Name, Error_Code); Begin Execution Section.... Exception WHEN Exception_Name THEN Exception Handler.... END;
User-defined Exceptions
The user-defined exceptions are the ones that are developed or programmed by the programmer. These are explicitly declared in the declaration section and explicitly raised in the execution section.
A PL/SQL exception definition contains parts such as exception type, error code/ number and an error message for the end-user. Every exception whether it is user-defined or pre-defined (System) has an error code associated with it.
Syntax for User-defined Exceptions
Declare Declaration Section Begin Execution Section Exception Exception Section… When ExceptionA Then ExceptionA-Handling Statements When ExceptionB Then ExceptionB-Handling Statements When ExceptionZ Then ExceptionZ-Handling Statements End;
Example
declare
admin_id integer;
admin_name varchar2(20);
begin
select ano,aname into admin_id,admin_name from Admin
where ano=admin_id;
dbms_output.put_line(admin_name);
exception
When NO_DATA_FOUND Then
dbms_output.put_line('Administrator Details Unmatched');
When OTHERS Then
dbms_output.put_line('Unknown Error');
End;
/
Output
Raise_Application_Error()
RAISE_APPLICATION_ERROR() is an in-built procedure which is used to display the user-defined error messages along with an error code which falls in the range of -20000 to -20999. It is only used to raise an exception and there is no method to handle it. Exceptions are raised by the Oracle database server automatically whenever there is any database malfunctioning. However, exceptions can be raised by the programmers by using the RAISE command.
Syntax
Raise_Application_Error (Error_Code, Error_Message);