How can I create a Unix shell script that will run individual SQL files having a seperate result file for each SQL file?

Thanks,
Richard

Richard:

You need to tell us what database.

Nails

Sorry...I am using Oracle and it's HP-Unix.

Thanks,
Richard

Richard:

I'm not an Oracle guy, but check out "Using SQL in Shell Scripts" by Gleed and Tarvainen:

http://www.samag.com/documents/s=1434/sam9505f/9505f.htm

Regards,

Nails

You can use a here-document, e.g:

sqlplus -s /nolog <<ENDSQL
connect ${connectstring}
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
SPOOL ${outfile1}
@script1.sql
SPOOL OFF
EXIT;
ENDSQL

An advantage of this is that you don't have to give away your username/password to anyone running 'ps -ef'.

Starting SQL*Plus and connecting to the database is relatively slow though, so if you have a lot of scripts to run you could call several scripts per SQL*Plus session, or set off one session in background as a coprocess.

None of this is great use of the database though. Perl has database connectivity modules that allow you to make a connection programmatically without having to call SQL*Plus (I'm going to have to learn Perl one day). Or you can write a PL/SQL package to output text files, XML and so on.

Thanks, however I am trying to use this shell script. It works great with one sql file but when I place each file on separate lines it doesn't work. I know it's something minor but I can't figure it out.

This works fine!!
#!/bin/ksh
sqlplus -s user/password @/var/cri/test.sql
exit
EOF

Error stating it can't find test1.sql
#!/bin/ksh
sqlplus -s user/password
@/var/cwi/test1.sql
@/var/cwi/test2.sql
exit
EOF