Skip to Main Content
  • Questions
  • Oracle External Table - Field terminated by clause

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Gary.

Asked: May 09, 2017 - 6:10 pm UTC

Last updated: April 06, 2023 - 7:18 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I have a flat file with the following record format:

2017-05-01 17:12:28ÿAuth_Referralÿ71631ÿ6803448.70

Notice the field sep is a small y with 2 dots above it. This is windows extended ascii for FF, or 255. I'm unable to designate that character as my field terminator as follows, but if I use a normal delimiter, like a "|", it works fine:

This works fine:

(CREATE_DATE DATE,
 TABLE_NAME VARCHAR2(30),
 RECORD_COUNT VARCHAR2(10),
 TOTAL_AMOUNT NUMBER(10,2))           

 ORGANIZATION external
(TYPE oracle_loader

  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE 
  BADFILE 'authsmry.bad'
 LOGFILE 'authsmry.log'
 READSIZE 1048576
 FIELDS TERMINATED BY '|' LRTRIM
 REJECT ROWS WITH ALL NULL FIELDS

  (CREATE_DATE CHAR(19) date_format DATE 'yyyy-mm-dd-HH24.MI.SS',
   TABLE_NAME CHAR(30),
   RECORD_COUNT CHAR(10),
   TOTAL_AMOUNT CHAR(13))
  )
  location
  ('authsmry') 
)REJECT LIMIT UNLIMITED;


But when I try FIELDS TERMINATED BY 'ÿ'
or FIELDS TERMINATED BY 0X'FF'
or FIELDS TERMINATED BY 0X'255'

etc. it won't recognize the field terminator.

I have looked all over for a solution to this problem including in your database, but no joy.

Please help,

Regards,

Gary


and Chris said...

The character ÿ has different encodings depending on the character set!

https://www.compart.com/en/unicode/U+00FF

Explicitly specify the character set in the access parameters and it should work:

create table t (
 CREATE_DATE varchar2(19),
 TABLE_NAME VARCHAR2(30),
 RECORD_COUNT VARCHAR2(10),
 TOTAL_AMOUNT NUMBER(10,2)
) ORGANIZATION external
(TYPE oracle_loader
  DEFAULT DIRECTORY tmp
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    CHARACTERSET 'UTF8'
    FIELDS TERMINATED BY 'ÿ' LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS (
      CREATE_DATE CHAR(19) ,
      TABLE_NAME CHAR(30),
      RECORD_COUNT CHAR(10),
      TOTAL_AMOUNT CHAR(13)
    )
 )
  location ('test.txt') 
) REJECT LIMIT UNLIMITED;

select * from t;

CREATE_DATE          TABLE_NAME     RECORD_COUNT  TOTAL_AMOUNT  
2017-05-01 17:12:28  Auth_Referral  71631         6,803,448.7  


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SAME ISSSU

A reader, December 15, 2021 - 10:31 am UTC


Chris Saxon
December 15, 2021 - 11:25 am UTC

Do you have a question?

External Table delimited by omega greek letter

Pablo R., April 04, 2023 - 10:50 pm UTC

Trying to create an external table with omega as a delimiter doesn't work.

create table t (
CREATE_DATE varchar2(19),
TABLE_NAME VARCHAR2(30),
RECORD_COUNT VARCHAR2(10),
TOTAL_AMOUNT NUMBER(10,2)
) ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET 'UTF8'
FIELDS TERMINATED BY 'Ω' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS (
CREATE_DATE CHAR(19) ,
TABLE_NAME CHAR(30),
RECORD_COUNT CHAR(10),
TOTAL_AMOUNT CHAR(13)
)
)
location ('test.txt')
) REJECT LIMIT UNLIMITED;

after table was created I checked the ddl

select dbms_metadata.get_ddl('TABLE','T') from dual


DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

CREATE TABLE "ROP"."T"
( "CREATE_DATE" VARCHAR2(19),
"TABLE_NAME" VARCHAR2(30),
"RECORD_COUNT" VARCHAR2(10),
"TOTAL_AMOUNT" NUMBER(10,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "UPBC_DATA_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
CHARACTERSET 'UTF8'
FIELDS TERMINATED BY '¿' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS (
CREATE_DATE CHAR(19) ,
TABLE_NAME CHAR(30),
RECORD_COUNT CHAR(10),
TOTAL_AMOUNT CHAR(13)
)
)
LOCATION
( 'test.txt'
)
)
REJECT LIMIT UNLIMITED


1 row selected.


Connor McDonald
April 06, 2023 - 7:18 am UTC

That looks like a characterset issue when you *created* the table. For example, I just created it and then for the DDL back (formatted slightly)

CREATE TABLE "ASKTOM"."TMP_EXT" ( "CREATE_DATE" VARCHAR2(19) COLLATE "USING_NLS_COMP", "TABLE_NAME" VARCHAR2(30) COLLATE "USING_NLS_COMP", "RECORD_COUNT" VARCHAR2(10) COLLATE "USING_NLS_COMP", "TOTAL_AMOUNT" NUMBER(10,2) ) DEFAULT COLLATION "USING_NLS_COMP" ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "TEMP" ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET 'UTF8'
FIELDS TERMINATED BY 'Ω' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
( CREATE_DATE CHAR(19) , TABLE_NAME CHAR(30), RECORD_COUNT CHAR(10), TOTAL_AMOUNT CHAR(13) ) ) LOCATION ( 'test.txt' ) ) REJECT LIMIT UNLIMITED

so you can see the omega was preserved.

My suspicion is that when you sent the DDL to the database, the character was deemed invalid via your client characterset hence the familiar upsidedown question mark.

Thus your table won't work and DBMS_METADATA reports back that we are no longer seeing the omega. Make sure your charactersets are in alignment and support the character.

(For example, this is why I did my demo in APEX not my usual SQLPlus on Windows because the windows console isn't a fan of exotic characters :-))