8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 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>
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:
- External Tables : Querying Data From Flat Files in Oracle
- External Tables : Querying Data From Flat Files in Oracle
- External Tables : All Articles
- External Table Enhancements in Oracle Database 12c Release 1 (12.1)
- The ORACLE_LOADER Access Driver
- Loading LOB Data Using SQL*Loader
Hope this helps. Regards Tim...