sqloader example. Loading several files types via bash and sqloader.

====================
General
====================

Load via bash and sqloader to three tables.

====================
Files
====================
ora_env.ini
ipn_profile_load.ini
main_ipn_conv_load.sh
load_handler.sh
sql_loader_cmd.sh
sql_loader_vrs_configuration.ctl
sql_loader_black_list.ctl
sql_loader_mccmnc.ctl

====================
Code
====================

==================================

ora_env.ini

==================================

ORA_VER=1120
ORACLE_SID=igt
ORACLE_BASE=/software/oracle
ORACLE_HOME=/software/oracle/112

==================================
ipn_profile_load.ini
==================================
DB_USER=ipn_conv
DB_PASS=ipn_conv
DB_INST=igt
DELIMITER="==================================="

WORK_DIR=/starhome/iu/workarea/IPN_LOAD
SCRIPTS_DIR=/starhome/iu/workarea/IPN_LOAD/scripts

LOG_FILE=/starhome/iu/workarea/IPN_LOAD/logs/ipn_load.log

INPUT_DIR=/starhome/iu/workarea/IPN_LOAD/files/input
PROCESS_DIR=/starhome/iu/workarea/IPN_LOAD/files/in_process
HANDLED_FILES_DIR=/starhome/iu/workarea/IPN_LOAD/files/handled
LOG_DIR=/starhome/iu/workarea/IPN_LOAD/logs

INPUT_FILE_NAME_ZIP=moco_managed_IPN_LOAD*dat.gz
INPUT_FILE_NAME=moco_managed_IPN_LOAD*dat

PATTERN_EXAMPLE=IPN_LOAD_[0-9]{2}.[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}-[0-9]{2}.csv
INPUT_FILE_PATTERN_1=vrs_configuration.csv
INPUT_FILE_PATTERN_2=black_list.csv
INPUT_FILE_PATTERN_3=mccmnc.csv

FLOW_1=vrs_configuration
FLOW_2=black_list
FLOW_3=mccmnc

SQL_LOADER_DIR=/starhome/iu/workarea/IPN_LOAD/loader_code
SQL_LOADER_DIR_IN=/starhome/iu/workarea/IPN_LOAD/loader_code/in
SQL_LOADER_DIR_BAD=/starhome/iu/workarea/IPN_LOAD/loader_code/bad
SQL_LOADER_DIR_LOG=/starhome/iu/workarea/IPN_LOAD/loader_code/log
SQL_LOADER_DIR_OLD=/starhome/iu/workarea/IPN_LOAD/loader_code/old

SQL_LOADER_DAT_FILE=/starhome/iu/workarea/IPN_LOAD/loader_code/in/load_data.dat
SQL_LOADER_BAD_FILE=/starhome/iu/workarea/IPN_LOAD/loader_code/bad/bad_load_data.dat
SQL_LOADER_HIST_FILE=/starhome/iu/workarea/IPN_LOAD/loader_code/old/load_data.old
SQL_LOADER_LOG_FILE=/starhome/iu/workarea/IPN_LOAD/loader_code/log/load_load.log

CTL_FILE_1=/starhome/iu/workarea/IPN_LOAD/loader_code/sql_loader_vrs_configuration.ctl
CTL_FILE_2=/starhome/iu/workarea/IPN_LOAD/loader_code/sql_loader_black_list.ctl
CTL_FILE_3=/starhome/iu/workarea/IPN_LOAD/loader_code/sql_loader_mccmnc.ctl

==================================
main_ipn_conv_load.sh
==================================
!/bin/bash
INI_FILE=/starhome/iu/workarea/IPN_LOAD/scripts/ipn_profile_load.ini

#=====================
#functions()
#=====================
get_value(){
 FILE_NAME=$1
 VALUE_NAME=$2
 #echo "Looking for $VALUE_NAME in file $FILE_NAME"
 v_return_value=`grep -w $VALUE_NAME $FILE_NAME | sed s/\=/" "/ | awk '{print $2}'`
 echo $v_return_value
}

write_to_log(){
 echo $1
 echo $1 >> $LOG_FILE
}

create_folder(){
 FOLDER_NAME=$1
 if [[ ! -d ${FOLDER_NAME} ]]; then
   mkdir ${FOLDER_NAME}
 fi
}
#=====================
#set variables
#=====================
DELIMITER=$(get_value ${INI_FILE} DELIMITER)
LOG_FILE=$(get_value ${INI_FILE} LOG_FILE)
RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
this_script=`basename $0`

