在oracle中，有4個(gè)大對象（lobs）類型可用，分別是blob,clob,bfile,nclob。
下面是對lob數(shù)據(jù)類型的簡單介紹。
l blob:二進(jìn)制lob，為二進(jìn)制數(shù)據(jù)，最長可達(dá)4GB，存貯在數(shù)據(jù)庫中。
l clob:字符lob,字符數(shù)據(jù),最長可以達(dá)到4GB,存貯在數(shù)據(jù)庫中。
l bfile:二進(jìn)制文件;存貯在數(shù)據(jù)庫之外的只讀型二進(jìn)制數(shù)據(jù)，最大長度由操作系統(tǒng)限制。
l nclob：支持對字節(jié)字符集合(nultibyte characterset)的一個(gè)clob列。
對于如何檢索和操作這些lob數(shù)據(jù)一直是oracle數(shù)據(jù)庫開發(fā)者經(jīng)常碰到的問題。下面我將在oracle對lob數(shù)據(jù)處理的一些方法和技巧，介紹給讀者，希望能夠?qū)ψx者以后的開發(fā)有所幫助。
oracle中可以用多種方法來檢索或操作lob數(shù)據(jù)。通常的處理方法是通過dbms_lob包。
其他的方法包括使用api(application programming interfaces)應(yīng)用程序接口和oci（oracle call interface）oracle調(diào)用接口程序。
一、在oracle開發(fā)環(huán)境中我們可以用dbms_lob包來處理！dbms_lob包功能強(qiáng)大，簡單應(yīng)用。既可以用來讀取內(nèi)部的lob對象，也可以用來處理bfile對象。但處理兩者之間，還有一點(diǎn)差別。處理內(nèi)部lob對象（blob,clob）時(shí)，可以進(jìn)行讀和寫，但處理外部lob對象bfile時(shí)，只能進(jìn)行讀操作，寫的操作可以用pl/sql處理。另外用sql也可以處理lob，但要注意sql僅可以處理整個(gè)lob，不能操作lob的數(shù)據(jù)片。
在dbms_lob包中內(nèi)建了read(),append,write()，erase()，copy(),getlength()，substr()等函數(shù)，可以很方便地操作lob對象。這里不做深入討論，讀者可以參看相關(guān)的書籍。
對于pl/sql，下面介紹一種技巧，用動(dòng)態(tài)的pl/sql語句處理clob對象來傳替表名！
example 1.
動(dòng)態(tài)PL/SQL,對CLOB字段操作可傳遞表名table_name，表的唯一標(biāo)志字段名field_id，clob字段名field_name記錄號(hào)v_id，開始處理字符的位置v_pos，傳入的字符串變量v_clob 
修改CLOB的PL/SQL過程：updateclob 
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2, 
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
l /用法說明： 
在插入或修改以前，先把其它字段插入或修改，CLOB字段設(shè)置為空empty_clob()， 
然后調(diào)用以上的過程插入大于2048到32766個(gè)字符。 
如果需要插入大于32767個(gè)字符，編一個(gè)循環(huán)即可解決問題。 
查詢CLOB的PL/SQL函數(shù)：getclob 
create or replace function getclob(
table_name in varchar2,
field_id in varchar2, 
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000; 
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
l 用法說明： 
用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual; 
可以從CLOB字段中取2000個(gè)字符到partstr中， 
編一個(gè)循環(huán)可以把partstr組合成dbms_lob.getlength(field_name)長度的目標(biāo)字符串。 
二、對于在其他不同的開發(fā)環(huán)境，例如vc,vb,pb,java等環(huán)境下對lob的處理，處理方法不盡相同，在這里將簡要舉幾個(gè)例子來說明不在oracle開發(fā)環(huán)境下對lob的處理。
（一） 在pb中的處理
exampler 2.
string ls_path,ls_filename,ls_jhdh 
long ll_num,ll_count,rtn 
blob ole_blob 
ll_num=dw_lb.getrow() 
if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num] 
select count(*) into :ll_count from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx; 
if ll_count>0 then 
rtn=messagebox("提示","是否要修改此附件",question!,yesno!,1) 
if rtn=1 then 
SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx; 
ole_1.objectdata =ole_blob 
If ole_1.activate(offsite!) <> 0 Then 
Messagebox("OLE Activate","不能激活") 
Return -1 
end If 
end if 
else 
messagebox("提示","沒有附件") 
end if 
end if
（二）在vb中的處理
在vb中處理大對象，一般可以用OO4O(oracle objects for ole)來處理大對象。這里介紹一種不用0040處理大對象blob的方法。
下面這段程序可以將一個(gè)文件（文本文件，doc文件，圖象文件等）保存到數(shù)據(jù)庫中，并可以將其從數(shù)據(jù)庫讀出 
需要兩個(gè)commandbutton 
cmd1 名稱 cmdsave caption 保存 
cmd2 名稱 cmdread caption 讀取 
一個(gè)cmddialog控件 
同時(shí)需要?jiǎng)?chuàng)建一張表t_demo(字段id 類型 number,;字段text 類型 blob;)
exmple 3.
Option Explicit
Dim rn As ADODB.Connection
Public Function CreateDataSource(DataSource As String, UserID As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = "Provider=OraOledb.Oracle.1;" & _
"password=" & Password & ";" & _
"User ID =" & UserID & ";" & _
"Data Source=" & DataSource & ";" & _
"Locale Identifier=2052"
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End Function

Private Sub cmdRead_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
ComDlgDir.DialogTitle = "保存文件"
ComDlgDir.Filter = "*.*"
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub

Private Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
rs.AddNew 
ComDlgDir.DialogTitle = "選取文件"
ComDlgDir.ShowOpen 
rs.Fields("id").Value = 1
If ComDlgDir.filename <> "" Then
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)
rs.Update
End If 
Set rs = Nothing
Exit Sub
Set rs = Nothing 
End Sub

