Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs

You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it via the Oracle Objects for OLE (OO4O) API, by using one of the following objects interfaces:

• OraBlob: To provide methods for performing operations on BLOB datatypes in the database

• OraClob: To provide methods for performing operations on CLOB datatypes in the database

• OraBFile: To provide methods for performing operations on BFILE data stored in operating system files.

  Note: OracleBlob and OracleClob have been deprecated and no longer work!

OO4O Syntax Reference and Further Information

Syntax

OO4O syntax reference is viewed from the OO4O online help.

Oracle Objects for OLE (OO4O), a Windows-based product included with Oracle8i Client for Windows NT, has no manuals, only online help.

Its online help is available through the Application Development submenu of the Oracle8i installation. To view specific methods and properties from the Help Topics menu, select the Contents tab > OO4O Automation Server > Methods or Properties.

Further Information

For further information about OO4O, refer to the following web site:

http://technet.oracle.com

Select Products > Internet Tools > Programmer. Scroll down to "Oracle Objects for OLE". At the bottom of the page is a list of useful articles for using the interfaces.

http://www.oracle.com/products

Search for articles on OO4O or Oracle Objects for OLE.

OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

These interfaces encapsulate LOB locators, so you do not deal directly with locators, but instead, can use methods and properties provided to perform operations and get state information.

OraBlob and OraClob Objects Are Retrieved as Part of Dynaset and Represent LOB Locators

When OraBlob and OraClob objects are retrieved as a part of a dynaset, these objects represent LOB locators of the dynaset current row. If the dynaset current row changes due to a move operation, the OraBlob and OraClob objects represent the LOB locator for the new current row.

Use the Clone Method to Retain Locator Independent of the Dynaset Move

To retain the LOB locator of the OraBlob and OraClob object independent of the dynaset move operation, use the Clone method. This method returns the OraBlob and OraClob object. You can also use these objects as PL/SQL bind parameters.

Example of OraBlob and OraBfile

The following example shows usage of OraBlob and OraBfile. Functions and examples are explained in greater detail in Chapters 9, 10, and 11.

Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, 
OraMyBfile as OraBFile

OraConnection.BeginTrans
set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_
id", ORADYN_DEFAULT)
set OraSound1 = OraDyn.Fields("Sound").value
set OraSoundClone = OraSound1

OraParameters.Add "id", 1,ORAPARAM_INPUT
OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
OraParameters("mybfile").ServerType = ORATYPE_BFILE

OraDatabase.ExecuteSQL ("begin  GetBFile(:id, :mybfile ") end")

Set OraMyBFile = OraParameters("mybfile").value
'Go to Next row
OraDyn.MoveNext

OraDyn.Edit
'Lets update OraSound1 data with that from the BFILE
OraSound1.CopyFromBFile  OraMyBFile
OraDyn.Update

OraDyn.MoveNext
'Go to Next row
OraDyn.Edit
'Lets update OraSound1 by appending with LOB data from 1st row represenetd by 
'OraSoundClone
OraSound1.Append  OraSoundClone
OraDyn.Update

OraConnection.CommitTrans

In the above example:

OraSound1 -- represents the locator for the current row in the dynaset OraSoundClone -- represents the locator for the 1st row.

A change in the current row (say a OraDyn.MoveNext) means the following:

OraSound1 -- will represent the locator for the 2nd row

OraSoundClone -- will represent the locator in the 1st row. OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).

OraMyBFile -- refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.

Note: A LOB obtained via executing SQL is only valid for the duration of the transaction. For this reason, "BEGINTRANS" and "COMMITTRANS" are used to specify the duration of the transaction.

OO4O Methods and Properties to Access Data Stored in BLOBs, CLOBs, NCLOBs, and BFILEs

Oracle Objects for OLE (OO4O) includes methods and properties that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These methods and properties are listed in the tables below, and are discussed in greater detail in the following chapters:

• Chapter 9, "Internal Persistent LOBs"

• Chapter 11, "External LOBs (BFILEs)"

You will also be able to access these scripts from your Oracle8i software CD /rdbms/demo directory in a future release.

See Also: The OO4O online help for detailed information including parameters, parameter types, return values, and example code. Oracle Objects for OLE (OO4O), a Windows-based product included with Oracle8i Client for Windows NT, has no manuals, only online help. Its online help is available through the Application Development submenu of the Oracle8i installation.

The following OO4O methods and properties operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

