Anonymous

FUNCTION getPrice(item_Id IN ITEM.itemId%TYPE) RETURN NUMBER IS
	
        v_listprice item.listprice%TYPE;

        cursor getprice(item_id in number) is
        select listprice
        from item
        where itemid = item_id;

        invalid_item_id exception;
        pragma exception_inig(invalid_item_id, -20000);

BEGIN
        open getprice(item_id);
        fetch getprice into v_listprice;
        if getprice%notfound then
                raise invalid_item_id;
        end if;
        close getprice;
        RETURN v_listprice;

EXCEPTION
	WHEN INVALID_ITEM_ID THEN
		dbms_output.put_line('Item ID: ' || item_id || ' is invalid.');
  	        if getprice$isopen then
                      close getprice;
                end if;

END getPrice;
/