Oracle Illustrated

Feb 21 2010   9:13AM GMT

Read Hexa-decimal file via External table



Posted by: Lakshmi Venkatesh
Tags:

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: