Re: put_line: any way to have nonbuffered output?
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.comReceived on Wed Oct 05 2011 - 13:08:20 CDT