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

Tim X <timx_at_spamto.devnul.com> wrote in message news:<873co5hidc.fsf_at_tiger.rapttech.com.au>...
> afilonov_at_yahoo.com (Alex Filonov) writes:
>
> > John Russell <netnews4_at_johnrussell.mailshell.com> wrote in message news:<7kuh1vcj0kinc1tti72b9q146chqnc8cdn_at_4ax.com>...
> > > On Sun, 05 Jan 2003 19:45:43 GMT, "Computer Person" <xx_at_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

Connection overhead is negligible. Overhead of procedure call with dynamic SQL calls is significant. I tried both.

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

Ever tried to debug somebody's stored procedure with WHEN OTHERS exceptions in every block? Unforgettable experience.

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

Again, I did both and always prefer to modify scripts. Code reuse doesn't
deserve the fetish status it enjoys.

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

I know how to debug with pipes. Here's an example:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=336da121.0211262043.5257a9ec%40posting.google.com&rnum=15

However, in most cases you need to modify stored objects in order to debug. With scripts, problems are usually obvious from the output.

>
> > > 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/db92.vbook?search=table+function&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/db92.drilldown?remark=&word=external+table&book=a96624&preference=&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.
>

Completely agree here. That's what I'm trying to say from the beginning:
there is no perfect tool. Sometimes stored code is good, sometimes it's
bad, sometimes it's working perfect but obvious overkill. Same can be told about scripts. The only point I want to make is that for DDL and maintenance tasks scripts are better.

> Tim
Received on Tue Jan 07 2003 - 14:53:15 CST