How to fix ORA-01422:- Fetch isnt working

Question

I'm not understanding why this error is occurring. I have seen examples with him but the people doesn't use the for or the fetch loop. As I have understood, the fetch would loop in all the output of the cursor, in this case, taking row by row of the query and avoiding this error.

See the code:

CREATE OR REPLACE TYPE tab_dias IS TABLE OF INTEGER;

CREATE OR REPLACE FUNCTION  uf_dias_internado_paciente
(v_cod_paciente IN internacao.COD_PACIENTE%TYPE, 
v_dt_inicio IN internacao.DT_HORA_ENTRADA %TYPE, 
v_dt_fim IN internacao.DT_HORA_ALTA %TYPE)
RETURN tab_dias
IS 
    v_dias tab_dias := tab_dias();
    CURSOR c_dias IS
        SELECT EXTRACT(DAY FROM (i.DT_HORA_ALTA - i.DT_HORA_ENTRADA)) AS dias
        FROM INTERNACAO i 
        WHERE i.COD_PACIENTE  = v_cod_paciente
        AND i.DT_HORA_ENTRADA >= v_dt_inicio --13
        AND i.DT_HORA_ALTA <= v_dt_fim;
    linha_dias c_dias%ROWTYPE;
BEGIN 
        OPEN c_dias;
        LOOP
            FETCH c_dias INTO linha_dias;
            EXIT WHEN c_dias%NOTFOUND;
            v_dias(v_dias.last) := linha_dias.dias;
        END LOOP;
        CLOSE c_dias;
        RETURN v_dias;

END;

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

The code I'm running:

SELECT uf_dias_internado_paciente (5007, CURRENT_TIMESTAMP - 500000, CURRENT_TIMESTAMP ) FROM DUAL;

The desired output:

   SELECT (EXTRACT(DAY FROM (i.DT_HORA_ALTA - i.DT_HORA_ENTRADA)))
    FROM INTERNACAO i 
    WHERE i.COD_PACIENTE  = 5007
    AND i.DT_HORA_ENTRADA >= CURRENT_TIMESTAMP - 500000
    AND i.DT_HORA_ALTA <= CURRENT_TIMESTAMP;

(EXTRACT(DAYFROM(I.DT_HORA_ALTA-I.DT_HORA_ENTRADA)))|
----------------------------------------------------|
                                                  11|
                                                   1|
                                                   1|

Oracle documentation: https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/06_ora.htm#i36655 https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems020.htm

Answer 1

Code - as you wrote it - will return

ORA-06502: PL/SQL: numeric or value error: NULL index table key value

because you're missing v_dias.EXTEND. It can't fail with ORA-01422 (which is TOO_MANY_ROWS); cursors don't return it (unless you have a subquery - which you don't).

Therefore, there's something wrong in code you posted vs. what you are saying.

---

As I don't have your tables, here's an example based on Scott's EMP table:

SQL> CREATE OR REPLACE TYPE tab_dias IS TABLE OF INTEGER;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION f_dias (par_deptno IN NUMBER)
  2     RETURN tab_dias
  3  IS
  4     v_dias      tab_dias := tab_dias ();
  5
  6     CURSOR c_dias IS
  7        SELECT empno AS dias
  8          FROM emp
  9         WHERE deptno = par_deptno;
 10
 11     linha_dias  c_dias%ROWTYPE;
 12  BEGIN
 13     OPEN c_dias;
 14
 15     LOOP
 16        FETCH c_dias INTO linha_dias;
 17
 18        EXIT WHEN c_dias%NOTFOUND;
 19        v_dias.EXTEND;                                   --> this
 20        v_dias (v_dias.LAST) := linha_dias.dias;
 21     END LOOP;
 22
 23     CLOSE c_dias;
 24
 25     RETURN v_dias;
 26  END;
 27  /

Function created.

Testing:

SQL> SELECT f_dias (10) FROM DUAL;

F_DIAS(10)
--------------------------------------------------------------------------------
TAB_DIAS(7782, 7839, 7934)

Or:

SQL> SELECT * FROM TABLE(f_dias (10));

COLUMN_VALUE
------------
        7782
        7839
        7934

SQL>

---

Instead of a loop, perhaps you should consider bulk collect; it is simpler and more efficient:

SQL> CREATE OR REPLACE FUNCTION f_dias (par_deptno IN NUMBER)
  2     RETURN tab_dias
  3  IS
  4     v_dias  tab_dias := tab_dias ();
  5
  6     CURSOR c_dias IS
  7        SELECT empno AS dias
  8          FROM emp
  9         WHERE deptno = par_deptno;
 10  BEGIN
 11     OPEN c_dias;
 12
 13     FETCH c_dias BULK COLLECT INTO v_dias;
 14
 15     CLOSE c_dias;
 16
 17     RETURN v_dias;
 18  END;
 19  /

Function created.

SQL> SELECT * FROM TABLE (f_dias (10));

COLUMN_VALUE
------------
        7782
        7839
        7934

SQL>

---

So, basically, it works. There's something else that produces TOO_MANY_ROWS, not code you posted.