NO_DATA_FOUND exception

From: David <auto90059_at_hushmail.com>
Date: Sat, 13 Sep 2003 21:52:10 +1000
Message-ID: <3f6304eb$0$10355$afc38c87@news.optusnet.com.au>

Basically the issue is this:

When I call CheckAuthor('invalid_name', 'valid_address') a NO_DATA_FOUND exception is raised. Fair enough, no row is returned by the select statement. But when I call CheckAuthor('valid_author', 'invalid_address'), no exception is raised at all even though, like in the first example, no row is returned.

Why on earth is this?

CREATE OR REPLACE FUNCTION CheckAuthor

    (author IN People.Name%TYPE,
    address IN People.Address%TYPE)
    RETURN BOOLEAN
IS

v_author        People.Name%TYPE;
v_address       People.Address%TYPE;

BEGIN
    SELECT People.Name, People.Address
    INTO v_author, v_address
    FROM People, Authors
    WHERE People.PersonID = Authors.AuthorID     AND address = People.Address
    AND author = People.Name;
    RETURN (v_author = author AND v_address = address); EXCEPTION
    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE ('EXCEPTION RAISED?');
        RETURN FALSE;

END;
/
show errors

• Table definitions

create table People (
PersonID INT PRIMARY KEY,
name varchar2(15),
address varchar2(15),
UNIQUE (name, Address)
);

create table Authors (
AuthorID INT UNIQUE REFERENCES People(PersonID), Published INT DEFAULT 0,
UnderReview INT DEFAULT 0
);
Received on Sat Sep 13 2003 - 06:52:10 CDT