|
 |
|
Insert Oracle CLOB column
Oracle Database Tips by Donald BurlesonMay 5, 2015
|
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:
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; /
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|

|
|