External Tables Containing LOB Data

This articles shows how external tables are used to read CLOB and BLOB data, enabling parallel load operations of large quanities of data.

Create a directory object pointing to a suitable filesystem directory.

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

Download the following documents and place them on the filesystem in the location pointed to by the directory object.

The first two documents are plain text files, while the second two are Microsoft Word documents. The documents contain the CLOB and BLOB data to be read by the external table. If you prefer you can create your own documents, but make sure the names are reflected in the datafile below.

The lob_test_data.txt file contains regular data and references to the files holding the CLOB and BLOB data.

1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc
2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc

Now we have all the data, we need an external table to pull it all together.

DROP TABLE lob_tab;

CREATE TABLE lob_tab (
  number_content    NUMBER(10),
  varchar2_content  VARCHAR2(100),
  date_content      DATE,
  clob_content      CLOB,
  blob_content      BLOB
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY temp_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE temp_dir:'lob_tab_%a_%p.bad'
    LOGFILE temp_dir:'lob_tab_%a_%p.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      number_content    CHAR(10),
      varchar2_content  CHAR(100),
      date_content      CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      clob_filename     CHAR(100),
      blob_filename     CHAR(100) 
    )
    COLUMN TRANSFORMS (clob_content FROM LOBFILE (clob_filename) FROM (temp_dir) CLOB,
                       blob_content FROM LOBFILE (blob_filename) FROM (temp_dir) BLOB)
  )
  LOCATION ('lob_test_data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/

There are a couple of things to note in this external table definition:

The field names of the LOB data (clob_filename, blob_filename) do not match the column names (clob_content, blob_content). This emphasizes these fields in the datafile do not contain the actual data, just filenames where the data can be found.
The COLUMN TRANSFORMS clause explains how the CLOB and BLOB data should be loaded. In this case we are using a field from the datafile to identify the filename and a constant for the directory name. To load from multiple directories use an additional field in the datafile to identify directory object.

The following query shows the external table is correctly referencing both the regular data and the LOB data.

COLUMN varchar2_content FORMAT A16
COLUMN date_content FORMAT A12
COLUMN clob_content FORMAT A20

SELECT number_content,
       varchar2_content,
       TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,
       clob_content,
       DBMS_LOB.getlength(blob_content) AS blob_length
FROM   lob_tab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT         BLOB_LENGTH
-------------- ---------------- ------------ -------------------- -----------
             1 one              01-JAN-2006  This is a clob test1       24064
             2 two              02-JAN-2006  This is a clob test2       24064

2 rows selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.