Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_output from dbms_jobs procedures

Re: dbms_output from dbms_jobs procedures

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 14 Dec 2005 15:31:56 -0800
Message-ID: <1134603116.785969.94660@f14g2000cwb.googlegroups.com>


William Robertson wrote:
> Mark C. Stock wrote:
> > "Jeremy" <jeremy0505_at_gmail.com> wrote in message
> > news:MPG.1e0a0b70784541598a071_at_news.individual.net...
> > > In article <1134550096.100826.241610_at_g14g2000cwa.googlegroups.com>,
> > > sybrandb_at_yahoo.com says...
> > >> Right now, your dbms_output is ending up in a 'black hole' (in reality
> > >> the PGA), and this may cause your procedure to fail.
> > >>
> > >
> > > Hi Sybrand, how may this cause the procedure to fail? I know that if
> > > running the procedure in sqlplus the buffer may be exceeded causing an
> > > exception but my understanding (perhaps incorrect?) was that the output
> > > would simply be disregarded unless being run by a "dbms_output-aware"
> > > tool?
> > >
> > > --
> > >
> > > jeremy
> >
> > if dbms_output is not initialized (with the dbms_output.enable procedure)
> > the effect is minimal, but i believe there is some effect -- i think it was
> > daniel morgan who posted a case in a recent thread
> >
> > think of dbms_output as half of an interprocess communication pipe --
> > actually kind of like a spigot and a bucket. calls to put_line fill the
> > bucket, if the spigot is open and a bucket has been placed under it.
> > evidently, though (based on what i remember from daniel's post) there's a
> > slight leak in the spigot. so any program that can call pl/sql can first
> > open the spigot and later empty the bucket.
> >

>

> Spigot? Bucket?
>

> Isn't the code for PUT_LINE just this:
>

> procedure put_line(a varchar2) is
> begin
> if enabled then
> tmpbuf := tmpbuf || a;
> new_line;
> end if;
> end;
>

> (plus a couple of pointless overloadings)
>

> So if enabled is false it does nothing, as you can surely test for
> yourself in SQL*Plus.

I forgot to mention, the above is from Oracle 7.2 which is the last release before they wrapped the source code. DBMS_OUTPUT was updated in 10gR2 and so might behave differently now, possibly even in the manner of a spigot/bucket arrangement. I very much doubt whether it changed in the intervening versions. Received on Wed Dec 14 2005 - 17:31:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US