This tutorial explains PL SQL Exception Handling and related concepts like types of exceptions, user-defined and system-defined exceptions, how to raise an exception, etc:
In PL/SQL Datetime Format of PL/SQL series, we learned about some useful functions around Datetime, Timestamp, and Interval
In this article, we will discuss exception handling in PL SQL. Also, we shall explore the advantages of exceptions and how to raise them.
Let’s start with the learnings!!
What You Will Learn:
PL SQL Exception Handling
An error or a warning event is called an exception. It is encountered during the run time execution of the PL/SQL program. PL/SQL has mechanisms to handle these exceptional conditions with the help of the EXCEPTION block of code where it is defined how to counter the error condition.
There are two types of exceptions. They are listed below:
- User-defined exceptions.
- System defined exceptions.
Some of the popular System defined exceptions are out of memory and division by zero, having names like STORAGE_ERROR and ZERO_DIVIDE respectively. User-defined exceptions are declared in a package, subprogram, or within the declaration section of the PL/SQL block of code and should be assigned names.
Once an exception occurs, the natural flow of execution is halted, and then the execution points to the exception section of the PL/SQL code. While the system defined exceptions are thrown by default, the user-defined ones have to be thrown explicitly by the RAISE keyword.
Thus the exception handling helps to deal with the errors that are encountered during the run time execution and not while compiling the program.
Advantages Of Exception Handling
The advantages are listed below:
- If our code does not have an exception handling, then each time we execute a statement, we must verify errors in execution.
- If we avoid exception handling in our code, the actual errors get missed which gives rise to some other errors.
- The exception handling allows skipping multiple verifications in the code.
- It provides better readability of the code by isolating the error handlers in the code.
Exception Handling Syntax:
DECLARE << declaration section >> BEGIN <<Block of executable code>> EXCEPTION << exception handling >> WHEN excp1 THEN << excp1 handling block >> WHEN excp2 THEN << excp2 handling block >> ........ WHEN others THEN << excp2 handling block>> END;
The default exception is carried out with WHEN others THEN.
Let us consider a table which is named CITIZEN.
SELECT * FROM CITIZEN;
We have created the CITIZEN table with the help of the SQL statement given below.
CREATE TABLE CITIZEN ( ID INT NOT NULL, NAME VARCHAR (15) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID) );
Insert values to this table with SQL statements given below:
INSERT INTO CITIZEN VALUES (1, 'ZAKS', 14); INSERT INTO CITIZEN VALUES (8, 'RANNY', 75); INSERT INTO CITIZEN VALUES (5, 'TOM', 37);
Coding Implementation with exception handling:
DECLARE citizen_id citizen.id%type; citizen_name citizen.name%type; citizen_age citizen.age%type := 9; BEGIN SELECT id, name INTO citizen_id, citizen_name FROM citizen WHERE age = citizen_age; DBMS_OUTPUT.PUT_LINE ('Citizen id is: '|| citizen_id); DBMS_OUTPUT.PUT_LINE ('Citizen name is: '|| citizen_name); EXCEPTION WHEN no_data_found THEN dbms_output.put_line ('No citizen detail found'); WHEN others THEN dbms_output.put_line ('Errors'); END; /
The output of the above code:
In the above code, as there are no citizens with age 9 in the CITIZEN table, a runtime exception is raised.
Raise Exception In PL/SQL
An exception can be raised by the developers explicitly with the help of the keyword RAISE.
The syntax for exception raising:
DECLARE exception_n EXCEPTION; BEGIN IF condition THEN RAISE exception_n; END IF; EXCEPTION WHEN exception_n THEN << exception handling code >>; END;
Here the exception_n is the name of the exception that we are raising. In this way, we can raise both user-defined and system-defined exceptions.
The developers can build their own exceptions and use them for handling errors. They can be created in the declaration part of a subprogram and can be accessed only inside that subprogram.
An exception that is created at the package level can be used whenever the package is accessed. A user-defined exception can be raised by using the RAISE keyword.
The syntax for a user-defined error is:
DECLARE exception_n EXCEPTION;
Here, the exception_n is the name of the exception that we are raising. Thus we can declare an exception by giving a name followed by the EXCEPTION keyword. An exception can be declared in a similar manner like variables are declared. However, an exception is an unexpected condition and not a data item.
Scope of Exception in PL/SQL:
The rules of scope for PL/SQL exception are listed below:
- An exception cannot be declared more than once in the same block of code. But we can declare the same exception in two different blocks of code.
- An exception declared within a block of code is local within that block and global to every sub-block.
- If we again declare a global exception inside a sub-block, the local declaration overrules.
Let us again consider a table which is named CITIZEN.
Code Implementation with user defined exceptions:
DECLARE firstexception EXCEPTION; j NUMBER; BEGIN FOR j IN (SELECT * FROM CITIZEN) LOOP IF j.ID = 8 THEN RAISE firstexception; END IF; END LOOP; EXCEPTION WHEN firstexception THEN dbms_output.put_line ('Citizen with ID 8 already exist in table.'); END; /
The output of the above code:
In violation of database rules or going beyond the system-dependent threshold, an internal exception is thrown automatically. All these errors have a unique number and every exception is defined as a name. For example, PL/SQL throws NO_DATA_FOUND predefined exception when a SELECT query fetches no rows.
All the predefined exceptions of PL/SQL are declared in the STANDARD package which deals with the PL/SQL environment.
|Exception||Oracle Error||SQL CODE||Description|
|ACCESS_INTO_NULL||ORA - 06530||-6530||This exception is raised if a null object is naturally assigned a value.|
|CASE_NOT_FOUND||ORA - 06592||-6592||This exception is raised if none of the options in the WHEN clause is chosen and there is no existence of an ELSE clause.|
|COLLECTION_IS_NULL||ORA - 06531||-6531||This exception is raised when the code tries to apply collection methods except EXISTS to a nested table or varray which is not initialized. It can also be raised if our code tries to assign values to a nested table or varray which is not initialized.|
|DUP_VAL_ON_INDEX||ORA - 00001||-1||This exception is raised if duplicate values are tried to be stored in a column that is constrained by a unique index.|
|CURSOR_ALREADY_OPEN||ORA - 06511||-6511||This exception is raised if our code tries to open an already open cursor.|
|INVALID_CURSOR||ORA - 01001||-1001||This exception is raised if we try to do some operations on cursors which are not permitted. For example, attempting to close an already closed cursor.|
|INVALID_NUMBER||ORA - 01722||-1722||This exception is raised if the conversion to a character string to a number does not pass as the string is representing an invalid number.|
|LOGIN_DENIED||ORA - 01017||-1017||This exception is raised if the program tried to log on to the database with an improper username and password.|
|NO_DATA_FOUND||ORA - 01403||100||This exception is raised if a SELECT query fetches no rows.|
|NOT_LOGGED_ON||ORA - 01012||-1012||This exception is raised if we attempt to do a database operation without connecting to it.|
|PROGRAM_ERROR||ORA - 06501||-6501||This exception is raised if the PL/SQL program has encountered an internal error.|
|ROWTYPE_MISMATCH||ORA - 06504||-6504||This exception is raised if a cursor attempts to return values to a variable with incompatible data type.|
|SELF_IS_NULL||ORA - 30625||-30625||This exception is raised if a member method is called without initializing its object type.|
|STORAGE_ERROR||ORA - 06500||-6500||This exception is raised if the PL/SQL program ran out of memory or its memory got corrupted while executing.|
|TOO_MANY_ROWS||ORA - 01422||-1422||This exception is raised if a SELECT query fetches multiple rows.|
|SUBSCRIPT_BEYOND_COUNT||ORA - 06533||-6533||This exception is raised if a subscript is more than the total count of the number of elements in the collection.|
|SUBSCRIPT_OUTSIDE_LIMIT||ORA - 06532||-6532||This exception is raised if a subscript is beyond the threshold range.|
|SYS_INVALID_ROWID||ORA-01410||-1410||This exception is raised if the conversion to a character string to a universal row id does not pass as the character string is representing an invalid row id.|
|TIMEOUT_ON_RESOURCE||ORA-00051||-51||This exception is raised if Oracle is waiting for a resource.|
|VALUE_ERROR||ORA-06502||-6502||This exception is raised if a mathematical, conversion, truncation error happens in our program.|
|ZERO_DIVIDE||ORA-01476||-1476||This exception is raised if we try to divide a number by 0.|
Frequently Asked Questions And Answers
Q #1) What is a predefined exception in PL/SQL?
Answer: A predefined exception is thrown by PL/SQL automatically if there is a violation of the rules of the database by a program.
Q #2) What is PL/SQL exception handling and why it is needed?
Answer: An error or unexpected event in the program is called an Exception. It halts the normal flow of execution of the program. In PL/SQL, the exception is handled within an EXCEPTION block in the code and proper action against the exception is described inside that block.
Q #3) What is meant by exception handling?
Answer: It is the method of responding to unexpected events that may occur while executing the code.
Q #4) What are the two forms of error handling?
Answer: The two forms of error handling include compile-time error and run time error. The run time error happens during the execution of the program due to improper data or logic. The compile-time errors are caught during the compilation of our code.
Q #5) What is the difference between error and exception?
Answer: Exceptions are incidents that are handled with the help of try/catch block and can be encountered both in the compile-time and run-time of the program execution. The errors, on the other hand, are encountered at the run time only.
In this tutorial, we have discussed in detail some basic concepts of PL/SQL that are essential to managing the exceptions in our programs.
We have covered the following topics listed below:
- Types of Exceptions.
- Some predefined Exceptions.
In the next tutorial, we shall discuss the PL/SQL transactions, OOPS concepts, and many more connected topics.
<<PREV Tutorial | FIRST Tutorial>>