Field Delimiter in External Table

[Jr]

Hi ,

I got a text file with delimiter description as x'6a' .
What would be the exact syntax in the External Table Configuration parameter.
i tired with
.... Fields terminated by "x'6a'"
.... Fields terminated by "x''6a''"
.... Fields terminated by "0x'6a'"
.... Fields terminated by "0x''6a''"

Table gets created with no errors, but errors generate during data selection,cause, field terminator is not matched with above defined.

Following is the external table defination
------------------------ def start here -----------
drop table mci
/
create table mci ( allcol varchar2(13),
col1 varchar2(100),
col2 varchar2(100),
col3 varchar2(100),
col4 varchar2(100),
col5 varchar2(100),
col6 varchar2(100),
col7 varchar2(100),
col8 varchar2(100),
col9 varchar2(100),
col10 varchar2(100),
col11 varchar2(100),
col12 varchar2(100),
col13 varchar2(100),
col14 varchar2(100),
col15 varchar2(100),
col16 varchar2(100)
)
organization external
( type oracle_loader
default directory F_test
access parameters
(
RECORDS DELIMITED BY NEWLINE fields terminated by "0x''6a''" (
allcol char(13),
col1 char(100),
col2 char(100),
col3 char(100),
col4 char(100),
col5 char(100),
col6 char(100),
col7 char(100),
col8 char(100),
col9 char(100),
col10 char(100),
col11 char(100),
col12 char(100),
col13 char(100),
col14 char(100),
col15 char(100),
col16 char(100)
)
)
location ( 'ia03.txt' )
)
/

------------------------ def end here --------------
I appreciate your time and suggestion .

Thanks
Jr.

[gandolf989]

Have you thought about doing a find replace on the file and changing the delimeter to ~ (tilde)? If you are on a unix system sed and or awk might work, they aren't my strong suit. You may want to change to a simpler delimeter going forward.

[Jr]

Thanks for the reply Gandolf, unfortunately the datafile is 20+GB,so i cant do that.

Guess what,
simple copy and paste from the datafile to control file worked.
Jr.