Oracle Database - How to load a CLOB field with an external table
Table of Contents
1 - 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.
2 - Articles Related
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 ====== ===== 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 ....."|
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':
- the vertical bar comes frome the file
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;
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