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?

That error just means you're exceeding the dbms_output buffer. How many rows are you expecting the constructed query to find? (And why are you constructing a query like that instead of using a collection of values in the query? Also: `=NULL` is never true, you need `IS NULL`.) — Alex Poole, Jan 12 '18 at 12:27
Possible duplicate of [How to increase dbms\_output buffer?](https://stackoverflow.com/questions/16476568/how-to-increase-dbms-output-buffer) — Barbaros Özhan, Jan 12 '18 at 12:31
@AlexPoole I expect only 3 rows. Im not using collection because I will add additional columns to REKOM_CROSS_PROM (price, max_price number) and after I will change WHERE clause to add: 'AND p_price BETWEEN PRICE AND MAX_PRICE. So when I add this, I couldn't use collection like this: WHERE LINIA_PROD IN (SELECT p_line FROM prodLines) — anton1009, Jan 12 '18 at 12:34
I don't see why you can't still use a collection, but maybe I'm missing something. Off-topic, anyway... — Alex Poole, Jan 12 '18 at 12:44

Alex Poole · Accepted Answer · 2018-01-12T12:45:36.593

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.

I didn't notice it :D Thanks for your eagle eye :) – anton1009 Jan 12 '18 at 12:44 — anton1009, Jan 12 '18 at 12:44

How to execute dynamic sql into cursor in Oracle?

1 Answers1