generating "large" files through dbms_output

Question

... before anyone marks this as a duplicate ... i have checked Is there any way to flush output from PL/SQL in Oracle? and am looking for more information on how to actually do what that answer referenced ... so hold it.

I absolutely need to use DBMS_OUTPUT due to various reasons. UTL_FILE won't help.

For the benefit of this question, let's say i'm generating a flat file.

declare

function printerFunction(someArray) <---- this function swallows an array and dumps it to 
is begin 
  for each element of some array ... dbms_output.put_line(element)
end printerFunction;

begin
   for row in some_cursor loop
       printerFunction(someArrayData);
   end loop;
end 

The aforementioned code block is essentially the jist of the matter... nothing special.

Dave Costa mentions something along the lines of "break up a large PL/SQL block into multiple smaller blocks" ... the question is how.

I understand I have a nested loop structure and this is most likely the reason behind the output buffer not flushing itself, but can't think of a way to keep some_cursor open or for all intents and purposes switch back and forth between two code blocks outside of that loop .

dbms_output.enable(null) is kind of a dumb idea in this case as well. Ideally i'd like to essentially flush out the stuff in the buffer to my sqlDeveloper scriptOutput window and move on with processing at a specific rate ... say every 10000 rows or so. is this even possible? ... I mean... the main begin [pl/sql code] end structure is essentially a code block itself.

... the db I'm working on is a production environment, and i can only use a limited, read-only set of commands. ... in other words ... SYS stuff is beyond my reach.

Answer 1

As Dave Costa explains in the answer you link to, if you want the client to display the information before all your processing is done, you'd need to break your code up into separate blocks that you could send separately to the database. The client has no way to read the data out of the dbms_output buffer until the database returns control to the client.

You wouldn't realistically keep a single cursor open across these separate calls. You'd open a new cursor each time. For example, if you had a block

begin
  for cursor in (select * from ... order by ... offset x fetch next y rows only)
  loop
    <<do something>>
  end loop;
end;

you could have that run for 10,000 rows (or whatever value of y you want), get the output in SQL Developer, then run another block to process the next y rows (or have x and y be variables defined in SQL Developer that you update in each iteration or create a package to save state in package variables but I assume that is not an option here). But you'd need to submit a new PL/SQL block to the database for each set of y rows that you wanted to process. And this query will get less efficient as you try to get later and later pages since it has to sort more and more data to get the results.

Practically, it is pretty unlikely that you'd really want to break your logic up like this. You'd almost always be better off submitting a single PL/SQL block and letting the client fetch the data once at the end. But using dbms_output to generate a file is pretty weird in the first place. You'd generally be better served letting SQL Developer generate the file. For example, if you return a sys_refcursor for

select *
  from table(someArrayData)

or just run whatever query that was used to generate someArrayData, SQL Developer can happily save that data into a CSV file, and XLS(X) file, etc.

Answer 2

You can clear the buffer by consuming the messages manually. This snippet puts something in the buffer and then clears it. My client shows no output.

DECLARE
  var_status integer := 0;
  var_dummy varchar2(32767);
BEGIN
  dbms_output.put_line('this is a test');
  
  WHILE var_status = 0
  LOOP
    DBMS_OUTPUT.GET_LINE (line => var_dummy,
                          status => var_status);
  END LOOP;
END;