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

)

Oracle SQL Loader by example

===========================================
General
===========================================
Oracle SQL Loader is pretty straight forward, and all related reference is well documented.
Reference:
ORA-FAQ
Oracle Reference for SQL-Loader
The only trick is when loading data into fields not from input csv file.

===========================================
Example A - Load first field from a sequence
===========================================
In this example, The range_id field is the first field in table DATA_RANGE_INPUT.
range_id, field is populated by sequence.
Note the leading "," in the csv file.
It is a "place holder" for a sequence field , which is not populated by input data from scv file.

Files:
data_input.csv
data_load.cmd
data_load.ctl

data_input.csv
range_id,code_a,code_b,from_data,to_data,status_ind,status
,44,36,0012,9999,Y,New
,44,36,0010,9999,Y,New
,44,36,1000,4567,Y,New

run_tn_range_load.cmd

sqlldr user/password@connection_string control=data_load.ctl

tn_range_load.ctl

OPTIONS(SKIP=1)

LOAD DATA

INFILE 'data_input.csv' 

BADFILE 'data_input.err' 

APPEND INTO TABLE DATA_RANGE_INPUT 

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

 range_id "DATA_INPUT_SEQ_01.NEXTVAL",

 code_a, 

 code_b,

 from_data, 

 to_data, 

 status_ind,

 status,

 creation_date  SYSDATE,

 last_modification_date SYSDATE,
 skip_this_field  FILLER,

 update_user CONSTANT "DATALOAD")

OPTIONS(SKIP=1) - skip header line


===========================================
Example B - Load Huge file
===========================================
Issue B.

In this example, the load is processing 1,000,000 rows and more.

OPTIONS (ROWS=5000000, BINDSIZE = 400000000, READSIZE=400000000, ERRORS=20000000)

LOAD DATA
APPEND
INTO TABLE SQLLOAD_COUNTERS_HIST
fields terminated by ","
TRAILING NULLCOLS
(
ts_last_modified,
node_id,
static_id,
dyn1,
dyn2,
counter_sum,
counter_delta,
counter_name 
)

ROWS -- Number of rows in conventional path bind array or between direct path data saves
BINDSIZE -- Size of conventional path bind array in bytes  
            (Default 256000)
READSIZE -- Size of read buffer                  
            (Default 1048576)
ERRORS -- Number of errors to allow            
          (Default 50)

===========================================
Example C - Load DATE using TO_DATE and SUBSTR functions
===========================================
Issue A.
In this example, The first field has miliseconds in the date format.
In the database, this field is DATE, without milliseconds.

Solution
Use String and Conversion functions:
 "TO_DATE(SUBSTR(:ts_last_modified,1,19),'YYYY-MM-DD hh24:mi:ss')" 

Issue B.
Inside the data, there are numerous delimiter lines, in this format '------------------------' , that should be ignored.

Solution
Add Options part, where runtime parameters can be specified inside the control file.
Inside the Options part, add ERRORS=1000000.
By setting the ERRORS parameter to a very high value, we avoid the error "MAXIMUM ERROR COUNT EXCEEDED".


Data looks like:
2014-11-01 00:12:08.716,104032100000000,298,0,34,0,codeA
2014-11-01 00:12:08.716,104032000000001,1,405,13868,7,codeB

2014-11-01 00:12:08.716,104032000000001,1,298,24076,6,codeC
----------------------------------
2014-11-01 00:27:08.717,104032100000000,298,0,34,0,codeA
2014-11-01 00:27:08.717,104032000000001,1,405,13871,3,codeB

2014-11-01 00:27:08.717,104032000000001,1,298,24082,6,codeC

----------------------------------

control_file.ctl

OPTIONS (ERRORS=1000000)

LOAD DATA

INFILE 'counter_for_load.txt'

BADFILE 'counter_for_load.bad'

DISCARDFILE 'counter_for_load.dis'

APPEND 

INTO TABLE MY_COUNTERS_TABLE

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(ts_last_modified "TO_DATE(SUBSTR(:ts_last_modified,1,19),'YYYY-MM-DD hh24:mi:ss')",

 static_id INTEGER EXTERNAL(20),

 dyn1 INTEGER EXTERNAL(22),

 dyn2 INTEGER EXTERNAL(22),

 counter_sum INTEGER EXTERNAL(20),

 counter_delta INTEGER EXTERNAL(20),

 counter_name FILLER,

 dyn3 "0",

 dyn4 "0",

 dyn5 "0",

 is_processed "0"

 )

runtime command:

sqlldr userA/passA@orainst control=control_file.ctl log=my_sqlldr.log

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

Example D - Runtime error SQL*Loader-704:
===========================================
When running sqlldr, there is the following error:
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

The TNS ORA-12154 error suggests that the connect identifier is not known.

But when testing that very same connection with sqlplus, there is no error at all, and the connection is OK.
What is going on??!!

Example:
sqlplus userA/passA@orainst

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Dec 1 14:10:39 2014
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options


SQL>

>sqlldr userid = userA/passA@orainst control=control_file.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Dec 1 14:10:16 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

Solution:

The issue is with Oracle Client installation and Path Environment Variable in Windows.

Apparently, that specific computer had three Oracle Client Installations:

Oracle 9, Oracle 10, Oracle 11.

Oracle 9 tnsnames.ora had the correct service descriptor.

Oracle 10 and Oracle 11 tnsnames.ora files had no services listed at all.

sqlplus was able to connect using the Oracle 9 Client tnsnames.ora

sqlldr issue was resolved only after adding the correct entry to Oracle 10 Client tnsnames.ora file.

This is a bit surprising, since checking the Path Environment Variable, Oracle 9 Client is listed first.

set | find "Path"

Path=C:\oracle\ora92\bin;

D:\oracle\product\10.2.0\client_1\bin;

C:\Oracle32\product\11.2.0\client_1\bin;

C:\WINDOWS\system32;

C:\WINDOWS;

C:\WINDOWS\System32\Wbem;

etc...

But when running sqlldr command without parameters, we get the sqlldr "about" and "help" page.
And sqlldr version is Oracle 10!
Now it is clear, why sqlldr is "looking" into the Oracle10 Client tnsnames.ora file.

>sqlldr
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Dec 1 19:34:19 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:

    userid -- ORACLE username/password
   control -- control file name
       log -- log file name
       bad -- bad file name
      data -- data file name
   discard -- discard file name
discardmax -- number of discards to allow          (Default all)
      skip -- number of logical records to skip    (Default 0)
      load -- number of logical records to load    (Default all)
    errors -- number of errors to allow            (Default 50)
      rows -- number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize -- size of conventional path bind array in bytes  (Default 256000)
    silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array  (Default 5000)
streamsize -- size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'.  One may specify parameters by position before
but not after parameters specified by keywords.  For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.