My environment: Oracle 12.2 on Red Hat version 7 ( database characterset AL32UTF8 )
I am having a huge issue dealing with an external table which points to a file in EBCDIC format coming from an AS400 system. I could figure out finally the length of the record and the position of each field. My problem now is that some fields are packets in COMP-3 format and I am struggling with the error KUP-03003.
Let me show you what happens. I load them all as varchar2 columns because later on I have a process to transform each field to the right data type format for the final staging table that is going to be used in the batch process.
1.This is the external table definition
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.Then I create the table
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.
What you see above is the definition I have of the same table in a SAS program that treats the same file without issues. Now, if I try to read the first field in the table, I got an error for the last field in the table.
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
Obviously, if I put all the fields to CHAR, then all the COMP-3 decimal packets are not loaded. What I did test was to verify which fields were the problem by putting all the fields in char, then starting to change one by one. What I can't understand is why the lengths are not matching with the specification for those fields and the position they occupy.
Let me show you an example. I create the table only with the right format in the first 4 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) 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
After creating the table with this format, I got
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
However, If I change the definition to ( only the four first fields )
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) ,
Then the query works until the fourth field.
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.
As I have no way to read the file to see whether the positions are matching the specification. I have some questions:
- Why if I put just
DECIMALwithout specifying precision or scale, the load does not work and I got the same KUP error ? - Do I have to do this for each field ? I am struggling because there are tables with more than 100 columns.
- Is there a better way to read packet decimal fields from external tables with EBCDIC codepage ? Or any utility that can convert this file to ascii that I can check the positions.
- Why the supposed length that works in other systems is not valid for the decimal data type ?
- I guess I am using the right characterset for the file ( WE8EBCDIC500 ). Do I ?
Hope you can help Thank you in advance.
