Recently at one of our client site we have to write shell script, which runs every night and goes through the database errors from logs of previous day. Now there are different ways of manipulating date in the korn shell it self but for all of that we have to write logic. Since we have to go through files of previous day, we have to be extra careful for range conditions. Ex. For 1st March we have to process data of 28th or 29th February based on the leap year. On 1st January we have to process data for 31st December of the previous year etc.
Instead of doing date arithmetic in the Korn shell, we decided to use Oracle database’s date logic since we can manipulate system date (sysdate) easily. In the following shell script we are showing 3 methods of obtaining the previous day’s date. Please copy the following contents into one file on AIX box and save it as a test.sh file and then execute the script. Before execution please make sure that you have proper privilege to execute the script.
#!/bin/ksh
DB_USER=${1}
DB_PSWD=${2}
DB_NAME=${3}
############
#Method 1:
############
v_prev_date=`TZ=bb24 date +%Y%m%d`
echo ‘Yesterday –> ‘$v_prev_date
##############################################
# Method 2:
# one way of getting system date from Oracle
# and pass it to shell script variable.
##############################################
SQLQUERY=”select to_char(sysdate – 1,’YYYYMMDD’),to_char(sysdate,’YYYYMMDD’) from dual”
print “
set pagesize 0;
set feedback off;
set heading off;
$SQLQUERY;
“| sqlplus -S $DB_USER/$DB_PSWD@$DB_NAME > TEMP_FILE
v_prev_date=`cat TEMP_FILE | awk ‘{print $1}’`
v_curr_date=`cat TEMP_FILE | awk ‘{print $2}’`
print ‘Method 2 –> ‘$v_prev_date
print ‘Method 2 –> ‘$v_curr_date
###################################################
# Method 3:
# Alternate way of getting system date from Oracle
# and pass it to shell script variable.
###################################################
var1=`sqlplus -S $DB_USER/$DB_PSWD@$DB_NAME << EOF
set echo off term off feed off ver off pages 0
select to_char(sysdate – 1,’YYYYMMDD’), to_char(sysdate,’YYYYMMDD’) from dual;
exit;
EOF`
v_prev_date=`echo $var1 | awk ‘{print $1}’`
v_curr_date=`echo $var1 | awk ‘{print $2}’`
print ‘Method 3 –> ‘$v_prev_date
print ‘Method 3 –> ‘$v_curr_date
Upon executing above shell script, we will get following results.
./test.sh scott tiger orcl
Result is
Yesterday –> 20090726
Method 2 –> 20090726
Method 2 –> 20090727
Method 3 –> 20090726
Method 3 –> 20090727
To display first line in the output, we are using AIX’s date command along with TZ (timezone) variable. 24 is offset to get the previous date and bb is just the string. It can be any string. Instead of ‘bb’ one can use ‘aa’ or ‘deci’ etc. If instead of 24 we use 48, then it will display day before yesterday’s date.
In method 2, output of SQL is written to file (temp_file) and then we are reading the file to get the date and previous date both.
In method 3, instead of writing the output to file, we are assigning it straight to shell variable and then from that shell variable, we are using awk command to get the desired result. Since we are relying on database’s date every condition is taken care of. So be it a 1st January 2010 or 1st March of 2008, we are going to get correct result of 20091231 and 20080229 respectively.
This shell script also serves as an example of how we can assign SQL*Plus output to shell variable. In most scenarios, we pass shell variable to SQL script but in this case it is reverse.
Resources:
