在加载LOB数据之前呢 我们必须了解LOB locator的概念,lob locator指的是一个lob定位器
(LOB的内部存储机制决定),相当于指针, 表示插入LOB数据的位置,那么在插入数据前呢必须要
知道lob locator然后将数据插入到lob locator指定的位置.加载LOB数据的方式有两种一是
internal lob方式。通过SQL DML语句将lob数据同其他数据一快插入.平常的应用(java/c#)都喜欢用这种方式;另一种是external lob方式,用dbms_lob或OCI等以文件的形式将数据插入数据库.我试验的就是external lob的数据加载形式

export ORACLE_SID=rac1
[oracle@rac1 ~]$ sqlplus mayp/mayp

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Jul 29 22:01:19 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> desc lob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(100)
IMAGE BLOB
NOTE CLOB

SQL> create directory mydump as '/u01/app/oracle/ora';

Directory created.

SQL> DECLARE
p_image_file BFILE;
p_note_file BFILE;
p_image BLOB;
p_note CLOB;
BEGIN
INSERT INTO lob_test
(name, image, note)
VALUES
('i test lob data', EMPTY_BLOB(), EMPTY_CLOB())
RETURNING image, note INTO p_image, p_note;
p_image_file := BFILENAME('MYDUMP', 'Blue.jpg');
p_note_file := BFILENAME('MYDUMP', 'readme.txt');
DBMS_LOB.FILEOPEN(p_image_file);
DBMS_LOB.FILEOPEN(p_note_file);
DBMS_LOB.LOADFROMFILE(p_image,
p_image_file,
DBMS_LOB.GETLENGTH(p_image_file));
DBMS_LOB.LOADFROMFILE(p_note,
p_note_file,
DBMS_LOB.GETLENGTH(p_note_file));
DBMS_LOB.FILECLOSE(p_image_file);
DBMS_LOB.FILECLOSE(p_note_file);
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13

PL/SQL procedure successfully completed.

SQL> select count(*) from lob_test;

COUNT(*)
----------
1

表里面已经有一条记录了,我么可以通过pl/sql察看blog和clob:

在这里还用到了empty_blob,empty_clob,bfilename三个函数.
他们都是用来初始化lob locator的.