MinhTech.com

Here is a simple way to convert data from IBM Extended Binary Coded Decimal Interchange Code, EBCDIC, format and upload into Oracle.

Analyze EBCDIC File:

Suppose we have a file in EBCDIC format with the following field name, offset, size, and type elements:

field1 0 10 ebcdic string(10)
field2 10 5 packed decimal(5)
field3 15 1 ebcdic string(1)

Next, Create an Oracle Table:

SQL> create table ebcdic_table
(
   field1 varchar2(10)
   field2 number(9)
   field3 varchar2(1)
);

Note the numeric field length. Each byte of a packed decimal contains two digits. So “field2″ is 5 * 2 = 10 = 9 digits plus the symbol. Use 5 if the numeric field is not a packed decimal.

Create SQL Loader Control File:

> vi sqlldr.ctl
LOAD DATA CHARACTERSET WE8EBCDIC500
INFILE '/home/mnguyen/filename' "fix 16"
APPEND INTO TABLE MNGUYEN.EBCDIC_TABLE
(
   FIELD1 position(1:10) char,
   FIELD2 position(11:15) decimal,
   FIELD3 position(16:16) char
)

> sqlldr mnguyen control=sqlldr.ctl log=sqlldr.log

It is super easy because Oracle handles the conversion via its built-in SQL Loader tool. Use the WE8EBCDIC500 character set and specify the correct record length (i.e. “fix #”) if an ASCII newline character is not used in the EBCDIC data. Use the “decimal external” data type if the numeric field is not a packed decimal.