Re: Automate SQL Question

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

It will depend on the platform you are running and how easily accesable the
tools/DB instances are.

On Windows you could put the stepss into a batch file (*.bat or *.cmd): For example in a file named 'update_test.bat': rem update_test.bat

  set SRC_DBS_CONNECT=id/pass_at_sourceDB
  set TST_DBS_CONNECT=id/pass_at_testDB
  set ROOT="C:\Path\to\working\directory"

  set DATA_SOURCE=report_file_name.txt

  sqlplus %SRC_DBS_CONNECT% @%ROOT%\generate_reports.sql %ROOT%\generate_reports.err

  sqlldr %TST_DBS_CONNECT% control=%ROOT%\test_data.ctl data=%ROOT%\%DATA_SOURCE% log=%ROOT%\%DATA_SOURCE%.log   rem will need to repeat the sqlldr statement for each file to be loaded...

  sqlplus %TST_DBS_CONNECT% @%ROOT%\update_test.sql %ROOT%\update_test.err

  rem the pause below keeps the command window open   rem to allow review of any output/errors on the command line.   rem Remove if not needed.
  pause

Note that the working directory will have to be the location where the generated reports are saved.

A similar script can be done for *nix but will depend on the shell used. Received on Tue Jul 18 2006 - 10:50:00 CDT