1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unable to load csv file into oracle table through SQL Loader using ctl file

Discussion in 'Server Administration and Options' started by kartheekbk, May 12, 2014.

  1. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I'm triying to load the csv data through ctl file suing sql loader. My csv file exists with 4 rows of data. When I run the concurrent program , only the last row is loading to the oracle table successfully. Other tables are failing with the error

    Record 1: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 3: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 4: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number


    even I created the data type of the column DISTRIBUTION_NUM as NUMBER, I receive the above error.

    Pl address the solution.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    Hi.
    At first check a separator of a fractional and integer part of number?
    Can you show CSV file and CTL-file?
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    Hi,

    Check column place,

    In ctl file and data file both column sequences are correct?
     
  4. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    hi, Thanks for the reply..Below are the required files which I used to load the data.
    which would be helpful to you to debug the error.

    ----------------------------------
    CSV file contents (xx_pur.csv)
    ---------------------------------
    661700,1000,204,ORIGINAL,STANDARD,USD,,"Stock, Ms. Pat",Advantage Corp,ADVANTAGE - US,M1- Seattle Mfg,V1- New York City,APPROVED,,,,701126,,1,,Trans Electric Fork Lift,Transmission / Drive Train Electric Fork lifts,8035,,2,,,,724445,2,,,13401,1

    661710,1000,204,ORIGINAL,STANDARD,USD,,"Stock, Ms. Pat",Advantage Corp,ADVANTAGE - US,M1- Seattle Mfg,V1- New York City,APPROVED,,,,701126,,1,,Trans Electric Fork Lift,Transmission / Drive Train Electric Fork lifts,8035,,2,,,,724445,2,,,13401,1

    661720,1000,204,ORIGINAL,STANDARD,USD,,"Stock, Ms. Pat",Advantage Corp,ADVANTAGE - US,M1- Seattle Mfg,V1- New York City,APPROVED,,,,701126,,1,,Trans Electric Fork Lift,Transmission / Drive Train Electric Fork lifts,8035,,2,,,,724445,2,,,13401,1

    661730,1000,204,ORIGINAL,STANDARD,USD,,"Stock, Ms. Pat",Advantage Corp,ADVANTAGE - US,M1- Seattle Mfg,V1- New York City,APPROVED,,,,701126,,1,,Trans Electric Fork Lift,Transmission / Drive Train Electric Fork lifts,8035,,2,,,,724445,2,,,13401,1

    661740,1000,204,ORIGINAL,STANDARD,USD,,"Stock, Ms. Pat",Advantage Corp,ADVANTAGE - US,M1- Seattle Mfg,V1- New York City,APPROVED,,,,701126,,1,,Trans Electric Fork Lift,Transmission / Drive Train Electric Fork lifts,8035,,2,,,,724445,2,,,13401,1


    -------------
    CTL file
    -------------
    options (BINDSIZE=12557648)
    LOAD DATA
    INFILE 'xx_pur.csv'
    INTO TABLE xxpo_import_data
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    INTERFACE_HEADER_ID,
    BATCH_ID,
    ORG_ID,
    ACTION,
    DOCUMENT_TYPE_CODE,
    CURRENCY_CODE,
    AGENT_ID,
    FULL_NAME,
    VENDOR_NAME,
    VENDOR_SITE_CODE,
    SHIP_TO_LOCATION,
    BILL_TO_LOCATION,
    APPROVAL_STATUS,
    FREIGHT_CARRIER,
    FOB,
    FREIGHT_TERMS,
    INTERFACE_LINE_ID,
    LINE_NUM,
    SHIPMENT_NUM,
    LINE_TYPE,
    ITEM,
    ITEM_DESCRIPTION,
    ITEM_ID,
    UOM_CODE,
    QUANTITY,
    UNIT_PRICE,
    SHIP_TO_ORGANIZATION_CODE,
    LIST_PRICE_PER_UNIT,
    INTERFACE_DISTRIBUTION_ID,
    QUANTITY_ORDERED,
    DESTINATION_ORGANIZATION_ID,
    SET_OF_BOOKS_ID,
    CHARGE_ACCOUNT_ID,
    DISTRIBUTION_NUM
    )


    --------------------------------------
    Staging table (xxpo_import_data)
    --------------------------------------
    CREATE TABLE XXPO_IMPORT_DATA
    (interface_header_id NUMBER,
    batch_id NUMBER,
    org_id NUMBER,
    action VARCHAR2(25),
    document_type_code VARCHAR2(25),
    currency_code VARCHAR2(15),
    agent_id NUMBER,
    full_name VARCHAR2(240),
    vendor_name VARCHAR2(240),
    vendor_site_code VARCHAR2(15),
    ship_to_location VARCHAR2(60),
    bill_to_location VARCHAR2(60),
    approval_status VARCHAR2(25),
    freight_carrier VARCHAR2(25),
    fob VARCHAR2(25),
    freight_terms VARCHAR2(25),
    interface_line_id NUMBER,
    line_num NUMBER,
    shipment_num NUMBER,
    line_type VARCHAR2(25),
    item VARCHAR2(1000),
    item_description VARCHAR2(240),
    item_id NUMBER,
    uom_code VARCHAR2(3),
    quantity NUMBER,
    unit_price NUMBER,
    ship_to_organization_code VARCHAR2(3),
    list_price_per_unit NUMBER,
    interface_distribution_id NUMBER,
    quantity_ordered NUMBER,
    destination_organization_id NUMBER,
    set_of_books_id NUMBER,
    charge_account_id NUMBER,
    distribution_num NUMBER);
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    loading didn't cause problems.....

    Code (SQL):

    SELECT * FROM xxpo_import_data;

    SQL>
     
    INTERFACE_HEADER_ID   BATCH_ID     ORG_ID ACTION                    DOCUMENT_TYPE_CODE        CURRENCY_CODE     AGENT_ID FULL_NAME                                                                        VENDOR_NAME                                                                      VENDOR_SITE_CODE SHIP_TO_LOCATION                                             BILL_TO_LOCATION                                             APPROVAL_STATUS           FREIGHT_CARRIER           FOB                       FREIGHT_TERMS             INTERFACE_LINE_ID   LINE_NUM SHIPMENT_NUM LINE_TYPE                 ITEM                                                                             ITEM_DESCRIPTION                                                                    ITEM_ID UOM_CODE   QUANTITY UNIT_PRICE SHIP_TO_ORGANIZATION_CODE LIST_PRICE_PER_UNIT INTERFACE_DISTRIBUTION_ID QUANTITY_ORDERED DESTINATION_ORGANIZATION_ID SET_OF_BOOKS_ID CHARGE_ACCOUNT_ID DISTRIBUTION_NUM
    ------------------- ---------- ---------- ------------------------- ------------------------- --------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------- ------------------------- ------------------------- ------------------------- ----------------- ---------- ------------ ------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------- ---------- ---------- ------------------------- ------------------- ------------------------- ---------------- --------------------------- --------------- ----------------- ----------------
                 661700       1000        204 ORIGINAL                  STANDARD                  USD                        Stock, Ms. Pat                                                                   Advantage Corp                                                                   ADVANTAGE - US   M1- Seattle Mfg                                              V1- NEW York City                                            APPROVED                                                                                                           701126                       1                           Trans Electric Fork Lift                                                         Transmission / Drive Train Electric Fork lifts                                         8035                   2                                                                             724445                2                                                         13401                1
                 661710       1000        204 ORIGINAL                  STANDARD                  USD                        Stock, Ms. Pat                                                                   Advantage Corp                                                                   ADVANTAGE - US   M1- Seattle Mfg                                              V1- NEW York City                                            APPROVED                                                                                                           701126                       1                           Trans Electric Fork Lift                                                         Transmission / Drive Train Electric Fork lifts                                         8035                   2                                                                             724445                2                                                         13401                1
                 661720       1000        204 ORIGINAL                  STANDARD                  USD                        Stock, Ms. Pat                                                                   Advantage Corp                                                                   ADVANTAGE - US   M1- Seattle Mfg                                              V1- NEW York City                                            APPROVED                                                                                                           701126                       1                           Trans Electric Fork Lift                                                         Transmission / Drive Train Electric Fork lifts                                         8035                   2                                                                             724445                2                                                         13401                1
                 661730       1000        204 ORIGINAL                  STANDARD                  USD                        Stock, Ms. Pat                                                                   Advantage Corp                                                                   ADVANTAGE - US   M1- Seattle Mfg                                              V1- NEW York City                                            APPROVED                                                                                                           701126                       1                           Trans Electric Fork Lift                                                         Transmission / Drive Train Electric Fork lifts                                         8035                   2                                                                             724445                2                                                         13401                1
                 661740       1000        204 ORIGINAL                  STANDARD                  USD                        Stock, Ms. Pat                                                                   Advantage Corp                                                                   ADVANTAGE - US   M1- Seattle Mfg                                              V1- NEW York City                                            APPROVED                                                                                                           701126                       1                           Trans Electric Fork Lift                                                         Transmission / Drive Train Electric Fork lifts                                         8035                   2                                                                             724445                2                                                         13401                1
     


     
    can you show log-file ?

    try to remove from the CTL file an option options (BINDSIZE=12557648)
    can you show result this query for your of user: : select * from nls_session_parameters ?
     
  6. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    Hi Sergey,

    result for the query: select * from nls_session_parameters ::

    PARAMETER VALUE

    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE


    and when I removed that options (BINDSIZE=12557648) I'm getting the below error log and none of the rows are loaded into the DB table.

    log file:

    +---------------------------------------------------------------------------+

    +---------------------------------------------------------------------------+

    Current system time is 13-MAY-2014 14:10:18

    +---------------------------------------------------------------------------+


    +-----------------------------
    | Starting concurrent program execution...
    +-----------------------------

    Arguments
    ------------
    abcd.csv
    ------------

    SQL*Loader: Release 10.1.0.5.0 - Production on Tue May 13 14:10:18 2014

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Control File: xxpo_import_all.ctl


    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional

    Table XXPO_IMPORT_DATA, loaded from every logical record.
    Insert option in effect for this table: APPEND
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    INTERFACE_HEADER_ID FIRST * , O(") CHARACTER
    BATCH_ID NEXT * , O(") CHARACTER
    ORG_ID NEXT * , O(") CHARACTER
    ACTION NEXT * , O(") CHARACTER
    DOCUMENT_TYPE_CODE NEXT * , O(") CHARACTER
    CURRENCY_CODE NEXT * , O(") CHARACTER
    AGENT_ID NEXT * , O(") CHARACTER
    FULL_NAME NEXT * , O(") CHARACTER
    VENDOR_NAME NEXT * , O(") CHARACTER
    VENDOR_SITE_CODE NEXT * , O(") CHARACTER
    SHIP_TO_LOCATION NEXT * , O(") CHARACTER
    BILL_TO_LOCATION NEXT * , O(") CHARACTER
    APPROVAL_STATUS NEXT * , O(") CHARACTER
    FREIGHT_CARRIER NEXT * , O(") CHARACTER
    FOB NEXT * , O(") CHARACTER
    FREIGHT_TERMS NEXT * , O(") CHARACTER
    INTERFACE_LINE_ID NEXT * , O(") CHARACTER
    LINE_NUM NEXT * , O(") CHARACTER
    SHIPMENT_NUM NEXT * , O(") CHARACTER
    LINE_TYPE NEXT * , O(") CHARACTER
    ITEM NEXT * , O(") CHARACTER
    ITEM_DESCRIPTION NEXT * , O(") CHARACTER
    ITEM_ID NEXT * , O(") CHARACTER
    UOM_CODE NEXT * , O(") CHARACTER
    QUANTITY NEXT * , O(") CHARACTER
    UNIT_PRICE NEXT * , O(") CHARACTER
    SHIP_TO_ORGANIZATION_CODE NEXT * , O(") CHARACTER
    LIST_PRICE_PER_UNIT NEXT * , O(") CHARACTER
    INTERFACE_DISTRIBUTION_ID NEXT * , O(") CHARACTER
    QUANTITY_ORDERED NEXT * , O(") CHARACTER
    DESTINATION_ORGANIZATION_ID NEXT * , O(") CHARACTER
    SET_OF_BOOKS_ID NEXT * , O(") CHARACTER
    CHARGE_ACCOUNT_ID NEXT * , O(") CHARACTER
    DISTRIBUTION_NUM NEXT * , O(") CHARACTER

    value used for ROWS parameter changed from 64 to 29
    Record 1: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 3: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 4: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number

    Record 5: Rejected - Error on table XXPO_IMPORT_DATA, column DISTRIBUTION_NUM.
    ORA-01722: invalid number


    Table XXPO_IMPORT_DATA:
    0 Rows successfully loaded.
    5 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.


    Space allocated for bind array: 254388 bytes(29 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 5
    Total logical records rejected: 5
    Total logical records discarded: 0

    Run began on Tue May 13 14:10:18 2014
    Run ended on Tue May 13 14:10:19 2014

    Elapsed time was: 00:00:00.44
    CPU time was: 00:00:00.07



    +---------------------------------------------------------------------------+
    Executing request completion options...


    Finished executing request completion options.

    +---------------------------------------------------------------------------+
    Concurrent request completed
    Current system time is 13-MAY-2014 14:10:19

    +---------------------------------------------------------------------------+


    Regards,
    B Kartheek
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    In .ctl file just use "REPLACE:)DISTRIBUTION_NUM,CHR(13),'')" at the end.

    it will remove extra space which we didn't see actually.
     
  8. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    Finally it was worked by taken instructions from Jagadekara Reddy.

    I just added DISTRIBUTION_NUM "REPLACE:)DISTRIBUTION_NUM,CHR(13),'')" at the end of the ctl file. then all the data have been loaded into the table.

    I have some doubts here. pl explain.

    1. why it was not worked earlier? as I don't see any wrong with the data which I configured here.
    and why it was only loading last row data into the table and throwing errors for all the other records.
    I used here APPEND to insert the data into the table and used normal csv file(not used ms-dos csv file)

    2. How the REPLACE functions used effectively here than NUMBER data type

    3. what is options (BINDSIZE=12557648), bind array, read array and how they behaves here ?
    becuase if I not use the BINDSIZE here , by default the bind size would be 256000 bytes
    and it throwing the error as Space allocated for bind array: 254388 bytes(29 rows)..

    seems something issue with space allocation here.

    It would be grateful for me to improve my skills in oracle apps, if you answer all these. Thanks .


    Regards,
    B. Kartheek
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: Unable to load csv file into oracle table through SQL Loader using ctl file

    Hi.

    1 . Get rid of the excess translations of lines
    And everything will work for you without additional processing of input data

    2
    BINDSIZE (maximum size)

    Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.
    BINDSIZE specifies the maximum size (bytes) of the bind array. The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS.


    http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_control_file.htm#i1005907

    READSIZE (read buffer size)

    Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.
    The READSIZE parameter is used only when reading data from datafiles. When reading records from a control file, a value of 64 kilobytes (KB) is always used as the READSIZE.
    The READSIZE parameter lets you specify (in bytes) the size of the read buffer, if you choose not to use the default. The maximum size allowed is platform dependent.
    In the conventional path method, the bind array is limited by the size of the read buffer. Therefore, the advantage of a larger read buffer is that more data can be read before a commit operation is required.
    For example, setting READSIZE to 1000000 enables SQL*Loader to perform reads from the external datafile in chunks of 1,000,000 bytes before a commit is required.
    Note:
    If the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.
    The READSIZE parameter has no effect on LOBs. The size of the LOB read buffer is fixed at 64 kilobytes (KB).