Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 474 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How can we load data into CLOB datatype column using SQL Loader?

Hi Experts-
We have a tab delimiter file which will be received on timely basis, apart from other column values there is one column which contains huge data which I am trying to load into a clob column, and we are not sure how big the data will be in that column as it is not specified. Currently I am trying to load the data by extending the size of the column in control file every time which may not be good practice. Could you please let me know if there is any other possibility to load the data without specifying the size. I tried searching for a solution everything I found is about lobfile which may not help here as we are not receiving the data in a separately alone for the specific column.
--For example the table structure looks like this.
CREATE TABLE TMP_LOAD_CLOB_DATA
(
CUSTOMER_ID VARCHAR2(255),
FIRSTNAME VARCHAR2(255),
LASTNAME VARCHAR2(255),
CUSTOMER_DETAILS CLOB
);
--Assuming the data will look like shown below.
CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS
123 ABC XYZ Lot Of Information about the customer which should be loaded into clob
--The control file will look like this
OPTIONS (DIRECT=TRUE, MULTITHREADING=TRUE, PARALLEL=TRUE)
load data
infile '/test.txt'
into table TMP_LOAD_CLOB_DATA
APPEND
fields terminated by X'09'
trailing nullcols
(CUSTOMER_ID,
FIRSTNAME,
LASTNAME,
CUSTOMER_DETAILS char(50000))
How can I avoid adding the column size like char(50000) and still load the data into clob column?
Thank you,
Nirvan
Best Answer
-
Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:
LOAD DATA INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT' INTO TABLE TMP_LOAD_CLOB_DATA FIELDS TERMINATED BY X'09' ( CUSTOMER_ID, FIRSTNAME, LASTNAME, CUSTOMER_DETAILS_CLOB_FILE FILLER, CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF )
Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:
123 ABC XYZ C:\TEMP\TMP_LOAD_CLOB1.TXT 456 DEF ZZZ C:\TEMP\TMP_LOAD_CLOB2.TXT
Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:
Lot Of Information about customer ABC XYZ which should be loaded into a clob
Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:
Lot Of Information about customer DEF ZZZ which should be loaded into a clob
Now:
SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / no rows selected SQL> host Microsoft Windows [Version 10.0.17763.1637] (c) 2018 Microsoft Corporation. All rights reserved. I:\>sqlldr [email protected]/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table TMP_LOAD_CLOB_DATA: 2 Rows successfully loaded. Check the log file: TMP_LOAD_CLOB_DATA.log for more information about the load. I:\>exit SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS ----------- --------- -------- ------------------------------ 123 ABC XYZ Lot Of Information about customer ABC XYZ which should be loaded into a clob 456 DEF ZZZ Lot Of Information about customer DEF ZZZ which should be loaded into a clob SQL>
SY.
Answers
-
Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:
LOAD DATA INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT' INTO TABLE TMP_LOAD_CLOB_DATA FIELDS TERMINATED BY X'09' ( CUSTOMER_ID, FIRSTNAME, LASTNAME, CUSTOMER_DETAILS_CLOB_FILE FILLER, CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF )
Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:
123 ABC XYZ C:\TEMP\TMP_LOAD_CLOB1.TXT 456 DEF ZZZ C:\TEMP\TMP_LOAD_CLOB2.TXT
Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:
Lot Of Information about customer ABC XYZ which should be loaded into a clob
Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:
Lot Of Information about customer DEF ZZZ which should be loaded into a clob
Now:
SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / no rows selected SQL> host Microsoft Windows [Version 10.0.17763.1637] (c) 2018 Microsoft Corporation. All rights reserved. I:\>sqlldr [email protected]/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table TMP_LOAD_CLOB_DATA: 2 Rows successfully loaded. Check the log file: TMP_LOAD_CLOB_DATA.log for more information about the load. I:\>exit SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS ----------- --------- -------- ------------------------------ 123 ABC XYZ Lot Of Information about customer ABC XYZ which should be loaded into a clob 456 DEF ZZZ Lot Of Information about customer DEF ZZZ which should be loaded into a clob SQL>
SY.
-
Hi Solomon, Thank you for your response..
-
Hi,
For above is there any way to load CLOB data without having separate files ??
-
To load lobs, use the insert, update, or merge statement.
-
What to do when a large text is part of a single file and needs to be inserted with an SQLLDR.
eg: it is 498,780 characters long.
ID | VALUE | DATA
1 | Dr4wQQ | it is 498,780 characters long......
2 | WQWQ3 | it is 925,342 characters long......
3 | DERRQQ | it is 478,998 characters long......
-
I'll assume CLOB doesn't start with new line. I will also assume, based on your data sample, vertical pipe can't appear in CLOB - otherwise you simply can't use it as field separator. If so, control file TMP_LOAD_CLOB_DATA.CTL:
LOAD DATA INFILE 'C:\MY_TEMP\TMP_LOAD_CLOB_DATA.TXT' "str X'7C0D0A'" INTO TABLE TMP_LOAD_CLOB_DATA FIELDS TERMINATED BY "|" ( ID, VALUE, DATA )
7C is hex ascii code for vertical pipe. 0D0A is new line (CRLF - carriage return, line feed). Now we need to add vertical pipe at the end of each line in data file TMP_LOAD_CLOB_DATA.TXT:
1 | Dr4wQQ | it is 498,780 characters long......| 2 | WQWQ3 | it is 925,342 characters long......| 3 | DERRQQ | it is 478,998 characters long line1 line2 many more lines|
Now we load:
I:\>sqlldr [email protected]/yyy control=c:\my_temp\tmp_load_clob_data.ctl log=c:\my_temp\tmp_load_clob_data.log SQL*Loader: Release 12.2.0.1.0 - Production on Fri Aug 12 06:55:18 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Commit point reached - logical record count 3 Table TMP_LOAD_CLOB_DATA: 3 Rows successfully loaded. Check the log file: c:\my_temp\tmp_load_clob_data.log for more information about the load. I:\>
Now we check what was loaded:
SQL> select * from tmp_load_clob_data; ID VALUE DATA ---------- ---------- ---------------------------------------- 1 Dr4wQQ it is 498,780 characters long...... 2 WQWQ3 it is 925,342 characters long...... 3 DERRQQ it is 478,998 characters long line1 line2 many more lines SQL>
SY.