Exception Handling in PL/SQL
An exception is an error situation which may arise during program execution
PLSQL supports Oracle named (predefined) and unnamed (not predefined) exceptions as well as user-defined exceptions
In any type of Exception, when the exception takes place the exception is said to be “RAISED”
An Exception Handler is used to specify the response for a raised exception
When an exception is raised, either implicitly or explicitly, the normal execution of the program is abandoned
The control then shifts to the corresponding exception handler if it is present else control shifts to the calling environment
In any case, when an exception is fired then the control can not return to the executable section of the current block
If an exception is handled then the program unit is considered as successful i. e. the effect of the operations performed before the exception is raised, is saved else the earlier operations are rolled back
If an exception is raised in the executable section and there is an associated handler, the exception is trapped (handled)
Handlers for various exceptions can be specified in the EXCEPTION section
A handler for an exception is of the form
WHEN exception_name THEN
a sequence of statements
Example
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('WRONG INPUT');
END;
Types of Exceptions
Named (predefined) Oracle Exceptions
Unnamed (Not predefined) Oracle Exceptions
User-defined Exceptions
Named Exceptions
Oracle has around 20 named predefined exceptions
Trapped within the Exception handling block
Some commonly encountered oracle named exceptions:
NO_DATA_FOUND – fired when SELECT INTO statement does not return any row
TOO_MANY_ROWS - fired when SELECT INTO statement returns more than one row
INVALID_CURSOR – fired when trying to FETCH from a cursor that is not open
ZERO_DIVIDE – fired when trying to divide by zero
VALUE_ERROR – fired when trying to put an incompatible value in a variable or column
Raising an Exception
An exception is raised implicitly if any Oracle error occurs e. g. when no rows are retrieved by SELECT statement, PL/SQL raises the exception NO_DATA_FOUND
We raise an exception explicitly by issuing the RAISE statement within the block
Exception Handling: Example
DECLARE
myname VARCHAR2 (8);
BEGIN
SELECT ename
INTO myname
FROM emp
WHERE empno = &eno;
DBMS_OUTPUT.put_line('Name of the employee : ' || myname);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('WRONG EMPLOYEE NUMBER');
WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.put_line(' Employee Name size larger than 8');
END;
/
The OTHERS Handler
Most of the times Exceptions occur because of inappropriate values passed for user inputs
In a good program the probable exceptions are visualized and handled
However for a program, one may not be able to visualize all the probable exceptions
Handling all possible exceptions may make the program very bulky
To overcome this problem a “General Exception Handler” named “OTHERS” is provided by PL/SQL
The handler OTHERS traps exceptions not handled in the other handlers in the exception handling section of the block
Thus in a program separate handlers are written for those named exceptions, that require some specific actions to be taken when the exception is fired while for all the remaining exceptions, the general OTHERS handler is used
Guidelines for Trapping Exceptions
WHEN OTHERS should be put up as the last handler
Handlers for several exceptions can be included in a single block but for a single exception only one handler can be written
At a time, there can be only one active exception
Various Predefined Named Exceptions: Example
DECLARE
myjob emp.job%TYPE;
emprec emp%ROWTYPE;
BEGIN
myjob := '&myJob';
SELECT *
INTO emprec
FROM emp
WHERE UPPER (job) = UPPER (myjob);
DBMS_OUTPUT.put_line(emprec.ename || ' has salary ' || emprec.sal);
EXCEPTION
-- Various error handlers for predefined named exceptions
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('The job does not exist');
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line('More than one employee with the job');
/* The handler OTHERS traps exceptions not handled in the other handlers in the exception handling section*/
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Other error ');
END;
/
Functions for Trapping Error Code and Message
There are two built-in functions which can be used to find out which exception occurred during an execution of a program unit
SQLCODE
Returns the error number of the corresponding Oracle error that was raised
Returns 1 for user-defined exception
SQLERRM
Returns the error message associated with the given error number
Returns “USER DEFINED EXCEPTION” for user-defined exceptions
SQLCODE and SQLERRM: Example
CREATE TABLE ERRORS (
ercode NUMBER,
ermessage VARCHAR2(512));
DECLARE
v_code NUMBER;
v_errm VARCHAR2 (512);
myno NUMBER (1);
BEGIN
myno := '&myNo';
EXCEPTION
WHEN OTHERS
THEN
v_code := SQLCODE;
v_errm := SQLERRM (SQLCODE);
DBMS_OUTPUT.put_line(SQLERRM);
INSERT INTO ERRORS
VALUES (v_code, v_errm);
END;
/
Note: SQLCODE and SQLERRM being procedural statements can not be directly used inside SQL statement; hence their values are assigned to variables first and then used in INSERT statement
Unnamed Oracle Exceptions
Unnamed exceptions can be trapped by using OTHERS handler
Unnamed exceptions can be handled by PRAGMA EXCEPTION_INIT which tells the
compiler to associate an exception name with an oracle error number
PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL block is executed
PRAGMA EXCEPTION_INIT: Example
DECLARE
e_emp_exist EXCEPTION;
--Naming non-predefined oracle error
PRAGMA EXCEPTION_INIT (e_emp_exist, -2292);
v_deptno dept.deptno%TYPE := &dno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
COMMIT;
EXCEPTION
WHEN e_emp_exist
THEN
DBMS_OUTPUT.put_line('Can not delete dept Employees exist');
END;
User-defined Exceptions
Declared in the Declare Section with data type as EXCEPTION
Raised Explicitly by using the RAISE keyword
Handled in the Exception Section
Local to a PL/SQL block
User-defined Exception: Example
DECLARE
-- Declaring user defined exception
e_check_sal EXCEPTION;
v_empno emp.empno%TYPE := &empno;
emp_sal emp.sal%TYPE := &empsal;
mgr_sal emp.sal%TYPE;
BEGIN
SELECT e1.sal
INTO mgr_sal
FROM emp e, emp e1
WHERE e.mgr = e1.empno AND e.empno = v_empno;
IF (emp_sal >mgr_sal)
THEN
RAISE e_check_sal; -- Raising user-defined exception
ELSE
UPDATE emp
SET sal = emp_sal
WHERE empno = v_empno;
END IF;
DBMS_OUTPUT.put_line('Salary updated');
COMMIT;
EXCEPTION
--Handling user defined exception
WHEN e_check_sal
THEN
DBMS_OUTPUT.put_line
('Employee salary can not be greater than Manager salary');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(' Wrong input');
END;
Exception Propagation
Exceptions Raised in the Executable Section
PL/SQL uses the following rule to determine which exception handler to invoke:
Current block has a handler for the exception, execute it and complete the block successfully. Control then passes to the enclosing block.
No handler for current exception, propagate the exception by raising it in the enclosing block. Step 1 is executed for the enclosing block. If there is no enclosing block, the exception will be propagated out to the calling environment, such as SQL* Plus.
Exceptions Raised in the Executable Section: Example 1
Exceptions Raised in the Executable Section: Example 2
Exceptions Raised in Declarative Section: Example 1
Exceptions Raised in Declarative Section:Example 2
Exceptions Raised in Exception Section:Example 1
Example – Exception Propagation
BEGIN
UPDATE dept
SET loc = 'New1'
WHERE deptno = 10;
DECLARE
myjob emp.job%TYPE := '&myjob';
emprec emp%ROWTYPE;
BEGIN
UPDATE dept
SET loc = 'New2'
WHERE deptno = 20;
SELECT *
INTO emprec
FROM emp
WHERE job = UPPER (myjob);
DBMS_OUTPUT.put_line(emprec.ename || ' has salary ' || emprec.sal);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('The job does not exist');
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line('More than one employee with the job');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Other error ' || SQLERRM);
END;
UPDATE dept
SET loc = 'New3'
WHERE deptno = 30;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('The inner block error is trapped here as raised in
DECLARE section'
);
END;
Hello Buddy,
ReplyDeleteLove it absolutely! So crystalline. No mumbo jumbo. No non-sense. Straight and simple. You guys need a standing ovation for your good work.
I want to update the whole table (shown in the picture) per id. As it shown in the picture I need to calculate value for columns in each row for each user_id :
interim balance = opening_balance + Debit+ Credit
calculated_interest = (opening_balance + Debit+ Credit) * interest_rate
closing_balance = interim balance + calculated_interest
The opening_balance for each row is the closing_balance from the previous row. Therefore, the opening_balance for the next row depends on calculation of the previous row.
I have tried to using lagging but since the calculation for previous row is not done, all values become 0.
Here is the code I have tried and it is not working.
UPDATE M
SET M.closing_balance = res.closing_balance + res.Credit + res.Debit
FROM [Test].[dbo].[Main] M
JOIN
(SELECT
a.[User_ID], a.[calender_day], a.[Debit], a.[Credit],
LAG(closing_balance, 1, (a.[opening_balance] + a.[Debit] + a.[Credit])) OVER (PARTITION BY [User_ID] ORDER BY [calender_day] ASC) AS closing_balance
FROM
[Test].[dbo].[Main] a) res ON res.[User_ID] = M.User_ID
AND res.[calender_day] = M.[calender_day]
User_ID calender_day Credit Debit opening_balance interim_balance calculated_interest closing_balance interest_rate
1 11/1/2017 0 0 100 0 0 0 2.5
1 11/2/2017 0 0 0 0 0 0 2.5
1 11/3/2017 0 0 0 0 0 0 2.5
1 11/4/2017 -3 0 0 0 0 0 2.5
1 11/5/2017 0 0 0 0 0 0 3.5
1 11/6/2017 0 0 0 0 0 0 3.5
1 11/7/2017 -4 0 0 0 0 0 2.5
1 11/8/2017 0 0 0 0 0 0 2.5
1 11/9/2017 0 0 0 0 0 0 4.5
1 11/10/2017 0 0 0 0 0 0 4.5
2 11/1/2017 0 0 200 0 0 0 1.1
2 11/2/2017 0 0 0 0 0 0 1.1
2 11/3/2017 -3 0 0 0 0 0 1.1
2 11/4/2017 0 4 0 0 0 0 1.1
2 11/5/2017 0 0 0 0 0 0 1.1
2 11/6/2017 0 0 0 0 0 0 1.1
2 11/7/2017 0 0 0 0 0 0 1.1
2 11/8/2017 0 0 0 0 0 0 2
2 11/9/2017 0 0 0 0 0 0 2
2 11/10/2017 0 0 0 0 0 0 2
3 11/1/2017 0 0 400 0 0 0 3.3
3 11/2/2017 -2 0 0 0 0 0 2.1
3 11/3/2017 0 0 0 0 0 0 3.1
3 11/4/2017 0 5 0 0 0 0 3.1
3 11/5/2017 0 0 0 0 0 0 3.1
3 11/6/2017 0 0 0 0 0 0 3.1
3 11/7/2017 -3 0 0 0 0 0 3.1
3 11/8/2017 0 0 0 0 0 0 3.1
3 11/9/2017 0 0 0 0 0 0 3.1
Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.
Ciao,
Hi There,
ReplyDeleteYour blog is such a complete read. I like your approach with Oracle Exceptions. Clearly, you wrote it to make learning a cake walk for me.
Greetings of the day!
I need a help. I am working on a report now, in which I am using a materialized view, while refreshing it I am getting this error "out of process memory when trying to allocate 1094464 bytes", I have tried extending the temp table space memory but found the same error.
I look forward to see your next updates.
Grazie,
Neha
Ohayo,
ReplyDeleteBrilliant article, glad I slogged through the Oracle Exceptions it seems that a whole lot of the details really come back to from my past project.
Please, can you advise me on this query if it is correct? I have been asked to produce a report of vacation by staff for the year 2017 and group by line manager. The question is looking at this query, I have ordered by line manager which I think is showing staff under each manager but I am not sure If I need to break on the line manager and how to accomplish this. Please note that I inherited these table definitions.
By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
Many Thanks,
Radhey