4 Replies Latest reply: Jan 3, 2013 5:49 AM by vk82 RSS

    Loading Data Error while using SQL Loader

    vk82
      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
          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
            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
              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
                there is no error and FILENAME column exists in target table