5 Replies Latest reply: May 5, 2013 10:51 PM by 957963 RSS

    Unable to load data from data file tocustom table using CTL file(SQLLoader)

    957963
      Hi,

      I am not able to load the data from data file to custom table using control(CTL) file program.
      The issue is "global_attribute10" column having the some spl character. this column having in middle of the datafile.
      If I put the global_attribute10 column as last column, am able to load the data. If I put this column before last column, am not able load the data and getting error out. But I will get the data file as global_attribute10 before last colum

      many many thanks in advance!!!

      Cheers,
      Thirupathi
        • 1. Re: Unable to load data from data file tocustom table using CTL file(SQLLoader)
          799603
          How will be the data in global_attribute10?

          Also,you can try like this
                  LOAD DATA     
                  APPEND     
                  INTO TABLE xxc_p        
                  fields terminated by '|' 
                  optionally enclosed by '"'       
                  trailing nullcols
                  (record_type   POSITION(1:2) CHAR NULLIF record_type = BLANKS "RTRIM(global_attribute10)",
          
          {code}
          
          Edited by: sandy on May 4, 2013 5:48 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Unable to load data from data file tocustom table using CTL file(SQLLoader)
            Srini Chavali-Oracle
            Pl post the complete sqlldr command used, the contents of the controlfile, description of the table, contents of the sqlldr log file showing the complete error code and message and a sample of data being loaded.

            HTH
            Srini
            • 3. Re: Unable to load data from data file tocustom table using CTL file(SQLLoader)
              Hussein Sawwan-Oracle
              Please post the details of the application release, database version and OS.
              I am not able to load the data from data file to custom table using control(CTL) file program.
              The issue is "global_attribute10" column having the some spl character. this column having in middle of the datafile.
              If I put the global_attribute10 column as last column, am able to load the data. If I put this column before last column, am not able load the data and getting error out. But I will get the data file as global_attribute10 before last colum
              What is the error message?

              Thanks,
              Hussein
              • 4. Re: Unable to load data from data file tocustom table using CTL file(SQLLoader)
                957963
                Hi Srini,

                Please find the custom table Script.
                ======================

                CREATE TABLE XX_INV_SYSTEM_ITEMS_STG
                (
                ORGANIZATION_ID NUMBER
                , SEGMENT1 VARCHAR2(32)
                , DESCRIPTION VARCHAR2(240)
                , ITEM_TYPE VARCHAR2(30)
                , COST_OF_SALES_ACCOUNT NUMBER
                , SALES_ACCOUNT NUMBER
                , ATTRIBUTE_CATEGORY VARCHAR2(30)
                , ATTRIBUTE1 VARCHAR2(240)
                , ATTRIBUTE2 VARCHAR2(240)
                , ATTRIBUTE3 VARCHAR2(240)
                , ATTRIBUTE4 VARCHAR2(240)
                , ATTRIBUTE5 VARCHAR2(240)
                , ATTRIBUTE6 VARCHAR2(240)
                , ATTRIBUTE7 VARCHAR2(240)
                , ATTRIBUTE8 VARCHAR2(240)
                , ATTRIBUTE9 VARCHAR2(240)
                , ATTRIBUTE10 VARCHAR2(240)
                , ATTRIBUTE11 VARCHAR2(240)
                , ATTRIBUTE12 VARCHAR2(240)
                , ATTRIBUTE13 VARCHAR2(240)
                , ATTRIBUTE14 VARCHAR2(240)
                , ATTRIBUTE15 VARCHAR2(240)
                , GLOBAL_ATTRIBUTE10 VARCHAR2(150)
                , REF_INVENTORY_ITEM_ID NUMBER
                , REF_ORGANIZATION_ID NUMBER
                , STATUS_FLAG VARCHAR2(2)
                , PROCESS_FLAG NUMBER
                , SET_PROCESS_ID NUMBER
                , TRANSACTION_TYPE VARCHAR2(20)
                , LAST_UPDATE_DATE DATE
                , LAST_UPDATED_BY NUMBER
                , CREATION_DATE DATE
                , CREATED_BY NUMBER
                , LAST_UPDATE_LOGIN NUMBER
                , ERROR_CODE VARCHAR2(2000)
                , ERROR_MESSAGE VARCHAR2(4000)
                );


                CTL File Script:
                ==========

                OPTIONS (SKIP = 1)
                LOAD DATA
                INFILE '$P_DATA_FILE'
                APPEND
                INTO TABLE XX_INV_SYSTEM_ITEMS_STG
                FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                TRAILING NULLCOLS
                ( organization_id
                , segment1
                , description
                , item_type
                , cost_of_sales_account
                , sales_account
                , attribute_category
                , attribute1
                , attribute2
                , attribute3
                , attribute4
                , attribute5
                , attribute6
                , attribute7
                , attribute8
                , attribute9
                , attribute10
                , attribute11
                , attribute12
                , attribute13
                , attribute14
                , attribute15
                , global_attribute10
                , ref_inventory_item_id
                , ref_organization_id
                , status_flag
                , process_flag
                , set_process_id
                , transaction_type
                , last_update_date
                , last_updated_by
                , creation_date     
                , created_by
                , last_update_login     
                , error_code
                , error_message
                )

                DATA FILE:
                =======

                ORGANIZATION_ID     SEGMENT1     DESCRIPTION     ITEM_TYPE     COST_OF_SALES_ACCOUNT     SALES_ACCOUNT     ATTRIBUTE_CATEGORY     ATTRIBUTE1     ATTRIBUTE2     ATTRIBUTE3     ATTRIBUTE4     ATTRIBUTE5     ATTRIBUTE6     ATTRIBUTE7     ATTRIBUTE8     ATTRIBUTE9     ATTRIBUTE10     ATTRIBUTE11     ATTRIBUTE12     ATTRIBUTE13     ATTRIBUTE14     ATTRIBUTE15     GLOBAL_ATTRIBUTE10     REF_INVENTORY_ITEM_ID     REF_ORGANIZATION_ID
                85     1CK34099FD.1000.1     THIRD ITEM CONVERSION TEST LOAD # 55 / ITEM ALREADY EXISTS AT MASTER LEVEL ONLY     SH-MUSIC FG     1388     1578     SH-FINISH GOOD     110457H1A          SEE GSK CLIFTON QUALITY CONTROL PLAN          .016 MET POLY     ||     N/A     SATIN UV VARNISH (SPW-0-SATIN-0) & WIKOFF 5131 UV COATING     NO|EMBOSS     CYAN, 280, COOL GREY 8, 2 WHITES CS#9888     SE - HEAT TREATED PALLET REQUIRED     L          110457H1A | 1 1/4 X 7/8 X 4 11/16     21 UP 19 11/16 X 35 9/32     Radius Reference # 155     1026446     85


                I am not able to load the data from datafile to custom table using above ctlfile program. the reason is 'GLOBAL_ATTRIBUTE10' column having some spl character (Radius Reference # 155) {like chr(13)value} .If i put this column as last column, am able to load the data. if i put this column as it is like datafile, getting errorout and its showing in the logfile as ref_inventory_item_id and ref_organization_id is invalid numbers. Please suggest me shall i need to any thing changes in CTL file. I tried like "TRIM(:global_attribute10)" and "REPLACE(:global_attribute10, chr(13), ' ')" but could not workout.

                Kindly let me know how to handle this issue.

                Thanks in advance!!

                Cheers,
                Thirupathi
                • 5. Re: Unable to load data from data file tocustom table using CTL file(SQLLoader)
                  957963
                  Hi Hussein,

                  Please find the custom table Script.
                  ======================

                  CREATE TABLE XX_INV_SYSTEM_ITEMS_STG
                  (
                  ORGANIZATION_ID NUMBER
                  , SEGMENT1 VARCHAR2(32)
                  , DESCRIPTION VARCHAR2(240)
                  , ITEM_TYPE VARCHAR2(30)
                  , COST_OF_SALES_ACCOUNT NUMBER
                  , SALES_ACCOUNT NUMBER
                  , ATTRIBUTE_CATEGORY VARCHAR2(30)
                  , ATTRIBUTE1 VARCHAR2(240)
                  , ATTRIBUTE2 VARCHAR2(240)
                  , ATTRIBUTE3 VARCHAR2(240)
                  , ATTRIBUTE4 VARCHAR2(240)
                  , ATTRIBUTE5 VARCHAR2(240)
                  , ATTRIBUTE6 VARCHAR2(240)
                  , ATTRIBUTE7 VARCHAR2(240)
                  , ATTRIBUTE8 VARCHAR2(240)
                  , ATTRIBUTE9 VARCHAR2(240)
                  , ATTRIBUTE10 VARCHAR2(240)
                  , ATTRIBUTE11 VARCHAR2(240)
                  , ATTRIBUTE12 VARCHAR2(240)
                  , ATTRIBUTE13 VARCHAR2(240)
                  , ATTRIBUTE14 VARCHAR2(240)
                  , ATTRIBUTE15 VARCHAR2(240)
                  , GLOBAL_ATTRIBUTE10 VARCHAR2(150)
                  , REF_INVENTORY_ITEM_ID NUMBER
                  , REF_ORGANIZATION_ID NUMBER
                  , STATUS_FLAG VARCHAR2(2)
                  , PROCESS_FLAG NUMBER
                  , SET_PROCESS_ID NUMBER
                  , TRANSACTION_TYPE VARCHAR2(20)
                  , LAST_UPDATE_DATE DATE
                  , LAST_UPDATED_BY NUMBER
                  , CREATION_DATE DATE
                  , CREATED_BY NUMBER
                  , LAST_UPDATE_LOGIN NUMBER
                  , ERROR_CODE VARCHAR2(2000)
                  , ERROR_MESSAGE VARCHAR2(4000)
                  );


                  CTL File Script:
                  ==========

                  OPTIONS (SKIP = 1)
                  LOAD DATA
                  INFILE '$P_DATA_FILE'
                  APPEND
                  INTO TABLE XX_INV_SYSTEM_ITEMS_STG
                  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                  TRAILING NULLCOLS
                  ( organization_id
                  , segment1
                  , description
                  , item_type
                  , cost_of_sales_account
                  , sales_account
                  , attribute_category
                  , attribute1
                  , attribute2
                  , attribute3
                  , attribute4
                  , attribute5
                  , attribute6
                  , attribute7
                  , attribute8
                  , attribute9
                  , attribute10
                  , attribute11
                  , attribute12
                  , attribute13
                  , attribute14
                  , attribute15
                  , global_attribute10
                  , ref_inventory_item_id
                  , ref_organization_id
                  , status_flag
                  , process_flag
                  , set_process_id
                  , transaction_type
                  , last_update_date
                  , last_updated_by
                  , creation_date     
                  , created_by
                  , last_update_login     
                  , error_code
                  , error_message
                  )

                  DATA FILE:
                  =======

                  ORGANIZATION_ID     SEGMENT1     DESCRIPTION     ITEM_TYPE     COST_OF_SALES_ACCOUNT     SALES_ACCOUNT     ATTRIBUTE_CATEGORY     ATTRIBUTE1     ATTRIBUTE2     ATTRIBUTE3     ATTRIBUTE4     ATTRIBUTE5     ATTRIBUTE6     ATTRIBUTE7     ATTRIBUTE8     ATTRIBUTE9     ATTRIBUTE10     ATTRIBUTE11     ATTRIBUTE12     ATTRIBUTE13     ATTRIBUTE14     ATTRIBUTE15     GLOBAL_ATTRIBUTE10     REF_INVENTORY_ITEM_ID     REF_ORGANIZATION_ID
                  85     1CK34099FD.1000.1     THIRD ITEM CONVERSION TEST LOAD # 55 / ITEM ALREADY EXISTS AT MASTER LEVEL ONLY     SH-MUSIC FG     1388     1578     SH-FINISH GOOD     110457H1A          SEE GSK CLIFTON QUALITY CONTROL PLAN          .016 MET POLY     ||     N/A     SATIN UV VARNISH (SPW-0-SATIN-0) & WIKOFF 5131 UV COATING     NO|EMBOSS     CYAN, 280, COOL GREY 8, 2 WHITES CS#9888     SE - HEAT TREATED PALLET REQUIRED     L          110457H1A | 1 1/4 X 7/8 X 4 11/16     21 UP 19 11/16 X 35 9/32     Radius Reference # 155     1026446     85


                  I am not able to load the data from datafile to custom table using above ctlfile program. the reason is 'GLOBAL_ATTRIBUTE10' column having some spl character (Radius Reference # 155) {like chr(13)value} .If i put this column as last column, am able to load the data. if i put this column as it is like datafile, getting errorout and its showing in the logfile as ref_inventory_item_id and ref_organization_id is invalid numbers. Please suggest me shall i need to any thing changes in CTL file. I tried like "TRIM(:global_attribute10)" and "REPLACE(:global_attribute10, chr(13), ' ')" but could not workout.

                  Kindly let me know how to handle this issue.

                  Thanks in advance!!

                  Cheers,
                  Thirupathi