8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

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.

Related articles.

Multiple CLOB Files

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 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>

Inline CLOB Files

Create a directory object pointing to a suitable filesystem directory.

create or replace directory temp_dir as '/tmp/';

Download the following document and place them on the filesystem in the location pointed to by the directory object. The file contains two rows of CSV data made up of an ID column and a large string, much longer than the 4000 byte VARCHAR2 limit.

Now we have the data, we need an external table to query it. Notice the CLOB_CONTENT is defined as a CLOB in the table definition as a very large CHAR in the column list.

drop table lob_tab;

create table lob_tab (
  id            number,
  clob_content  clob
)
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 ',' optionally enclosed by '"'
    missing field values are null
    (
      id,
      clob_content  char(1000000) 
    )
  )
  location ('inline_clob_test1.txt')
)
parallel 2
reject limit unlimited;

The following query shows the clob data is read correctly, and has a length in excess of the 4000 byte limit.

select id, dbms_lob.getlength(clob_content) as clob_length
from   lob_tab
order by id;

        ID CLOB_LENGTH
---------- -----------
         1        9000
         2        9000

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.