Thursday, August 22, 2013

Calling SQL Plus Utility in UNIX

I am writing a shell script which invokes sqlplus utility. This script calls another two fucntions to return the db username/pwd and SID. 
Function 1 to get the db username/pwd 
Funtion 2 to get the db instance. 
Could you please help me cretaing tjese 2 functions..
Here is an example :: 

#!/bin/ksh
#
# Make a two dimension array of time interval and successful
# course registrations during the interval. Intervals can be
# second, minute, hour, day.

# Count up timestamps with associative array then filter with sort command.
counter () {
/bin/nawk '
{time_array[$0]++}
END {for (time in time_array) print time, time_array[time] }' |
sort -nr
}

# Usage message.
usage () {
echo
echo Usage: $0 's m h d date-string'
echo "s=second, m=minute, h=hour, d=day"
echo "date-string=11-MAR-98"
echo Example: $0 'h 11-SEP-98'
echo
exit
}

############################################## #########
# Main

export ORACLE_HOME=/home/oracle/app/oracle/product/7.3.2
export ORACLE_SID=p_web

COMMENT=""
DAY=\'$2\'

case $1 in
s) INTERVAL=\'YYYY-MM-DD:HH24:MI:SS\';;
m) INTERVAL=\'YYYY-MM-DD:HH24:MI\';;
h) INTERVAL=\'YYYY-MM-DD:HH24\';;
d) INTERVAL=\'YYYY-MM-DD\'; COMMENT="--" ;;
*) usage;;
esac

case $2 in
"") usage;;
*) ;;
esac

# format string in SQL: select to_char(entry_date,'YYYY-MM-DD:HH24:MI:SS')
sqlplus << EOF | counter
name/passwd
set pages 0 feed off echo off
select to_char(entry_date,$INTERVAL)
from feestmt
$COMMENT where to_char(entry_date) = $DAY
order by 1;
EOF
#################### cut here ######################
ORACLE_HOME=/oracle/home; export ORACLE_HOME 
ORACLE_SID=RP9WL; export ORACLE_SID 
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin; export PATH 
echo -e " \n \n" 
/oracle/home/bin/sqlplus 
username/password@sid@/opt/omni/lbin/anl_tab_l st.sql <<EOF 
EOF


More Information:




0 comments:

Post a Comment

 
Design by BABU | Dedicated to grandfather | welcome to BABU-UNIX-FORUM