#---------------------
#main()
#---------------------

echo "LOG_FILE = ${LOG_FILE}"
write_to_log ""
write_to_log $DELIMITER
write_to_log "$this_script .........: Starting at ${RUN_DATE}"
write_to_log $DELIMITER
write_to_log ""

#write_to_log "INPUT_DIR"
INPUT_DIR=$(get_value ${INI_FILE} INPUT_DIR)
create_folder ${INPUT_DIR}

#write_to_log "PROCESS_DIR"
PROCESS_DIR=$(get_value ${INI_FILE} PROCESS_DIR)
create_folder ${PROCESS_DIR}

#write_to_log "HANDLED_FILES_DIR"
HANDLED_FILES_DIR=$(get_value ${INI_FILE} HANDLED_FILES_DIR)
create_folder ${HANDLED_FILES_DIR}

#write_to_log "LOG_DIR"
LOG_DIR=$(get_value ${INI_FILE} LOG_DIR)
create_folder ${LOG_DIR}

#write_to_log "SQL_LOADER_DIR"
SQL_LOADER_DIR=$(get_value ${INI_FILE} SQL_LOADER_DIR)
create_folder ${SQL_LOADER_DIR}

#write_to_log "SQL_LOADER_DIR_IN"
SQL_LOADER_DIR_IN=$(get_value ${INI_FILE} SQL_LOADER_DIR_IN)
create_folder ${SQL_LOADER_DIR_IN}

#write_to_log "SQL_LOADER_DIR_BAD"
SQL_LOADER_DIR_BAD=$(get_value ${INI_FILE} SQL_LOADER_DIR_BAD)
create_folder ${SQL_LOADER_DIR_BAD}

#write_to_log "SQL_LOADER_DIR_LOG"
SQL_LOADER_DIR_LOG=$(get_value ${INI_FILE} SQL_LOADER_DIR_LOG)
create_folder ${SQL_LOADER_DIR_LOG}

#write_to_log "SQL_LOADER_DIR_OLD"
SQL_LOADER_DIR_OLD=$(get_value ${INI_FILE} SQL_LOADER_DIR_OLD)
create_folder ${SQL_LOADER_DIR_OLD}

write_to_log ""
. /etc/sh/orash/oracle_login.sh igt
WORK_AREA=/starhome/iu/workarea/IPN_LOAD/scripts

cd ${WORK_AREA}
./load_handler.sh
exit_status=$?
RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
write_to_log ""
write_to_log $DELIMITER
write_to_log "Finished Running $this_script. Process status: $exit_status"
write_to_log $DELIMITER
write_to_log ""

mv ${LOG_FILE} ${LOG_FILE}_${RUN_DATE}
write_to_log "Log File: ${LOG_FILE}_${RUN_DATE}"

exit $exit_status

==================================
load_handler.sh
==================================
INI_FILE=/starhome/iu/workarea/IPN_LOAD/scripts/ipn_profile_load.ini

#=====================
#functions()
#=====================
get_value(){
 FILE_NAME=$1
 VALUE_NAME=$2
 #echo "Looking for $VALUE_NAME in file $FILE_NAME"
 v_return_value=`grep -w $VALUE_NAME $FILE_NAME | sed s/\=/" "/ | awk '{print $2}'`
 echo $v_return_value
}

write_to_log(){
 echo $1
 echo $1 >> $LOG_FILE
}


