#!/usr/bin/bash

# This script calls the Ad_1.sql script and spools the output with date stamp to a log file
# Update: You MUST add your environment variables one way or another, e.g. look below

export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/OraHome_1
PATH=${PATH}:${ORACLE_HOME}/bin; export PATH
export LIBPATH=${LIBPATH}:${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib

if [ ! -f lock ]; then

sqlplus -s user/password@sid << EOF
column dcol new_value mydate noprint
select sysdate dcol from dual;
spool log_ad_1_&mydate..log
@Ad_1.sql;
spool off
EOF

fi

where:

if [ ! -f lock ]: very useful statement, it checks whether a file named “lock” exists or not, if it doesn’t exist, it will not execute the code block within.

sqlplus -s usr/passwod@sid: to run sqlplus commands as if I’m entering the commands manually, the -s flag lets sqlplus accept the standardin input until it finds the word “EOF”

Part 2 will be about adding the above script in crontab and executing it daily.