• To modify internal LOBs, see Table 3-29

• To read or examine internal and external LOB values, see Table 3-30

• To open and close BFILEs, see Table 3-31

• For LOB buffering, see Table 3-32

• Properties such as to see if LOB is NULL, or to get or set polling amount, see Table 3-33

• For read-only BFILE methods, see Table 3-34

• For BFILE properties, see Table 3-35

OO4O Methods To Modify Internal LOBs (BLOB, CLOB, and NCLOB) Values

Table 3-29 OO4O Methods To Modify Interna; LOBs (BLOB, CLOB, and NCLOB) Values

Methods	Description
OraBlob.Append OraClob.Append	Appends BLOB value to another LOB. Appends CLOB or NCLOB value to another LOB.
OraBlob.Copy OraClob.Copy	Copies a portion of a BLOB into another LOB Copies a portion of a CLOB or NCLOB into another LOB
OraBlob.Erase OraClob.Erase	Erases part of a BLOB, starting at a specified offset Erases part of a CLOB or NCLOB, starting at a specified offset
OraBlob.CopyFromBFile OraClob.CopyFromBFile	Loads BFILE data into an internal BLOB Loads BFILE data into an internal CLOB or NCLOB
OraBlob.Trim OraClob.Trim	Truncates a BLOB Truncates a CLOB or NCLOB
OraBlob.CopyFromFile OraClob.CopyFromFile	Writes data from a file to a BLOB Writes data from a file to a CLOB or NCLOB
OraBlob.Write OraClob.Write	Writes data to the BLOB Writes data to the CLOB or NCLOB

OO4O Methods To Read or Examine internal and External LOB Values

Table 3-30 OO4O Methods To Read or Examine Internal and External LOB Values

Function/Procedure	Description
OraBlob.Read OraClob.Read OraBFile.Read	Reads a specified portion of a non-null BLOB into a buffer Reads a specified portion of a non-null CLOB into a buffer Reads a specified portion of a non-null BFILE into a buffer
OraBlob.CopyToFile OraClob.CopyToFile	Reads a specified portion of a non-null BLOB to a file Reads a specified portion of a non-null CLOB to a file

OO4O Methods To Open and Close External LOBs (BFILEs)

Table 3-31 OO4O Methods To Open and Close External LOBs (BFILEs)

Method	Description
OraBFile.Open	Opens BFILE.
OraBFile.Close	Closes BFILE.

and Close

OO4O Methods For Internal LOB-Buffering

Table 3-32 OO4O Methods For Internal LOB-Buffering

Method	Description
OraBlob.FlushBuffer OraClob.FlushBuffer	Flushes changes made to the BLOB buffering subsystem to the database (server) Flushes changes made to the CLOB buffering (server) subsystem to the database (sever)
OraBlob.EnableBuffering OraClob.EnableBuffering	Enables buffering of BLOB operations Enables buffering of CLOB operations
OraBlob.DisableBuffering OraClob.DisableBuffering	Disables buffering of BLOB operations Disables buffering of CLOB operations

OO4O Properties For Operating on LOBs

Table 3-33 OO4O Properties for Operating on LOBs

Property	Description
IsNull (Read)	Indicates when a LOB is Null
PollingAmount(Read/Write)	Gets/Sets total amount for Read/Write polling operation
Offset(Read/Write)	Gets/Sets offset for Read/Write operation. By default, it is set to 1.
Status(Read)	Returns the polling status.Possible values are ORALOB_NEED_DATA There is more data to be read or written ORALOB_NO_DATA There is no more data to be read or written ORALOB_SUCCESS LOB data read/written successfully
Size(Read)	Returns the length of the LOB data

OO4O Read-Only Methods For External Lobs (BFILEs)

Table 3-34 OO4O Read-Only Methods For External LOBs (BFILEs)

Methods	Description
OraBFile.Close	Closes an open BFILE
OraBFile.CloseAll	Closes all open BFILEs
OraBFile.Open	Opens a BFILE
OraBFile.IsOpen	Determines if a BFILE is open

OO4O Properties For Operating

Table 3-35 OO4O Properties For Operating on External LOBs (BFILEs)

Property	Description
OraBFile.DirectoryName	Gets/Sets the server side directory alias name.
OraBFile.FileName(Read/Write)	Gets/Sets the server side filename.
OraBFile.Exists	Checks whether a BFILE exists.

on External LOBs (BFILEs)