oracle - 使用二进制格式的 EBCDIC 文件的外部表 Oracle - 错误 KUP-03003
问题描述
我的环境：Red Hat 版本 7 上的 Oracle 12.2（数据库字符集 AL32UTF8）
我在处理指向来自 AS400 系统的 EBCDIC 格式文件的外部表时遇到了一个巨大的问题。我终于可以弄清楚记录的长度和每个字段的位置。我现在的问题是某些字段是 COMP-3 格式的数据包，我正在努力解决错误 KUP-03003。
让我告诉你会发生什么。我将它们全部加载为 varchar2 列，因为稍后我有一个过程将每个字段转换为将在批处理过程中使用的最终临时表的正确数据类型格式。
1.这是外部表定义
DROP TABLE CPL_HIST.EXT_L027_AS_RPLMREP purge;
CREATE TABLE CPL_HIST.EXT_L027_AS_RPLMREP
(
LMZMDT VARCHAR2(4000 CHAR),
LMORNB VARCHAR2(4000 CHAR),
LMH6NE VARCHAR2(4000 CHAR),
LMG9VA VARCHAR2(4000 CHAR),
LMHAVA VARCHAR2(4000 CHAR),
LMA8PS VARCHAR2(4000 char),
LMALEU VARCHAR2(4000 char),
LMHBVA VARCHAR2(4000 char),
LMHCVA VARCHAR2(4000 char),
LMIHNE VARCHAR2(4000 char),
LMHDVA VARCHAR2(4000 char),
LMA9PS VARCHAR2(4000 char),
LMBAPS VARCHAR2(4000 char),
LMO1TT VARCHAR2(4000 char),
LMAMEU VARCHAR2(4000 char),
LMHEVA VARCHAR2(4000 char),
LMFINB VARCHAR2(4000 char),
LMECNE VARCHAR2(4000 char),
LMYXNF VARCHAR2(4000 char),
LMD5EU VARCHAR2(4000 char),
LMSTVA VARCHAR2(4000 char),
LMSUVA VARCHAR2(4000 char),
LMQECE VARCHAR2(4000 char),
LMU4VA VARCHAR2(4000 char),
LMU5VA VARCHAR2(4000 char),
LMDVDT VARCHAR2(4000 char),
LMDWDT VARCHAR2(4000 char),
LMAJTM VARCHAR2(4000 char),
LMLPXT VARCHAR2(4000 char),
LMLQXT VARCHAR2(4000 char),
LMNUST VARCHAR2(4000 char)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY DIR_CPL_EXT
ACCESS PARAMETERS
(
RECORDS FIXED 176 CHARACTERSET WE8EBCDIC500
BADFILE 'EXT_L027_AS_RPLMREP.bad'
DISCARDFILE 'EXT_L027_AS_RPLMREP.dsc'
LOGFILE 'EXT_L027_AS_RPLMREP.log'
READSIZE 1048576
FIELDS LTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
LMZMDT (1:3) CHAR ,
LMORNB (4:9) DECIMAL(10,0) ,
LMH6NE (10:11) DECIMAL(2,0) ,
LMG9VA (12:18) DECIMAL(7,2) ,
LMHAVA (19:25) DECIMAL(7,2) ,
LMA8PS (26:28) DECIMAL(3,2) ,
LMALEU (29:38) CHAR ,
LMHBVA (39:45) DECIMAL(7,2) ,
LMHCVA (46:52) DECIMAL(7,2) ,
LMIHNE (53:53) DECIMAL(1,0) ,
LMHDVA (54:60) DECIMAL(7,2) ,
LMA9PS (61:63) DECIMAL(3,2) ,
LMBAPS (64:66) DECIMAL(3,2) ,
LMO1TT (67:76) CHAR ,
LMAMEU (77:86) CHAR ,
LMHEVA (87:93) DECIMAL(7,2) ,
LMFINB (94:95) DECIMAL(2,0) ,
LMECNE (96:100) DECIMAL(5,0) ,
LMYXNF (101:105) DECIMAL(5,0) ,
LMD5EU (106:115) CHAR ,
LMSTVA (116:122) DECIMAL(7,2) ,
LMSUVA (123:129) DECIMAL(7,2) ,
LMQECE (130:131) CHAR ,
LMU4VA (132:134) DECIMAL(3,3) ,
LMU5VA (135:142) DECIMAL(8,2) ,
LMDVDT (143:146) DECIMAL(4,0) ,
LMDWDT (147:150) DECIMAL(4,0) ,
LMAJTM (151:154) DECIMAL(4,0) ,
LMLPXT (155:164) CHAR ,
LMLQXT (165:174) CHAR ,
LMNUST (175:176) CHAR
)
)
location
(
'RPLMREP.bin'
)
)
REJECT LIMIT 0
/
2.然后我创建表
SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 11 13:13:03 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Table dropped.
Table created.
您在上面看到的是我在 SAS 程序中对同一个表的定义，该程序可以毫无问题地处理同一个文件。现在，如果我尝试读取表中的第一个字段，则会收到表中最后一个字段的错误。
SQL> select LMZMDT from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10 ;
select LMZMDT from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-03003: absolute end(154) - start(151) != fieldlength (3) for field LMAJTM
显然，如果我把所有的字段都放到 CHAR 中，那么所有的 COMP-3 十进制数据包都不会被加载。我所做的测试是通过将所有字段都放在char中来验证哪些字段是问题，然后开始一一更改。我无法理解的是为什么长度与这些字段的规范及其占据的位置不匹配。
让我给你看一个例子。我只在前 4 个字段中使用正确的格式创建表格
LMZMDT (1:3) CHAR ,
LMORNB (4:9) DECIMAL(10,0) ,
LMH6NE (10:11) DECIMAL(2,0) ,
LMG9VA (12:18) DECIMAL(7,2) ,
LMHAVA (19:25) DECIMAL(7,2) ,
LMA8PS (26:28) CHAR ,
LMALEU (29:38) CHAR ,
LMHBVA (39:45) CHAR ,
LMHCVA (46:52) CHAR ,
LMIHNE (53:53) CHAR ,
LMHDVA (54:60) CHAR ,
LMA9PS (61:63) CHAR ,
LMBAPS (64:66) CHAR ,
LMO1TT (67:76) CHAR ,
LMAMEU (77:86) CHAR ,
LMHEVA (87:93) CHAR ,
LMFINB (94:95) CHAR ,
LMECNE (96:100) CHAR ,
LMYXNF (101:105) CHAR ,
LMD5EU (106:115) CHAR ,
LMSTVA (116:122) CHAR ,
LMSUVA (123:129) CHAR ,
LMQECE (130:131) CHAR ,
LMU4VA (132:134) CHAR ,
LMU5VA (135:142) CHAR ,
LMDVDT (143:146) CHAR ,
LMDWDT (147:150) CHAR ,
LMAJTM (151:154) CHAR ,
LMLPXT (155:164) CHAR ,
LMLQXT (165:174) CHAR ,
LMNUST (175:176) CHAR
用这种格式创建表格后，我得到了
SQL> select LMZMDT from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10 ;
select LMZMDT from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-03003: absolute end(25) - start(19) != fieldlength (4) for field LMHAVA
但是，如果我将定义更改为（仅前四个字段）
LMZMDT (1:3) CHAR ,
LMORNB (4:9) DECIMAL(10,0) ,
LMH6NE (10:11) DECIMAL(2,0) ,
LMG9VA (12:18) DECIMAL(10,2) ,
LMHAVA (19:25) DECIMAL(10,2) ,
然后查询一直工作到第四个字段。
SQL> select LMZMDT from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10 ;
LMZMDT
--------------------------------------------------------------------------------
AKB
AKB
AKB
AKB
AKB
AKB
AKB
AKB
AKB
9 rows selected.
SQL> select LMORNB from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10 ;
LMORNB
--------------------------------------------------------------------------------
1020019630
1020083310
1020087030
1020120440
1020121681
1020127470
1020147350
1020169610
1020223080
SQL> select LMHAVA from cpl_hist.EXT_L027_AS_RPLMREP a where a.LMZMDT is not null and rownum < 10 ;
LMHAVA
--------------------------------------------------------------------------------
.00
.00
.00
.00
.00
.00
.00
.00
.00
9 rows selected.
因为我无法读取文件以查看位置是否符合规范。我有一些问题：
- 为什么如果我只放
DECIMAL不指定精度或比例，负载不起作用并且我得到相同的 KUP 错误？ - 我必须为每个字段执行此操作吗？我很挣扎，因为有超过 100 列的表。
- 有没有更好的方法来使用 EBCDIC 代码页从外部表中读取数据包十进制字段？或者任何可以将此文件转换为我可以检查位置的 ascii 的实用程序。
- 为什么在其他系统中工作的假定长度对十进制数据类型无效？
- 我想我为文件使用了正确的字符集（ WE8EBCDIC500 ）。我吗？
希望你能帮助提前谢谢你。
解决方案
原因是 ECBDIC 中的压缩小数通常包括无法加载到表中的小数列中的字符，例如“0C”。
唯一的建议是首先将您的 EBCDIC 文件转换为 UTF-8 格式。
看这个：
这是 iconv 支持的 EBCDIC 代码列表：
IBM-037 欧洲 IBM-1025 西里尔文 IBM-1026 拉丁文 5（土耳其） IBM-1027 日本拉丁文 IBM-1047 开放系统 IBM-1112 波罗的海 IBM-1122 爱沙尼亚 IBM-1140 芬兰、瑞典 IBM-1141 奥地利、德国 IBM-1142 丹麦, 挪威 IBM-1143 美国 IBM-1144 意大利 IBM-1145 西班牙，讲西班牙语的拉丁美洲 IBM-1146 英国 IBM-1147 法国 IBM-1148 比利时，瑞士 IBM-1149 冰岛 IBM-1388 中国 IBM-273 德国 IBM-274 比利时 IBM-277 丹麦 - 挪威 IBM-278 瑞典 - 芬兰 IBM-280 意大利 IBM-284 西班牙 - 拉丁美洲 IBM-285 英国 IBM-290 日本片假名 IBM -297 法国 IBM-424 以色列 IBM-500 国际 IBM-838 泰国 IBM-871 冰岛 IBM-875 希腊 IBM-924 IBM500/IBM1047 欧元 IBM-930 日语片假名/汉字多字节 IBM-933 韩国 IBM-935 中国 IBM-937台湾 IBM-939 日本扩展 IBM-970 拉丁语 2 IBM-971 冰岛 IBM-975 希腊韩国 IBM-935 中国 IBM-937 台湾 IBM-939 日本 扩展 IBM-970 拉丁文 2 IBM-971 冰岛 IBM-975 希腊韩国 IBM-935 中国 IBM-937 台湾 IBM-939 日本 扩展 IBM-970 拉丁文 2 IBM-971 冰岛 IBM-975 希腊
试试看。
或者 oracle 函数：
CONVERT(string1, char_set_to [, char_set_from]) 例如：convert(variable, 'UTF8', 'WE8EBCDIC500')
如果您在使用 iconv 时遇到问题，只需使用一个字段创建外部表，然后将任何行的 convert(...) 选择到另一个表中，或者使用 pl/sql 过程填充一个拆分转换后的行的新表。
推荐阅读
- python - 如何从 pandas.read_csv() 的结果中删除 '\x01'？
- google-sheets - 将换行符中的字符串列表转换为 Google 表格中的单独行
- javascript - 嗨，我发现使用 angular akita 在本地运行我的代码有困难
- chart.js - 在向左平移的折线图中加载更多历史数据
- java - 我必须解析 JSON 响应，我必须获取 leet speak 下的子节点（包含符号、字符、数字的随机字符串）
- node.js - 如何在 node.js (IIS) 中获取我的 Web 应用程序的物理路径
- angular - Angular：如何使用导航方法从父组件导航到子路由
- linux - 如何使用介子设置 googletest？
- wordpress - 我在插件的帮助下将 LDAP AD 添加到 WP，但是在 WP 中的 AD 更改后，我仍然有老用户
- python - 如何将网格转换为数据框中的 1 列并使其高效