Example: Inserting LOBs using OraParameter

Schema Description

Dim OraSession As OraSession

Dim OraDatabase As OraDatabase

Dim OraBlob As OraBlob

Dim ImageChunk() As Byte

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("ExampleDb",

"scott/tiger", 0&)

Set OraParameters = OraDatabase.Parameters

OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT

OraParameters("PartImage").ServerType = ORATYPE_BLOB

'BeginTrans needs to be called since LOB locators become

'invalid after the ExecuteSQL call

OraSession.BeginTrans

OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle

Application', EMPTY_BLOB(),NULL,NULL)

RETURNING part_image INTO :PartImage ")

set PartImage = OraDatabase.Parameters("PARTIMAGE").Value

FNum = FreeFile

'Open the file for reading PartImages

Open "part_picture.gif" For Binary As #FNum

'read the file and put it into buffer

Redim ImageChunk(LOF(FNum))

Get #FNum, , ImageChunk

Set OraBlob = OraDatabase.Parameters("PartImage").Value

amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE)

' commit the transaction and close the file

OraSession.CommitTrans

Close FNum