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.

Gernhard

If the loader accepts a function-like to_number(), maybe it will accept string functions like substring, string concatenation, or strip spaces? – Ence

That will be my last resort. I'm trying to avoid String operations as much as possible. We will have to run these control files for hundreds of billions of records. – Gernhard

to_number(to_char(thecolumn)) i guess – Vinni

That doesn't work because of the space. The to_char doesn't do anything useful since it is already a char. – Gernhard

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.

Valvular

Answer 2

Change number width with "set numw"

---

select num from blabla >

result >> 1,0293E+15

---

set numw 20;

select num from blabla >

result >> 1029301200000021

Demicanton

The question was about SQLLoader not SQL Plus. I don't believe you can use set numw w/ SQLLoader. – Gernhard

I came here from google for SQLPlus. So I will upvote this answer. – Tameika

Answer 3

Here is the solution I went with:

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)       "REPLACE(:FLOAT_VALUE,' ','')",
    FILED02                 CHAR(5)        "TRIM(:FILED02)",
    FILED03                 TIMESTAMP      "YYYY-MM-DD HH24:MI:SS.FF6",
    FILED04                 CHAR(38)
)

In my solution the conversion to a number is implicit: "REPLACE(:FLOAT_VALUE,' ','')"

Gernhard

Answer 4

In Oracle 11g, it's not needed to convert numbers specially.

Just use integer external in the .ctl-file:

I tried the following in my Oracle DB:

field MYNUMBER has type NUMBER.

Inside .ctl-file I used the following definition:

MYNUMBER integer external

In the datafile the value is: MYNUMBER: -1.61290E-03

As for the result: sqlldr loaded the notation correctly: MYNUMBER field: -0.00161290

I am not sure if it's a bug or a feature; but it works in Oracle 11g.

Mauney