Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ORA-01422: exact fetch returns more than requested number of rows

User_GVHF4Feb 9 2023 — edited Feb 10 2023

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;  
      
Comments
Post Details
Added on Feb 9 2023
7 comments
76 views