DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Handling exception no_data_found

  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Handling exception no_data_found

    I've just started using oracle but am having trouble with syntax when handling exceptions.
    I am using a function similiar to this basic one but it's not quite working saying "Function returned without a value". It should be returning values though.

    I'm pretty sure it has something to do with the exception part..any ideas?

    CREATE check (vstreet IN CHAR)
    RETURN CHAR
    IS v_test VARCHAR2(20);
    BEGIN

    SELECT INTO v_test
    FROM house
    WHERE street = vstreet;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_test:= 'notfound';


    RETURN (v_test);
    END;

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    CREATE check (vstreet IN CHAR)
    RETURN CHAR
    IS v_test VARCHAR2(20);
    BEGIN

    SELECT INTO v_test
    FROM house
    WHERE street = vstreet;

    return v_test; --there should be return coded here

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_test:= 'notfound';


    RETURN (v_test);
    END;


    ideally it should be something like this

    CREATE or replace function check1(vstreet IN CHAR)
    RETURN VARCHAR2 is
    v_test VARCHAR2(20);
    BEGIN
    SELECT account_idx INTO v_test
    FROM account
    WHERE account_idx=100;
    return v_test;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_test:= 'notfound';
    RETURN (v_test);
    END;

    go here fro some nice tutorials

    http://www.hot-oracle.com/index.html

    regards
    Hrishy
    Last edited by hrishy; 03-18-2004 at 08:44 AM.

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Or ...

    CREATE or replace function check1(vstreet IN CHAR)
    RETURN VARCHAR2 is
    v_test VARCHAR2(20);
    CURSOR c1 IS
    SELECT account_idx
    FROM account
    WHERE account_idx=100;
    BEGIN
    OPEN c1;
    FETCH c1 INTO v_test;
    IF c1%FOUND THEN
    RETURN v_test;
    ELSE
    RETURN 'Not Found';
    END IF;
    CLOSE c1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'Error in Function.';
    END;

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by JMac
    Or ...

    CREATE or replace function check1(vstreet IN CHAR)
    RETURN VARCHAR2 is
    v_test VARCHAR2(20);
    CURSOR c1 IS
    SELECT account_idx
    FROM account
    WHERE account_idx=100;
    BEGIN
    OPEN c1;
    FETCH c1 INTO v_test;
    IF c1%FOUND THEN
    RETURN v_test;
    ELSE
    RETURN 'Not Found';
    END IF;
    CLOSE c1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'Error in Function.';
    END;
    Yuk.

    Except for just returning the 'Not Found' textdirectly .. I seeno reason for ...
    Code:
    v_test:= 'notfound';
    RETURN (v_test);
    ... when ...
    Code:
    Return 'notfound';
    would do just as well.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Yuk.
    I was merely playing with the previous function and not supplying a definitive answer, O Wicked Witch of the West!

  6. #6
    Join Date
    Mar 2004
    Posts
    5

    Thumbs up

    awesome thanks guys. Thats my first hurdle overcome

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by JMac
    Or ...

    CREATE or replace function check1(vstreet IN CHAR)
    RETURN VARCHAR2 is
    v_test VARCHAR2(20);
    CURSOR c1 IS
    SELECT account_idx
    FROM account
    WHERE account_idx=100;
    BEGIN
    OPEN c1;
    FETCH c1 INTO v_test;
    IF c1%FOUND THEN
    RETURN v_test;
    ELSE
    RETURN 'Not Found';
    END IF;
    CLOSE c1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'Error in Function.';
    END;

    What's the point of using a cursor at all?!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    What's the point of using a cursor at all?!
    Well, if the function is supposed to return something from the database (account_idx from table account in JMac's case), then there is no other way but to use a cursor. Now, whether you use explicit or implicit cursor, that's for another topic...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well what I mean is why you cannot use a simple select into, if there are more than one rows returns then just use rownum < 2

  10. #10
    Join Date
    Mar 2004
    Posts
    5
    One more question I have.

    Can a function return more than one variable?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width