Oracle Database - How to load a CLOB field with an external table

About

A CLOB (or character large object) is a Oracle datatype that can contain single-byte or multibyte characters with a maximum size of (4 gigabytes - 1) * (database block size), then more than the varchar2 with this maximum of 4000 bytes.

If we want to load the content of the external table wiki page in the database, we will reach easily the limit of 4000 characters that as a varchar2. We need then to use the CLOB datatype.

Articles Related

Possibilities

You may find the CLOB in two forms:

embedded in the data source flat file
of in a separate flat file (the common way for LOB).

The CLOB embedded in the flat file data source

The data source

The first line are the header of the table and the second line contains the id and a big string (more than 4000 charcaters). All fields are enclosed by double quote and the records are delimited by a vertical bar.

"Id","Text"|
"1","====== External Tables ======

===== About =====
External tables feature lets you access data in external sources as if it were in a table in the database. 

The external tables feature is a complement to existing [[sql_loader:sql_loader|SQL*Loader]] functionality. External 
tables can be written to using the ORACLE_DATAPUMP access driver. Neither data manipulation language (DML) 
operations nor index creation are allowed on an external table. Therefore, SQL*Loader may be the better choice
in data loading situations that require additional indexing of the staging table.

===== Example =====

<code sql>
CREATE TABLE dept_external (
   deptno     NUMBER(6),
   dname      VARCHAR2(20),
   loc        VARCHAR2(25) 
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader ....."|

The script

If you pay a little bit attention :

the TEXT field has a CLOB datatype
and in the access parameter as SQL LOADER don't know this datatype, the TEXT field as a CHAR with a buffer of 10000 byte.
the records are delimited by '|\n':
- the vertical bar comes frome the file
- and the \n from the unix line separator system. If your database is on a other system, you may have to change it.

CREATE TABLE "STG"."EXT_TABLE_CLOB"
  (
    "ID" NUMBER(*,0),
    "TEXT" CLOB
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY "MY_DEFAULT_DIRECTORY" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '|\n' 
          CHARACTERSET WE8MSWIN1252 
          STRING SIZES ARE IN BYTES 
          BADFILE LOG_LOC:' EXT_TABLE_CLOB.bad' 
          NODISCARDFILE 
          LOGFILE LOG_LOC:' EXT_TABLE_CLOB.log' 
          SKIP 1 
          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"' 
          NOTRIM 
          ( 
            "ID" CHAR, 
            "TEXT" CHAR(10000) 
          ) 
    ) 
    LOCATION ( "MY_LOCATION":' CLOB.csv' )
  )
  REJECT LIMIT UNLIMITED;

The result

SQL> SELECT id,
  2         DBMS_LOB.getlength(TEXT) AS blob_length
  3  FROM   EXT_TABLE_CLOB;

        ID BLOB_LENGTH
---------- -----------
         1        6572

The CLOB in a separate file

See External Tables Containing LOB Data

Documentation / Reference

External Tables Containing LOB Data