CODEWITHSUNDEEP
oracle-databaseunixsql-loader
Praveen k. Agrawal
Praveen k. Agrawal

Reputation: 449

Loading text file in oracle from unix system

I am having text file contaning field in below manner.

"64252368","7489040","305762",
"64285217","12132108","787341",

I am using a below control file.

OPTIONS (SKIP=1)
LOAD DATA
TRUNCATE INTO TABLE test_table
FIELDS TERMINATED BY '",'

(
        LEARNEVENT_ID,
        ORGANIZATION,
        COURSE_ID
)

But, I am getting the error:

Record 1: Rejected - Error on table test_table, column LEARNEVENT_ID
ORA-01722: invalid number

Kindly help me on it.

Upvotes: 0

Views: 1705

Answers (2)

Ben
Ben

Reputation: 52863

You need to change your ctl file to include OPTIONALLY ENCLOSED BY option. 

OPTIONS (SKIP=1)
LOAD DATA
TRUNCATE INTO TABLE test_table
FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"'
(
        LEARNEVENT_ID,
        ORGANIZATION,
        COURSE_ID
)

I'd recommend reading up on SQL*Loader.

Upvotes: 2

GuZzie
GuZzie

Reputation: 974

The problem lays with the encapsulation of the numbers with the quotes " " and your fields terminated by '",' simply does not strip the quotes.

Try this

OPTIONS(SKIP=1)
LOAD DATA
TRUNCATE INTO TABLE test_table
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  LEARNEVENT_ID "replace ( :LEARNEVENT_ID ,'"', '')",
  ORGINAZATION "replace ( :ORGINAZATION ,'"', '')",
  COURSE_ID "replace ( :COURSE_ID ,'"', '')"
)

Upvotes: 0

Related Questions