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

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

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!