0

I am trying to load data from a text file that is separated by a pipe "|" using the following control file:

OPTIONS (DIRECT = FALSE, SKIP = 2, ERRORS = 2147483647)
LOAD INTO TABLE <table>
APPEND FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
    desc    CHAR (4000),
    sl_no   CHAR (500),
    amt_var CHAR (4000),
    amt    "DECODE(NVL(LENGTH(TRIM(TRANSLATE(:amt_var, ' +-.0123456789E',' '))),0),0,:amt_var,0)"
)

Here amt_var is CHAR(4000), amt is FLOAT. The amt_var column is loaded with data from the flat file which could be float numbers or text which is why I am using TRANSLATE to identify and accordingly save numbers in amt field.

Problem: Upon loading, all data in column: amt is showing as 0.0. In other words, all data in that column is interpreted as characters and not numbers. However, I can clearly see that that most of the data in the column are numbers. What could be the reason?

Sample Data:

Header Title|Line||
Desc|Line|Amount
Some text here |ABCD0001|58847811
Some text here |ABCD0002|111881
Some text here |ABCD0003|1571
Glenn Teitelbaum
  • 10,108
  • 3
  • 36
  • 80
Birupakhya Dash
  • 531
  • 3
  • 11
  • possible duplicate of [Oracle Decode function results with different formats](http://stackoverflow.com/questions/3853420/oracle-decode-function-results-with-different-formats) – smnbbrv Aug 31 '15 at 15:00
  • I've managed to resolve this issue. Apparently, the reason of the anomaly was a carriage return that was automatically getting added to the end of the column. This was resolved by using dos to unix conversion. – Birupakhya Dash Sep 01 '15 at 11:05

0 Answers0