How do you convert from scientific notation to Oracle SQL?

Question

We are trying to upload a file created by FastExport to the oracle database.
However, the Float column is exported as follows: 1.47654345670000000000 E010 .

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

The wait for the Script control is as follows:

 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 .

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 more effective solutions.

Answer 1

As far as I know, there is no way to ignore the to_number space, and you cannot do anything in SQL * Loader to prepare it. If you cannot delete it by pre-processing the file that you suggested, this is not an option, then you will need to use the line function at some point. I would not expect it to add a huge amount of processing, higher than what to_number will do anyway, but I always tried and saw, and did not expect anything - avoiding string functions a bit like premature optimization. In any case, the simplest can be replace :

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

or for display purposes only:

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

You can define your own function to simplify the management file a little, but not sure if it's worth it.

Two other options come to mind. (a) Download the temporary table as varchar , and then populate the actual table with to_number(replace()) ; but I doubt it will be any improvement in performance and could be significantly worse. Or (b) if you use 11g, load into the varchar column in the real table and make your column a virtual column column that uses functions.

Actually, the third option ... does not use SQLLoader at all, but use the CSV file as an external table and fill in your real table. You still have to do to_number(replace()) , but you can see the difference in performance than doing it in SQLLoader. The difference may be that it is certainly worse, but it may be worth a try.

Answer 2

Change the width of a number using "set numw"

---

select num from blabla>

result → 1,0293E + 15

---

install numw 20;

select num from blabla>

result → 1029301200000021

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, converting to a number is implicit: "REPLACE(:FLOAT_VALUE,' ','')"

Answer 4

In Oracle 11g, there was no need to specifically convert numbers.

Just use integer external in .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 data file, the value is: MYNUMBER: -1.61290E-03

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

I am not sure if this is a bug or function; but it works in Oracle 11g.