Re: Automate SQL Question

indiagirl (erin.l.anderson_at_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 Received on Tue Jul 18 2006 - 00:12:47 CEST