Bash Script to run a sql file (Draft) – Part 1

#!/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.

Advertisements

About SoCRaT

Research Support and Infrastructure Engineer, OSS & Linux Geek
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s