Loading text file in oracle from unix system

Question

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.

Ben · Accepted Answer · 2012-03-07 10:38:09Z

2

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.

edited Mar 7, 2012 at 10:38

answered Mar 7, 2012 at 9:58

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Praveen k. Agrawal Over a year ago

Thanks Ben, when i am trying to use enclosed by in my control file now geeting below error. no terminator found after TERMINATED and ENCLOSED field

Ben Over a year ago

If you data ends in a comma, then using TRAILING NULLCOLS. Remove this line if it doesn't...

Praveen k. Agrawal Over a year ago

my data end with comma. kindly suggest me what should i have to do in this case?

Ben Over a year ago

Apparently TRAILING NULLCOLS isn't needed in this situation. It worked for me without.., which I find a little confusing but never mind.

Benoit Over a year ago

@Praveenk.Agrawal : If you are content with this answer you should accept it, which is how all the sites on StackExchange (including this one) work.

GuZzie · Accepted Answer · 2012-03-07 09:57:17Z

0

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 ,'"', '')"
)

answered Mar 7, 2012 at 9:57

GuZzie

9741 gold badge6 silver badges23 bronze badges

2 Comments

Benoit Over a year ago

okay those are only numbers, but instead you should used Ben's solution.

GuZzie Over a year ago

The question only applies to numbers.

Collectives™ on Stack Overflow

Loading text file in oracle from unix system

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related