CREATE TABLE part( part_id NUMBER, part_name VARCHAR2(20), part_image BLOB, part_desc CLOB, part_collateral BFILE ); insert into part values (1,'ORACLE NETWORK',EMPTY_BLOB(),EMPTY_CLOB(),NULL); insert into part values (2,'ORACLE SERVER', EMPTY_BLOB(),EMPTY_CLOB(),NULL);2. VB的例子(目录: $ORACLE_HOME\oo4o\vb\samples\lob)''如果安装了oo4o,直接用vb打开例子，修改数据库实例名/用户/密码，跳过 ''下面的说明''如果没有安装，自己新建一个工程，引用类型库($ORACLE_HOME\bin\oip8.tlb)'' 准备一个partdesc.dat文件，任意一个文本文件即可(长度超过4000字节) ''先使用CLOB_Write_Click函数写入clob类型的数据,然后使用 '' CLOB_Read_Click 函数读取clob列的数据'' clob_write,clob_read为两个command button.Private Sub CLOB_Write_Click() Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim PartDesc As OraClob Dim buffer As String Dim chunksize As Long Dim amount_written As Long
'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. ''修改数据库实例名/用户/密码 Set OraDatabase = OraSession.OpenDatabase("sid", "username/password", 0&) 'Create the OraDynaset Object Set OraDynaset = OraDatabase.Dbcreatedynaset("select * from part", 0&)
Set PartDesc = OraDynaset.Fields("part_desc").Value
chunksize = 32000
'Re adjust the buffer size buffer = String$(chunksize, 32)
fnum = FreeFile 'Open the file. Open "partdesc.dat" For Binary As #fnum 'set the offset and PollingAmount properties for piece wiseWrite operation PartDesc.offset = 1 PartDesc.PollingAmount = LOF(fnum) remainder = LOF(fnum)
If (LOF(fnum) = 0) Then MsgBox "File size is zero. Make sure that existence of File and its path are correct" Exit Sub End If
'Lock the row for write operation OraDynaset.Edit
Get #fnum, , buffer
'Do first write operation amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)
While PartDesc.Status = ORALOB_NEED_DATA remainder = remainder - chunksize If remainder < chunksize Then piecetype = ORALOB_LAST_PIECE chunksize = remainder Else piecetype = ORALOB_NEXT_PIECE End If Get #fnum, , buffer amount_written = PartDesc.Write(buffer, chunksize, piecetype)
Wend
Close fnum
'call Update method to commit the transaction OraDynaset.Update
MsgBox PartDesc.Size
MsgBox "Write operation successfull" ''我自己添加的，释放对象 If OraDatabase.ConnectionOK Then OraDatabase.Close Set OraDatabase = Nothing Set OraSession = Nothing
End Sub'' 读取clob列，并写入desc.dat 文件 Private Sub CLOB_Read_Click() 'Declare variables as OLE Objects. Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartDesc As OraClob Dim AmountRead As Long Dim buffer As Variant Dim buf As String 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object.
''修改数据库实例名/用户/密码 Set OraDatabase = OraSession.OpenDatabase("sid", "username/password", 0&) 'Add PartDesc as an Output parameter and set its initial value. OraDatabase.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT OraDatabase.Parameters("PartDesc").serverType = ORATYPE_CLOB 'Execute the statement returing 'PartDesc' OraDatabase.ExecuteSQL ("BEGIN select part_desc into :PARTDESC from part where part_id = 1 for update NOWAIT; END;") 'Get 'PartDesc' from Parameters collection Set PartDesc = OraDatabase.Parameters("PartDesc").Value 'Get a free file number fnum = FreeFile
'Open the file. Open "Desc.Dat" For Binary As #fnum 'Read entire CLOB value, buffer must be a Variant AmountRead = PartDesc.Read(buffer) 'put will not allow Variant type buf = buffer
Put #fnum, , buf
Close fnum
MsgBox "Read operation successfull"
''我自己添加的，释放对象 If OraDatabase.ConnectionOK Then OraDatabase.Close Set OraDatabase = Nothing Set OraSession = Nothing
1. 先执行下面的脚本创建测试表:drop table part;
CREATE TABLE part(
part_id NUMBER,
part_name VARCHAR2(20),
part_image BLOB,
part_desc CLOB,
part_collateral BFILE
);
insert into part values (1,'ORACLE NETWORK',EMPTY_BLOB(),EMPTY_CLOB(),NULL);
insert into part values (2,'ORACLE SERVER', EMPTY_BLOB(),EMPTY_CLOB(),NULL);2. VB的例子(目录: $ORACLE_HOME\oo4o\vb\samples\lob)''如果安装了oo4o,直接用vb打开例子，修改数据库实例名/用户/密码，跳过
''下面的说明''如果没有安装，自己新建一个工程，引用类型库($ORACLE_HOME\bin\oip8.tlb)'' 准备一个partdesc.dat文件，任意一个文本文件即可(长度超过4000字节)
''先使用CLOB_Write_Click函数写入clob类型的数据,然后使用
'' CLOB_Read_Click 函数读取clob列的数据'' clob_write,clob_read为两个command button.Private Sub CLOB_Write_Click()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim PartDesc As OraClob
Dim buffer As String
Dim chunksize As Long
Dim amount_written As Long
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. ''修改数据库实例名/用户/密码
Set OraDatabase = OraSession.OpenDatabase("sid", "username/password", 0&) 'Create the OraDynaset Object
Set OraDynaset = OraDatabase.Dbcreatedynaset("select * from part", 0&)
Set PartDesc = OraDynaset.Fields("part_desc").Value
chunksize = 32000
'Re adjust the buffer size
buffer = String$(chunksize, 32)
fnum = FreeFile 'Open the file.
Open "partdesc.dat" For Binary As #fnum 'set the offset and PollingAmount properties for piece wiseWrite operation
PartDesc.offset = 1
PartDesc.PollingAmount = LOF(fnum)
remainder = LOF(fnum)
If (LOF(fnum) = 0) Then
MsgBox "File size is zero. Make sure that existence of File and its path are correct"
Exit Sub
End If
'Lock the row for write operation
OraDynaset.Edit
Get #fnum, , buffer
'Do first write operation
amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)
While PartDesc.Status = ORALOB_NEED_DATA
remainder = remainder - chunksize
If remainder < chunksize Then
piecetype = ORALOB_LAST_PIECE
chunksize = remainder
Else
piecetype = ORALOB_NEXT_PIECE
End If
Get #fnum, , buffer
amount_written = PartDesc.Write(buffer, chunksize, piecetype)
Wend
Close fnum
'call Update method to commit the transaction
OraDynaset.Update
MsgBox PartDesc.Size
MsgBox "Write operation successfull" ''我自己添加的，释放对象
If OraDatabase.ConnectionOK Then OraDatabase.Close
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub'' 读取clob列，并写入desc.dat 文件
Private Sub CLOB_Read_Click()
'Declare variables as OLE Objects.
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim PartDesc As OraClob
Dim AmountRead As Long Dim buffer As Variant
Dim buf As String
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object.
''修改数据库实例名/用户/密码
Set OraDatabase = OraSession.OpenDatabase("sid", "username/password", 0&) 'Add PartDesc as an Output parameter and set its initial value.
OraDatabase.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT
OraDatabase.Parameters("PartDesc").serverType = ORATYPE_CLOB 'Execute the statement returing 'PartDesc'
OraDatabase.ExecuteSQL ("BEGIN select part_desc into :PARTDESC from part where part_id = 1 for update NOWAIT; END;") 'Get 'PartDesc' from Parameters collection
Set PartDesc = OraDatabase.Parameters("PartDesc").Value 'Get a free file number
fnum = FreeFile
'Open the file.
Open "Desc.Dat" For Binary As #fnum
'Read entire CLOB value, buffer must be a Variant
AmountRead = PartDesc.Read(buffer) 'put will not allow Variant type
buf = buffer
Put #fnum, , buf
Close fnum
MsgBox "Read operation successfull"
''我自己添加的，释放对象
If OraDatabase.ConnectionOK Then OraDatabase.Close
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub