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

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
Received on Mon Jan 06 2003 - 13:25:36 CST