Home » RDBMS Server » Server Utilities » sql loader error records (oracle 10.2 solaris)
|
|
Re: sql loader error records [message #560370 is a reply to message #560369] |
Thu, 12 July 2012 07:45   |
 |
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
loader..
# This shell will loadd all the LOG files data in oracle
# Creating directory
NEWDIR=PACS_`date +%d-%b-%Y`
#echo $NEWDIR
mkdir -p /backup/temp/rajesh/PACS/pacs_archive/$NEWDIR
#cd /backup/temp/rajesh/loader
########Setting log directory path ########
cd /backup/temp/rajesh/PACS
#test -e *.LOG $1
# if [$? ne 0]; then
if [ -f *.log ]
then
for i in PACS_*.log
do
flnm=$i
#echo "file name is $flnm"
#sed "s:e:$flnm{i}:g" /backup/temp/rajesh/prac/control.ctl > /backup/temp/rajesh/prac/controlnew.ctl
sed "s/FILENAME/$flnm/g" /backup/temp/rajesh/PACS/control/pacs.ctl > /backup/temp/rajesh/PACS/control/pacsnew.ctl
echo " ...............Connecting......................"
#......Run sql loader to load data in oracle database..................
sqlldr rts_schema/rts_schema control = /backup/temp/rajesh/PACS/control/pacsnew.ctl data = $flnm
#.....Add the file to .zip file.........
mv $flnm /backup/temp/rajesh/PACS/pacs_archive/$NEWDIR
echo "files have been loaded .... $flnm"
done
echo ".....Please wait ..Zipping the logs file..."
zip -r /backup/temp/rajesh/PACS/pacs_archive/$NEWDIR . -i *.log
else
echo "Files doesnt exist"
fi
contol file
load data
BADFILE '/backup/temp/rajesh/PACS/BadFiles/PACS_WEB_Q36-RNH_20120530103802.log'
append into table TEMP_PACS_RESP_TIME_LND
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR,
TRANSACTION_ID CHAR,
SERVER_ID CHAR,
CLINICAL_TRANSACTION_ID CHAR,
RESPONSE_TIME DECIMAL EXTERNAL,
TRANSACTION_START_TIME
"TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
TRANSACTION_END_TIME
"TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
LOCATION_ID CHAR,
WAIT_TIME DECIMAL EXTERNAL,
INTERNAL_TRANSACTION_ID CHAR,
INTERNAL_TIME DECIMAL EXTERNAL,
EXTERNAL_SERVICE_ID CHAR,
EXTERNAL_SERVICE_TIME DECIMAL EXTERNAL,
LOCAL_SERVICE_ID CHAR,
LOCAL_SERVICE_TIME DECIMAL EXTERNAL,
MESSAGE_GUID CHAR,
RETURN_MESSAGE_GUID CHAR,
TRS_SIZE DECIMAL EXTERNAL,
FILE_NAME CONSTANT
"PACS_WEB_Q36-RNH_20120530103802.log",
DATE_LOADED SYSDATE ,
ORIGINAL_DATE_LOADED SYSDATE
)
I want to capture all errorneous record into some other table instead of badfiles.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 22 20:48:07 CST 2025
|