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

geos wrote:

There used to be something called dbms_pipe.

Gerard H. Pille wrote:
yes, but his is what I wanted to do (modified example from
Feuerstein/Pribyl book, page 111: http://tinyurl.com/6funmrc)

session 1: http://geos2005.republika.pl/sesja1.sql
session 2: http://geos2005.republika.pl/sesja2.sql

pl/sql (2) can "communicate" with pl/sql (1) but the latter one can't
display until it is finished.

thanks,
geos

geos wrote:

The reading side of dbms_pipe should be handled by a PRO*C or Java program, and spool output
immediately.

Gerard H. Pille wrote:
do I understand right, that Pro*C means running the compiled output as
system program, not "inside" sqlplus as it is with anonymous block, and
to display the messages appropriate Pro*C "display function" should be
used?

thank you,
geos

On Oct 6, 2:26*am, geos <g... (AT) nowhere (DOT) invalid> wrote:
The dbms_pipe package, which should generally not be used in a RAC
environment, is a mean to pass data between two Oracle sessions. The
first session packs the data into the pipe and the second session
unpacks the data from the pipe. Depending on your version you can
find information on this Oracle provided package in the PL/SQL
Packages and Types manual or Supplied PL/SQL Packages and Types
Reference.

If you used dbms_pipe instead of dbms_output a pro*C program could
unpack the pipe concurrently to the first session filling the pipe and
write out the output or insert it into a table independently of the
first session transaction.

An alternate approach that work in a RAC environment would be to
substitute a logging routine for dbms_output where the logging routine
was written as an anonymous transaction; however, you would have to
read the results from a separate session so neither of these
approaches may be of any use if what you want is for the current
session to see the dbms_output as it is generated on the current
session screen.

HTH -- Mark D Powell --

Mark D Powell wrote:
"anonymous transaction" being "autonomous transaction" ?

On Oct 6, 4:35*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
Yes. http://download.oracle.com/docs/cd/B....htm#sthref307

Thanks. I wonder how long I have been doing that.

HTH -- Mark D Powell --