Config Router

You are here: Home / Interview Questions / Exception Handling – ORACLE PL/SQL Chapter Wise Interview Questions

Exception Handling – ORACLE PL/SQL Chapter Wise Interview Questions

by

Exception Handling – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What is RAISE_APPLICATION_ERROR?
Answer:
It is a built in function. – This enables you to specified user defined error messages.
Syntax:

RAISE_APPLICATlON_ERROR(errnum, ’errmsg ’). errnum:it

indicates an error number that contains any value between – 20,000 to -20,999.

Question 2:
What is Exception Handling?
Answer:
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.

  1. Type of Exception
  2. An Error Code
  3. A message

By handling the exceptions we can ensure a PL/SQL block does not exit abruptly.

Question 3:
What is the general structure of Exception Handling?
Answer:
The General Syntax for coding the exception section

DECLARE 
Declaration section 
BEGIN
Exception section 
EXCEPTION
WHEN ex_name1 THEN -Error handling statements 
WHEN ex_name2 THEN -Error handling statements
...
END;

General PL/SQL statements can be used in the Exception Block.

Question 4:
How to write Nested Exceptions?
Answer:

DECLARE 
Declaration section 
BEGIN 
DECLARE 
Declaration section 
BEGIN
Execution section EXCEPTION Exception section
END;
EXCEPTION
Exception section 
END;

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.

Question 5:
What are the different types of Exception?
Answer:
There are 3 types of Exceptions:

  1. Named System Exceptions
  2. Unnamed System Exceptions
  3. User-defined Exceptions

Question 6:
What are the Named & Unnamed Exceptions?
Answer:

  • 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:

  1. Not Declared explicitly,
  2. Raised implicitly when a predefined Oracle error occurs,
  3. 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:

  1. By using the WHEN OTHERS exception handler, or
  2. 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.

Question 7:
Explain RAISE_APPLICATION_ERROR & its purpose.
Answer:
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,

  1. To create a unique id for a user-defined exception.
  2. 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);

  1. The Error number must be between -20000 and -20999
  2. The Error_message is the message you want to display when the error occurs.

Question 8:
What are the steps involved to use
RAISE_APPLICATION_ERROR?
Answer:
Steps to be followed to use RAISE_APPLICATION_ERROR procedure:

  1. Declare a user-defined exception in the declaration section.
  2. Raise the user-defined exception based on a specific business rule in the execution section.
  3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLlCATION_ERROR.

Question 9:
Any three PL/SQL Exceptions?
Answer:
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others .