Previous | Table of Contents | Next

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.sql—A 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.sql—The 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:

  1. Oracle opened the cursor and fetched a row.
  2. Not satisfied, Oracle tried another fetch and got another row!
  3. Since you can't stuff ten pounds of rows in a five-pound variable, Oracle closed the cursor (well, it would have closed it anyway) and generated the exception.

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.

Handling Exceptions

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.sql—Using 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.

Previous | Table of Contents | Next