sql loader error records [message #560368] |
Thu, 12 July 2012 07:22 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Hi ,
My oracle is sitting on unix, i have a sql loader scripts which load the data in oracle at every 10 min and badfiles is written into a directory. since the file names are same it overwrite the badfiles in case of error record. i can devise a code to write the bad file with different name. I want to write error record into oracle table, is this possible and how can i achive.
Rajesh
|
|
|
|
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.
|
|
|
|
|
Re: sql loader error records [message #560386 is a reply to message #560377] |
Thu, 12 July 2012 08:10 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:If my file name chage every time then i can't work with external table
Yes, you can, you just have to execute an ALTER TABLE to change the file name (location parameter).
Regards
Michel
|
|
|