Hi,
I have just started learning PL/SQL and am trying to implement a stored procedure that will check a condition, however it raises the error: "exact fetch returns more than requested number of rows"
This is my procedure:
CREATE OR REPLACE PROCEDURE INSERT_ORDER_DETAIL(p_order_id IN ORDER_DETAIL.ORDER_ID%TYPE,
p_product_name IN ORDER_DETAIL.PRODUCT_NAME%TYPE,
p_unit_price IN ORDER_DETAIL.UNIT_PRICE%TYPE,
p_quantity IN ORDER_DETAIL.QUANTITY%TYPE,
p_discount IN ORDER_DETAIL.DISCOUNT%TYPE) IS
product_discontinued PRODUCT.DISCONTINUED%TYPE;
fail EXCEPTION;
BEGIN
--
BEGIN
SELECT PRODUCT.DISCONTINUED
INTO product_discontinued
FROM PRODUCT
WHERE PRODUCT.PRODUCT_NAME = product_name;
END;
IF product_discontinued != 'Y' THEN
INSERT INTO ORDER_DETAIL(ORDER_ID, PRODUCT_NAME, UNIT_PRICE, QUANTITY, DISCOUNT)
VALUES(p_order_id, p_product_name, p_unit_price, p_quantity, p_discount);
COMMIT;
ELSE
RAISE fail;
END IF;
EXCEPTION
WHEN fail THEN
DBMS_OUTPUT.PUT_LINE('Error: Product discontinued!');
END INSERT_ORDER_DETAIL;
/
The error is occurring on the select statement. I have tested the select statement by itself and it definitely only returns one result. PRODUCT_NAME is also the primary key on the PRODUCT table so it's not possible for there to be more than one matching row anyway.
I must be missing something very obvious, any help is greatly appreciated.