Automate SQL Question

indiagirl · *nix forums beginner Joined: 17 Jul 2006 Posts: 2

I need to generate reports on one database (SQL statements developed to

build delimited files), load them into a test database (control files
built using sql loader), and then update certain tables based on the
new information received within the test database (SQL update
statements built to do this). I need help with a simple way to develop
an automated process to do this. If someone could point me in the right

direction for a beginner.

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

indiagirl (erin.l.anderson@citigroup.com) wrote:
: I need to generate reports on one database (SQL statements developed to

: build delimited files), load them into a test database (control files
: built using sql loader), and then update certain tables based on the
: new information received within the test database (SQL update
: statements built to do this). I need help with a simple way to develop
: an automated process to do this. If someone could point me in the right

: direction for a beginner.

Oracle has a nice new thing called BPEL that sounds like it should be easy
for a beginner to use - just drag and drop icons within JDeveloper to
define the steps to take (assuming everything is setup!).

Not used it yet though, so I don't know how easy it really is.

I would tend to write a shell script and run it from cron. Run the script
on a server that has access to all the databases.

Create a script that does the following...

Check that a flag file (see next step) does not exist. If it does then
fail with an error message. The cron job error will be mailed to
someone and they can manually check what went wrong last time.

touch the flag file to create it to indicate you're working

use sqlplus to connect to the first database and run the extract
scripts. spool or pipe the output to local file(s)

use sqlldr to connect to the test database and run the control files
with the data files just saved.

use sqlplus to connect to the final database and run the update scripts
to merge in the new data

rename the various files so you have a nice dated copy of what you did.

delete or rename the flag file

maybe send a mail message (maybe not as cron will already send its
output).

Now schedule that script to be run in cron (well actually next step is to
test it manually first, but I assume you realize that).

If the output of one step is not quite what you need as the input of the
next step then add a few lines to your script to fix that problem. I
typically use perl, but things like grep, sed, awk and pretty common too.

On a windows server, write a batch file to do the same thing. Perl is the
best bet to use if you need to do any data fix ups. schedule the batch
file using the AT command. Getting output is harder on windows, so save
all the messages into a log file (perhaps on an easily accessible network
share) and then make sure someone reads the logs files once in a while.

$0.10

Google