Re: put_line: any way to have nonbuffered output?

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 5 Oct 2011 18:08:20 +0000 (UTC)
Message-ID: <pan.2011.10.05.18.08.20_at_gmail.com>



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

> 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

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



| Id | Operation | Name | Rows | Cost (%CPU)| Time |
|   0 | SELECT STATEMENT |	  |	  |	1 (100)|	  |
|   1 |  SORT AGGREGATE  |	  |	1 |	       |	  |

PLAN_TABLE_OUTPUT



| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

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
Received on Wed Oct 05 2011 - 13:08:20 CDT

Original text of this message