Re: PL/SQL script to execute multiple .sql files?

Jim Lewis wrote:

> We have about 100 WebDB scripts to execute on our production server to
> migrate reports from development to production.
>
> We have consulted the PL/SQL Programming manual for help in writing a
> PL/SQL script that will go through the WebDB scripts directory
> executing them all in turn. Unfortunately, we haven't come up with
> anything yet.
>
> Can anyone share such a script with us. Our production machine is a
> Sun, running Solaris 7, by the way.
>
> Thank you in advance.
>
>

You should be able to do it via a shell script and not PL/SQL. The following is an example. Just put in the file names & you should be set.

#!/bin/sh
TMP=/tmp/tmpfile

sqlplus -s / <<! > $TMP
  whenever sqlerror exit failure;
  whenever oserror exit failure;
  _at_<path_name>/<file_name>.sql
  ...
  ...
  exit;
!

if grep "ORA-" $TMP >/dev/null
then

   echo "Failed"
   exit 1
fi

rm -f $TMP
echo "Done"

Harry Received on Mon Oct 30 2000 - 22:26:13 CET