Insert Oracle CLOB columnInsert Oracle CLOB columnStoring a CLOB column into a tableProcedure to load a PDF as a BFILE:Procedure to load a PDF into a BLOB column of a table:

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 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.  

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

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;

/

CODE

  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;

  OPEN get_id;

  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(

  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;

  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);

本文转自海天一鸥博客园博客，原文链接：http://www.cnblogs.com/sgsoft/archive/2010/12/15/1907359.html，如需转载请自行联系原作者