Read Hexa-decimal file via External table

External table was introduced in Oracle 9i which can be used to read file from ORACLE DIRECTORY. Multiple files can be read at the same time. It can also read hexa-decimal files. The following example demonstrates the use of external table to read from hexa-decimal file.

TEST : External table to read from hexa-decimal delimited with different hexa-decimal values.

FILE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3

create or replace directory ext_dir as 'C:\'

drop table tp_emp_ext;

create table tp_emp_ext
(Emp_name_field varchar2(200),
Emp_name_value varchar2(200),
Dept_id_field varchar2(200),
Dept_id_value varchar2(200))
organization external (
type oracle_loader
default directory ext_dir
access parameters ( records delimited by newline
nodiscardfile
nologfile
fields terminated by 0X'11'
missing field values are null
REJECT ROWS WITH ALL NULL
FIELDS
(Emp_name_field char(200) terminated by 0X'02',
Emp_name_value char(200) terminated by 0X'03',
Dept_id_field char(200) terminated by 0X'02',
Dept_id_value char(200) terminated by 0X'03')
)
location ('samp_text.dat')
)
reject limit unlimited


select * from tp_emp_ext

EMP_NAME_FIELD EMP_NAME_VALUE DEPT_ID_FIELD DEPT_ID_VALUE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3