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

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 08 Jan 2003 09:23:44 -0800
Message-ID: <3E1C5EA0.D636BF8@exesolutions.com>

Alex Filonov wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E1A276E.180A370B_at_exesolutions.com>...
> > 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.
> >

>

> What if not? What if for any reason I have a mix of stored procedures
> and packages, scripts, sqlloader scripts and such? Like every system
> in the real world? And cron is not the most sofisticated scheduling
> system in UNIX world, even though the most reliable.

>


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

>

> Once you have one directory available for UTL_FILE open, you have problems.
> If you don't, how are you going to load outside files using UTL_FILE?
>
> > Daniel Morgan

With respect to mixed scripts then I agree. But, as I indicated, if the sole purpose is running inside the database then shell scripts and Perl are not the best way to go: The single point of failure becomes the SA who undoubtedly has far better things to do than read error logs and report to department managers.

With respect to UTL_FILE ... I still fail to see the issue with having a directory available to UTL_FILE. What gets into or out of that directory can be completely controlled by security on the server. And UTL_FILE isn't writing or reading anywhere other than a single location identified by the SA and DBA.

If the concern is how things get there or get out of there don't make it possible for anything to FTP to or from that directory except an approved and secure process.

Daniel Morgan Received on Wed Jan 08 2003 - 11:23:44 CST