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.
You may find the CLOB in two forms:
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 ....."|
If you pay a little bit attention :
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;
SQL> SELECT id,
2 DBMS_LOB.getlength(TEXT) AS blob_length
3 FROM EXT_TABLE_CLOB;
ID BLOB_LENGTH
---------- -----------
1 6572