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: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 14 Dec 2005 06:57:11 -0500
Message-ID: <ENSdnTIc0MsFlT3eRVn-uw@comcast.com>

"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.

that being said, if the OP wants to capture the dbms_output from existing procedures for dbms_job submissions, OP simply create a wrapper procedure that enables dbms_output, calls the original stored procedure, than makes dbms_output.get_lines calls to grab the output and put it someplace -- like a logging table or a file via utl_file.

Received on Wed Dec 14 2005 - 05:57:11 CST

Original text of this message

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