问题：
19c上的外部表读取问题
贺福
我们正在从Oracle 11g迁移DB-
表有以下配置：
CREATE TABLE TEST
(
AA VARCHAR2 (40 BYTE),
BB VARCHAR2 (2 BYTE),
CC VARCHAR2 (3 BYTE),
DD VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_DIRECTORY
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE TEST_DIRECTORY : 'TEST.bad'
LOGFILE TEST_DIRECTORY : 'TEST.log'
FIELDS
TERMINATED BY '\t' LTRIM REJECT ROWS WITH ALL NULL FIELDS
(AA,
BB,
CC,
DD))
LOCATION (TEST_DIRECTORY:'TEST.dat'))
REJECT LIMIT UNLIMITED;
测试数据（用制表器代替^I）：
NAME1^I0^I ^IUK
NAME2^I0^I ^IUS
当我删除LTRIM时，所有数据都在新数据库上读取（但我们需要保留LTRIM，因为输入文件包含不必要的空格）。我注意到一个字段的值为一个空格，它看起来会导致这个问题，但为什么只在新数据库上？有什么想法是什么原因或者如何轻松修复吗？
两个数据库上的NLS db/会话参数相同。。。但也许有一些全局参数会导致这个问题？
手动更新的测试数据在两个db上都起作用（将第三列中的空格替换为X）
NAME1^I0^IX^IUK
NAME2^I0^IX^IUS
演示：
在11g和19c上创建的下表：
CREATE TABLE TEST
(
AA VARCHAR2 (40 BYTE),
BB VARCHAR2 (2 BYTE),
CC VARCHAR2 (3 BYTE),
DD VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_DIRECTORY
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE TEST_DIRECTORY : 'TEST.bad'
LOGFILE TEST_DIRECTORY : 'TEST.log'
FIELDS
TERMINATED BY '\t' LTRIM
REJECT ROWS WITH ALL NULL FIELDS
(AA,
BB,
CC ,
DD))
LOCATION (TEST_DIRECTORY:'TEST.dat'))
REJECT LIMIT UNLIMITED;
两个表都源于相同的文件测试。dat（由制表符分隔的数据，显示为2个字符^I）：
$ cat -A TEST.dat
NAME1^I0^I ^IUK$
NAME2^I0^I ^IUS$
11g查询：
SQL> SELECT * FROM TEST;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
SQL> SELECT dump(CC) FROM TEST;
DUMP(CC)
--------------------------------------------------------------------------------
NULL
NULL
在19c上查询：
SQL> SELECT * FROM TEST;
no rows selected
测试。在19c上运行查询后显示的日志：
Bad File: TEST.bad
Field Definitions for table TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
AA CHAR (255)
Terminated by " "
Trim whitespace from left
BB CHAR (255)
Terminated by " "
Trim whitespace from left
CC CHAR (255)
Terminated by " "
Trim whitespace from left
DD CHAR (255)
Terminated by " "
Trim whitespace from left
KUP-04021: field formatting error for field DD
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /home/fff/TEST.dat
KUP-04021: field formatting error for field DD
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file /home/fff/TEST.dat
然后，我在没有LTRIM的情况下在两个db上重新创建了表：
CREATE TABLE TEST
(
AA VARCHAR2 (40 BYTE),
BB VARCHAR2 (2 BYTE),
CC VARCHAR2 (3 BYTE),
DD VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_DIRECTORY
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE TEST_DIRECTORY : 'TEST.bad'
LOGFILE TEST_DIRECTORY : 'TEST.log'
FIELDS
TERMINATED BY '\t'
REJECT ROWS WITH ALL NULL FIELDS
(AA,
BB,
CC ,
DD))
LOCATION (TEST_DIRECTORY:'TEST.dat'))
REJECT LIMIT UNLIMITED;
在11g中查询新表：
SQL> SELECT * FROM TEST;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
SQL> SELECT dump(CC) FROM TEST;
DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32
在19c中查询新表：
SQL> SELECT * FROM TEST;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
SQL> SELECT dump(CC) FROM TEST;
DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32
共有2个答案
端木存
它不是LTRIM，而是LDRTRIM。
SQL> create table et
2 ( c1 varchar2(16),
3 c2 varchar2(8),
4 c3 varchar2(8),
5 c4 varchar2(8),
6 c5 varchar2(8),
7 c6 varchar2(8),
8 c7 varchar2(8)
9 )
10 ORGANIZATION EXTERNAL
11 ( TYPE ORACLE_LOADER
12 DEFAULT DIRECTORY temp
13 ACCESS PARAMETERS
14 ( RECORDS DELIMITED BY NEWLINE
15 BADFILE temp: 'TEST_FILE.bad'
16 LOGFILE temp: 'TEST_FILE.log'
17 FIELDS TERMINATED BY X'20A7' LTRIM
18 REJECT ROWS WITH ALL NULL FIELDS
19 (
20 c1,c2,c3,c4,c5,c6,c7
21 ) )
22 LOCATION (temp:'TEST_FILE.dat')
23 )
24 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from et;
C1 C2 C3 C4 C5 C6 C7
---------------- -------- -------- -------- -------- -------- --------
31234569999999 0 A X 0 Z GGGG
SQL>
SQL> drop table et;
Table dropped.
SQL>
SQL> create table et
2 ( c1 varchar2(16),
3 c2 varchar2(8),
4 c3 varchar2(8),
5 c4 varchar2(8),
6 c5 varchar2(8),
7 c6 varchar2(8),
8 c7 varchar2(8)
9 )
10 ORGANIZATION EXTERNAL
11 ( TYPE ORACLE_LOADER
12 DEFAULT DIRECTORY temp
13 ACCESS PARAMETERS
14 ( RECORDS DELIMITED BY NEWLINE
15 BADFILE temp: 'TEST_FILE.bad'
16 LOGFILE temp: 'TEST_FILE.log'
17 FIELDS TERMINATED BY X'20A7' LDRTRIM
18 REJECT ROWS WITH ALL NULL FIELDS
19 (
20 c1,c2,c3,c4,c5,c6,c7
21 ) )
22 LOCATION (temp:'TEST_FILE.dat')
23 )
24 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from et;
C1 C2 C3 C4 C5 C6 C7
---------------- -------- -------- -------- -------- -------- --------
31234569999999 0 A X 0 GGGG
31234569999999 0 A X 0 Z GGGG
贾越
让我试着在我自己的环境中重现你的问题
在Red Hat Linux 7.2上使用Oracle 19c
SQL> select version from v$instance ;
VERSION
-----------------
19.0.0.0.0
演示
更新：分隔符为选项卡
文件的内容
$ cat -A TEST.dat
NAME1^I0^I ^IUK$
NAME2^I0^I ^IUS$
外部表格
SQL> drop table TEST_EXTERNAL_TABLE ;
Table dropped.
SQL> CREATE TABLE TEST_EXTERNAL_TABLE
2 (
3 AA VARCHAR2 (40 BYTE),
4 BB VARCHAR2 (2 BYTE),
5 CC VARCHAR2 (3 BYTE),
6 DD VARCHAR2 (12 BYTE)
7 )
8 ORGANIZATION EXTERNAL
9 (
10 TYPE ORACLE_LOADER
11 DEFAULT DIRECTORY DIR_TEST
12 ACCESS PARAMETERS (
13 RECORDS DELIMITED BY NEWLINE
14 BADFILE DIR_TEST : 'TEST.bad'
15 LOGFILE DIR_TEST : 'TEST.log'
16 FIELDS TERMINATED BY '\t' NOTRIM
17 REJECT ROWS WITH ALL NULL FIELDS
18 (AA,
19 BB,
20 CC,
21 DD))
22* LOCATION (DIR_TEST:'TEST.dat'))
SQL> /
Table created.
SQL> select * from TEST_EXTERNAL_TABLE ;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
SQL> select dump(cc) from TEST_EXTERNAL_TABLE ;
DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32
在我的例子中，我可以加载，但空格仍保留在字段中，这是NOTRIMvsLDRTRIM的预期行为。
LDRTRIM用于提供与SQL*加载程序修剪功能的兼容性。除以下情况外，与NOTRIM相同：
如果字段不是分隔字段，则将从右侧修剪空格。如果该字段是一个带分隔符的字段，可以选择用指定的括起来，并且特定实例缺少可选的附件，则将从左侧修剪空格。
对LDRTRIM执行相同操作
SQL> drop table TEST_eXTERNAL_TABLE;
Table dropped.
SQL> l
1 CREATE TABLE TEST_EXTERNAL_TABLE
2 (
3 AA VARCHAR2 (40 BYTE),
4 BB VARCHAR2 (2 BYTE),
5 CC VARCHAR2 (3 BYTE),
6 DD VARCHAR2 (12 BYTE)
7 )
8 ORGANIZATION EXTERNAL
9 (
10 TYPE ORACLE_LOADER
11 DEFAULT DIRECTORY DIR_TEST
12 ACCESS PARAMETERS (
13 RECORDS DELIMITED BY NEWLINE
14 BADFILE DIR_TEST : 'TEST.bad'
15 LOGFILE DIR_TEST : 'TEST.log'
16 FIELDS TERMINATED BY '\t' LDRTRIM
17 REJECT ROWS WITH ALL NULL FIELDS
18 (AA,
19 BB,
20 CC,
21 DD))
22* LOCATION (DIR_TEST:'TEST.dat'))
SQL> /
Table created.
SQL> select * from TEST_EXTERNAL_TABLE ;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
SQL> select dump(cc) from TEST_EXTERNAL_TABLE ;
DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32
SQL>
如果使用LTRIM，则无法工作，因为由于字段为空，空格位于右侧。这是默认行为，至少因为12c是它的工作方式，应该是这样。
SQL> drop table TEST_EXTERNAL_TABLE ;
Table dropped.
SQL> CREATE TABLE TEST_EXTERNAL_TABLE
(
AA VARCHAR2 (40 BYTE),
2 3 4 BB VARCHAR2 (2 BYTE),
CC VARCHAR2 (3 BYTE),
5 6 DD VARCHAR2 (12 BYTE)
7 )
8 ORGANIZATION EXTERNAL
(
9 10 TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_TEST
ACCESS PARAMETERS (
11 12 13 RECORDS DELIMITED BY NEWLINE
BADFILE DIR_TEST : 'TEST.bad'
LOGFILE DIR_TEST : 'TEST.log'
14 15 16 FIELDS TERMINATED BY '\t' LTRIM
REJECT ROWS WITH ALL NULL FIELDS
(AA,
BB,
17 18 19 20 CC,
DD))
LOCATION (DIR_TEST:'TEST.dat'))
21 22 23 REJECT LIMIT UNLIMITED;
Table created.
SQL> select * from TEST_EXTERNAL_TABLE ;
no rows selected
现在，由于整个字段中的空格是从右到左处理的，因此RTRIM的工作方式与预期一样。
SQL> drop table TEST_EXTERNAL_TABLE ;
Table dropped.
SQL> CREATE TABLE TEST_EXTERNAL_TABLE
2 (
AA VARCHAR2 (40 BYTE),
3 4 BB VARCHAR2 (2 BYTE),
CC VARCHAR2 (3 BYTE),
DD VARCHAR2 (12 BYTE)
5 6 7 )
ORGANIZATION EXTERNAL
(
8 9 10 TYPE ORACLE_LOADER
11 DEFAULT DIRECTORY DIR_TEST
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
12 13 14 BADFILE DIR_TEST : 'TEST.bad'
LOGFILE DIR_TEST : 'TEST.log'
15 16 FIELDS TERMINATED BY '\t' RTRIM
17 REJECT ROWS WITH ALL NULL FIELDS
18 (AA,
19 BB,
20 CC,
DD))
LOCATION (DIR_TEST:'TEST.dat'))
21 22 23 REJECT LIMIT UNLIMITED;
Table created.
SQL> select * from TEST_EXTERNAL_TABLE ;
AA BB CC DD
---------------------------------------- -- --- ------------
NAME1 0 UK
NAME2 0 US
我的建议是：使用LDRTRIM，或者更好的方法，避免空白，这是一种选择。关于您在11g中的测试，这是一个非常旧的版本，可能是一个bug造成的，尽管我找不到任何解释此行为的报告。
类似资料：
-
我有一个外部分区配置单元表，下面的文件行格式分隔字段以“通过配置单元直接读取数据就可以了，但是当使用Spark的Dataframe API时，分隔符”没有被考虑在内。 创建外部分区表： dataframe.show（）输出：
-
最近，我们将数据库从11g更新为19c。 在新数据库版本中测试应用程序时，我们遇到了一个特定视图的性能问题，该视图工作得非常好，但在19c中会导致性能问题。 在分析计划时，我们看到执行计划发生了巨大变化，这导致了19c中视图的性能非常差。 令人惊讶的是，其他观点的效果很好。 如果你能对这个问题有所了解，那就太好了。 谢谢你，JD
-
外部读取配置命令 可以写到一个文件中，用 Mininet 直接调用。例如脚本文件名为 my_cli_script，则可以执行 mininet> source my_cli_script 或者 # mn --pre my_cli_script
-
我想用mod在屏幕上显示一些东西，为此我想在测试中写下“helloworld”。txt（稍后将是json）并将其保存在某处（？）在我的minecraft文件夹中，所以我不需要重新编译我的mod来获得这些数据； 从我的mod读取此文件并将其显示在屏幕上的正确方法是什么？我正在使用MCreator，只有慢慢开始编码“自定义元素”，你可以称我为java编程的新手 我的想法是： （MCreator生成的代
-
我读了很多问题，但还没有找到答案。 我有两个不同的表WeatherData： @实体公共类WeatherData实现可序列化{ 有人有主意吗？ 谢谢
-
我们在OracleLinux8机器上安装了oracle DB（19c）。当我们用19c db连接我们的服务器时，DB机器的内存开始增长。只要服务器处理负载并执行db操作，内存就会不断增长，几个小时后所有内存都会用完，没有可用存储器。我们有简短的PS/SQL语句和存储过程，它们在执行不同的CRUD、提交、回滚操作时被执行。我们做了一些研究，发现这个命令可以释放内存，但这不起作用。 注意：我们在11g