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

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.

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

http://tahiti.oracle.com/pls/db92/db92.drilldown?remark=&word=external+table&book=a96624&preference=&vbook=1

John Received on Sun Jan 05 2003 - 21:54:27 CST