sql from shell script
I have the below shell script in which sql file is called which has set of select and insert statements. Right now it's spooling output/error of the sql select /insert commands to the csv file. I want the output and error of the sql commands redirected to the shell script LOGFILE instead of spool file. How can i do it.
LOGPATH=${TEST_LOG}
LOGFILE=${SCRIPTNAME}.$(date '+%Y%m%d_%H%M%S').log
sql_test=${REPORT_HOME}/month_report.sql
exec > ${LOGPATH}/${LOGFILE} 2>&1
main "$@"
exit 0
main()
{
SPOOLTEST="${REPORT}/testreports/report_`date +%Y%m%d_%H%M%S`.csv"
$ORACLE_HOME/bin/sqlplus -s << ENDSQL
${DBLOGIN}@${DBNAME}
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
开发者_如何学运维 SPOOL ${SPOOLTEST}
@${sql_test}
SPOOL OFF
ENDSQL
return
}
spool logs after a delay. after shell script logs i am able to find the spool logs. I tried the below one it's not working $ORACLE_HOME/bin/sqlplus -s << ENDSQL >> ${LOGPATH}/${LOGFILE} 2>&1
I have no installation of Oracle client on this machine, but would not replacing
SPOOL ${SPOOLTEST}
with
SPOOL ${LOGPATH}/${LOGFILE}
work ?
when i do the below in shell script it writes to the logfiles SET TRIMSPOOL ON
There is no easy way to do this - the shell can't "tell" which is an error, as SQL*Plus writes everything to STDOUT. It only writes to STDERR if there is a failure in SQL*Plus itself. You will have to do this using a language that can connect to the DB directly (e.g. Perl, Python, etc etc) and process the query results as a structured result set, and raise/catch exceptions for errors.
#!/bin/bash
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#Author: Rachangouda @
#Script: - Summary Data Cleanup Script @
#Version: v0.1 @
#Create date: Sep 23 2014 @
#Description: Script to cleanup aggregation table data based on retention period. Required BIPs will be excluded as configured@
# Cleanup will be done AFTER BCP OUT of all data. BCP files are stored under folder of naming convention: @
# <Tbl Name>_<dd_Mmm_yy of retention DATE> @
#IMPORTANT: Befor running this scripts please make sure that the below ENVIRONMENT VARIABLES are set. @
# Like: @
# export ORACLE_HOME=/disk2/ORACLE11G/product/11.2.0/db_1/ @
# export PATH=$PATH:$ORACLE_HOME/bin @
# export ORACLE_SID=orcl11g @
# @
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#Command LineArugments Parse
objArgs=$@
objArgs_lenth=$# #Length of Arguments passed to this script
ScriptName=$0 #this script name
FirstArg=$1 #Retention Days for all entity periods to be considered.
SecondArg=$2 #Clean up Flag value
NumMatch="^[0-9]+$"
clnupFlag="NO"
minRetentionDays=720; #Minimum retention days per site.
delBatch=100000; #Batch size for deleting table data. BE VERY CAUTIOUS with this value setting.
#Destination location where BCP OUT files will be archived and organized. This location should be write enabled for user which runs this script.
archivalLoc="/disk3/PROJECTS/Rachan"
#retentionDays=$FirstArg #Retention Days for all entity periods to be considered.
exclBIP="80,81,82,83,94,95,96,97,120,121,324999781,324999782" #BIP IDs that will be excluded from cleanup.
exclKATRealm="'whls-brighthouse','whls-brightlink','whls-brightlinkNE5','whls-comcasttsp','whls-comcasttspORIG'" #Realms to be excluded from KPI_AGGREGATED_TBL
########### DataBase Details ###############
USERNAME=dbusername
PASSWORD=paswd
SERVICE_ID=orcl11g
#### Command usage and validation
if [ "$objArgs_lenth" -eq "0" ] || [ "$objArgs_lenth" -gt "2" ]; then
echo "Usage: "$ScriptName" <Retention Days greater than 364> [Script Mode]"
echo "Set no second parameter (Script Mode), for BCP Only mode. Set second parameter as \"CLNUP\", to BCP and clean up data."
exit 1
fi
if [ "$objArgs_lenth" -le "2" ]; then
if [ $FirstArg != ${FirstArg//^[0-9]$/} ]; then
echo "First is Parameter (retention days) is Not a Number."
exit 1
fi
fi
if [ "$objArgs_lenth" -eq "2" ]; then
clnupFlag=$SecondArg
if [ $SecondArg=CLNUP -a "$FirstArg" -lt "$minRetentionDays" ]; then
echo "Minimum retention days allowed = "$minRetentionDays", when Script Mode is \"CLNUP\"."
echo "Example for using this utility: $0 720"
echo "OR $0 720 CLNUP"
exit 1
fi
#if [ $SecondArg != CLNUP ]; then
#echo "Clean up Script Mode is \"CLNUP\". example: $0 720 CLNUP"
#exit 1
#fi
fi
###Command validation End
##Main Script Start
# Foldercheck task
if [ ! -d "$archivalLoc" ]; then
echo "Destination Location mentioned below don't exist or not accessible: "$archivalLoc
else
# Control will enter here if $DIRECTORY doesn't exist.
echo "DESTINATION LOCATION = "$archivalLoc
echo "Retention Days = "$FirstArg
if [ $clnupFlag=CLNUP ]; then
echo "Script Mode = BCP and CLEANUP.";
else
echo "Script Mode = BCP ONLY.";
fi
fi
#echo $USERNAME" "$PASSWORD" "$SERVICE_ID
# Current date from DB
currDate=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF LINESIZE 16 ECHO OFF;
select to_char(sysdate, 'DDMMYYYY') || '_' || to_char(sysdate, 'hhmmss') from dual;
EXIT;
EOF`
echo $currDate
#$currDate="${currDate/\-/\_}"
#currDate=`echo $currDate_oracle | sed -e 's/\-/\_/g'`
echo "converted date is:"$currDate
tskFolder=$archivalLoc"/"$currDate
if [ ! -d "$tskFolder" ]; then
echo "Folder is creating now";
mkdir $tskFolder;
echo "task folder is:"$tskFolder
fi
#retention date calculation
retentionDate_oracle=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
select sysdate - $FirstArg from dual;
EXIT;
EOF`
echo "calculated ret date is: "$retentionDate_oracle
#During table creation Oracle Do not allow table names with "-", date is converted from 01-sep-1 to 01_sep_14
retentionDate=`echo $retentionDate_oracle | sed -e 's/\-/\_/g'`
echo "converted(01-sep-1 to 01_sep_14) retention date:"$retentionDate
bcpSfx="_"$retentionDate
echo "BCPFX is: "$bcpSfx
#idsTbl Check and population
idsTbl_lowercase="z_idsTbl_"$retentionDate
echo "idsTbl value is: "$idsTbl_lowercase
#Table presence check query works only on Upper case Table names
idsTbl_uppercase=`echo $idsTbl_lowercase | tr '[:lower:]' '[:upper:]'`
echo "idsTbl Upper Case val is:"$idsTbl_uppercase
idsTbl=$idsTbl_uppercase
echo "idsTbl value after upper case"$idsTbl
#idsTbl presence check
isTbl=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
select object_id from user_objects where object_name = '$idsTbl';
EXIT;
EOF`
echo "isTbl flag value is:"$isTbl":"
#If the table is present then isTbl Flag is set to some positive number else null
if [ ! -z "$isTbl" ]; then #-a "$isTbl" != " " ]; then
echo "Reusing existing table: [idsTbl]"$idsTbl
else
echo "inside [$idsTbl] creation"
## IMPORTANT convert table name to upper case first
sqlret_create1=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
CREATE TABLE $idsTbl (bip_id NUMBER(19), bpd_id NUMBER(19));
CREATE INDEX bip_id_idx ON $idsTbl (bip_id asc);
CREATE INDEX bpd_id_idx ON $idsTbl (bpd_id asc);
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS ERROR while creating [$idsTbl]"
exit 1
fi
fi
rowCount=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
SELECT count(*) FROM $idsTbl;
EXIT;
EOF`
echo "First [$idsTbl] row count val:"$rowCount":"
if [ "$rowCount" -eq "0" ]; then
echo "inside INSERT EXCLUDED BIPS"
sqlret_insert1=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
INSERT INTO $idsTbl SELECT DISTINCT bip.bip_id, bpd.bpd_id from bill_period bpd inner join bill_profile bip on bip.bip_id=bpd.bip_id where bip.bip_id not in ($exclBIP);
COMMIT;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS ERROR While inserting records into [$idsTbl]"
exit 1
fi
rowCount2=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
select count(*) from $idsTbl;
EXIT;
EOF`
if [ "$rowCount2" -eq "0" ]; then
echo "Zero BIP"
echo "Droping the Table[$idsTbl] and exiting"
sqlret_drop1=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
DROP TABLE $idsTbl;
COMMIT;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS ERROR while dropping table [$idsTbl].. Exiting from cleanup.."
exit 1
else
echo "Table [$idsTbl] is dropped sucessfully "
fi
fi
fi
############ sssTbl Check and population
sssTbl_lowercase="z_sssTbl_"$retentionDate
sssTbl_uppercase=`echo $sssTbl_lowercase | tr '[:lower:]' '[:upper:]'`
echo "sssTbl Upper Case val is:"$sssTbl_uppercase
sssTbl=$sssTbl_uppercase
#idsTbl check
issssTbl=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
select object_id from user_objects where object_name = '$sssTbl';
EXIT;
EOF`
echo "issssTbl flag value is:"$issssTbl":"
#If the table is present then issssTbl Flag is set to some positive number else null
if [ ! -z "$issssTbl" ]; then #-a "$isTbl" != " " ]; then
echo "Reusing existing table: [$sssTbl]"
else
echo "inside '$sssTbl' table creation"
## IMPORTANT convert table name to upper case first
sqlret_create2=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
CREATE TABLE $sssTbl (sss_id NUMBER(19));
CREATE INDEX sss_id_idx ON $sssTbl (sss_id asc);
EXIT;
EOF`
echo "SSS_ID table and index created"
fi
echo "row counting for sss_id table"
rowCount3=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
SELECT count(*) from $sssTbl;
EXIT;
EOF`
echo "row count val for sss_id table:"$rowCount3
if [ "$rowCount3" -eq "0" ]; then
echo "row count for sss_id table is zero so inserting records from settlement_summary table"
#echo "INSERT INTO $sssTbl SELECT DISTINCT sss.sss_id FROM AVEA_USG.SETTLEMENT_SUMMARY sss INNER JOIN AVEA_REF1.$idsTbl t1 ON t1.bip_id=sss.bip_id AND t1.bpd_id=sss.bpd_id where sss.evt_dttm < to_char(sysdate - $retentionDate_oracle);"
sqlret_insert2=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
INSERT INTO $sssTbl SELECT DISTINCT sss.sss_id FROM AVEA_USG.SETTLEMENT_SUMMARY sss INNER JOIN AVEA_REF1.$idsTbl t1 ON t1.bip_id=sss.bip_id AND t1.bpd_id=sss.bpd_id where sss.evt_dttm < to_char(sysdate - $FirstArg);
COMMIT;
EXIT;
EOF`
rowCount4=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
select count(*) from $sssTbl;
EXIT;
EOF`
echo "Row count After inserting sss_id table with settlement_summary:"$rowCount2
if [ "$rowCount4" -eq "0" ]; then
echo "Zero SSS"
echo "Droping the Table[$sssTbl] and exiting"
sqlret_drop2=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
drop table $sssTbl;
commit;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS Error in dropping table [$sssTbl] Exiting from cleanup.."
exit 1
else
echo "Table [$sssTbl] is dropped sucessfully "
fi
fi
fi
#Array of Tables to be bcp'ed OUT and deleted.
declare -a array=("01;idsTbl;$idsTbl;$tskFolder;$bcpSfx"
"02;sssTbl;$sssTbl;$tskFolder;$bcpSfx"
"03;SSE;AVEA_USG.settlement_summary_error where sse_id in (select distinct sss.sse_id from AVEA_USG.settlement_summary sss inner join $sssTbl t1 on t1.sss_id=sss.sss_id);$tskFolder;$bcpSfx"
"04;SSW;AVEA_USG.settlement_summary_work where sss_id in (select sss_id from $sssTbl);$tskFolder;$bcpSfx"
"05;SSS;AVEA_USG.settlement_summary where sss_id in (select sss_id from $sssTbl);$tskFolder;$bcpSfx"
"06;KPI;AVEA_REP.KPI_AGGREGATED_TBL where kat_id in (select distinct kat.kat_id from kpi_aggregated_tbl kat where kat.kat_realm not in ($exclKATRealm) and kat.evt_dttm < to_char(sysdate - $FirstArg));$tskFolder;$bcpSfx"
"07;RSM;AVEA_USG.report_summary where rsm_id in (select distinct rsm.rsm_id from AVEA_USG.report_summary rsm inner join $idsTbl t1 on t1.bip_id=rsm.bip_id and t1.bpd_id=rsm.bpd_id where rsm.rsm_dttm < to_char(sysdate - $FirstArg));$tskFolder;$bcpSfx"
"08;AIS;AVEA_USG.accruals_incoming_summary where ais_id in (select distinct ais.ais_id from AVEA_USG.accruals_incoming_summary ais inner join $idsTbl t1 on t1.bip_id=ais.bip_id and t1.bpd_id=ais.bpd_id where ais.ais_month < to_char(sysdate - $FirstArg));$tskFolder;$bcpSfx"
"09;ATS;AVEA_USG.accruals_its_summary where ats_id in (select distinct ats.ats_id from AVEA_USG.accruals_its_summary ats inner join $idsTbl t1 on (t1.bip_id=ats.ats_in_bip_id or t1.bip_id=ats.ats_out_bip_id) and (t1.bpd_id=ats.ats_in_bpd_id or t1.bpd_id=ats.ats_out_bpd_id) where ats.ats_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"10;AOS;AVEA_USG.accruals_outgoing_summary where aos_id in (select distinct aos.aos_id from AVEA_USG.accruals_outgoing_summary aos inner join $idsTbl t1 on t1.bip_id=aos.aos_out_bip_id and t1.bpd_id=aos.aos_out_bpd_id where aos.aos_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"11;ARD;AVEA_USG.accruals_rated_detail where ard_id in (select distinct ard.ard_id from AVEA_USG.accruals_rated_detail ard inner join $idsTbl t1 on t1.bip_id=ard.bip_id and t1.bpd_id=ard.bpd_id where ard.ard_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"12;ARDW;AVEA_USG.accruals_rated_detail_work where ard_id in (select distinct ardw.ard_id from AVEA_USG.accruals_rated_detail_work ardw inner join $idsTbl t1 on t1.bip_id=ardw.bip_id and t1.bpd_id=ardw.bpd_id where ardw.ard_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"13;DAS;AVEA_USG.daily_accruals_summary where das_id in (select distinct das.das_id from AVEA_USG.daily_accruals_summary das inner join $idsTbl t1 on (t1.bip_id=das.das_in_bip_id or t1.bip_id=das.das_out_bip_id) and (t1.bpd_id=das.das_in_bpd_id or t1.bpd_id=das.das_out_bpd_id) where das.das_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"14;DES;AVEA_USG.daily_estimated_summary where des_id in (select distinct des.des_id from AVEA_USG.daily_estimated_summary des inner join $idsTbl t1 on (t1.bip_id=des.des_in_bip_id or t1.bip_id=des.des_out_bip_id) and (t1.bpd_id=des.des_in_bpd_id or t1.bpd_id=des.des_out_bpd_id) where des.des_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"15;DPS;AVEA_USG.daily_projection_summary where dps_id in (select distinct dps.dps_id from AVEA_USG.daily_projection_summary dps inner join $idsTbl t1 on (t1.bip_id=dps.dps_in_bip_id or t1.bip_id=dps.dps_out_bip_id) and (t1.bpd_id=dps.dps_in_bpd_id or t1.bpd_id=dps.dps_out_bpd_id) where dps.dps_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"16;ESM;AVEA_USG.estimate_suspense_summary where esm_id in (select distinct esm.esm_id from AVEA_USG.estimate_suspense_summary esm inner join $idsTbl t1 on t1.bip_id=esm.bip_id and t1.bpd_id=esm.bpd_id where esm.esm_evt_dttm < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"17;EDS;AVEA_USG.ext_amts_daily_summary where eds_id in (select distinct eds.eds_id from AVEA_USG.ext_amts_daily_summary eds inner join $idsTbl t1 on t1.bip_id=eds.bip_id where eds.eds_date < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"18;HMN;AVEA_USG.hourly_margin where hmn_id in (select distinct hmn.hmn_id from AVEA_USG.hourly_margin hmn where hmn.hmn_evt_hour < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"19;ITS;AVEA_USG.incoming_traffic_summary where its_id in (select distinct its.its_id from AVEA_USG.incoming_traffic_summary its inner join $idsTbl t1 on t1.bip_id=its.bip_id and t1.bpd_id=its.bpd_id where its.its_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"20;TTS;AVEA_USG.its_traffic_summary where tts_id in (select distinct tts.tts_id from AVEA_USG.its_traffic_summary tts inner join $idsTbl t1 on (t1.bip_id=tts.tts_in_bip_id or t1.bip_id=tts.tts_out_bip_id) and (t1.bpd_id=tts.tts_in_bpd_id or t1.bpd_id=tts.tts_out_bpd_id) where tts.tts_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"21;OTS;AVEA_USG.outgoing_traffic_summary where ots_id in (select distinct ots.ots_id from AVEA_USG.outgoing_traffic_summary ots inner join $idsTbl t1 on t1.bip_id=ots.ots_out_bip_id and t1.bpd_id=ots.ots_out_bpd_id where ots.ots_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx"
"22;PRD;AVEA_USG.projection_rated_detail where prd_id in (select distinct prd.prd_id from AVEA_USG.projection_rated_detail prd inner join $idsTbl t1 on t1.bip_id=prd.bip_id where prd.prd_month < (sysdate - $FirstArg));$tskFolder;$bcpSfx");
#array declaration
#SPOOL AND ZIPPING PROCESS BEGINS
spoolOffTbl ()
{
echo "inside spool function"
var=$1
order=`echo $var | awk -F';' '{ printf $1}'` #Extracting Order value from input param
tblSfx=`echo $var | awk -F';' '{ printf $2}'` #Extracting Table Suffix value from input param
tblWhere=`echo $var | awk -F';' '{ printf $3}'` #Extracting Query value from input param
targetFolder=`echo $var | awk -F';' '{print $4 }'` #Extracting Traget folder value from input param
bcpSuffix=`echo $var | awk -F';' '{print $5}'` #Extracting Table suffix value from input param
Folder="$targetFolder/$order$tblSfx$bcpSuffix.bcp"
#echo $order
#echo $tblSfx
#echo $tblWhere
#echo $targetFolder
#echo $bcpSuffix
# IMPORTANT DO NOT ECHO THE QUERY IN TERMINAL
#echo "SPOOL stdout on to $targetFolder/$order"_"$tblSfx$bcpSuffix.bcp"
echo "Spooled files stored in the Folder:"$archivalLoc
spoolret=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 LINESIZE 3000 TRIMOUT ON TRIMSPOOL ON TAB OFF WRAP OFF VERIFY OFF ECHO OFF HEADING OFF FEEDBACK OFF TERMOUT OFF;
SPOOL $Folder
SET COLSEP ","
select * from $tblWhere;
SPOOL OFF
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "ERROR! SQL*Plus failed..."
exit 1
else
echo "zipping started"
#zip <Options> <NameOfZippingFolder> <FromDirectory>
#Option -r is for recursivly checks files to be zipped
zip -r $targetFolder $targetFolder
echo "zipping end"
if [ $? -ne 0 ]; then
echo "Error occured during Zipping"
exit 1
else
if [ -f $Folder ]; then
rm -r $Folder
fi
fi
fi
}
#Iterating the array
for i in "${array[@]}"
do
#echo "printing first array"
#echo $i IMPORTANT ALWAYS pass ARRAY ARGUMENT INSIDE DOUBLE QUOTE TO METHOD otherwise string break when empty space is encountered
spoolOffTbl "$i"
if [ $? == 1 ]; then
echo "error in Spool Off and Zipping"
exit 1
fi
echo "Spool off is done"
done
#SPOOL AND ZIPPING PROCESS ENDS
#CLEANUP BEGINS
clnupTbl ()
{
echo "inside Clean Up function..."
var=$1
tblWhere=`echo $var | awk -F';' '{ printf $3}'`
Folder="$targetFolder/$order$tblSfx$bcpSuffix.bcp"
cleanupret=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
declare v_cnt number;
begin
select count(*) into v_cnt from $tblWhere and rownum = 1;
while (v_cnt > 0)LOOP
delete from $tblWhere and rownum <= $delBatch;
commit;
select count(*) into v_cnt from $tblWhere and rownum = 1;
end LOOP;
end;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "ERROR! SQL*Plus failed..."
exit 1
else
echo "Clean Up is Done"
fi
}
if [ "$clnupFlag" == "CLNUP" ]; then
echo "Clean up is starting..."
for i in "${array[@]}"
do
clnupTbl "$i"
if [ $? == 1 ]; then
echo "Error in table data cleanup:"
exit 1
fi
echo "Data Cleanup is done"
done
fi
#CLEANUP ENDS
#DROP TABLE $idsTbl; COMMIT;
final_dropidsTbl=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS Error in dropping table [$idsTbl]"
exit 1
else
echo "Table [$idsTbl] is dropped sucessfully"
fi
#add drop query DROP TABLE $sssTbl; COMMIT;
final_dropsssTbl=`sqlplus -s -l $USERNAME/$PASSWORD@$SERVICE_ID <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;
EXIT;
EOF`
if [ $? -ne 0 ]; then
echo "SQLPLUS Error in dropping table [$sssTbl]"
exit 1
else
echo "Table [$sssTbl] is dropped sucessfully "
fi
echo "Settlement summery Clean Up is completed"
#Main script
#Following Bill Profiles are excluded from Summary Cleanup: Refer to var exclBIP.
加载中,请稍侯......
精彩评论