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