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:

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

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

If the loader accepts a function-like `to_number()`, maybe it will accept string functions like substring, string concatenation, or strip spaces? — wallyk, Feb 29 '12 at 19:30
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. — ScrappyDev, Feb 29 '12 at 19:40
That doesn't work because of the space. The to_char doesn't do anything useful since it is already a char. — ScrappyDev, Feb 29 '12 at 21:48

score 9 · Accepted Answer · answered Feb 29 '12 at 22:38

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.

score 6 · Answer 2 · answered Oct 29 '15 at 16:10

6

Change number width with "set numw"

select num from blabla >

result >> 1,0293E+15

set numw 20;

select num from blabla >

result >> 1029301200000021

answered Oct 29 '15 at 16:10

Add080bbA

1,818
1
18
25

The question was about SQL*Loader not SQL Plus. I don't believe you can use `set numw` w/ SQL*Loader. – ScrappyDev Oct 29 '15 at 21:06
1

I came here from google for SQLPlus. So I will upvote this answer. – Raja Anbazhagan Sep 27 '21 at 08:59

score 1 · Answer 3 · edited Aug 18 '14 at 07:18

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.

score 1 · Answer 4 · answered Mar 01 '12 at 14:43

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

How do you convert from scientific notation in Oracle SQL?

4 Answers4