-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|