Re: DBMS_OUTPUT

On 01/03/2007 05:35:29 AM, David Moss wrote:
>
> Hi,
>
> Just a quick question, hope someone can illuminate me... I'm trying to debug a function using dbms_output but I can't gt anything I ask it to print displayed (not immediately anyway)... sample session:
>
> SQL> set serveroutput on;
> SQL> show serveroutput;
> serveroutput ON size 2000 format WORD_WRAPPED
> SQL> create or replace function test return number is begin DBMS_OUTPUT.put_line('Hello, world!'); return 0; end;
> 2 /
>
> Function created.
>
> SQL> select test() from dual;
>
> TEST()
> ----------
> 0
>
> SQL>
>
> Am i missing something really obvious or is this some sort of bug? I also realized that if then i do:
>
> SQL> create or replace procedure test2 is begin DBMS_OUTPUT.put_line('Hello again!'); end;
> 2 /
>
> Procedure created.
>
> SQL> select test() from dual;
>
> TEST()
> ----------
> 0
>
> SQL> call test2();
> Hello, world!
> Hello, world!
> Hello again!
>
> Call completed.
>
> SQL>
>
> odd... anyone?

Actually, not odd at all. This is happening because of the implementation of DBMS_OUTPUT. Package DBMS_OUTPUT actually stores lines in a PL/SQL table of varchar(255). Sqlplus is programmed so that if it is executing a PL/SQL call, it will occasionally take a look into the PL/SQL output table and output whatever is in there, removing the lines from the buffer. Sqlplus knows what kind of statement it is executing. To prove that, one only needs to connect to a 10G database using 9.2 sqlplus and try purging recycle bin. The error will tell him that he's trying to execute an invalid SQL command, despite the fact that the command is valid. Sqlplus has a built-in SQL parser. If sqlplus is executing a SQL command, like select, the PL/SQL containing the output lines is not checked. It is, however, still populated by DBMS_OUTPUT commands, so that when you call test2 procedure, it will output everything in the buffer, including the things that were put there by the previous two queries. You must have in mind the two task architecture of Oracle RDBMS. Your PL/SQL is actually executed by a background process which can, and frequently does, run on a different machine from your sqlplus command. The only place that the background process can put output is memory, PGA to be precise. The only program that can get it out to the STDOUT is sqlplus itself. It does so ONLY when it's executing a PL/SQL block (call, exec or anonymous PL/SQL block).

An example of a program that can do that is mod_plsql, an Apache module which performs communication between Apache and Oracle. OWA_HTP package is, essentially, a bunch of DBMS_OUTPUT calls. My DBA_Helper tool has a SQL worksheet that can also do that, by calling dbms_output.get_line, but only after the call has been completed. I believe that SQL*Plus does it while the call is still executing. In other words, it's a programming trick, nothing else. You shouldn't be relying on DBMS_OUTPUT too much.

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l