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

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/e...ad/oad_crs.asp

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@exesolutions.com> wrote in message
news:3E171AB9.9FACDF9F@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/e...ad/oad_crs.asp
>

"computer person" <fake_address@nothing.com> wrote:
> Does anyone know what the advantages are to using stored procedures and
> java stored procedures over and above running from flat unix files.

flat unix files don't have flow control and exception handling? It seems
to me that the two do fundamentally different things.

> I find that since our application is all stored in the database it is
> harder to understand when something goes wrong with it.

Perhaps that means it shouldn't be your job to understand when
something goes wrong with it. Isn't that the developer's job?

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

What does 'everything' consist of?

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

Yes, UTL_FILE can be a pain in the ass. But why are they reading and
writing files as a routine matter in the first place? You have a database,
read and write data to it, not files. There are specifics where flat files
are better than stored procedures, but in general they are not and you
haven't given us any specifics.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service New Rate! $9.95/Month 50GB

"Computer Person" <xx@xx.com> writes:

> I am finding that the UTL_FILE security is flawed in major ways which is
> contributing to the problems.

We have a number of apps which make use of utl_file - I would really
like to know what the security flaws are with it - my experience has
been that utl_file can be a pain, but this is primarily because of its
security restrictions. It would be most useful to know about the
security flaws so that I can determine if our system has security
holes I'm not aware of.

Tim
--
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you
really need to send mail, you should be able to work it out!

Tim X <timx@spamto.devnul.com> wrote in message news:<87bs2vhyc3.fsf@tiger.rapttech.com.au>...
> "Computer Person" <xx@xx.com> writes:
>
> > I am finding that the UTL_FILE security is flawed in major ways which is
> > contributing to the problems.
>
> We have a number of apps which make use of utl_file - I would really
> like to know what the security flaws are with it - my experience has
> been that utl_file can be a pain, but this is primarily because of its
> security restrictions. It would be most useful to know about the
> security flaws so that I can determine if our system has security
> holes I'm not aware of.
>

UTL_FILE writes all files as Oracle Database owner. If you want to load
files using UTL_FILE, you need to create them first as some other user.
That user has to have write access to this directory. Now, simple
trick. Make that user to create a soft link to some important file
owner by Oracle owner, some executable for example. And make a simple
PL/SQL program which will remove this file using UTL_FILE. Sounds
impressive enough? This is the very first thing coming to mind, but
I'm sure inventive person can produce lots of problems. Not to mention
not very smart persons, which can do much more damage...

> Tim

Every time they have a problem with the UTIL_FILE they blame it on Unix so
it is very much my job to understand what they are doing..

<ctcgag@hotmail.com> wrote in message
news:20030105145729.827$Zf@newsreader.com...
> "computer person" <fake_address@nothing.com> wrote:
> > Does anyone know what the advantages are to using stored procedures and
> > java stored procedures over and above running from flat unix files.
>
> flat unix files don't have flow control and exception handling? It seems
> to me that the two do fundamentally different things.
>
> > I find that since our application is all stored in the database it is
> > harder to understand when something goes wrong with it.
>
> Perhaps that means it shouldn't be your job to understand when
> something goes wrong with it. Isn't that the developer's job?
>
> > 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.
>
> What does 'everything' consist of?
>
> > 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..
>
> Yes, UTL_FILE can be a pain in the ass. But why are they reading and
> writing files as a routine matter in the first place? You have a
database,
> read and write data to it, not files. There are specifics where flat
files
> are better than stored procedures, but in general they are not and you
> haven't given us any specifics.
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service New Rate! $9.95/Month 50GB

Tim X wrote:
> "Computer Person" <xx@xx.com> writes:
>
>
>>I am finding that the UTL_FILE security is flawed in major ways which is
>>contributing to the problems.
>
>
> We have a number of apps which make use of utl_file - I would really
> like to know what the security flaws are with it - my experience has
> been that utl_file can be a pain, but this is primarily because of its
> security restrictions. It would be most useful to know about the
> security flaws so that I can determine if our system has security
> holes I'm not aware of.
>
> Tim
Well, most of us can rest easy because we don't do foolish things with
setting the utl_file directories (like setting them to '*' or '.'
(dot)). But if you want to see some of the efforts hackers are proposing
to use against an Oracle database, you can find lots of stuff on the
Web. For example (will probably wrap):

http://www.blacksheepnetworks.com/se...e-security.htm

shows some attempts to inform hackers of 'holes' in an Oracle db (though
most of these holes are only available when a DBA is lazy or doesn't
care about security or has never looked into it or...). No conscientious
DBA will ever leave these 'holes'. Scan down to the bottom of the web
page for hints on hacking utl_file ... but a DBA will have to open this
hole on purpose.

Alex Filonov wrote:

> Tim X <timx@spamto.devnul.com> wrote in message news:<87bs2vhyc3.fsf@tiger.rapttech.com.au>...
> > "Computer Person" <xx@xx.com> writes:
> >
> > > I am finding that the UTL_FILE security is flawed in major ways which is
> > > contributing to the problems.
> >
> > We have a number of apps which make use of utl_file - I would really
> > like to know what the security flaws are with it - my experience has
> > been that utl_file can be a pain, but this is primarily because of its
> > security restrictions. It would be most useful to know about the
> > security flaws so that I can determine if our system has security
> > holes I'm not aware of.
> >
>
> UTL_FILE writes all files as Oracle Database owner. If you want to load
> files using UTL_FILE, you need to create them first as some other user.
> That user has to have write access to this directory. Now, simple
> trick. Make that user to create a soft link to some important file
> owner by Oracle owner, some executable for example. And make a simple
> PL/SQL program which will remove this file using UTL_FILE. Sounds
> impressive enough? This is the very first thing coming to mind, but
> I'm sure inventive person can produce lots of problems. Not to mention
> not very smart persons, which can do much more damage...
>
> > Tim

Provided the inventive person works on a server managed by a SA who is clueless about security.

But then on that subject I can point you to government databases in the US where the passwords
for SYS and SYSTEM are unchanged from the default.

Daniel Morgan

afilonov@yahoo.com (Alex Filonov) writes:

> John Russell <netnews4@johnrussell.mailshell.com> wrote in message news:<7kuh1vcj0kinc1tti72b9q146chqnc8cdn@4ax.com>. ..
> > On Sun, 05 Jan 2003 19:45:43 GMT, "Computer Person" <xx@xx.com> wrote:
> > >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).
> >
> > Your setup is different than I thought from your original post.
> > Mostly I see scripts that are sequences of DROP TABLE, CREATE TABLE,
> > DROP INDEX, CREATE INDEX, etc. Scripts like that can be done better
> > by wrapping them in PL/SQL procedures -- only drop the table if it
> > already exists, only create the table if it doesn't already exist,
> > etc. Running a set of SQL commands, you have to expect and ignore
> > certain errors.
> >
>
> Scripts are much better than procedures. Why create
> overhead running a procedure and dynamic SQL? And what about error
> processing in procedures? If script fails, it exits with code 1,
> what would procedure do?
>
Sorry, not sure I agree. I think the overhead of creating a connection
and executing the script is probably higher than executing a procedure
from within the database. Also, not all sql in procedures is dynamic -
only DDL needs to be dynamic and things like weak ref cursors
etc. With respect to error handling, I find using exceptions etc MUCH
more powerful and flexible than the error handling available via
scripts and remember a number of threads in the oracle newsgroups were
people have complained about the lack of support for error handling
within scripts.

Another advantage of stored procedures is that your sql is more likely
to be cached and re-used. Oracle is pretty dumb in this way and it
only takes very minor diffeences between two statements which are
exactly the same for Oracle not to use a previously parsed and cached
version, such as extra spaces or differences in case etc. Using stored
procedures also allows you to maximise code re-use and can make
maintenance a lot simpler - which would you prefer, changing a couple
of stored procedures or modifying a lot of scripts which use the same
bits of sql?

> > Specifically for data warehousing:
> >
> > You could write progress / log output via DBMS_OUTPUT, and grab that
> > text back in the shell (by invoking the whole thing via the sqlplus
> > command from a shell).
> >
>
> You can't expect every log to be not bigger than 1M (DBMS_OUTPUT
> restriction). BTW, if you ever read DBMS_OUTPUT documentation, it's
> recommended for DEBUG PURPOSES EXCLUSIVELY and not recommended for
> reporting.

Agreed. I thought exactly the same thing when I saw the original post
- dbms_output is really only useful for debugging and even then its
usefulness is fairly limited. However, you could use pipes or write
the status info to a log table and access that.


> > You could use table functions to break up the massaging into discrete
> > steps, passing the data from one function to another, similar to Unix
> > pipes.
> >
> > http://tahiti.oracle.com/pls/db92/db...ction&search2=
> >
> > You could define an "external table" that references the flat file.
> > That lets you format the data in a SQL*Plus like file, and grab it via
> > SQL statements.
> >
>
> External table is implemented through sqlloader. Sometimes it's useful,
> but you can load the file into a table directly as well.
>
> > http://tahiti.oracle.com/pls/db92/db...rence=&vbook=1
> >
> > John

I agree with some of your points and not others. However, I think the
generic sweeping statement that scripts are better than stored
procedures is being way too general. Both have their place and I find
myself using both depending on the job which needs to be done. Often I
have unix scripts which contain code that uses a lot of stored
procedures - this gives me the advantages of both and I find it more
maintainable than lots of independent scripts which often contain common
code.

Tim
--
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you
really need to send mail, you should be able to work it out!