Re: dbms_output from dbms_jobs procedures

Mark C. Stock wrote:
> "William Robertson" <william.robertson_at_bigfoot.com> wrote in message
> news:1134603116.785969.94660_at_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.
> >

>

Fair point.

However, "enabled" defaults to false, so in the OP's scenario unless it is explicitly enabled there will be no unwanted filling of the PGA black hole bucket, just the small overhead of a basically no-op procedure call.

If it is enabled, I agree it would be straightforward to capture output via a GET_LINES call in a wrapper procedure so long as you can live with the limitations and potential failures of DBMS_OUTPUT, which as James mentioned was designed as a primitive debugging tool rather than a production batch logger.

The source of DBMS_OUTPUT and others as of Oracle 7.2 are available at http://www.clamage.com/articles.htm. Received on Thu Dec 15 2005 - 08:28:22 CST