put_line places data into buffer. this data is displayed as soon as
pl/sql code is finished and control returns to invoking environment (eg.
sqlplus). in some sense the display via put_line is delayed compared to
the moment of data availability.

is there any other way in oracle, built-in or via third party
methods/packages, that controls display from pl/sql code without using
the "display buffer", ie. immediately after the data is available?

I would greatly appreciate your help and explanations.
thank you,
geos

--
FUT to: comp.databases.oracle.misc

On Wed, 05 Oct 2011 10:33:57 +0200, geos wrote:

If you check the file $ORACLE_HOME/rdbms/admin/dbmsotpt.sql, you will see
that it begins like this:

CREATE OR REPLACE TYPE dbmsoutput_linesarray IS
VARRAY(2147483647) OF VARCHAR2(32767);
/

That means that Oracle will place lines in the variable of the type
dbmsoutput_linesarray during the execution. The line buffer is private to
the package body, so only member functions can access it.

This is where the lines from DBMS_OUTPUT.PUT_LINE get placed when the
"PUT_LINE" procedure is executed. After the execution ends, sqlplus
executes dbms_output.get_lines to retrieve lines and displays them on the
standard output. As Oracle session can only execute a single SQL or PL/
SQL
at any given time, you can't have something that would asynchronously
check on the state of the array. Now an illustration:

SQL> set serveroutput on
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v
$sql_p
lan)

The dbms_xplan.display_cursor displayed dbms_output.get_lines because of
the serveroutput sqlplus setting, which instructed sqlplus to execute
dbms_output.get_lines after the execution and has therefore changed the
last cursor. If I stop the server output, the result is something
completely different, and I don't mean John Cleese in a bikini:

SQL> set serveroutput off
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp

Plan hash value: 2937609675

-------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


14 rows selected.

SQL>




8 rows selected.


So, the answer to your question is no. You cannot asynchronously check
the content of the line buffer because Oracle session can execute only a
single SQL (or PL/SQL) unit at any given time. If you describe V$SESSION,
you will see only the entries describing the current and the previous
SQL.
That wouldn't be possible, if there was a possibility to have 2 or more
simultaneous statements that could be executed by the same session.





--
http://mgogala.byethost5.com

Mladen Gogala wrote:
thanks for this expanded answer!

cheers,
geos

On Wed, 05 Oct 2011 20:40:46 +0200, geos wrote:


Would a simple "no" have satisfied you?



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
I really appreciate your effort, I didn't expect that someone would
answer this long! it's sometimes difficult to grasp the idea behind the
problem and your example was very helpful.

thank you,
geos