pl/sql | Notes

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

using varray

set serveroutput ON

DECLARE 
   type aud_int_type IS varray(10) of integer; 
   type aud_str_type IS varray(10) of varchar2(60); 
   audits aud_int_type;
   audit_names aud_str_type;
   total integer;
begin
  audits := aud_int_type(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML);
  
  audit_names := aud_str_type('SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML');

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i)) then
      dbms_output.put_line(audits(i) || ' initialized');
    else
      dbms_output.put_line(audits(i) || ' not initialized');
    end if;
  end LOOP;

  total := audit_names.count;
  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || (audit_names(i) || ');');
  end LOOP;
end;
/

using record

set serveroutput ON

DECLARE 
   type aud_type IS record (value integer, name varchar2(60)); 
   type aud_tab IS table of aud_type INDEX by binary_integer;
   audits aud_tab;
   total integer;
begin
  audits(1).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD;
  audits(2).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD;
  audits(3).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES;
  audits(4).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS;
  audits(5).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD;
  audits(6).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML;
  
  audits(1).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD';
  audits(2).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD';
  audits(3).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES';
  audits(4).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS';
  audits(5).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD';
  audits(6).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML';

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i).value) then
      dbms_output.put_line(audits(i).name || ' initialized');
    else
      dbms_output.put_line(audits(i).name || ' not initialized');
    end if;
  end LOOP;

  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || audits(i).name || ');');
  end LOOP;
end;
/

Notes

database administration

Category Archives: pl/sql

load text file into CLOB column