Stored Procedure cannot EXCEPTION WHEN NO_DATA_FOUND THEN?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Stored Procedure cannot EXCEPTION WHEN NO_DATA_FOUND THEN?

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Posts
    3

    Question

    Hi everyone,
    I've a stored procedure which have a
    EXCEPTION WHEN NO_DATA_FOUND THEN code in it, but it seems like the EXCEPTION is not working as the msg I put doesn't display at all... here is the code:

    PROCEDURE UpdateInventoryCurrPrice
    (nItemId IN NUMBER) IS
    CURSOR InventoryCursor IS
    SELECT ItemSize, InvId, Curr_Price FROM Inventory
    WHERE ItemId = nItemId ORDER BY ItemSize;
    InventoryRow InventoryCursor%ROWTYPE;
    BEGIN
    OPEN InventoryCursor;
    LOOP
    FETCH InventoryCursor INTO InventoryRow;
    EXIT WHEN InventoryCursor%NOTFOUND;
    -- ..... other stuff here
    END LOOP
    EXCEPTION WHEN NO_DATA_FOUND THEN
    TEXT_IO.PUT_LINE ('No data returned for this ItemId ' ||
    nItemId);
    END;

    How come I cannot see the msg, even I know the value I pass in is not inside the table... can someone pls. help me?

    Thanks.
    Regards,
    Wai Chong

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There is no exception to be raised! When your explict cursor returns no rows you handle this with "EXIT WHEN ..." inside your fetch loop, so there realy is no unhandled exception in your case. If you want to display a message when there is no records returned you should use cursor's ROWCOUNT pseudocolumn outside your loop before you close your cursor:

    OPEN InventoryCursor;
    LOOP
    FETCH InventoryCursor INTO InventoryRow;
    EXIT WHEN InventoryCursor%NOTFOUND;
    -- ..... other stuff here
    END LOOP;
    IF InventoryCursor%ROWCOUNT = 0 THEN ....;
    END IF;
    CLOSE InventoryCursor;
    ....


    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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