sql - 如何从 Oracle SQL 中的科学计数法转换？

Question

我们正在尝试将 FastExport 创建的文件加载到 Oracle 数据库中。
然而，Float 列是这样导出的: 1.47654345670000000000 E010 。

如何配置 SQL*Loader 来像这样导入它。

期望控制脚本如下所示:

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)
)

我尝试过 to_number('1.47654345670000000000 E010', '9.99999999999999999999 EEEE')

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

我尝试过 to_number('1.47654345670000000000 E010', '9.99999999999999999999EEEE')

Error: ORA-01722: invalid number

这些是我按优先顺序提出的解决方案:

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

我想知道是否有更好的解决方案。

Answer 1

据我所知，没有办法让 to_number 忽略该空格，并且您在 SQL*Loader 中也无能为力来准备它。如果您无法通过预处理文件来删除它(您建议这不是一个选项)，那么在某些时候您将不得不使用字符串函数。我不希望它添加大量的处理，高于 to_number 无论如何都会做的事情，但我总是尝试它并查看而不是假设任何事情 - 避免字符串函数听起来有点像过早的优化。无论如何，最简单的可能是替换:

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

或仅用于显示目的:

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


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

您可以定义自己的函数来稍微简化控制文件，但不确定它是否值得。

我想到了另外两个选择。 (a) 以 varchar 形式加载到临时表中，然后使用 to_number(replace()) 填充真实表；但我怀疑这会对性能产生任何改善，并且可能会变得更糟。或者 (b) 如果您运行的是 11g，则加载到实际表中的 varchar 列中，并将数字列设置为应用函数的 virtual column。

实际上，第三种选择...根本不使用 SQLLoader，而是使用 CSV 文件作为外部表，并从中填充您的真实表。您仍然需要执行to_number(replace())，但您可能会发现与在 SQL Loader 中执行此操作相比，性能有所不同。当然，区别可能在于它更糟糕，但可能值得尝试。