
November 8th, 2005, 08:03 PM
|
|
Contributing User
|
|
Join Date: Apr 2004
Location: Australia
Posts: 37
Time spent in forums: 5 h 13 m 4 sec
Reputation Power: 5
|
|
|
calling a function from a procedure pl/SQL
im not quite sure what im doing wrong with this procedure. Errors say that i cannot call the function getprice from the inside of SQL. but i dont know where else to do it .
Code:
CREATE OR REPLACE
PROCEDURE createorderitem(p_orderid number,p_itemid ITEM.itemid%TYPE,qty NUMBER) IS
-- procedure to create an order item for specifi orderid and custid
v_orderid NUMBER;
v_itemid ITEM.itemid%TYPE;
v_QTY NUMBER;
INVALID_ORDER_ID EXCEPTION;
BEGIN
SELECT orders.orderid INTO v_orderid
FROM orders
WHERE p_orderid=orders.orderid;
IF SQL%NOTFOUND THEN
RAiSE INVALID_ORDER_ID;
END IF;
SELECT item.itemid INTO v_itemid
FROM item
WHERE p_itemid=item.itemid;
IF SQL%NOTFOUND THEN
RAiSE INVALID_ITEM_ID;
END IF;
INSERT INTO orderitem
VALUES (v_orderid, v_itemid, qty, getprice(v_itemid));
EXCEPTION
WHEN INVALID_ORDER_ID THEN
dbms_output.put_line('INVALID ORDER ID');
WHEN INVALID_ITEM_ID THEN
dbms_output.put_line('INVALID ITEM ID');
END createorderitem;
/
any help would be super
|