Insert Oracle CLOB column

Insert Oracle CLOB column Oracle Tips by Burleson Consulting May 5, 2010

Question:  I have a table with a CLOB column (XMLTYPE datatype) and I want to know the best way to do an insert into this large object column.  So the SQL insert basically inserts a record with new XML, and a SQL updated replaces the old XML.  What is the fastest way to do a large batch insert of XML into an unstructured CLOB table column?

Answer:  When dealing with large objects, where are relatively few tuning options other than using a large enough blocksize to ensure that the XMLTYPE column does not fragment onto multiple data blocks. 

Also, super-fast storage makes a big difference for inserts, and SSD have do well over 100,000 rows per second insert rates.  Here are the speed options for various insert methods:

When storing a LOB, you can either load it inline, or leave it in a flat file and use BFILE:

• Store the CLOB as BFILE - You can also leave the CLOB in a flat file, and use  the BFILE utility to map the CLOB into Oracle using the dbms_lob.loadfromfile utility.  The advantage is that you don't have to actually load the CLOB into an Oracle tablespace, but the disadvantage is that the CLOB is outside of Oracle, and it cannot be managed (backed-up, kept consistent) is if the CLOB resided inside a table.  See here, how to store image files into Oracle tables. BFILE is fastest because the CLOB is never really loaded into a tablespace, it's still outside Oracle in a flat file.

• Store the CLOB inside the table - The third option is to use a PL/SQL procedure to store the CLOB directly into an Oracle table, and you use the same dbms_lob.loadfromfile utility. 
• Store the CLOB using PHP:  Some use PHP to store CLOB columns.
• Use SQL*Loader:  SQL*Loader is the fastest way to bulk load data.  See here how to load a CLOB with SQL*Loader.

Storing a CLOB column into a table

Below are procedures for loading a PDF file, very similar to loading any CLOB file:

Procedure to load a PDF as a BFILE:

CREATE OR REPLACE PROCEDURE load_lob AS
  id         NUMBER;
  image1     BLOB;
  locator    BFILE;
  bfile_len  NUMBER;
  bf_desc    VARCHAR2(30);
  bf_name    VARCHAR2(30);
  bf_dir     VARCHAR2(30);
  bf_typ     VARCHAR2(4);
  ctr integer;
  CURSOR get_id IS
    SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
BEGIN
  OPEN get_id;
LOOP
  FETCH get_id INTO id, bf_desc, bf_typ;
  EXIT WHEN get_id%notfound;
  dbms_output.put_line('ID: '||to_char(id));
  SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;
  dbms_lob.filegetname(
locator,bf_dir,bf_name);
  dbms_output.put_line('Dir: '||bf_dir);
  dbms_lob.fileopen(locator,dbms_lob.file_readonly);
  bfile_len:=dbms_lob.getlength(locator);
  dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
  SELECT temp_blob INTO image1 FROM temp_blob;  bfile_len:=dbms_lob.getlength(locator);
  dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
  INSERT INTO internal_graphics VALUES (id,bf_desc,image1,bf_typ);
  dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
  ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
  dbms_lob.fileclose(locator);
END LOOP;
END;
/

Procedure to load a PDF into a BLOB column of a table:

CODE

CREATE OR REPLACE PROCEDURE load_lob AS
  id         NUMBER;
  image1     BLOB;
  locator    BFILE;
  bfile_len  NUMBER;
  bf_desc    VARCHAR2(30);
  bf_name    VARCHAR2(30);
  bf_dir     VARCHAR2(30);
  bf_typ     VARCHAR2(4);
  ctr integer;
  CURSOR get_id IS
    SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
BEGIN
  OPEN get_id;
LOOP
  FETCH get_id INTO id, bf_desc, bf_typ;
  EXIT WHEN get_id%notfound;
  dbms_output.put_line('ID: '||to_char(id));
  SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;
  dbms_lob.filegetname(
locator,bf_dir,bf_name);
  dbms_output.put_line('Dir: '||bf_dir);
  dbms_lob.fileopen(locator,dbms_lob.file_readonly);
  bfile_len:=dbms_lob.getlength(locator);
  dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
  SELECT temp_blob INTO image1 FROM temp_blob;
  bfile_len:=dbms_lob.getlength(locator);
  dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
  INSERT INTO internal_graphics VALUES (id,bf_desc,image1,bf_typ);
  dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
  ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
  dbms_lob.fileclose(locator);
END LOOP;
END;
/