User-Defined Exception Help
811155Nov 8 2010 — edited Nov 8 2010Alright, so here's the task at hand:
"Create a PL/SQL block that can be used to retrieve the description and price of an inventory item based on its item ID. Include the appropriate exception handling to display a message if the item is not found."
I know NO_DATA_FOUND only works with implicit cursors. The issue is that the tables I'm supposed to use has multiple entries of a single particular item ID's, so when I try to pull a valid item_id value, it throws an error since implicit cursors are supposed to retrieve only 1 row.
If I try to use an explicit cursor, NO_DATA_FOUND isn't raised since "no rows returned" is considered to be valid. I've tried creating user-defined exceptions but I can't figure out how to do it and the text book doesn't really help nor has any Google/Yahoo searches.
Here's the code I have so far. It will display the output correctly when the item ID entered is valid. If it's an invalid item ID, it just tells me the procedure is successfully completed without raising the exception. I'm guessing my IF condition is wrong so if anyone can help me it'd be greatly appreciated. Thanks.
DECLARE
v_item_id NUMBER := &v_item_id;
CURSOR item_cursor IS
SELECT item.item_id, item_desc, color, inv_size, inv_price
FROM inventory, item
WHERE inventory.item_id = item.item_id
AND item.item_id = v_item_id;
item_info item_cursor%ROWTYPE;
e_no_rows EXCEPTION;
BEGIN
OPEN item_cursor;
LOOP
FETCH item_cursor INTO item_info;
EXIT WHEN item_cursor%NOTFOUND;
IF item_info.item_id IS NULL THEN
RAISE e_no_rows;
ELSE
DBMS_OUTPUT.PUT_LINE(item_info.item_desc || ' ' || item_info.color || ' ' || item_info.inv_size || ' ' || TO_CHAR(item_info.inv_price, '$999.99'));
END IF;
END LOOP;
CLOSE item_cursor;
EXCEPTION
WHEN e_no_rows THEN
DBMS_OUTPUT.PUT_LINE('Item ID #' || v_item_id || ' not found.');
DBMS_OUTPUT.PUT_LINE('Please specify a valid Item ID.');
END;