CODEWITHSUNDEEP
oracle-databaseplsql
Nicolas23
Nicolas23

Reputation: 19

I get keep getting this error I can't figure out what is wrong

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 19 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned.

*Action: Rewrite the query or change number of rows requested

This is my code. it always gives me the same problem and I do not understand how to solve it. Someone helps me please.

VARIABLE b_colacion NUMBER;
VARIABLE b_locomocion NUMBER;

EXECUTE  :b_colacion := 8000;
EXECUTE  :b_locomocion := 12000;

DECLARE
v_min_ven NUMBER;
v_max_ven NUMBER;
v_id_vendedor NUMBER;
v_sueldo_base NUMBER;
v_annos_contratado NUMBER(2);
v_valor_asig_annos NUMBER(8):=0;

BEGIN

SELECT MIN(id_vendedor) , MAX(id_vendedor) 
INTO v_min_ven , v_max_ven 
FROM vendedor;

WHILE v_min_ven <= v_max_ven

 LOOP

SELECT id_vendedor , sueldo_base , TO_CHAR(SYSDATE , 'YYYY') - TO_CHAR(fecha_contrato , 'YYYY')
INTO v_id_vendedor , v_sueldo_base , v_annos_contratado
FROM vendedor
ORDER BY id_vendedor ASC;


IF v_annos_contratado > 0 THEN
SELECT ROUND(v_sueldo_base * (porc_asignado_contr/100))
INTO v_valor_asig_annos
FROM porc_bonif_annos_contrato
WHERE v_annos_contratado BETWEEN annos_inf AND annos_sup;

END IF;

 INSERT INTO HABERES_CALCULADOSOS 
 VALUES(v_min_ven , :b_colacion , :b_locomocion , v_valor_asig_annos);


 COMMIT;

 END LOOP;

END;

Upvotes: 1

Views: 66

Answers (1)

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

Error message - exact fetch returns more than requested number of rows

What it means is - you are trying to save multiple rows into a single variable.

For Example - SELECT id_vendedor , sueldo_base , TO_CHAR(SYSDATE , 'YYYY') - TO_CHAR(fecha_contrato , 'YYYY') INTO v_id_vendedor , v_sueldo_base , v_annos_contratado FROM vendedor ORDER BY id_vendedor ASC; will return N number of rows, where N = Count of records in the table - vendedor.

What you can do to understand your error message better is to run the below code, which will help you debug the issues.

VARIABLE b_colacion NUMBER;
VARIABLE b_locomocion NUMBER;

EXECUTE  :b_colacion := 8000;
EXECUTE  :b_locomocion := 12000;

DECLARE
    v_min_ven NUMBER;
    v_max_ven NUMBER;
    v_id_vendedor NUMBER;
    v_sueldo_base NUMBER;
    v_annos_contratado NUMBER(2);
    v_valor_asig_annos NUMBER(8):=0;

BEGIN

    BEGIN
        SELECT MIN(id_vendedor) , MAX(id_vendedor) 
        INTO v_min_ven , v_max_ven 
        FROM vendedor;
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception in the above Select Statment : ' || SQLERRM);
    END;

    WHILE v_min_ven <= v_max_ven

     LOOP
        BEGIN
            SELECT id_vendedor , sueldo_base , TO_CHAR(SYSDATE , 'YYYY') - TO_CHAR(fecha_contrato , 'YYYY')
            INTO v_id_vendedor , v_sueldo_base , v_annos_contratado
            FROM vendedor
            ORDER BY id_vendedor ASC;
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Exception in the above Select Statement : ' || SQLERRM);
        END;

        IF v_annos_contratado > 0 THEN
            BEGIN
                SELECT ROUND(v_sueldo_base * (porc_asignado_contr/100))
                INTO v_valor_asig_annos
                FROM porc_bonif_annos_contrato
                WHERE v_annos_contratado BETWEEN annos_inf AND annos_sup;
            EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Exception in the above Select Statement : ' || SQLERRM);
            END;
        END IF;

        INSERT INTO HABERES_CALCULADOSOS 
        VALUES(v_min_ven , :b_colacion , :b_locomocion , v_valor_asig_annos);
        COMMIT;

    END LOOP;
END;

The exception which you wanna catch is - WHEN TOO_MANY_ROWS THEN

Just to let you know, WHEN OTHERS is an easy way to catch exceptions, however not a good thing to use in production code :)

Upvotes: 2

Related Questions