1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by yowancristo, Dec 11, 2014.

  1. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi All,

    In Pl/sql When multiple rows are being inserted into a scalar variable - It throws exception ORA-01422: exact fetch returns more than requested number of rows

    However even the exception is caught, the variable still holds the first value that is picked by the select statement.

    Eg:

    Code (SQL):


    DECLARE
        l_po_id         NUMBER;
        l_invoice_id    NUMBER:= 1907525;
    BEGIN
        SELECT DISTINCT POH.po_header_id
        INTO    l_po_id
        FROM    AP_INVOICES_ALL              API,
                AP_INVOICE_DISTRIBUTIONS_ALL AID,
                PO_DISTRIBUTIONS_ALL        POD,
                PO_HEADERS_ALL              POH,
                PO_LINES_ALL                POL
        WHERE   API.invoice_id = AID.invoice_id
        AND     AID.po_distribution_id = POD.po_distribution_id
        AND     POD.po_header_id = POH.po_header_id
        AND     API.invoice_type_lookup_code = 'STANDARD'
        AND     NVL (AID.reversal_flag, 'N') <> 'Y'
        AND     UPPER (AID.line_type_lookup_code) <> 'TAX'
        AND     POH.type_lookup_code = 'STANDARD'
        AND     API.invoice_id = l_invoice_id;
    EXCEPTION
        WHEN    TOO_MANY_ROWS    THEN
            dbms_output.put_line('Exception Too Many Rows - Value of l_po_id '||l_po_id);
        WHEN    OTHERS    THEN
            dbms_output.put_line('Exception '||SQLCODE||' '||SQLERRM);  
    END;
    /  

     
    In the above case if the invoice id 1907525 is matched to multiple POs then it will go into exception 'too_many_rows' , but still variable l_po_id will have the value of first po_header_id that is picked by select statement.

    Is there some other interesting cases that you have noticed like this.:)

    Best Regards,
    Yowan Cristo
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's expected behavior. The first row of a result set populates the variables that are to be populated. Oracle is only telling you that "I expected one row and received many so I can't process this the way it's coded". Using bulk collect and forall usually fixes such issues.
     
    yowancristo likes this.
  3. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Thanks David, Yea that's right. I was thinking that once this exception is raised, the value of variable will be null.

    Regards,
    Yowan Cristo