Executing procedure using script

Shell Programming and Scripting
1

Hi,

I want to have a automted script to exceute 20 procedures one by one.
Suppose below is one procedure once it get executed script will write
"PL/SQL procedure successfully completed." to a log

for ex-

exec dbms_stats.gather_table_stats();

Now later procedure starts executing only when previous one gets completed.
Can it be possible to have such script

Your prompt help would be much appreciated.

2

See the top line: the proc name followed by \. Add as many procedures as you wish.
They have to be procedures because functions produce a return value. procedures do not.

for proc in  'dbms_stats.gather_table_stats();' \
                'dbms_stats.foo_table_stats();' \
                'dbms_stats.bar_table_stats();' \
                'last you procedure want goes here with a comma at the end ; '
do
   $ORACLE_HOME/bin/sqlplus -s user/password@oracleinstancename <<EOF
    set lines 120
    set whatever else you want
    DECLARE
    BEGIN
    $proc
    exit
    END;
/
EOF
    echo "$proc Succesfully completed"
done > mylogfile.log

See the two EOF? they have to be placed exactly as shown, with the last EOF has to be in the leftmost column. The /
above the last EOF also should be in the leftmost column.

1 Like