Support for Oracle Object-Relational and LOB Datatypes

Oracle Objects for OLE provides full support for accessing and manipulating instances of REFs, value instances, variable-length arrays (VARRAYs), nested tables, and LOBs in an Oracle database server.

The Oracle Object-Relational and LOB Datatypes diagram illustrates the containment hierarchy for instances of all types in Oracle Objects for OLE.

Instances of these types can be fetched from the database or passed as input or output variables to SQL statements and PL/SQL blocks, including stored procedures and functions. All instances are mapped to COM Automation Interfaces that provide methods for dynamic attribute access and manipulation. These interfaces may be obtained from:

· The value property of an OraField object in a Dynaset

· The value property of an OraParameter object used as an input or an output parameter in SQL Statements or PL/SQL blocks

· An attribute of another object/REF instance

· An element in a collection (VARRAY or a Nested Table)

OraObject

The OraObject interface is a representation of an Oracle embedded object or a value instance. It contains a collection interface (OraAttributes ) for accessing and manipulating (updating and inserting) individual attributes of a value instance. Individual attributes of an OraAttributes collection interface can be accessed by using a subscript or the name of the attribute.

The following Visual Basic example illustrates how to access attributes of the Address object in the person_tab table:

Set Person = OraDatabase.CreateDynaset("select * from person_tab",0&)

set Address = Person.Fields("Addr").Value

msgbox Address.Zip

msgbox.Address.City

OraRef

The OraRef interface represents an instance of a referenceable object (REF) in client applications. The object attributes are accessed in the same manner as attributes of an object represented by the OraObject interface. OraRef is derived from an OraObject interface via the containment mechanism in COM. REF objects are updated and deleted independent of the context they originated from, such as Dynasets. The OraRef interface also encapsulates the functionality for navigating through graphs of objects utilizing the Complex Object Retrieval Capability (COR) in OCI.

OraCollection

The OraCollection interface provides methods for accessing and manipulating Oracle collection types, namely variable-length array (VARRAYs) and Nested Tables in OO4O. Elements contained in a collection are accessed by subscripts.

The following Visual Basic example illustrates how to access attributes of the EnameList object from the department table:

Set Person = OraDatabase.CreateDynaset("select * from department",0&)

set EnameList = Department.Fields("Enames").Value

'access all elements of the EnameList VArray

for I=1 to I=EnameList.Size

msgbox EnameList(I)

Next I

OraBLOB and OraCLOB

OraBLOB and OraCLOB interfaces in OO4O provides methods for performing operations on large objects in the database including BLOB, CLOB and NCLOB, and BFILE data types.

The following Visual Basic example illustrates how to read PartImage from the part table:

Dim Buffer as Variant

Set Part = OraDatabase.CreateDynaset("select * from part", 0&)

set PartImage = OraDynaset.Fields("part_image").Value

'read the data into the buffer

amount_read = PartImage.Read(buffer)

'copy the image content into the file

PartImage.CopyToFile "d:\image\partimage.jpg"