Odi's astoundingly incomplete notes

External tables in Oracle

Oracle can use plain-text files and provide them to the DB engine like read-only tables. That's great for master data imports.
The syntax to declare those tables is borrowed from SQLLoader. SQLLoader as we know is very powerful. It can read practically any type of CSV-like file. The most common problems with plain text files are usually:

You don't know the character encoding
Platform specific line endings

People tend to make considerable efforts to convert both character encodings and line endings when they process flat files. Of course that's easy on Linux with the iconv and dos2unix/unix2dos tools.

With external tables (or SQLLoader) you can leave this job to Oracle - and it's even damn good at it. Oracle knows practically every oh so weird character encoding used on this planet. And it can also deal with explicit or platform specific line endings if you tell it. An external table definition looks like this:

CREATE TABLE EXT_MYTABLE
(
  F1  VARCHAR2(100 BYTE),
  F2  VARCHAR2(100 BYTE),
  F3  VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXT_MYTABLES
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY '\r\n' CHARACTERSET AL32UTF8
	BADFILE     'mytable.bad'
	DISCARDFILE 'mytable.dis'
	LOGFILE     'mytable.log'
	FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
	MISSING FIELD VALUES ARE NULL
     )
     LOCATION (EXT_MYTABLES:'mytable.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

The important part is the RECORDS DELIMITED BY and CHARACTERSET parameters. Just specify it correctly and the data will appear correctly in the DB. If you are not sure about the character encoding you may want to use the Linux file command. Used with a representative file it can guess the encoding using sophisticated heuristics:

bash-3.2$ file my.csv
my.csv: ISO-8859 text

Similarly you can determine the line endings with hexdump (0D is \r, 0A is \n):

bash-3.2$ hexdump -C my.csv| head
00000000  4d fc 6e 63 68 65 6e 3b  31 30 30 3b 34 37 0a 42
00000010  e4 72 6e 3b 32 38 32 3b  32 39 0a

posted on 2007-02-16 10:50 UTC in Code | 0 comments | permalink

Add comment