Oracle External Tables by Examples part 4 - column_transforms Clause (load CLOB BLOB or any constant using external tables)

Starting Oracle 10g you can load any CLOB/BLOB (any file including text, xml, jpeg, doc, pdf, …. whatever format you like) which resides in separate file(s) to Oracle using external tables and no longer need SQL*Plus (unless you plan to load data over network). It is done using very interesting column_transforms clause.

let’s assume you have customer.dat file which looks like this

CUSTOMER_ID,CUSTOMER_ACQUIRE_DT, CUSTOMER_FILE_NM
123456,12/23/2001,jiri_smith.txt
234567,01/17/1999,scott_walker.txt
345678,05/16/2009,jim_brown.txt

the file includes next to customer_id also a date when was the customer acquired and also a file name which includes more detail information. In my example above it means that I have next to customer.dat file also jiri_smith.txt file, scott_walker.txt and jim_brown.txt file.

Here is the goal:

1. use external tables to load customer.dat file as well as all txt files
2. load txt files into CLOB field (you can load it to BLOB using the same technique)
3. present date as true date (correctly parse mask
4. add additional static column which defines what vendor sent the file
5. add windows drive name http://myserver/ to customer_file_nm so users know where the text file reside (this is kinda silly requirement since we load it to CLOB, but still …).

First let’s define directories, this is very straightforward. Notice I define separate directory for customer.dat and individual txt files (this can of course be one directory).

CREATE OR REPLACE DIRECTORY "DIR_USERS_JIRI_DATA" AS '/work/users/jiri/data';
CREATE OR REPLACE DIRECTORY "DIR_USERS_JIRI_FILES" AS '/work/users/jiri/files';
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, WRITE ON DIRECTORY DIR_USERS_JIRI_FILES TO JIRI;
GRANT READ ON DIRECTORY DIR_USERS_JIRI_LOG TO JIRI;
GRANT READ ON DIRECTORY DIR_USERS_JIRI_BAD TO JIRI;

The next step is to define external table (yes, all steps 1-5 can be done using one single DDL)

CREATE TABLE customer_ext
(
 CUSTOMER_ID         number,
 CUSTOMER_ACQUIRE_DT date,
 SERVICER_NM         varchar2(60),
 FILE_NM             varchar2(120),
 CUSTOMER_PROFILE    clob
)
organization external
(
 type oracle_loader
 default directory dir_sample_data
 access parameters
 (
 records delimited by NEWLINE
 skip 1
 logfile dir_sample_log:'customer.log'
 badfile dir_sample_bad:'customer.bad'
 nodiscardfile
 fields terminated by ','
 missing field values are null
 (
 CUSTOMER_ID          CHAR(10),
 CUSTOMER_ACQUIRE_DT  CHAR(11) DATE_FORMAT DATE MASK "MM/DD/YYYY",
 CUSTOMER_FILE_NM     CHAR(60)
 )
 column transforms
  (
   SERVICER_NM      FROM CONSTANT 'Web Data Delivery Inc.',
   FILE_NM          FROM CONCAT  (CONSTANT 'http://myserver/', CUSTOMER_FILE_NM),
   CUSTOMER_PROFILE FROM LOBFILE (CUSTOMER_FILE_NM) FROM (dir_sample_files)
  )
 )
 location (dir_sample_data:'customer.dat')
)
reject limit 0
noparallel
nomonitoring;

it cannot be easier than that

SELECT CUSTOMER_ID,
       to_char(CUSTOMER_ACQUIRE_DT, 'YYYYMMDD') ACQUIRE_DT,
       SERVICER_NM,
       FILE_NM,
       SUBSTR(CUSTOMER_PROFILE, 1, 10) CUSTOMER_PROFILE,
       DBMS_LOB.getlength(CUSTOMER_PROFILE) CLOB_LENGTH
  FROM CUSTOMER_EXT;

CUSTOMER_ID ACQUIRE_DT SERVICER_NM            FILE_NM                           CUSTOMER_PROFILE CLOB_LENGTH
----------------------------------------------------------------------------------------------------
123456     20011223    Web Data Delivery Inc. http://myserver/jiri_smith.txt    dfdfdfdfdf       55
234567     19990117    Web Data Delivery Inc. http://myserver/scott_walker.txt  rrrererere       564
345678     20090516    Web Data Delivery Inc. http://myserver/jim_brown.txt     gretgertgr       444
----------------------------------------------------------------------------------------------------

as you can see, we can do all requirements in one simple external table DDL.

1. we used external table
2. we load LOB using COLUMN_TRANSFORMS LOBFILE clause
3. we set the correct mask for date using DATE MASK
4. we created new SERVICER_NM static column (virtual column) wih static description using COLUMN TRANSFORMS CONSTANT
5.we appended constant to existing field (merged field with static value) using COLUMN TRANSFORMS CONCAT

please note COLUMN TRANSFORMS cannot be used for any other transformation -  CONSTANT, CONCAT and LOBFILE are the only options. I wish there would be more options and chance to add custom functions, but there is currently no such option.

You can find complete documentation here (there are however no real examples on how to use column transforms clause)

About these ads