"William Robertson" <william.robertson_at_bigfoot.com> wrote in message
news:1134656902.031819.250200_at_g43g2000cwa.googlegroups.com...
> 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.
>> >
>>
>> spigot = 'if enabled'
>> bucket = 'tmpbuf := tmpbuf||a;'
>>
>> ++ mcs
>
> 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.
>
OP's goal was to capture... hopefully the code doesn't enable DBMS_OUTPUT
itself
i would not think that there's any PGA usage, but i've got this nagging
memory of a damorgan post indicating if you call dbms_output.put_line often
enough, even with dbms_output enabled, it does use up some PGA
DAMORGAN: did you post something about a leak in dbms_output?
Received on Thu Dec 15 2005 - 09:46:06 CST
