This discussion is archived
4 Replies Latest reply: Jan 3, 2013 3:49 AM by vk82 RSS

Loading Data Error while using SQL Loader

vk82 Explorer
Currently Being Moderated
Hi All,


I am facing another issue while loading the data from text file to oracle db.
I have a table in which data is loaded from multiple files. What i want the when i load the data i want to load the filename as well in the table in front of the record. the script which works fine when i load data in windows OS but i am getting error in Unix OS. My sql loader script is as follows.

source $HOME/. .bash_profile
export LOGFILE=/NSN/rawfiles/scripts/PAYMOBILE_LOAD/load.log
ORACLE_ACCESS=cdr/cdr123_awcc@$ORACLE_SID
######################################################################
FILES=`ls /NSN/rawfiles/bkp/PAYMOBILE/JAN2013/*.txt`;

###########################################################################################
for file in ${FILES[@]}
do
filename=`expr substr "$file" 43 50`
echo $filename
###########################################################################################
sqlldr $ORACLE_ACCESS control=/NSN/rawfiles/scripts/PAYMOBILE/CAT.ctl log=$LOGFILE data=/NSN/rawfiles/bkp/PAYMOBILE/JAN2013/$filename skip=0 direct=true

cd
mv /NSN/rawfiles/bkp/PAYMOBILE/JAN2013/$filename /NSN/backup/PAYMOBILE/JAN2013/
done


Control File for the same which i run on unix is :

Load data
INFILE "AR_101_1011_01-01-2013-01-32.txt"
Append
into table CDR.PAY_MOBILE_012013
fields terminated by "|" optionally enclosed by " "
trailing nullcols
(
VERSION, TICKETTYPE, GMT_TIME Date 'YYYY-MM-DD HH24:MI:SS' , LOCAL_TIME Date 'YYYY-MM-DD HH24:MI:SS', TRANSACID, EXTERNAL_TRANSACTION_ID, MEDIUM, ALIASCATEGORY, SERVICE, OPERATION, ACTORID,
ALIASNAME, SENDERACTORID, SENDERALIASNAME, RECIPIENTACTORID, RECIPIENTALIASNAME, THIRDACTORID, THIRDALIASNAME, ERRORHRN, CLASS, SNE,
MASTERSNE, VOUCHERCATEGORYID, VOUCHERSCENARIOID, EXPIRYDATE Date 'YYYY-MM-DD HH24:MI:SS', UNITVALUEID, CREDIT, VAT, IERROR, OPERSTATE, OPERERROR, CREATION_DATE Date 'YYYY-MM-DD HH24:MI:SS',
LAST_UPDATE Date 'YYYY-MM-DD HH24:MI:SS', BRANDID, CREDIT_PERIOD, CREDIT_EXPIRY_DATE Date 'YYYY-MM-DD HH24:MI:SS', SENDER_ACCOUNT, SENDER_TRANSAC, SENDER_ACCOUNT_TYPE, RECIPIENT_ACCOUNT,
RECIPIENT_TRANSAC, RECIPIENT_ACCOUNT_TYPE, RECIPIENT_MEDIUM, TIMESTAMP Date 'YYYY-MM-DD HH24:MI:SS', SENDERBILLINGTYPE, RECIPIENTBILLINGTYPE
)



Control file which works fine in windows is as follows:


Load data
     
     Append
     into table CDR.PAY_MOBILE_012013
     fields terminated by "|" optionally enclosed by " "
     trailing nullcols
(
VERSION, TICKETTYPE, GMT_TIME Date 'YYYY-MM-DD HH24:MI:SS' , LOCAL_TIME Date 'YYYY-MM-DD HH24:MI:SS', TRANSACID, EXTERNAL_TRANSACTION_ID, MEDIUM, ALIASCATEGORY, SERVICE, OPERATION, ACTORID,
ALIASNAME, SENDERACTORID, SENDERALIASNAME, RECIPIENTACTORID, RECIPIENTALIASNAME, THIRDACTORID, THIRDALIASNAME, ERRORHRN, CLASS, SNE,
MASTERSNE, VOUCHERCATEGORYID, VOUCHERSCENARIOID, EXPIRYDATE Date 'YYYY-MM-DD HH24:MI:SS', UNITVALUEID, CREDIT, VAT, IERROR, OPERSTATE, OPERERROR, CREATION_DATE Date 'YYYY-MM-DD HH24:MI:SS',
LAST_UPDATE Date 'YYYY-MM-DD HH24:MI:SS', BRANDID, CREDIT_PERIOD, CREDIT_EXPIRY_DATE Date 'YYYY-MM-DD HH24:MI:SS', SENDER_ACCOUNT, SENDER_TRANSAC, SENDER_ACCOUNT_TYPE, RECIPIENT_ACCOUNT,
RECIPIENT_TRANSAC, RECIPIENT_ACCOUNT_TYPE, RECIPIENT_MEDIUM, TIMESTAMP Date 'YYYY-MM-DD HH24:MI:SS', SENDERBILLINGTYPE, RECIPIENTBILLINGTYPE, FILENAME CONSTANT "AR_101_1011_01-12-2012-23-32.txt"
)



