A SQL Loader Script

Shell Programming and Scripting
1

Hi Guys,

I am looking to develop a SQL Loader script that would bascially allow the user to have a txt file (or such) as an input file containing .sql scripts procedures, triggers, bascially anything against a database that could then be run automatically.

Let me break it down a bit more, what i would like is to have an input file that may be in the format:

@Database
--database details (SID, username, password)
@SQL
--.sql scripts to be run
@Procedures
--procedures to be compilied
@Triggers
--triggers to be compilied

The idea is to make the release procedure more streamlined so we can have an audit trail effectively. So the output of each would be written to a log file with times, details etc.

I really have a couple of questions on this, firstly I know how to read in a file etc, but can anyone suggest the quickest way to parse a file like this perharps into different arrays so that i then know what type of array i am dealing with (i.e. .sql scripts, procedures etc), so that i know what commands to use on sqlldr?

Also does anyone know if there has been something like this done somewere on the web?

I am keen to write one myself as I like doing it so I can learn more about Unix Scripting, my scripting at present is not too bad, but thought it mite be worth while running it past this site incase there are better ways of doing things.

I am not looking for you to provide a complete solution here, just help and advice on a good solution, and perhaps any sites you know that may be useful.

Thanks

2

Guys,

Quick question on this, hopefully.

I am going to go down the road that i have mentioned above, i.e. having one control file of format:

@database
--connection details
@procedure
--procedures etc
.
.
.
@database
--connection details

Basically my question is could someone give me some hints on how to parse the file so that i can grab all the stuff between (for example) the two @databases. I want to know what i should be loading against one database, and what against the others, if there is more than one @database, as it may be the case that everything has to be done against one database.

Hope that makes sense.

3

If I understand, you want to create metacode that generates a .ctl on the fly. There have been discussions at various levels about this idea at
www.orafaq.com

There are several examples - search for dynamic control file

To answer your question - you have to use a scripting facility (perl, ksh, etc.), I assume you are. Then you can use patternmatching or regex to find your database name.

A list of all the Oracle sids must live in the tnsnames.ora in the $ORACLE_HOME/network/admin tree. First, you have to parse that file for db names. You have to validate the names anyway, bad name=load failure. Then use the array of names to search for '@' prefixed on all of your possible dbnames.

Our tnanames files all have the sid on a single line by itself, so this works

#!/bin/ksh
let cnt=0
set -A sidlist
tr -s '[:upper:]'  '[:lower:]' < $ORACLE_HOME/network/admin/tnsnames.ora |\
grep 'sid =' | awk '{print $3}' \
while read sid
do
    sidlist[cnt]=$sid
    let cnt=$cnt+1
done