Review Questions
1. Which section of the PL/SQL block handles errors and abnormal
conditions?
A. Declaration section
B. Exception section
C. Executable section
D. Anonymous block
2. What is the mandatory clause in a SELECT statement when used inside
a PL/SQL block?
A. INTO
B. WHERE
C. ORDER BY
D. GROUP BY
3. Which line of the code has an error?
1 declare
2 X1 number := 0;
3 1Y number := 0;
4 begin
5 X := 10;
6 1Y := X + 1Y;
7 end;
A. Line 2
B. Line 3
C. Line 5
D. The code has no error.
Search WWH ::
Custom Search
What is Exception Handling? A message is showed, if an exception occurs that says the reason for its cause. PLSQL Exception Handling has three parts. The syntax DECLARE BEGIN EXCEPTION
WHEN exp1 THEN Exp1statements WHEN exp2 THEN Exp2-statements ….. WHEN others THEN
Exp3-statements
END;
Syntax Explanation
- From the syntax, we can say that the exception handling block has a series of WHEN conditions. After every WHEN condition, specify the name of the exceptions that are expected to occur at run time.
- If any exception occurs at run time, then the PLSQL server will try to find the exception-handling part for a specific exception. Exception starts searching from the first WHEN condition and searches serially.
- When it finds the exception handling for specific exceptions, then it will start executing the exception handling code. For the exception raised, if there is no WHEN condition, then the server will start executing WHEN OTHERS code.
- During run time, only one exception is executed for a block. Remaining exception handlings will be skipped and will go out of the present block.
- learn some of the important pl sql interview questionsfor the interview
Types
There are two types of PLSQL Exception Handling.
System-defined Exceptions
- These are defined and managed completely, by the Oracle Server itself. These are specially specified, in the Oracle Standard Package.
- If an exception occurs in the program, then the Oracle server compares and finds the accurate exception from the set of exceptions, present in the Oracle Standard package.
- These are predefined in PLSQL, which raises WHEN any rule is broken. These are of two types.
Named System Exceptions
- These are not defined by the developers. They have names in the standard package of plsql. There are various pre-defined named exceptions, these are executed if any database rule is broken by a program.
- Some example for this type are CASE_NOT_FOUND, COLLECTION_IS_NULL, ACCESS_INTO_NULL, INVALID_CURSOR, DUP_VAL_ON_INDEX, 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.
Unnamed System Exceptions
- These are also predefined in the Oracle server, but they don’t have any names. These do not occur regularly, and these are written with program code and with a related message.
These are handled in two ways:
- With the help of WHEN OTHERS exception handler.
- By giving a name to the exception code, and applying it as a named exception.
Oracle enables us to declare and use our own exceptions. These are known as User-defined exceptions, which are defined by the users.
These can be declared in 3 ways.
1. Using the RAISE_APPLICATION_ERROR method.
2. Using the PRAGMA EXCEPTION_INIT function.
3. Using variable of EXCEPTION Type.
Get in depth knowledge on pl sql through step by step through pl sql training from scratch
Gökhan YAVAŞ Muhammed Hilmi Koca NERMİN PARLAK