load_file(){
 INPUT_FILE_PATTERN=$1
 FLOW_TYPE=$2
 #write_to_log "Input Files in ${INPUT_DIR}:"
 find ${INPUT_DIR} -type f -printf "%f\n"
 #write_to_log "Looking for ${INPUT_FILE_PATTERN} inside ${INPUT_DIR}"
 find ${INPUT_DIR} -type f | egrep ${INPUT_FILE_PATTERN}

 files_num=`find ${INPUT_DIR} -type f | egrep ${INPUT_FILE_PATTERN} 2>/dev/null | wc -l`
 #write_to_log "$this_script ...: Number of Input Files: ${INPUT_DIR}/*${INPUT_FILE_PATTERN}:  $files_num"

 if [[ $files_num == 0 ]];then
  write_to_log "$this_script ...: No Files to Process"
  return 0
 fi

 if [[ $files_num > 1 ]];then
  write_to_log "$this_script ...: More than 1 input File. Exiting !!! "
  exit 1
 fi

 file_name=`find ${INPUT_DIR} -type f | egrep ${INPUT_FILE_PATTERN}`
 BASENAME=`basename $file_name`
 write_to_log "Loading File: $file_name"
 #write_to_log "cp $file_name ${PROCESS_DIR}/${BASENAME}"
 #Remove Empty Lines
 less $file_name | grep -v ^$ > ${PROCESS_DIR}/${BASENAME}

 #!!!!!!!!!!!!!!!!!!!!!!!
 #Remove the file
 rm $file_name
 #!!!!!!!!!!!!!!!!!!!!!!!

 #cp $file_name ${PROCESS_DIR}/${BASENAME}
 #write_to_log "./sql_loader_cmd.sh ${INI_FILE} ${file_name} ${FLOW_TYPE}"

 ./sql_loader_cmd.sh ${INI_FILE} ${PROCESS_DIR}/${BASENAME} ${FLOW_TYPE}
 #write_to_log "$this_script .............: Finished "
 mv ${PROCESS_DIR}/${BASENAME} ${HANDLED_FILES_DIR}/${BASENAME}
 #write_to_log $DELIMITER
}

#=====================
#set variables
#=====================
RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
SCRIPTS_DIR=$(get_value ${INI_FILE} SCRIPTS_DIR)
LOG_DIR=$(get_value ${INI_FILE} LOG_DIR)
INPUT_FILE_PATTERN_1=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_1)
INPUT_FILE_PATTERN_2=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_2)
INPUT_FILE_PATTERN_3=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_3)

DELIMITER=$(get_value ${INI_FILE} DELIMITER)
LOG_FILE=$(get_value ${INI_FILE} LOG_FILE)
WORK_DIR=$(get_value ${INI_FILE} WORK_DIR)
INPUT_DIR=$(get_value ${INI_FILE} INPUT_DIR)

INPUT_DIR=$(get_value ${INI_FILE} INPUT_DIR)
PROCESS_DIR=$(get_value ${INI_FILE} PROCESS_DIR)
HANDLED_FILES_DIR=$(get_value ${INI_FILE} HANDLED_FILES_DIR)


FLOW_1=$(get_value ${INI_FILE} FLOW_1 )
FLOW_2=$(get_value ${INI_FILE} FLOW_2 )
FLOW_3=$(get_value ${INI_FILE} FLOW_3 )

#=====================
#main
#=====================
. /etc/sh/orash/oracle_login.sh igt
this_script=`basename $0`
echo "Log File is: $LOG_FILE"
touch ${LOG_FILE}
#write_to_log ""
#write_to_log $DELIMITER
#write_to_log "$this_script .............: Starting Run at $RUN_DATE"
#write_to_log $DELIMITER
cd ${SCRIPTS_DIR}

write_to_log ""
write_to_log "${DELIMITER}"
write_to_log "Start Loading file type ${INPUT_FILE_PATTERN_1} Flow: ${FLOW_1}"
load_file "${INPUT_FILE_PATTERN_1}" "${FLOW_1}"
ret_code=$?
if [[ $ret_code > 0 ]]; then
 exit $ret_code
fi
write_to_log ""

write_to_log ""
write_to_log "${DELIMITER}"
write_to_log "Start Loading file type ${INPUT_FILE_PATTERN_2} Flow: ${FLOW_2}"
sleep 2
load_file "${INPUT_FILE_PATTERN_2}" "${FLOW_2}"
ret_code=$?
if [[ $ret_code > 0 ]]; then
 exit $ret_code
fi
write_to_log ""


write_to_log ""
write_to_log "${DELIMITER}"
write_to_log "Start Loading file type ${INPUT_FILE_PATTERN_3} Flow: ${FLOW_3}"
sleep 2
load_file "${INPUT_FILE_PATTERN_3}" "${FLOW_3}"
ret_code=$?
if [[ $ret_code > 0 ]]; then
 exit $ret_code
fi
write_to_log ""

write_to_log "${DELIMITER}"
write_to_log ""

exit 0

