#

| Chapter 8 Exception Handlers |

## 8.8 NO_DATA_FOUND: Multipurpose Exception

The NO_DATA_FOUND exception is raised under three different circumstances:

- An implicit query returns no data.

- You attempt to reference a row in a PL/SQL table which has not been defined. (PL/SQL tables are covered in Chapter 10, PL/SQL Tables.)

- You attempt to read past the end of an operating system file (using TEXT_IO in the Oracle Developer 2000 environment or UTL_FILE in PL/SQL Release 2.3).

This overlapping use of the same exception could cause some confusion and difficulty in your program. Suppose that in a single PL/SQL block I query from an implicit cursor and also make references to a PL/SQL table's rows. The NO_DATA_FOUND exception could be raised from either source, but the actual problem that caused the exception would be very different: bad data in the database (raised by the implicit cursor) versus an illegal memory reference (raised by the table access).

I want to be able to distinguish between the two situations. I can accomplish this by nesting the SELECT statement (the implicit cursor) inside its own PL/SQL block and thus trapping the NO_DATA_FOUND exception distinct from the PL/SQL table exception.

In the version of company_name shown in the following example, I have added a parameter to specify two types of access: from database (access type = DBMS) or from a PL/SQL table (access type = MEMORY). I want to check for NO_DATA_FOUND for each particular instance:

>     FUNCTION company_name
>        (id_in IN NUMBER, access_type_in IN VARCHAR2)
>     RETURN VARCHAR2
>     IS
>        /* Return value of the function */
>        return_value VARCHAR2 (60);

>        /* My own exception - used to represent bad data NO_DATA_FOUND. */
>        bad_data_in_select EXCEPTION;

>     BEGIN
>        /* Retrieve company name from the database */
>        IF access_type_in = 'DBMS'
>        THEN
>           /* Place the SELECT inside its own BEGIN-END. */
>           BEGIN
>              SELECT name INTO return_value
>                FROM company
>               WHERE company_id = id_in;
>              RETURN return_value;

>           /* Now it can have its OWN exception section too ! */
>           EXCEPTION
>              /* This NO_DATA_FOUND is only from the SELECT. */
>              WHEN NO_DATA_FOUND
>              THEN
>                 /*
>                 || Raise my exception to propagate to
>                 || the main body of the function.
>                 */
>                 RAISE bad_data_in_select;
>           END;

>        /* Retrieve company name from an in-memory PL/SQL table */
>        ELSIF access_type_in = 'MEMORY'
>        THEN
>           /*
>           || Direct access from table. If this ID is not defined
>           || then the NO_DATA_FOUND exception is raised.
>           */
>           RETURN company_name_table (id_in);
>        END IF;
>     EXCEPTION
>        /*
>        || This exception occurs only when NO_DATA_FOUND was raised by
>        || the implicit cursor inside its own BEGIN-END.
>        */
>        WHEN bad_data_in_select
>        THEN
>           DBMS_OUTPUT.PUT_LINE
>              (' Unable to locate company in database!');
>        /*
>        || This exception occurs only when I have not previously placed
>        || the company name for company id id_in in the table.
>        */
>        WHEN NO_DATA_FOUND
>        THEN
>           DBMS_OUTPUT.PUT_LINE
>              (' Unable to locate company in memorized table!');
>     END;

You can see how the scoping rules for exceptions provide a great deal of flexibility in managing the impact of exceptions. Whenever you want to isolate the effect of a raised exception, just nest the statements inside their own BEGIN-END, give them their own exception section, and then decide what you want to do when the problem occurs. You are guaranteed to trap it there first.

8.7 Client-Server Error Communication |  | 8.9 Exception Handler as IF Statement

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.