MinhTech.com

Yet another technology tutorial blog.

Load EBCDIC in Oracle SQLLDR

By • Jan 15th, 2009 • Category: Featured Tutorial, Oracle

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.

is a technology junkie.
Email this author | All posts by

One Response »

  1. Hello,

    I have a problem with a file coming from MVS. In this file, there is a numeric signed field. It’s not packed.

    i like know the method to transfer and to load this file with SQL LOADER ORACLE on AIX.

    Example, description of a file with one field on MVS:
    F0 F0 F0 F0 F0 F0 F1 D5
    What is the mode to use for the transfert MVS/AIX?

    Thank you very much for your reponse.

Leave a Reply