==================================
sql_loader_cmd.sh
==================================
#!/bin/bash
INI_FILE=$1
FILE_NAME=$2
FLOW_TYPE=$3
#=====================
#functions()
#=====================
get_value(){
 FILE_NAME=$1
 VALUE_NAME=$2
 #echo "Looking for $VALUE_NAME in file $FILE_NAME"
 v_return_value=`grep -w $VALUE_NAME $FILE_NAME | sed s/\=/" "/ | awk '{print $2}'`
 echo $v_return_value
}
write_to_log(){
 echo $1
 echo $1 >> $LOG_FILE
}
#=====================
#set variables
#=====================
RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
INPUT_FILE_PATTERN=$(get_value ${INI_FILE} INPUT_FILE_PATTERN)
INPUT_FILE_PATTERN_1=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_1)
INPUT_FILE_PATTERN_2=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_2)
INPUT_FILE_PATTERN_3=$(get_value ${INI_FILE} INPUT_FILE_PATTERN_3
)
LOG_FILE=$(get_value ${INI_FILE} LOG_FILE)
INPUT_DIR=$(get_value ${INI_FILE} INPUT_DIR)
SOURCE_DATA=$(get_value ${INI_FILE} SOURCE_DATA)
OUTPUT_DIR=$(get_value ${INI_FILE} OUTPUT_DIR)
INPUT_FILE_NAME=$(get_value ${INI_FILE} INPUT_FILE_NAME)
HANDLED_FILES_DIR=$(get_value ${INI_FILE} HANDLED_FILES_DIR)
SQL_LOADER_LOG_FILE=$(get_value ${INI_FILE} SQL_LOADER_LOG_FILE)

#CTL_FILE=$(get_value ${INI_FILE} CTL_FILE)
CTL_FILE_1=$(get_value ${INI_FILE} CTL_FILE_1)
CTL_FILE_2=$(get_value ${INI_FILE} CTL_FILE_2)
CTL_FILE_3=$(get_value ${INI_FILE} CTL_FILE_3)

FLOW_1=$(get_value ${INI_FILE} FLOW_1 )
FLOW_2=$(get_value ${INI_FILE} FLOW_2 )
FLOW_3=$(get_value ${INI_FILE} FLOW_3 )

SQL_LOADER_DAT_FILE=$(get_value ${INI_FILE} SQL_LOADER_DAT_FILE)
SQL_LOADER_BAD_FILE=$(get_value ${INI_FILE} SQL_LOADER_BAD_FILE)
SQL_LOADER_HIST_FILE=$(get_value ${INI_FILE} SQL_LOADER_HIST_FILE)
DB_USER=$(get_value ${INI_FILE} DB_USER)
DB_PASS=$(get_value ${INI_FILE} DB_PASS)
DB_INST=$(get_value ${INI_FILE} DB_INST)
DELIMITER=$(get_value ${INI_FILE} DELIMITER)

this_script=`basename $0`
write_to_log "$this_script ...: ----------------------------------------------------------"
write_to_log "$this_script ...: Starting ${this_script} at ${RUN_DATE}"
#write_to_log "$this_script ...: sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}"

. /etc/sh/orash/oracle_login.sh igt
. /starhome/iu/workarea/IPN_LOAD/scripts/ora_env.ini


#write_to_log "cp ${FILE_NAME} ${SQL_LOADER_DAT_FILE}"
cp ${FILE_NAME} ${SQL_LOADER_DAT_FILE}

if [[ $FLOW_TYPE == ${FLOW_1} ]]; then
 CTL_FILE=${CTL_FILE_1}
fi
if [[ $FLOW_TYPE == ${FLOW_2} ]]; then
 CTL_FILE=${CTL_FILE_2}
fi
if [[ $FLOW_TYPE == ${FLOW_3} ]]; then
 CTL_FILE=${CTL_FILE_3}
fi


if [[ -f ${FILE_NAME} ]]; then
  #write_to_log "$this_script ...: sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}"
  #write_to_log "$this_script ...: ls -l /starhome/iu/workarea/IPN_LOAD/loader_code/in/IPN_LOAD_data.dat"
  #write_to_log "$this_script ...: `ls -l /starhome/iu/workarea/IPN_LOAD/loader_code/in/IPN_LOAD_data.dat`"
  #write_to_log "$this_script ...: `env | grep ORA`"
  SQLDR_HOME=${ORACLE_HOME}/bin
  echo ${SQLDR_HOME}/sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}
  ${SQLDR_HOME}/sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}
  v_status=$?
  if [[ $v_status -ne 0 ]];then
    write_to_log "$this_script ...: ERROR in step: sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}"
    write_to_log "$this_script ...: Status: $v_status "
  fi
else
  write_to_log "$this_script ...: Error!! Could not find input file : ${FILE_NAME}"
