How to execute dynamic sql into cursor in Oracle?

Question

I have problem with execute dynamic sql statement into sys_refcursor in my stored procedure. I have looked in documentation and I think that I build my procedure properly, but I still do not know why error occurs. Please look below, what I created:

CREATE TABLE REKOM_CROSS_PROM (
      LINIA_PROD VARCHAR2(20), 
      ID_REKOM_OFERTA VARCHAR2(20),
      PRICE NUMBER,
      MAX_PRICE NUMBER
    );
/

CREATE OR REPLACE TYPE prodType AS OBJECT (
p_line VARCHAR2(20)
,p_price NUMBER
);
/

CREATE OR REPLACE TYPE prodTypeList IS TABLE OF prodType;
/

CREATE OR REPLACE PROCEDURE my_proc (prodLines IN prodTypeList ,rekom OUT SYS_REFCURSOR)
IS
v_pLine VARCHAR2(20);
v_query VARCHAR2(4000);
BEGIN
  v_query := 'SELECT ID_REKOM_OFERTA FROM REKOM_CROSS_PROM WHERE 
LINIA_PROD=NULL';
FOR i IN 1 .. prodLines.COUNT
    LOOP
        v_pLine := prodLines(i).p_line;
        v_query := v_query || ' UNION ALL SELECT ID_REKOM_OFERTA FROM 
                    REKOM_CROSS_PROM WHERE LINIA_PROD=''' || v_pLine || '''';
    END LOOP;
OPEN rekom FOR v_query;
END my_proc;
/

And when I want to call the procedure, error occur.

set serveroutput on
    declare
    type1 prodTypeList := prodTypeList(prodType('test1',1), prodType('test2', 20));
    rc SYS_REFCURSOR;
    row varchar2(200);
BEGIN
    MY_PROC(type1, rc);
    fetch rc into row;
    while (rc%found) loop
    dbms_output.put_line(row);
end loop;
close rc;
end;

I get the message:

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

*Cause: The stored procedure 'raise_application_error' was called which causes this error to be generated.

*Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.

Can sombody help me to resolve the problem?

Answer 1

You have an infinite loop. That means you're calling dbms_output.put_line forever - or until it runs out of buffer space, and throws that exception.

BEGIN
  MY_PROC(type1, rc);
  -- fetch first row from result set
  fetch rc into row;
  -- check if last fetch found something - always true
  while (rc%found) loop
    dbms_output.put_line(row);
  end loop;
  close rc;
end;

Every time around the loop you're checking the result of that first fetch, which stays true (assuming there is any data). You need to fetch each time round the loop:

BEGIN
  MY_PROC(type1, rc);
  -- fetch first row from result set
  fetch rc into row;
  -- check if last fetch found something
  while (rc%found) loop
    dbms_output.put_line(row);
    -- fetch next row from result set
    fetch rc into row;
  end loop;
  close rc;
end;

Or perhaps more commonly, only fetch inside the loop, and stop when nothing is found, using %notfound rather than %found:

BEGIN
  MY_PROC(type1, rc);
  loop
    -- fetch row from result set
    fetch rc into row;
    -- check if last fetch found something
    exit when rc%notfound;
    dbms_output.put_line(row);
  end loop;
  close rc;
end;

---

Not related to your current issue, but the predicate WHERE LINIA_PROD=NULL is never true; null isn't equal to (or not equal to) anything else. You need to use WHERE LINIA_PROD IS NULL instead.