Insert Oracle CLOB column-阿里云开发者社区

开发者社区> 数据库> 正文

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


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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章