如何从Oracle SQL中的科学符号转换？

Question

我们正在尝试将FastExport创建的文件加载到oracle数据库中。

但是，浮点数列的导出方式如下：1.47654345670000000000 E010。

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

预期Control脚本如下所示：

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

错误：ORA-01481: invalid number format model错误。

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

错误：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无论如何都要做的事情，但我总是尝试查看，而不是假设任何事情--避免字符串函数听起来有点像过早的优化。无论如何，最简单的可能是replace

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列中，并将number列设置为应用函数的虚列。

实际上，第三种选择..。根本不要使用SQLLoader，而是使用CSV文件作为外部表，并从中填充真正的表。您仍然需要执行to_number(replace()) _but，您可能会发现在性能上与在SQL_Loader中执行不同。不同的是，这当然更糟糕，但可能值得一试。

Answer 2

用“设置数字”更改数字宽度

从blabla中选择num >

结果>> 1,0293E+15

设定数字20；

从blabla中选择num >

结果>> 1029301200000021

Answer 3

下面是我提出的解决方案：

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

在我的解决方案中，向数字的转换是隐式的："REPLACE(:FLOAT_VALUE,' ','')"