I am getting the error ORA-01422 when the following SQL is executed. This query returns 2 records and I want to process both records one at a time or together.
How do I modify or rewrite this query so I don't get the error?
-- Data fields
v_SFCID COMPANY.company_id%TYPE;
v_SFC COMPANY.acronym%TYPE;
v_SFC_location_id COMPANY_LOCATION.company_location_id%TYPE;
v_SHIP_location_id COMPANY_LOCATION.company_location_id%TYPE;
v_header_id ORDER_HEADER.order_header_id%TYPE;
v_order_type ORDER_HEADER.order_type%TYPE;
v_order_plant_cd ORDER_HEADER.order_plant_cd%TYPE;
v_estimated_ship_date ORDER_HEADER.estimated_ship_date%TYPE;
v_estimated_release_date ORDER_HEADER.estimated_release_date%TYPE;
v_acronym COMPANY.acronym%TYPE;
v_stock_descr PRODUCTS.stock_desc%TYPE;
stage_rcd MyDB.MyTable%ROWTYPE;
<<VALIDATE>>
BEGIN
--Retreive order information PJM - review whether line number will always be sent
SELECT h.order_header_id, c.company_id, c.acronym, h.sold_from_company_location_id,
h.ship_to_company_location_id, h.order_type, c.acronym, h.order_plant_cd,
h.estimated_ship_date, h.estimated_release_date, p.stock_desc
INTO v_header_id, v_SFCID, v_SFC, v_SFC_location_id, v_SHIP_location_id, v_order_type,
v_acronym, v_order_plant_cd, v_estimated_ship_date, v_estimated_release_date,
v_stock_descr
FROM ORDER_HEADER h
JOIN COMPANY_LOCATION l ON l.company_location_id = h.sold_from_company_location_id
JOIN COMPANY c ON (c.sap_id = stage_rcd.sfc_sap_id AND c.company_id = l.company_id)
JOIN ORDER_DETAIL d ON d.order_header_id = h.order_header_id
JOIN PRODUCTS p ON p.product_id = d.product_id
WHERE h.order_nbr = stage_rcd.order_nbr;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- ORA-01403
v_reject_cnt := v_reject_cnt + 1;
v_error_msg := v_acronym||' Order not found: '|| stage_rcd.order_nbr;
v_process_status := 'X';
GOTO skip_record;
WHEN TOO_MANY_ROWS THEN -- ORA-01422
v_reject_cnt := v_reject_cnt + 1;
v_error_msg := v_acronym||' ORA-01422: exact fetch returns more than requested number of rows: '|| stage_rcd.order_nbr;
v_process_status := 'A';
GOTO skip_record;
WHEN OTHERS THEN
v_reject_cnt := v_reject_cnt + 1;
v_error_msg := v_acronym||' Other Error: '|| stage_rcd.order_nbr;
v_process_status := 'A';
GOTO skip_record;
END VALIDATE;