fi


if [[ -f ${SQL_LOADER_LOG_FILE} ]]; then
  #write_to_log "$this_script ...: moving ${SQL_LOADER_LOG_FILE} : `ls -l ${SQL_LOADER_LOG_FILE}`"
  #write_to_log "$this_script ...: mv ${SQL_LOADER_LOG_FILE} ${SQL_LOADER_LOG_FILE}_${RUN_DATE}"
  mv ${SQL_LOADER_LOG_FILE} ${SQL_LOADER_LOG_FILE}_${RUN_DATE}
  write_to_log "sqloder log file: ${SQL_LOADER_LOG_FILE}_${RUN_DATE}"
else
  write_to_log "$this_script ...: ERROR! Cannot find Log File ${SQL_LOADER_LOG_FILE}"
fi

if [[ -f ${SQL_LOADER_BAD_FILE} ]]; then
  #write_to_log "$this_script ...: mv ${SQL_LOADER_BAD_FILE} ${SQL_LOADER_BAD_FILE}_${RUN_DATE}"
  mv ${SQL_LOADER_BAD_FILE} ${SQL_LOADER_BAD_FILE}_${RUN_DATE}
fi
if [[ -f ${SQL_LOADER_DAT_FILE} ]]; then
  #write_to_log "$this_script ...: moving ${SQL_LOADER_DAT_FILE}  : `ls -l ${SQL_LOADER_DAT_FILE} `"
  #write_to_log "$this_script ...: mv ${SQL_LOADER_DAT_FILE} ${SQL_LOADER_HIST_FILE}_${RUN_DATE}"
  mv ${SQL_LOADER_DAT_FILE} ${SQL_LOADER_HIST_FILE}_${RUN_DATE}
fi

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
write_to_log "$this_script ...: ----------------------------------------------------------"
write_to_log "$this_script ...: Finished ${this_script} in mode ${RUN_MODE} at ${RUN_DATE}"
write_to_log "$this_script ...: ----------------------------------------------------------"

exit $v_status

==================================
sql_loader_vrs_configuration.ctl
==================================
LOAD DATA
INFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/in/load_data.dat'
BADFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/bad/bad_load_data.dat'
DISCARDMAX 0
TRUNCATE
INTO TABLE IPN_CONV_CONFIGURATION
WHEN (COUNTRY != BLANKS)
FIELDS TERMINATED BY ','
(
country              "TRIM (:country)",
comunity             "TRIM (:comunity)",
network_name         "TRIM (:network_name)",
redirection_code     "TRIM (:redirection_code)",
network_type         "TRIM (:network_type)",
rdc_pct              "TRIM (:rdc_pct)",
mcc                  "TRIM (:mcc)",
mnc                  "TRIM (:mnc)",
mcc_mnc              "TRIM (:mcc_mnc)",
tomia_network_name   "TRIM (:tomia_network_name)",
tomia_mcc_mnc        "TRIM (:tomia_mcc_mnc)",
tomia_community      "TRIM (:tomia_community)",
aux                  "TRIM (:aux)",
tomia_configuration  "TRIM (:tomia_configuration)",
tomia_network_barring "TRIM (:tomia_network_barring)",
ts_last_modified     "SYSDATE"
)

==================================

sql_loader_black_list.ctl

==================================

LOAD DATA

INFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/in/load_data.dat'

BADFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/bad/bad_load_data.dat'

DISCARDMAX 0

TRUNCATE

INTO TABLE IPN_CONV_BLACK_LIST

FIELDS TERMINATED BY ','

(

imsi                 "TRIM (:imsi)",

ts_last_modified     SYSDATE

)

==================================

sql_loader_mccmnc.ctl

==================================

LOAD DATA

INFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/in/load_data.dat'

BADFILE '/starhome/iu/workarea/IPN_LOAD/loader_code/bad/load_data.dat'

DISCARDMAX 0

TRUNCATE

INTO TABLE IPN_CONV_TOMIA_MCCMNC

FIELDS TERMINATED BY ','

(

mcc              "TRIM (:mcc)",

mnc              "TRIM (:mnc)",

mccmnc           "TRIM (:mccmnc)",

iso              "TRIM (:iso)",

country_name     "TRIM (:country_name)",

country_code     "TRIM (:country_code)",

network_name     "TRIM (:network_name)",

country_network  "TRIM (:country_network)",

ts_last_modified SYSDATE

)