Exception Handling – ORACLE PL/SQL Chapter Wise Interview Questions
What is RAISE_APPLICATION_ERROR?
It is a built in function. – This enables you to specified user defined error messages.
RAISE_APPLICATlON_ERROR(errnum, ’errmsg ’). errnum:it
indicates an error number that contains any value between – 20,000 to -20,999.
What is Exception Handling?
PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a message which explains its cause is received.
PL/SQL Exception message consists of three parts.
- Type of Exception
- An Error Code
- A message
By handling the exceptions we can ensure a PL/SQL block does not exit abruptly.
What is the general structure of Exception Handling?
The General Syntax for coding the exception section
WHEN ex_name1 THEN -Error handling statements
WHEN ex_name2 THEN -Error handling statements
General PL/SQL statements can be used in the Exception Block.
How to write Nested Exceptions?
Execution section EXCEPTION Exception section
In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.
What are the different types of Exception?
There are 3 types of Exceptions:
- Named System Exceptions
- Unnamed System Exceptions
- User-defined Exceptions
What are the Named & Unnamed Exceptions?
- Named System Exceptions:
System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which is known as Named System Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
Named system exceptions are:
- Not Declared explicitly,
- Raised implicitly when a predefined Oracle error occurs,
- Caught by referencing the standard name within an exception-handling routine.
- Unnamed System Exceptions:
Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed system exceptions:
- By using the WHEN OTHERS exception handler, or
- By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_NIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.
Explain RAISE_APPLICATION_ERROR & its purpose.
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and – 20999.
Whenever a message is displayed using
RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
- To create a unique id for a user-defined exception.
- To make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message);
- The Error number must be between -20000 and -20999
- The Error_message is the message you want to display when the error occurs.
What are the steps involved to use
Steps to be followed to use RAISE_APPLICATION_ERROR procedure:
- Declare a user-defined exception in the declaration section.
- Raise the user-defined exception based on a specific business rule in the execution section.
- Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLlCATION_ERROR.
Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others .