Oracle External Tables by Examples part 1 – TAB delimited fields


Little Intro

Let me start with simple personal statement… “I love Oracle External Tables”. Why? Well because they are so simple to use, so quick to create and can be really powerful and fast. If you are still living in 2001 and use SQL*Loader, then you’d better jump to Oracle Documentation and read about external tables. They are as fast as SQL*Loader (let me repeat that – they are as fast as SQL*Loader) and can do almost everything SQL*Loader does (everything except load over network and CLOB load).

I’m really not going to waste anybody’s time and explain basics of external tables nor post samples how to load comma separated file. Oracle documentation did excellent job and explains it all.

I want to share few tips and tricks how to load some specific formats. I figured that it’s better to show examples and let you play than giving you long story about the syntax. All my samples were tested on Oracle 10.2.0.4 on Sun Solaris 10. I will try to make them a bit more universal so even Windows can use it (RECORDS DELIMITED BY NEWLINE statement can be different on each system – I will try to avoid it).

 

 

TAB delimited DOS end-of-line

I will base all the samples on 3 oracle directories which map to three unix (or windows) directories. I always recommend to separate actual data directory from LOG/BAD directories. This is very good security practice – oracle ID can have READ access to data files and READ/WRITE to LOG/BAD files. All my samples will use schema JIRI

CREATE OR REPLACE DIRECTORY "DIR_USERS_JIRI_DATA" AS '/work/users/jiri/data';
CREATE OR REPLACE DIRECTORY "DIR_USERS_JIRI_LOG" AS '/work/users/jiri/log';
CREATE OR REPLACE DIRECTORY "DIR_USERS_JIRI_BAD" AS '/work/users/jiri/bad';

GRANT READ, WRITE ON DIRECTORY DIR_USERS_JIRI_DATA TO JIRI;
GRANT READ ON DIRECTORY DIR_USERS_JIRI_LOG TO JIRI;
GRANT READ ON DIRECTORY DIR_USERS_JIRI_BAD TO JIRI;

 

OK , now you are a bit confused. What the heck is this format about? Well… most of use work with this every single day and don’t even realize it. Yup just copy-paste few cells from Excel to Notepad. Bingo !!

I don’t think any serious data ETL is using this format (I hope not), but there are situations where I found it useful – some of web based apps might use copy-paste functionality from excel to web form, not so advanced users might find it easier to copy-paste data from excel to notepad than save-as CSV (just don’t ask me why).

dowload source file here

CREATE TABLE JIRI.SAMPLE_01_EXT
(
   PRODUCT         VARCHAR2(60),
   NUMBER_OF_UNITS NUMBER(10,0),
   TOTAL_PRICE     NUMBER(10,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DIR_USERS_JIRI_DATA
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY '\r\n'
    SKIP 1
    LOGFILE DIR_USERS_JIRI_LOG:'sample_01.log'
    BADFILE DIR_USERS_JIRI_BAD:'sample_01.bad'
    NODISCARDFILE
    FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION (DIR_USERS_JIRI_DATA:'sample_01.dat')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;
 

the file is DOS end-of-line (copy-paste from excel to notepad) RECORDS DELIMITED BY ‘\r\n
‘TAB is the field delimiter FIELDS TERMINATED BY ‘\t’

 
it cannot be easier than that

 

 

 here are most common escape sequences

\b Backspace
\t (Horizontal) Tab
\n New Line Feed (LF)
\v Vertical Tab
\f Form Feed
\r Carriage Return (CR)
\\ Back slash
\' Single quote
\" Double quote
About these ads
Tagged , ,

One thought on “Oracle External Tables by Examples part 1 – TAB delimited fields

  1. [...] What external tables or sql loader for better can do actually is reading line oriented, file based data in bulk and quite fast. It offers a lot of settings to parametrize the loading process, the reader, according to your input format. You may for example have a look at the excellent series of posts discussing this options by Jiri starting here Oracle External Tables by Examples part 1 – TAB delimited fields. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: