Home » RDBMS Server » Server Utilities » sql loader error records (oracle 10.2 solaris)
sql loader error records [message #560368] Thu, 12 July 2012 07:22 Go to next message
guddu_12
Messages: 219
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 #560369 is a reply to message #560368] Thu, 12 July 2012 07:40 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you post the sql loader script..
Re: sql loader error records [message #560370 is a reply to message #560369] Thu, 12 July 2012 07:45 Go to previous messageGo to next message
guddu_12
Messages: 219
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 #560374 is a reply to message #560370] Thu, 12 July 2012 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table instead then you can use INSERT SELECT with LOG ERRORS clause (I think it would work but I never tested it with an external table but documentation does not mention any restriction on this type of table).

Regards
Michel

[Edit: add link to restrictions]

[Updated on: Thu, 12 July 2012 07:52]

Report message to a moderator

Re: sql loader error records [message #560377 is a reply to message #560374] Thu, 12 July 2012 07:57 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
If my file name chage every time then i can't work with external table, any other suggestion to trap errorneous record
Re: sql loader error records [message #560386 is a reply to message #560377] Thu, 12 July 2012 08:10 Go to previous message
Michel Cadot
Messages: 67369
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
Previous Topic: Import table with different name
Next Topic: oracle table (2 Merged)
Goto Forum:
  


Current Time: Thu Sep 24 04:17:41 CDT 2020