and the sql loader script which works fine in windows is as follows:

cd K:\paymobilefiles\DEC2012\1
for %%f in (*.txt) do (K:\paymobilefiles\DEC2012\1\LOAD\REPTEXT K:\paymobilefiles\DEC2012\1\LOAD\TEST.ctl "%%f">K:\paymobilefiles\DEC2012\1\LOAD\MYMOBILE.ctl
sqlldr userid=cdr/cdr123_awcc@tsiindia control=K:\paymobilefiles\DEC2012\1\LOAD\MYMOBILE.ctl rows=50000 bindsize=20000000 readsize=20000000 data=%%f log=K:\paymobilefiles\DEC2012\1\LOAD\MYMOBILE.log
move %%f K:\paymobilefiles\DEC2012\backup\1
)


when using windows based scripts i am getting filename in the filename column but when running the above mentioned scripts i am not able to get the filenames in the filename column.


Can anyone help over the same please
  • 1. Re: Loading Data Error while using SQL Loader
    moreajays Pro
    Currently Being Moderated
    HI,

    There is Difference in Control files , no INFILE & FILENAME CONSTANT values
     Load data
    
    Append
    into table CDR.PAY_MOBILE_012013
    fields terminated by "|" optionally enclosed by " "
    trailing nullcols
    (
    VERSION, TICKETTYPE, GMT_TIME Date 'YYYY-MM-DD HH24:MI:SS' , LOCAL_TIME Date 'YYYY-MM-DD HH24:MI:SS', TRANSACID, EXTERNAL_TRANSACTION_ID, MEDIUM, ALIASCATEGORY, SERVICE, OPERATION, ACTORID,
    ALIASNAME, SENDERACTORID, SENDERALIASNAME, RECIPIENTACTORID, RECIPIENTALIASNAME, THIRDACTORID, THIRDALIASNAME, ERRORHRN, CLASS, SNE,
    MASTERSNE, VOUCHERCATEGORYID, VOUCHERSCENARIOID, EXPIRYDATE Date 'YYYY-MM-DD HH24:MI:SS', UNITVALUEID, CREDIT, VAT, IERROR, OPERSTATE, OPERERROR, CREATION_DATE Date 'YYYY-MM-DD HH24:MI:SS',
    LAST_UPDATE Date 'YYYY-MM-DD HH24:MI:SS', BRANDID, CREDIT_PERIOD, CREDIT_EXPIRY_DATE Date 'YYYY-MM-DD HH24:MI:SS', SENDER_ACCOUNT, SENDER_TRANSAC, SENDER_ACCOUNT_TYPE, RECIPIENT_ACCOUNT,
    RECIPIENT_TRANSAC, RECIPIENT_ACCOUNT_TYPE, RECIPIENT_MEDIUM, TIMESTAMP Date 'YYYY-MM-DD HH24:MI:SS', SENDERBILLINGTYPE, RECIPIENTBILLINGTYPE, FILENAME CONSTANT "AR_101_1011_01-12-2012-23-32.txt"
    )
    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 2. Re: Loading Data Error while using SQL Loader
    vk82 Explorer
    Currently Being Moderated
    i check the same by providing the FILENAME CONSTANT but that will load the same filename on the filename column for every file which is mentioned on the controlfile
  • 3. Re: Loading Data Error while using SQL Loader
    moreajays Pro
    Currently Being Moderated
    HI,

    Does row getting appended or any error ?
    FILENAME Column exists in target table ?


    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 4. Re: Loading Data Error while using SQL Loader
    vk82 Explorer
    Currently Being Moderated
    there is no error and FILENAME column exists in target table

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points