Error Handling and Cursor
Error Handling
Compile-Time Warnings
While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation.
To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS
In the SQL*Plus environment, use the command SHOW ERRORS.
Compile-Time Warning Categories
By setting the compilation parameter PLSQL_WARNINGS, you can:
Enable and disable all warnings, one or more categories of warnings, or specific warnings.
You can set the value of PLSQL_WARNINGS
Use the ALTER SYSTEM statement
Example:Setting Value of PLSQL_WARNINGS Compilation Parameter
For the session, enable all warnings
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
For the session, enable PERFORMANCE warnings:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
For the session, disable all warnings:
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
Example:
CREATE OR REPLACE PROCEDURE p1
x CONSTANT BOOLEAN := TRUE;
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;
/
Result:
Errors for p1:
LINE/COL ERROR
-------- ------------------------------------------------
7/5 PLW-06002: Unreachable code
Overview of Exception Handling
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources.
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers.
syntax:
EXCEPTION
WHEN ex_name_1 THEN statements_1 -- Exception handler
WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler
WHEN OTHERS THEN statements_3 -- Exception handler
END;
When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part.
If ex_name_1 was raised, then statements_1 run.
If either ex_name_2 or ex_name_3 was raised, then statements_2 run.
If any other exception was raised, then statements_3 run.
After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:
If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
If the exception handler is in an anonymous block, then control transfers to the host environment .
Exception Categories
The exception categories are:
Internally defined
The runtime system raises internally defined exceptions implicitly (automatically). Examples of internally defined exceptions are ORA-00060 (deadlock detected while waiting for resource) and ORA-27102 (out of memory).
An internally defined exception always has an error code, but does not have a name unless PL/SQL gives it one or you give it one.
Predefined
A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR.
User-defined
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.
You must raise user-defined exceptions explicitly.
Exception Categories
Advantages of Exception Handlers
1)Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions.
2)Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it.
3)It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable (for example, bad data might be undetectable until you use it in a calculation).
4)Error-handling code is scattered throughout the program.
With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur.
5)In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it.
6)Error-handling code is isolated in the exception-handling parts of the blocks.
Predefined Exceptions
Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.
PL/SQL Predefined Exceptions
Example: calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE and the executable part of the block transfers control to the exception-handling part.
Example: Anonymous Block Handles ZERO_DIVIDE
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio := stock_price / net_earnings; -- raises ZERO_DIVIDE exception
DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
pe_ratio := NULL;
END;
/
Result:
Company had zero earnings.
Example: Anonymous Block Avoids ZERO_DIVIDE
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio :=
CASE net_earnings
WHEN 0 THEN NULL
ELSE stock_price / net_earnings
END;
END;
/
User-Defined Exceptions
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.
An exception name declaration has this syntax:
exception_name EXCEPTION;
You must raise a user-defined exception explicitly.
Redeclared Predefined Exceptions
Oracle recommends against redeclaring predefined exceptions—that is, declaring a user-defined exception name that is a predefined exception name.
If you redeclare a predefined exception, your local declaration overrides the global declaration in package STANDARD. Exception handlers written for the globally declared exception become unable to handle it.
Example:Redeclared Predefined Identifier
CREATE TABLE t (c NUMBER);
In the following block, the INSERT statement implicitly raises the predefined exception INVALID_NUMBER, which the exception handler handles.
DECLARE
default_number NUMBER := 0;
BEGIN
INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999'));
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
INSERT INTO t VALUES(default_number);
END;
/
Result:
Substituting default value for invalid number.
The following block redeclares the predefined exception INVALID_NUMBER. When the INSERT statement implicitly raises the predefined exception INVALID_NUMBER, the exception handler does not handle it.
DECLARE
default_number NUMBER := 0;
i NUMBER := 5;
invalid_number EXCEPTION; -- redeclare predefined exception
BEGIN
INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999'));
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
INSERT INTO t VALUES(default_number);
END;
/
Result:
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
The exception handler in the preceding block handles the predefined exception INVALID_NUMBER if you qualify the exception name in the exception handler:
Example
DECLARE
default_number NUMBER := 0;
i NUMBER := 5;
invalid_number EXCEPTION; -- redeclare predefined exception
BEGIN
INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999'));
EXCEPTION
WHEN STANDARD.INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
INSERT INTO t VALUES(default_number);
END;
/
Result:
Substituting default value for invalid number.
Raising Exceptions Explicitly
Raising User-Defined Exception with RAISE Statement
the procedure declares an exception named past_due, raises it explicitly with the RAISE statement, and handles it with an exception handler.
Example: Declaring, Raising, and Handling User-Defined Exception
CREATE PROCEDURE account_status (
due_date DATE,
today DATE
) AUTHID DEFINER
IS
past_due EXCEPTION; -- declare exception
BEGIN
IF due_date < today THEN
RAISE past_due; -- explicitly raise exception
END IF;
EXCEPTION
WHEN past_due THEN -- handle exception
DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
BEGIN
account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'),
TO_DATE('09-JUL-2010', 'DD-MON-YYYY'));
END;
/
Result:
Account past due.
Raising Internally Defined Exception with RAISE Statement
Although the runtime system raises internally defined exceptions implicitly, you can raise them explicitly with the RAISE statement if they have names. above Table lists the internally defined exceptions that have predefined names. "Internally Defined Exceptions" explains how to give user-declared names to internally defined exceptions.
An exception handler for a named internally defined exception handles that exception whether it is raised implicitly or explicitly.
exception INVALID_NUMBER either explicitly or implicitly, and the INVALID_NUMBER exception handler always handles it.
Example: Explicitly Raising Predefined Exception
CREATE TABLE t (c NUMBER);
CREATE PROCEDURE p (n NUMBER) AUTHID DEFINER IS
default_number NUMBER := 0;
BEGIN
IF n < 0 THEN
RAISE INVALID_NUMBER; -- raise explicitly
ELSE
INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); -- raise implicitly
END IF;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
INSERT INTO t VALUES(default_number);
END;
/
BEGIN
p(-1);
END;
/
Result:
Substituting default value for invalid number.
BEGIN
p(1);
END;
/
Result:
Substituting default value for invalid number.
Propagation of Exceptions Raised in Declarations
An exception raised in a declaration propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block). Therefore, the exception handler must be in an enclosing or invoking block, not in the same block as the declaration.
In below Example the VALUE_ERROR exception handler is in the same block as the declaration that raises VALUE_ERROR. Because the exception propagates immediately to the host environment, the exception handler does not handle it.
Example: Exception Raised in Declaration is Not Handled
DECLARE
credit_limit CONSTANT NUMBER(3) := 5000; -- Maximum value is 999
BEGIN
NULL;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Exception raised in declaration.');
END;
/
Result:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2
Example: Exception Raised in Declaration is Handled by Enclosing Block
BEGIN
DECLARE
credit_limit CONSTANT NUMBER(3) := 5000;
BEGIN
NULL;
END;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Exception raised in declaration.');
END;
/
Result:
Exception raised in declaration.
Propagation of Exceptions Raised in Exception Handlers
An exception raised in an exception handler propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block). Therefore, the exception handler must be in an enclosing or invoking block.
In below Example when n is zero, the calculation 1/n raises the predefined exception ZERO_DIVIDE, and control transfers to the ZERO_DIVIDE exception handler in the same block. When the exception handler raises ZERO_DIVIDE, the exception propagates immediately to the invoker. The invoker does not handle the exception, so PL/SQL returns an unhandled exception error to the host environment.
In EXAMPLE: the exception-handling part of the procedure has exception handlers for user-defined exception i_is_one and predefined exception ZERO_DIVIDE. When the i_is_one exception handler raises ZERO_DIVIDE, the exception propagates immediately to the invoker (therefore, the ZERO_DIVIDE exception handler does not handle it). The invoker does not handle the exception, so PL/SQL returns an unhandled exception error to the host environment.
Example: Exception Raised in Exception Handler is Not Handled
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS
BEGIN
DBMS_OUTPUT.PUT_LINE(1/n); -- handled
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error:');
DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined'); -- not handled
END;
/
BEGIN -- invoking block
print_reciprocal(0);
END;
Result:
Error:
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "HR.PRINT_RECIPROCAL", line 7
ORA-01476: divisor is equal to zero
ORA-06512: at line 2
Example: Exception Raised in Exception Handler is Handled by Invoker
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS
BEGIN
DBMS_OUTPUT.PUT_LINE(1/n);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error:');
DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined');
END;
/
BEGIN -- invoking block
print_reciprocal(0);
EXCEPTION
WHEN ZERO_DIVIDE THEN -- handles exception raised in exception handler
DBMS_OUTPUT.PUT_LINE('1/0 is undefined.');
END;
/
Result:
Error:
1/0 is undefined.
Example 11-20 Exception Raised in Exception Handler is Handled by Enclosing Block
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(1/n);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error in inner block:');
DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined.');
END;
EXCEPTION
WHEN ZERO_DIVIDE THEN -- handles exception raised in exception handler
DBMS_OUTPUT.PUT('Error in outer block: ');
DBMS_OUTPUT.PUT_LINE('1/0 is undefined.');
END;
/
BEGIN
print_reciprocal(0);
END;
/
Result:
Error in inner block:
Error in outer block: 1/0 is undefined.
Unhandled Exceptions
If there is no handler for a raised exception, PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome.
If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram.
The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses. If one set of values raises an unhandled exception, then PL/SQL rolls back all database changes made earlier in the FORALL statement.
Cursors:
Overview of Cursor
Types of cursor
Invalid cursor Exception.
A cursor is a pointer to this context area.
PL/SQL controls the context area through a cursor.
A cursor holds the rows (one or more) returned by a SQL statement.
The set of rows the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time.
There are two types of cursors −
Implicit cursors
Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.
Programmers cannot control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement.
For INSERT operations, the cursor holds the data that needs to be inserted.
For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes –
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.
Example
We will be using the CUSTOMERS table we had created and used in the previous chapters.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The following program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
6 customers selected
PL/SQL procedure successfully completed.
If you check the records in customers table, you will find that the rows have been updated −
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area.
An explicit cursor should be defined in the declaration section of the PL/SQL Block.
It is created on a SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement;
Working with an explicit cursor includes the following steps −
Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −
OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −
CLOSE c_customers;
Example
Following is a complete example to illustrate the concepts of explicit cursors &minua;
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.
ORA-01001: invalid cursor
Oracle SQL Error: ORA-01001: invalid cursor
Cause:
Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE.
Solution:
Check the error call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.
Example:
DECLARE
CURSOR c1 IS SELECT * FROM books;
rec books%ROWTYPE;
BEGIN
LOOP
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec.NAME);
END LOOP;
END;
Output:
ORA-01001: invalid cursor
ORA-06512: at line 6
Correct
DECLARE
CURSOR c1 IS SELECT * FROM books;
rec books%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec.NAME);
END LOOP;
CLOSE c1;
END;
Output:
anonymous block completed
