Page 223
You get:
GARBAGE PL/SQL procedure successfully completed.
This was done to prove a point: If the FETCH fails, it does not overwrite what it's fetching into.
Using Implicit CursorsImplicit cursors are relatively low maintenance. They are used only when you're expecting to get a single row. If more than one row is found, the exception TOO_MANY_ROWS is raised, and you get nothing for your trouble. Listing 10.21 reveals an implicit cursor in action.
Listing 10.21impcur.sqlA Simple Implicit Cursor
DECLARE -- record variable definitions get_tables_rec all_tables%ROWTYPE; -- based on the table -- local variables local_owner all_tables.owner%TYPE := `DEMO'; -- search criteria local_table all_tables.table_name%TYPE := `CUSTOMER'; BEGIN SELECT * INTO get_tables_rec -- looking for a single row FROM ALL_TABLES WHERE owner = local_owner AND table_name = local_table; DBMS_OUTPUT.put_line(get_tables_rec.tablespace_name); END; /
And you get:
USER_DATA PL/SQL procedure successfully completed.
This is what happens if you don't construct your search so as to return exactly one row (see Listing 10.22).
Listing 10.22toomany.sqlThe Implicit Cursor Fails when Too Many Rows Are Found
DECLARE -- record variable definitions get_tables_rec all_tables%ROWTYPE; -- based on table -- local variables local_owner all_tables.owner%TYPE := `DEMO'; BEGIN SELECT * INTO get_tables_rec FROM ALL_TABLES WHERE owner = local_owner; DBMS_OUTPUT.put_line(get_tables_rec.tablespace_name); END; /
Page 224
The server complains with a meaningful error message.
ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 7
Here's what really happened:
TIP |
Oracle always does two fetches on an implicit cursor, just to see if it would return another row (and hence fail the requirement of an implicit cursor to return exactly one row). This is an ANSI requirement. |
When all you want is one row, and you don't want to mess with exceptions, use an explicit cursor and do the one FETCH yourself. If you want to know if there might be more than one row, and you don't mind handling the exception, then use an implicit cursor. In fact, you can use implicit cursors in single-row lookup routines for just this reason. You need to know if the single-row lookup will actually return more than one row, which you perceive as an error. If you only cared about getting the one row, you'd do it more efficiently with an explicit cursor.
Implicit cursors can't be used with an array to handle the extra rows. By definition, all they are supposed to return is a single row.
An exception is a non-fatal event that immediately interrupts normal program execution and causes an unconditional branch to the current block's exception handler. Some exceptions, like ROW_NOT_FOUND or TOO_MANY_ROWS, are events that may be considered a normal part of processing. Exceptions like VALUE_ERROR indicate a program bug or some unexpected event. Yet other exceptions indicate a severe problem, such as running out of memory.
If no exception handlers are defined for a block, the exception is returned to the next higher block (if any). The exception "bubbles up" through enclosing blocks until either an exception handler is found for the block, or control is returned to the calling context (in our case here, SQL*Plus).
Your programs will experience exceptions all the time. All you need is an exception handler, to handle them. Refer again to the implicit cursor that blew up last time (see Listing 10.23).
Listing 10.23graceful.sqlUsing an Exception Handler
DECLARE -- record variable definitions get_tables_rec all_tables%ROWTYPE; -- based on table -- local variables
Page 225
local_owner all_tables.owner%TYPE := `DEMO'; status NUMERIC := 0; -- capture error code (initialize to OK) BEGIN SELECT * INTO get_tables_rec FROM ALL_TABLES WHERE owner = local_owner; DBMS_OUTPUT.put_line(get_tables_rec.tablespace_name); EXCEPTION WHEN TOO_MANY_ROWS THEN status := SQLCODE; DBMS_OUTPUT.put_line(`get_tables: ` || SQLERRM(status)); DBMS_OUTPUT.put_line(`get_tables: Exiting gracefully.'); END; /
This time, the server gives us:
get_tables: ORA-01422: exact fetch returns more than requested number of rows
get_tables: Exiting gracefully. PL/SQL procedure successfully completed.
A variable is defined to store the potential error code on line 6.
A WHEN clause is used in the exception section to specify the exception on line 12.
The error code is trapped in line 13.
The error message associated with the error code is displayed in line 14.
The server now tells you that the program completed successfully. Had the error not occurred, the SQLCODE would have been set to zero, which means OK. SQLCODE is actually a built-in function that returns the SQL status.
Table 10.8 is a list of all the pre-defined exceptions.
Table 10.8 Exceptions That Can Be Explicitly Tested for in Exception Block (WHEN THEN)
Exception Name | Oracle Error | SQLCODE Value |
CURSOR_ALREADY_OPEN | ORA-06511 | -6511 |
DUP_VAL_ON_INDEX | ORA-00001 | -1 |
INVALID_CURSOR | ORA-01001 | -1001 |
INVALID_NUMBER | ORA-01722 | -1722 |
LOGIN_DENIED | ORA-01017 | -1017 |
NO_DATA_FOUND | ORA-01403 | +100 |
NOT_LOGGED_ON | ORA-01012 | -1012 |
continues
Page 226
Table 10.8 Continued
Exception Name | Oracle Error | SQLCODE Value |
PROGRAM_ERROR | ORA-06501 | -6501 |
STORAGE_ERROR | ORA-06500 | -6500 |
TIMEOUT_ON_RESOURCE | ORA-00051 | -51 |
TOO_MANY_ROWS | ORA-01422 | -1422 |
TRANSACTION_BACKED_OUT | ORA-00061 | -61 |
VALUE_ERROR | ORA-06502 | -6502 |
ZERO_DIVIDE | ORA-01476 | -1476 |
Notice how most of the exceptions have negative SQLCODE values, with NO_DATA_FOUND being the conspicuous exception (no pun intended). This is because the ANSI standard calls for the SQLCODE value to be +100 when no row is found, even as the Oracle error code is negative. And Oracle is an ANSI-compliant database. You might say it makes them ANSI (bad pun intended).
If the exception you anticipate is not covered by the above list, or you're not sure which exceptions might occur, or you don't even care except to handle any of them, you can use the WHEN OTHERS THEN clause. This is the catch-all that allows your exception handler to handle any error that might arise.
You can also use any combination of exception names, and end the list with the WHEN OTHERS THEN clause. They act together like an IF ELSIF ELSE group of statements. If any one of them is true, its corresponding code is executed, and the block is exited.
EXCEPTION WHEN NO_DATA_FOUND THEN -- row not found! -- do this and skip to end of block WHEN ZERO_DIVIDE THEN -- divide by zero! -- do that and skip to end of block WHEN VALUE_ERROR THEN -- 10 lbs of data in a 5lb var -- do these and skip to end of block WHEN OTHERS THEN -- dunno, don't care -- do the other and skip to end of block END;
TIP |
Always code a WHEN OTHERS clause at the end of your exception handler. Sometimes it'll be the only handler. |