Re: DBMS_OUTPUT
- From: Mladen Gogala <mgogala@xxxxxxxxxxx>
- To: David.Moss@xxxxxxxxxxxx
- Date: Wed, 03 Jan 2007 06:02:07 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