Private Sub Form_Load()
If Not CreateDataSource("sid", "systemp", "manager") Then
MsgBox "Connection failure!"
End If
End Sub

fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "field doesn't support the GetChunk method."
End If
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"
fnum = FreeFile 
Open filename For Binary As fnum
bytesleft = LOF(fnum)
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub

Sub BlobToFile(fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "field doesn't support the GetChunk method."
End If
If Dir$(filename) <> "" Then Kill filename
fnum = FreeFile
Open filename For Binary As fnum
bytesleft = fld.ActualSize
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
tmp = fld.GetChunk(bytes)
Put #fnum, , tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub

(三)用jdbc處理lob
exmple 4.
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data 
java.io.OutputStream outstream;
// read data into a byte array 
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
還有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)"); 
ops.setBLOB(1, my_blob);
ops.execute();
最后應(yīng)該注意：
insert的時(shí)候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");

（四）在pro*c中的處理
PRO*C可以用三種方式對LOB字段處理。 
1、The DBMS_LOB package inside PL/SQL blocks. 
2、OCI (Oracle Call Interface) function calls. 
3、Embedded SQL statements. 
Embedded SQL statements.的方式簡單而且比較靈活。OTN上提供一個(gè)例子： 
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file. 
Our buffer is small, so depending on the size of the BLOB we are reading, we may 
be able to read the BLOB value into the buffer in a single READ statement or we 
may be required to utilize a standard polling method instead. 
First we start off with oci.h and some simple local variable declarations 
example 5.
#include <oci.h> 
OCIBlobLocator *blob ; 
FILE *fp ; 
unsigned int amt, offset = 1 ; 
Now we need a buffer to store the BLOB value and then write to the file from: 
#define MAXBUFLEN 5000 
unsigned char buffer[MAXBUFLEN] ; 
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ; 
Allocate the BLOB host variable and select a BLOB which we will READ: 
EXEC SQL ALLOCATE :blob ; 
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ; 
We can then open the external file to which we will write the BLOB value: 
fp = fopen((const char *)"image.gif", (const char *)"w") ; 
If the buffer can hold the entire LOB value in a single READ we need to catch the 
NOT FOUND condition to signal LOB READ termination: 
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ; 
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It 
is larger than our buffer so if the LOB doesn't fit we will READ using a polling 
mode: 
amt = 4294967295 ; 
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ; 
If we get here then it means that the buffer was not large enough to hold the entire 
LOB value, so we must write what we have using binary I/O and continue reading: 
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ; 
We use a standard polling method to continue reading with the LOB READ inside 
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop: 
EXEC SQL WHENEVER NOT FOUND DO break ; 
while (TRUE) 
{ 
During polling, the offset is not used so we can omit it in subsequent LOB READs. 
We need the amount, however, because it will tell us how much was READ in the 
last READ invocation 
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ; 
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ; 
} 
Here, we have reached the end of the LOB value. The amount holds the amount of 
the last piece that was READ. During polling, the amount for each interim piece 
was set to MAXBUFLEN, or the maximum size of our buffer: 
end_of_lob: 
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ; 

（五） 在delphi中的處理
對于lob字段而言，個(gè)人認(rèn)為其使用比long類型有很大的靈活性，而且lob字段可以保存各類的數(shù)據(jù)，可以保存圖片，大量的文字，現(xiàn)就clob跟blob兩種類型加以說明，其中blob保存圖片信息，clob保存大量文字。
exmple 6.
Create table test_table
(c_no number(1) not null,
c_blob blob,
c_clob clob,
constraint pk_test_table primary key (c_no));

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DBCtrls, Grids, DBGrids, DB, DBTables, ExtDlgs;

type
TForm1 = class(TForm)
Database1: TDatabase; //用于連接數(shù)據(jù)庫
Table1: TTable; //獲取表信息
DataSource1: TDataSource; 
DBGrid1: TDBGrid;
DBMemo1: TDBMemo; //顯示c_clob字段內(nèi)容
DBImage1: TDBImage; //顯示c_blob字段內(nèi)容
Button1: TButton; //插入按鈕
Button2: TButton; //保存按鈕
Table1C_NO: TFloatField; //Tfiled
Table1C_BLOB: TBlobField;
Table1C_CLOB: TMemoField;
OpenPictureDialog1: TOpenPictureDialog; //從文件獲取圖片
OpenDialog1: TOpenDialog; //從文件獲取文字
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin //插入操作
with Table1 do
begin
Insert; //將表狀態(tài)置為插入狀態(tài)
if OpenPictureDialog1.Execute then //獲得圖片信息
Table1C_BLOB.LoadFromFile(OpenPictureDialog1.FileName);
if OpenDialog1.Execute then //獲得文字信息
Table1C_CLOB.LoadFromFile(OpenDialog1.FileName);
end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin //提交插入內(nèi)容
try
Table1.Post;
except
Application.MessageBox('錯(cuò)誤發(fā)生','警告',0);
end;
end;

end.

注意：
openpiceturedilog只能打開dmp,ico,wmf等文件，事先需要將圖片文件格式保存成這幾類；
在文字字段不從文件獲得時(shí)，可以手動(dòng)輸入
本例只是對lob字段的一個(gè)小小的探索，用法不當(dāng)及需改正之處，還請多多指教。

注:本篇文章大部分例子均取自論壇，如有侵犯您的版權(quán)，請來信告知，我會(huì)做相應(yīng)處理。 

注意：openpiceturedilog只能打開dmp,ico,wmf等文件，事先需要將圖片文件格式保存成這幾類；
在文字字段不從文件獲得時(shí)，可以手動(dòng)輸入