Cacher is the code snippet organizer for pro developers

#!/usr/bin/ksh

#Parameter : yyyymmdd, example 20180701

ORACLE_SID="OPRD016"
ORACLE_BASE="/apps/oracle/"
ORACLE_HOME="/apps/oracle/product/11.1/"
PATH=$PATH:$ORACLE_HOME/bin

cd $data_dir
export ORACLE_SID ORACLE_HOME ORACLE_BASE PATH

if [ $# -eq 0 ]
then
	#Using 1 days prior
	KEMARIN="-1"

	eval `date "+day=%d; month=%m; year=%Y"`
	typeset -Z2 day month
	typeset -Z4 year

	# Subtract offset from day, if it goes below one use 'cal'
	# to determine the number of days in the previous month.
	# cari tanggal kemaren, klo kemaren tanggalnya kurang dari 1 berarti pake fungsi 'cal'
	# buat ngitung jumlah hari di bulan sebelumnya
	day=$((day + KEMARIN))
	if (( day <= 0 )) ;then
	  month=$((month - 1))
	  if (( month == 0 )) ;then
	   year=$((year - 1))
	   month=12
	  fi
	  set -A days `cal $month $year`
	  xday=${days[$(( ${#days[*]}-1 ))]}
	  day=$((xday + day))
	fi

	day=$(printf "%02g" $day)
	month=$(printf "%02g" $month)
	year=$(printf "%04g" $year)

	DATE=$year$month$day
	echo "Using 3 days prior date : "$DATE
else
	DATE=$1
	echo "Using parameter date : "$DATE
	eval `date "+day=%d; month=%m; year=%Y"`
	day=$(echo $DATE|cut -c7-8)
	month=$(echo $DATE|cut -c5-6)
	year=$(echo $DATE|cut -c1-4)
	DATE=$year$month$day
fi

if [ -z != "$DATE" ];then
	FILE='LOG_SOAP_FA_'$DATE'.txt'
	echo "Preparing loading "$FILE
	DATA_DIR='/data1/TSEL_POIN/REDEEM'
	TABLE_NAME='REDEEM_POIN_POSTPAID'
	DIR_CONTROL='/data1/TSEL_POIN/SCRIPTS/CONTROLS'
	DIR_LOG='/data1/TSEL_POIN/SCRIPTS/LOGS'
	LOADING_FILE="/data1/TSEL_POIN/SCRIPTS/LOADING/loading_post.txt"
	awk -F'|' '{if(NR > 1) print $0}' $DATA_DIR/$FILE > $LOADING_FILE
	gzip -q $DATA_DIR/$FILE
	echo "Table Name : COPR."$TABLE_NAME

	# oracle details
	UNAME='copr'
	PWD='copr01'
	SID='OPRD016'

	echo "Day : "$day
	# delete existing data to prevent duplicate
	echo "Attempting to delete data for period: "$DATE
	sqlplus -s $UNAME/$PWD@$SID<<!
	SET SERVEROUTPUT ON;
	set echo on;
	DELETE FROM COPR.$TABLE_NAME WHERE FILE_DATE='$DATE';
	COMMIT;
	exit;
!

	LINENUM=$(cat $LOADING_FILE | wc -l)
	echo "Loading "$LINENUM" lines from "$LOADING_FILE
	echo "Attempting to load data from flat file "$FILE" into table "$TABLE_NAME
	file_control=$DIR_CONTROL'/'$FILE'_control_file.ctl'
	file_log=$DIR_LOG'/'$FILE'_log_file.log'
	bad_log=$DIR_LOG/bad_$FILE.log
	error_log=$DIR_LOG/err_$FILE.log
	DIRORAC=$ORACLE_HOME/bin

	# ctrl file
	echo "" > $file_control
	echo "OPTIONS ( ERRORS = 10000000, DIRECT = FALSE, SKIP_INDEX_MAINTENANCE = FALSE, " >> $file_control
	echo " PARALLEL = FALSE, ROWS = 3000, BINDSIZE = 10000000, READSIZE = 10000000) " >> $file_control
	echo "LOAD DATA " >> $file_control
	echo "INTO TABLE COPR.$TABLE_NAME APPEND " >> $file_control
	echo "FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( " >> $file_control
	echo "EVENT,MSISDN,OFFER_ID,RETVAL char(1000),INSERT_DATE,REQ_DATE,RESP_DATE,FLAG,INFO,KEYWORD,PID,CALLBACK,PROGRAM_ID" >> $file_control
	echo ",FILE_NAME CONSTANT \"$FILE\" " >> $file_control
	echo ",FILE_DATE CONSTANT \"$DATE\" " >> $file_control
	echo ")" >> $file_control

	echo "start loading"
	$DIRORAC/sqlldr userid=$UNAME/$PWD@$SID control=$file_control data=$LOADING_FILE direct=true log=$file_log 2>$error_log bad=$bad_log
	echo "done loading "$LOADING_FILE" to "$TABLE_NAME" \n\n"
fi