Skip to Main Content
  • Questions
  • SQLcl works worse with pipelined function than SQL*Plus

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krzysztof.

Asked: October 19, 2017 - 5:07 pm UTC

Last updated: October 22, 2017 - 9:18 am UTC

Version: 11g Express Edition

Viewed 1000+ times

You Asked

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?

and Connor said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Krzysztof, October 22, 2017 - 9:26 am UTC

Thank you for the answer. So I will use SQL*Plus still for that purpose.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library