Loading CLOB data into oracle table using SQL Loader

SQL Loader utility can be used to load data from flat files into oracle tables. This utility also supports loading the content of files into LOB data type columns as well. This utility comes handy whenever you want load huge amount of text data from files into LOB columns. This post shows how to load the content of text file into CLOB column. For demo purpose, let us consider we want to load content of three text files ( "1_SQL_Loader_details.txt", "2_External_table_details.txt" & "3_UTL_File_details.txt" ) into table. So to capture the file name, and its content we might need two columns, to hold name and content respectively.

Here is the table creation script.

To load the data using SQL Loader utility, we need the data file and control file.
"Data file" contains the information on "what" to load, and "Control File" contains the information about "where & how" to load.

Given below the content of data file. (data_file.txt)

1,1_SQL_Loader_details.txt 2,2_External_table_details.txt 3,3_UTL_File_details.txt

Given below the content of control file. (control_file.ctl)

LOAD DATA INFILE 'data_file.txt'    INTO TABLE file_content_tab    FIELDS TERMINATED BY ','    (s_no    ,     file_name    CHAR(100),     file_data    LOBFILE(file_name) TERMINATED BY EOF)

Upon running sql loader utility for above control file, the data from data files would be read, and loaded into the target table specified.
On successful completion of load, all the contents of the file would be loaded into CLOB column.

Here is the vedio with real time example.