CODEWITHSUNDEEP
oracle-databasesql-loader
arjun gaur
arjun gaur

Reputation: 548

loading data in table using SQL Loader

I'm loading data into my table through SQL Loader data loading is successful but i''m getting garbage(repetitive) value in a particular column for all rows

After inserting : column TERM_AGREEMENT is getting value '806158336' for every record My csv file contains atmost 3 digit data for that column,but i'm forced to set my column definition to Number(10). 

   LOAD DATA
    infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
    REPLACE
    INTO TABLE LOAN_BALANCE_MASTER_INT
    fields terminated by ',' optionally enclosed by '"'
    (
    ACCOUNT_NO,
    CUSTOMER_NAME,
    LIMIT,
    REGION,

    **TERM_AGREEMENT INTEGER**
    )


create table LOAN_BALANCE_MASTER_INT
(
  ACCOUNT_NO             NUMBER(30),
  CUSTOMER_NAME          VARCHAR2(70),
  LIMIT                  NUMBER(30),

  PRODUCT_DESC           VARCHAR2(30),
  SUBPRODUCT_CODE        NUMBER,

  ARREARS_INT            NUMBER(20,2),
  IRREGULARITY           NUMBER(20,2),
  PRINCIPLE_IRREGULARITY NUMBER(20,2),


  **TERM_AGREEMENT         NUMBER(10)**
)

Upvotes: 3

Views: 3024

Answers (2)

arjun gaur
arjun gaur

Reputation: 548

I actually solved this issue on my own. Firstly, thanks to @Gary_W AND @Alessandro for their inputs.Really appreciate your help guys,learned some new things in the process. Here's the new fragment which worked and i got the correct data for the last column 

 LOAD DATA
    infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
    REPLACE
    INTO TABLE LOAN_BALANCE_MASTER_INT
    fields terminated by ',' optionally enclosed by '"'
    (
    ACCOUNT_NO,
    CUSTOMER_NAME,
    LIMIT,
    REGION,

    **TERM_AGREEMENT INTEGER Terminated by Whitspace**
    )
  • 'Terminated by whitespace' - I went through some threads of SQL Loader and i used 'terminated by whitespace' in the last column of his ctl file. it worked ,this time i didn't even had to use 'INTEGER' or 'EXTERNAL' or EXPRESSION '..' for conversion. Just one thing, now can you guys let me now what could possibly be creating issue ?what was there in my csv file in that column and how by adding this thing solved the issue ? Thanks.

Upvotes: 1

Alessandro Vecchio
Alessandro Vecchio

Reputation: 186

INTEGER is for binary data type. If you're importing a csv file, I suppose the numbers are stored as plain text, so you should use INTEGER EXTERNAL. The EXTERNAL clause specifies character data that represents a number.

Edit: The issue seems to be the termination character of the file. You should be able to solve this issue by editing the INFILE line this way:

INFILE'/ipoapplication/utl_file/LBR_HE_Mar16.csv' "STR X'5E204D'"

Where '5E204D' is the hexadecimal for '^ M'. To get the hexadecimal value you can use the following query:

SELECT utl_raw.cast_to_raw ('^ M') AS hexadecimal FROM dual;

Hope this helps.

Upvotes: 1

Related Questions