Re: Oracle stored procedures vs Running from a flat .sql file

Alex Filonov wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E171AB9.9FACDF9F_at_exesolutions.com>...
> > computer person wrote:
> >
> > > Does anyone know what the advantages are to using stored procedures and java
> > > stored procedures over and above running from flat unix files.
> > >
> > > I find that since our application is all stored in the database it is harder
> > > to understand when something goes wrong with it. The traditional way to
> > > running a job stream is to have a unix script with steps in it. The way this
> > > application is set up is to run everything as one long call from a stored
> > > procedure.
> > >
> > > Anyone have experiences with this? The develepers have gone as far as
> > > reading and writing files using the UTIL_FILE package instead of doing this
> > > with a ksh. This has caused a great deal of effort for debugging at the unix
> > > level because they can't even tell me (as the Unix System admin) if there is
> > > a permission problem with the files it tries to access when the application
> > > fails.. It's all guess work to fix something..
> >
> > Congratulations to your developers. They are doing things the right way for
> > security, scalability, performance, and error handling.
> >
>
> Security with UTL_FILE? Come on. There is no security once you start using
> UTL_FILE.
>
> As for batches and job scheduling, I can say that Oracle, as any other
> database, is not very good for it. There are a lot of applications you
> can use under UNIX for it, what you can do in Oracle? Advanced queueing?
>
> > No insult intended but my guess is that you are very much like the guy that
> > only has a hammer that sees every problem as a nail.
> >
>
> Opposite effect: you don't need anything more complex than a hammer when
> you have a nail.
>
> > Either learn Oracle or leave your Oracle developers and DBAs alone. There is
> > almost nothing you can do with a Korn Shell Script they can't do better within
> > the database. And if you want me to exapand on that I gladly will. But for one
>
> File management, directory structure management, job scheduling, error
> reporting... May be not much, but very important.
>
> > classic example ... error logs in the database can be easily used to develop
> > statistical reports. Error logs in the shell are unavailable to everyone except
> > you. And you'd look pretty funny doing a trend report of problems this quarter
> > vs. problems last quarter.
> >
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp

There is nothing you can do in cron I can't do better with DBMS_JOB ... assuming what is being scheduled is actions taking place in PL/SQL or JSQL.

And I have yet to find, anywhere, a single security hole related to UTL_FILE that exists if a competent SA and DBA manage the server and its environs.

Daniel Morgan Received on Mon Jan 06 2003 - 19:03:42 CST