CODEWITHSUNDEEP
sqloracle-databasesql-loader
ScrappyDev
ScrappyDev

Reputation: 2778

How do you convert from scientific notation in Oracle SQL?

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.

Upvotes: 5

Views: 46653

Answers (4)

Add080bbA
Add080bbA

Reputation: 1876

Change number width with "set numw"

select num from blabla >

result >> 1,0293E+15

set numw 20;

select num from blabla >

result >> 1029301200000021

Upvotes: 7

Jana-guest
Jana-guest

Reputation: 11

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.

Upvotes: 1

ScrappyDev
ScrappyDev

Reputation: 2778

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

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.

Upvotes: 9

Related Questions