|Oracle® Objects for OLE Developer's Guide
11g Release 2 (11.2) for Microsoft Windows
Part Number E12245-01
Writes a buffer into the
CLOB value of this object and returns the total amount of the data written.
amount_written = OraBlob.Write buffer, chunksize, piece amount_written = OraClob.Write buffer, chunksize, piece
The arguments for the method are:
||The character array for an
Obtain either a row-level lock or object-level lock before calling the
Write method. This method writes the
CLOB data from the offset specified by the
Offset property. For a multiple-piece write operation, the
PollingAmount property can be set to the value of the total amount of data to be written, and the
Status property must be checked for the success of each piece operation. If the total amount is not known, then the
PollingAmount property can be set to
0 and polling still occurs as long as the piece type is not
For the last piece, set the piece argument to
ORALOB_LAST_PIECE. You must write the polling amount in bytes or characters. It is not possible to terminate the
Write operation early if the
PollingAmount property is not zero.
When the OraLOB
0 but the piece type on OraLOB
Write is not
ORALOB_ONE_PIECE, polling still occurs. Polling completes when
ORALOB_LAST_PIECE is sent as an argument to a call to the
Write method. This is useful when calling the
OraCLOB.Write method in a variable-width character set, when counting the total amount of characters ahead of time may be costly.
Note:When manipulating LOBs using LOB methods, such as the
CopyFromFile, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the
Trim(OraLOB) method to shrink the LOB object to the size of the new data.
Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .
Multiple-Piece Write of a LOB Example
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("ExampleDb", "scott/tiger", 0&) 'Create the OraDynaset Object Set OraDynaset = OraDatabase.CreateDynaset("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 wise 'Write operation PartDesc.offset = 1 PartDesc.PollingAmount = LOF(FNum) remainder = LOF(FNum) '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
Single-Piece Write of a LOB Example
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartImage As OraBlob Dim buffer() As Byte 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Add PartDesc as an Output parameter and set its initial value. OraDatabase.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT OraDatabase.Parameters("PartImage").ServerType = ORATYPE_BLOB 'Begin the transaction OraSession.BeginTrans 'Execute the statement returning 'PartDesc' OraDatabase.ExecuteSQL ("BEGIN select part_image into :PARTIMAGE" & _ "from part where part_id = 1 for update NOWAIT; END;") 'Get 'PartDesc' from Parameters collection Set PartImage = OraDatabase.Parameters("PartImage").Value 'Get a free file number FNum = FreeFile 'Open the file. Open "PartImage.Dat" For Binary As #FNum 'Re adjust the buffer size to hold entire file data ReDim buffer(LOF(FNum)) Get #FNum, , buffer 'Do one write operation amount_written = PartImage.Write(buffer) Close FNum MsgBox "Amount written to the LOB data is " & amount_written 'Ends the transaction OraSession.CommitTrans