start page | rating of books | rating of authors | reviews | copyrights

Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 8.7 Client-Server Error Communication Chapter 8
Exception Handlers
Next: 8.9 Exception Handler as IF Statement
 

8.8 NO_DATA_FOUND: Multipurpose Exception

The NO_DATA_FOUND exception is raised under three different circumstances:

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.


Previous: 8.7 Client-Server Error Communication Oracle PL/SQL Programming, 2nd Edition Next: 8.9 Exception Handler as IF Statement
8.7 Client-Server Error Communication Book Index 8.9 Exception Handler as IF Statement

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference