runsql.sh shell tool to run a SQL file

Purpose: Run SQL or PL/SQL in a file.

Background:

For the SQL that takes a long time to finish, we usually let them run as a backend job in database server. E.g. rebuild index, Online Table Redefinition.
This shell tool will make it simple to call a SQL_file.
.
When doing low level things I prefer to run as sys via sqlplus on the local server (not via client sqlplus and never thru tool).  General good practice.  Eliminate workstation or network flaps etc from causing issues.
.

Notes: use full name when referencing the database objects. e.g. scott.emp;

cat  $SQLPATH/runsql.sh
.
{code start}

#Goal: run SQL in a file, so we can run it with nohup in background,


if [ $# != 1 ]
then
 echo Usage: $0 "SQL file_name"
 echo Usage 2: nohup $0 "SQL_file_name > 0.log &"
 exit
else
 echo $1
 sql_file=$1
fi

#my_path=$(pwd)
#. $HOME/.bash_profile
#echo $my_path

sqlplus -S / as sysdba  <<+++
set wrap off
--set feedback off
set pagesize 0
set verify off
set termout off

set echo off
set verify off
set arraysize 2000
set linesize 5000
set trimspool on
set long 5000
set serveroutput on
set timing on

column filename new_val filename

select '$sql_file'||'_'|| instance_name || to_char(sysdate, '_yyyymmdd_hh24miss')||'.log' filename
from v\$instance;

spool &filename

select sysdate from dual;

@$sql_file

exit;

+++

MAIL_TO=your_name@abc.com
CC=your_group_name@abc.com

tail "$sql_file"*.log | mail -s "SQL $sql_file done at $(hostname) $ORACLE_SID" -c $CC $MAIL_TO

{code end}
.