Re: triggers, db jobs, & raising errors

Sybrand:

Thank you, that was a very clear definition of batch job. I see now why you used to word "misuse". I mispoke when I compared the parallelism I'm trying to achieve to what I can do in a single query; you're quite right that we would want to use a parallel query for that. It *is* parallelism in processing that we want to achieve, though. I was afraid you'd mention dbms_pipe.

Technically, the stored procedure we're running via the trigger is not a part of the transaction to which the trigger belongs; at least, it need not be. The idea was to use the trigger to indicate when we want the stored procedure to execute.

All of this is being done in our monthly batch processing, which used to take two-six days. I've gotten it down to less than 24 hours, including report printing. A single stored procedure (in a package) drives the vast majority of the processing, and, as each section completes, a column in a "flag table" is updated to indicate success. That is what I've been basing the trigger on. We've been adding extracts to the monthly batch, and I had been using this trigger/job/stored procedure methodology as a way to prevent the processing time from ballooning back to what it was before I arrived. From what I can tell, we will only be adding more processes to the monthly batch as time goes on.

If you would be so kind as to help me determine a suitable alternative, I would be glad to provide whatever details I can. I have no formal Oracle training, and my mentor, who has had some Oracle training and considerable experience w/database technology over the years, had no objections to this method.

Thanks,
Neal

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ul9fgba08hojc7_at_corp.supernews.com...
> The conventional meaning of "batch job" is that this is a background task,
> so you are NOT intererested in when the batch job completes. It is a
> background task, you want it to be done, but you want to proceed with your
> own work.
> You seem to be trying a 'batch job' as part of an ordinary transaction.
> That's not going to work. Your 'batch job' would need to communicate with
> foreground process with dbms_pipe and/or the aq facility. You will end up
> with many job queues and batch jobs preventing other batch jobs to run.
>
> That said, the description of your problem is just to be vague to be
> adressed properly. I can only shiver with 'trying to achieve some degree
of
> parallelism in processes (vs. in a
> > single query).'
> as it sounds like you want to re-invent parallel query available in the
> Enterprise Edition. Even if you do not want to re-invent that: I once was
> involved in a project where the original developer designed it's own
> interprocess communication mechanism in a 3GL language (as opposed to
> assembler or C) because the (version of) the O/S we were using didn't have
> that facility.
> Unfortunately , as it has been written in 3GL and was too slow, we never
got
> it working properly.
>
> Regards
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
> "Neal Helman" <nhelman_at_peakpeak.com> wrote in message
> news:3d54a14b$1_at_news.peakpeak.com...
> > So would there be another method for calling a stored procedure from
> another
> > stored procedure such that the caller continues while the other runs?
We
> > were trying to achieve some degree of parallelism in processes (vs. in a
> > single query). Is there some distinction between a "batch job" and a
> > "stored procedure" that I'm missing, other than that the latter
> necessarily
> > resides in the database?
> >
> > Thanks,
> > Neal
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:oai6luc116v1siv6au6hu6kmemtco7c7rn_at_4ax.com...
> > > On Thu, 08 Aug 2002 21:22:23 GMT, "Neal Helman" <nhelman_at_peakpeak.com>
> > > wrote:
> > >
> > > >I realize that the job is probably running under a background
process,
> > but
> > > >isn't there some sort of connection between the calling connection
and
> > that
> > > >background process?
> > >
> > > There isn't. Why should it? Basically you run batch jobs through
> > > dbms_job.
> > > You are misusing the dbms_job facility for purposes it wasn't designed
> > > for.
> > >
> > > Regards
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> >
>
>
Received on Sun Aug 11 2002 - 12:12:15 CDT