How do you convert from scientific notation in Oracle SQL?

Question

We are trying to load a file created by FastExport into an oracle database.
However the Float column is being exported like this: 1.47654345670000000000 E010.

How do you configure SQL*Loader to import it like that.

Expecting Control Script to look like:

OPTIONS(DIRECT=TRUE, ROWS=20000, BINDSIZE=8388608, READSIZE=8388608)
UNRECOVERABLE LOAD DATA 
infile 'data/SOME_FILE.csv'
append
INTO TABLE SOME_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols (
    FLOAT_VALUE             CHAR(38)       "???????????????????",
    FILED02                 CHAR(5)        "TRIM(:FILED02)",
    FILED03                 TIMESTAMP      "YYYY-MM-DD HH24:MI:SS.FF6",
    FILED04                 CHAR(38)
)

I tried to_number('1.47654345670000000000 E010', '9.99999999999999999999 EEEE')

Error: ORA-01481: invalid number format model error.

I tried to_number('1.47654345670000000000 E010', '9.99999999999999999999EEEE')

Error: ORA-01722: invalid number

These are the solutions I came up with in order of preference:

1. to_number(replace('1.47654345670000000000 E010', ' ', ''))
2. to_number(TRANSLATE('1.47654345670000000000 E010', '1 ', '1'))

I would like to know if there are any better performing solutions.

Answer 1

As far as I'm aware there is no way to have to_number ignore the space, and nothing you can do in SQL*Loader to prepare it. If you can't remove it by pre-processing the file, which you've suggested isn't an option, then you'll have to use a string function at some point. I wouldn't expect it to add a huge amount of processing, above what to_number will do anyway, but I'd always try it and see rather than assuming anything - avoiding the string functions sounds a little like premature optimisation. Anyway, the simplest is possibly replace:

select to_number(replace('1.47654345670000000000 E010',' ',''),
    '9.99999999999999999999EEEE') from dual;

or just for display purposes:

column num format 99999999999
select to_number(replace('1.47654345670000000000 E010',' ',''),
    '9.99999999999999999999EEEE') as num from dual


         NUM
------------
 14765434567

You could define your own function to simplify the control file slightly, but not sure it'd be worth it.

Two other options come to mind. (a) Load into a temporary table as a varchar, and then populate the real table using the to_number(replace()); but I doubt that will be any improvement in performance and might be substantially worse. Or (b) if you're running 11g, load into a varchar column in the real table, and make your number column a virtual column that applies the functions.

Actually, a third option... don't use SQLLoader at all, but use the CSV file as an external table, and populate your real table from that. You'll still have to do the to_number(replace()) but you might see a difference in performance over doing it in SQLLoader. The difference could be that it's worse, of course, but might be worth trying.