Let's imagine, we have a pipelined function like shown in this example
https://oracle-base.com/articles/misc/pipelined-table-functions#pipelined_table_functions My aim is to show, wether that function really return data row by row, rather than at once in a "package". At first, let's add a
DBMS_LOCK.SLEEP(0.2)
line inside the
LOOP...END LOOP
block, to slow down the generation of rows. The second step, is to run SQL*Plus, and execute
SET ARRAYSIZE 1
statement to permit returning rows line by line. It works fine, and I can watch how out pipelined function returning data slowly, row by row. I tried to obtain the same effect using new version of command line, SQLcl. Although
SET ARRAYSIZE 1
statement can be run too, it does not give the same effect as in the case of SQL*Plus. In a SQLcl console, generated rows are returned as a whole "package", not row by row, even using
SET ARRAYSIZE 1
and
DBMS_LOCK.SLEEP(0.2)
within the loop. How can I obtain my proper effect then?
I asked the PM for this.
"We buffer and print on end"
So yeah, with SQLcl you are not going to be able to see that effect.
Sorry.