Lots of email alerts reporting SQL Loader failures (the data is actually loading) but I want to prevent all these email alerts being fired.
We have an SQL Loader script that is failing regularly with this error, however the data does end up in the tables so it must run subsequently succesfully the log files are cleared out quite quickly so it is difficult to track the errors.
Why is there no filename just a.day reference in the error log file? Below is the shell script I do not have much script experience, so I am unable to see how I can alter this...could I add some kind of exclusive lock check to see if I actually have access to the file before SQL Loader tries to Load it?
value used for ROWS parameter changed from 64 to 63
SQL*Loader-500: Unable to open file (/e2e_ms_xfer/cent01/.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
This is the full error log file
SQL*Loader: Release 11.2.0.3.0 - Production on Sat Jun 15 12:17:38 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: /tmp/e2e_load_ms_raw_coda.ctl
Data File: /e2e_ms_xfer/cent01/.dat
Bad File: /tmp/e2e_load_ms_raw_coda.bad
Discard File: /tmp/e2e_load_ms_raw_coda.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table MS_RAW_CODA, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CODA_RECORD FIRST 4000 CHARACTER
Terminator string : '<{+*^6!->'
CODA_FILEDATE CONSTANT
Value is '00--2013 :00'
value used for ROWS parameter changed from 64 to 63
SQL*Loader-500: Unable to open file (/e2e_ms_xfer/cent01/.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Table MS_RAW_CODA:
0 Rows successfully loaded.
Unix Script
#!/bin/ksh
#
# $Id: e2e_load_ms_raw_parcels.ksh
#
# Control the Unix processing cycle for uploading data
#
# 1.0 DL 19/09/2012 Initial Version
#
set +xv
# Library functions
. $BIN/common_functions.ksh
THIS=$( basename $0 )
THIS_NODE=$( uname -n | rev | cut -c1 )
fn_banner "Process files"
# Check we are on the correct node (default to node 1)
fn_unpackParameters "$*"
[ "$node" = "" ] && node=1
if [ "$THIS_NODE" != "$node" ]
then
# We are running on the wrong node, exit nicely
junk="Wrong server (node:${THIS_NODE})"
echo "$junk"
fn_writeTrace "$junk"
exit 0
fi
# Check I'm not already running
jCount=$(ps -ef | grep -v grep | grep $THIS | wc -l)
if [ $jCount -gt 1 ]
then
# There is a process running, so DON'T start another one
junk="Job already running (${THIS})"
echo "$junk"
fn_writeTrace "$junk"
exit 0
fi
echo "Starting: $(date)"
fn_writeTrace "starting"
# Local variables
#
bookmark="10"
JOBNAME=$( echo $THIS | cut -f1 -d. )
TODAY=$(date "+%Y%m%d")
MASK=e2e_ms_parcels_*.csv
D_EXTTAB=/e2e_ms_xfer
D_DATA=$HOME/data
D_WORK=$XFMSC
D_DONE=$XFMSZ
F_ALERT=$D_DATA/${JOBNAME}.sem
F_SQLLDR_CTL=/tmp/${JOBNAME}.ctl
F_SQLLDR_INF=/tmp/${JOBNAME}.inf
F_SQLLDR_BAD=/tmp/${JOBNAME}.bad
F_SQLLDR_DSC=/tmp/${JOBNAME}.dsc
F_SQLLDR_LOGA=/tmp/${JOBNAME}_a.log
F_SQLLDR_LOGB=/tmp/${JOBNAME}_b.log
F_ERRLOG=$LOG/$( echo ${THIS} | cut -f1 -d. )_${SYSDATE}.errlog
fileCount=0
# Oracle
bookmark="20"
fn_oraenv
# Good to go
bookmark="30"
runControl=1
while [ $runControl -eq 1 ]
do
cd $D_WORK
# Are there any files?
bookmark="40"
fileFound=0
ls -1 $MASK > /dev/null 2>&1
if [ $? -eq 0 ]
then
# Oldest file
bookmark="50"
fileName=$( ls -1 $MASK | head -n 1 )
# Remove any records beginning with 'JD00022ABC' (Aborted Collections)
grep -v ^JD00022ABC $fileName > $F_SQLLDR_INF
let fileCount=fileCount+1
fileFound=1
# Create the SQLLDR control file
bookmark="70"
echo "-- Generated by '${THIS}' on $(date)" > $F_SQLLDR_CTL
echo "LOAD DATA" >> $F_SQLLDR_CTL
echo "INFILE '$F_SQLLDR_INF'" >> $F_SQLLDR_CTL
echo "BADFILE '$F_SQLLDR_BAD'" >> $F_SQLLDR_CTL
echo "DISCARDFILE '$F_SQLLDR_DSC'" >> $F_SQLLDR_CTL
echo "APPEND" >> $F_SQLLDR_CTL
echo "INTO TABLE ms_raw_parcels" >> $F_SQLLDR_CTL
echo "FIELDS TERMINATED BY '|'" >> $F_SQLLDR_CTL
echo "TRAILING NULLCOLS" >> $F_SQLLDR_CTL
echo "(parcel_id CHAR(100)" >> $F_SQLLDR_CTL
echo ",ms_pcl_no CHAR(100)" >> $F_SQLLDR_CTL
echo ",ms_pcl_inst CHAR(100)" >> $F_SQLLDR_CTL
echo ",amend_stamp CHAR(100)" >> $F_SQLLDR_CTL
echo ",exp_del_date CHAR(100)" >> $F_SQLLDR_CTL
echo ",edd_rescheduled CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_tour CHAR(100)" >> $F_SQLLDR_CTL
echo ",service_code CHAR(100)" >> $F_SQLLDR_CTL
echo ",service_description CHAR(400)" >> $F_SQLLDR_CTL
echo ",courier_type CHAR(100)" >> $F_SQLLDR_CTL
echo ",client_account CHAR(100)" >> $F_SQLLDR_CTL
echo ",client_contract CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_opu CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_name CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_1 CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_2 CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_town CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_county CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_country CHAR(100)" >> $F_SQLLDR_CTL
echo ",col_addr_pc CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_opu CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_name CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_1 CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_2 CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_town CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_county CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_country CHAR(100)" >> $F_SQLLDR_CTL
echo ",del_addr_pc CHAR(100)" >> $F_SQLLDR_CTL
echo ",trip_id CHAR(100)" >> $F_SQLLDR_CTL
echo ",status_code CHAR(100)" >> $F_SQLLDR_CTL
echo ")" >> $F_SQLLDR_CTL
# Upload the data
bookmark="80"
sqlldr $OCS control=$F_SQLLDR_CTL log=$F_SQLLDR_LOGA > $F_SQLLDR_LOGB
e=$?
if [ $e -ne 0 ]
then
cat $F_SQLLDR_LOGA $F_SQLLDR_LOGB >> $F_ERRLOG
fn_exceptionHandler "SQLLDR error: $e<br>File: ${fileName}<br>ErrLog: $F_ERRLOG"
fi
# Move the file to 'done'
mv $fileName $D_DONE
fn_writeTrace "uploaded file: ${fileName}"
fi # file found
# Check if 'stop' command issued
bookmark="90"
if [ -f "$F_ALERT" ]
then
rm -f "$F_ALERT"
runControl=0
fileFound=1 # to prevent the nap
fi
# If we hadn't found a file, take a nap
bookmark="100"
if [ $fileFound -eq 0 ]
then
sleep 15
fi
fn_setHeartbeat $JOBNAME
done # while
bookmark="200"
fn_cleanup
echo "Complete: $(date)"
fn_writeTrace "complete (files:${fileCount})"
exit 0