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

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.

## 3 - 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).

### 3.1 - The CLOB embedded in the flat file data source

#### 3.1.1 - 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 ======

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

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

===== Example =====

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

#### 3.1.2 - 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':
CREATE TABLE "STG"."EXT_TABLE_CLOB"
(
"ID" NUMBER(*,0),
"TEXT" CLOB
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY "MY_DEFAULT_DIRECTORY"
ACCESS PARAMETERS (
RECORDS DELIMITED BY '|\n'
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
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;

#### 3.1.3 - The result

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

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