load text file into CLOB column

The problem to solve

When you need to load a large text file into a clob column, or you need to load many files, and the files are on the client host, there is no simple insert or update statement you can write to accomplish the task.

To programmatically load text files, I created a python script that generates sql script from text file. Once the sql script is generated, I can run the script from sqlplus to insert or update the CLOB column. You can download the script from this link,

load_clob_from_file

How the program works

The script creates a LOB object, opens the object, reads the text file line by line, converts each line into a procedure call that appends the line into the LOB object, close the object, and updates or inserts the object into a table.

The output of the script is a sql file that can be run any time.

A demo

Here is a text file for demo purposes,

<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
</catalog>

To generate the sql script, I will run the command

load_clob_from_file.py demo.xml > demo.sql

The output of the script is shown here. You will need to replace the insert or update statement with the actual table and column name.

SPO load_xml.log
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
DECLARE
  l_text clob;
  l_line varchar2(32000);
BEGIN
DBMS_LOB.CREATETEMPORARY(l_text, TRUE);
DBMS_LOB.OPEN(l_text, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITEAPPEND(l_text, 22, q'~<?xml version="1.0"?>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 10, q'~<catalog>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 21, q'~   <book id="bk101">~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 30, q'~      <genre>Computer</genre>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 27, q'~      <price>44.95</price>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 46, q'~      <publish_date>2000-10-01</publish_date>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 11, q'~   </book>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 11, q'~</catalog>~'|| CHR(10));
DBMS_LOB.CLOSE(l_text);
-- insert into sales.customer values (4, 'test', sysdate, l_text);
-- update sales.customer set bio = l_text where customer_id = 31;
commit;

END;
/
SPOOL OFF