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

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?

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

> 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
Received on Mon Jan 06 2003 - 13:39:39 CST