Skip to Main Content

How can we load data into CLOB datatype column using SQL Loader?

NirvanFeb 5 2021

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

This post has been answered by Solomon Yakobson on Feb 5 2021
Jump to Answer
Comments
Post Details
Added on Feb 5 2021
8 comments
4,823 views