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

Computer Person wrote:

> Daniel, it's great to see someone so sure of themselves..All I can add is I
> think if our developers had the proper skill I might agree with you but it
> has taken the past 4 months to get this far in our project and they seem to
> be having problems with their framework all the time..I know as a developer
> myself (as well as system admin) that the same system could have been
> written/debugged and promoted to prod in a month if we were not using oracle
> and java stored procedures. This system is 100% batch. It simply reads in
> flat files and loads them with sqll loader and then masages the data and
> sends the data to data warehouse (ETL in other words).
>
> I am finding that the UTL_FILE security is flawed in major ways which is
> contributing to the problems.
>
> The good thing about our culture is the freedom to express ones opinions!
>
> Thanks for yours..
>
> "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.
> >
> > 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.
> >
> > 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
> > 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
> >

I would very much like to know what you think if flawed about UTL_FILE security. Since UTL_FILE can be forced to read only identified files in a single specifically identified directory on the server ... the only security lapse I can see would be if you, the UNIX SA gave access to the server via the UNIX O/S. And if you are doing that ... why?

If what you describe is correct about the use of UTL_FILE then SQL*Loader is likely the tool of choice and I will agree with you. But this is information not included in your original posting.

Daniel Morgan Received on Sun Jan 05 2003 - 14:56:45 CST