4 Advanced OO4O Features

This chapter describes advanced Oracle Objects for OLE features.This chapter contains these topics:

Support for Oracle Object-Relational and LOB Data Types

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

Table 4-0 illustrates the containment hierarchy for instances of all types in Oracle Objects for OLE.

Figure 4-1 Object-Relational and LOB Data Types Diagram

Object hierarchy starting with OraField and OraParameter
Description of "Figure 4-1 Object-Relational and LOB Data Types Diagram"

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 can 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).

Instantiating Oracle LOBs, Objects, and Collections

Oracle Objects for OLE provides COM Automation interfaces for working with LOBs, Oracle objects, and collection types. These interfaces provide methods and properties to access data associated with LOBs, Oracle objects, and collection instances.

Oracle LOBs, Objects, and Collections

Table 4-1 lists Oracle LOBs, Objects, and collection types with associated OO4O interfaces.

Table 4-1 Oracle LOBs, Objects, and Collections

Type OO4O Interface

Object

OraObject

REF

OraRef

VARRAY and Nested Table

OraCollection

BLOB

OraBlob

CLOB

OraClob

BFILE

OraBFile


How the preceding interfaces are retrieved in OO4O depend on how they are stored in the database or accessed in a SQL statement. These are the possible scenarios:

  • Column of a table

    If a table contains LOBs, object types, and collections as columns and the dynaset SELECT statement is based on this table, then the Value property of the OraField object representing that column returns corresponding OO4O interfaces for that type.

  • Bind variable in a SQL statement or PL/SQL block

    If a SQL statement or PL/SQL block has LOBs, object types, and collections as bind variables, then an OraParameter object should be created with a corresponding server type using the Add method. The Value property of the OraParameter object representing that bind variable returns the corresponding OO4O interfaces for that type.

  • Attribute of an Oracle object instance

    If an Oracle object instance has LOBs, object types, or collections as attributes, then the corresponding OO4O interface for any attribute is retrieved by using the subscript or name of the attribute from the OraObject or OraRef, or by using the Value property of an OraAttribute object.

  • Element of VARRAY and nested table

    If an Oracle VARRAY and nested table has object types and REF as its elements, then the corresponding OO4O interface is retrieved using the element index as the subscript from the OraCollection object.

When OO4O interfaces for these types are retrieved as part of a dynaset, then the OO4O interfaces represent instances of LOBs, objects, and collection types for the current row of the dynaset. If the current row changes due to a move operation, then the OO4O interfaces represent instances of LOBs, objects, and collection types for the new current row. When OO4O interfaces for these types are retrieved as part of an OraParameter object and the OraParameter value changes to due to a OraSQLStmt Refresh method, then the OO4O interface represents a new instance LOB, object, and collection type for that OraParameter.

Internally, OO4O maintains one OO4O interface for each OraField, OraParameter, and OraAttribute object. To retain the instance of LOBs, objects, and collection types independent of a dynaset move operation or an OraSQLStmt refresh operation, use the Clone method on the corresponding OO4O interface. This method makes a copy of LOBs, objects, and collection types instance and returns a corresponding OO4O interface associated with that copy.

Using Large Objects (LOBs)

The large object (LOB) data types (BLOB, CLOB, NCLOB, and BFILE) can provide storage for large blocks of unstructured data, such as text, images, video clips, and sound waveforms, up to 4 gigabytes in size. They provide efficient, random, piece-wise access to the data. In Oracle Objects for OLE, instances of LOB data types are represented as interfaces.

See Also:

This section includes the following topics:

LOB Data Types

Table 4-2 lists the four LOB data types and their corresponding OO4O interfaces.

Table 4-2 LOB Data Types

LOB Data Types a LOB whose value is composed of Corresponding OO4O Interface

BLOB

Unstructured binary (raw) data.

OraBLOB

CLOB

Fixed-width, single-byte character data that corresponds to the database character set defined for Oracle Database.

OraCLOB

NCLOB

Fixed-width, multiple-byte character data that corresponds to the national character set defined for Oracle Database.

OraCLOB

BFILE

A LOB whose large binary data is stored in operating system files outside of database tablespaces. BFILEs can also be located on tertiary storage devices such as hard disks, CD-ROMs, Photo CDs, and DVDs.

OraBFILE


The following example creates a table that has BLOB and CLOB columns, and inserts rows into the table using the ExecuteSQL method on an OraDatabase object.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
InvDb.ExecuteSQL("create table part(part_id NUMBER, part_name 
VARCHAR2(20),part_image BLOB, part_desc CLOB)") 
InvDb.ExecuteSQL ("insert into part values (1,'ORACLE NETWORK',EMPTY_BLOB()," & _
              "EMPTY_CLOB())") 
InvDb.ExecuteSQL ("insert into part values (2,'ORACLE SERVER', EMPTY_BLOB()," & _
           "EMPTY_CLOB())") 

The EMPTY_BLOB() and EMPTY_CLOB() PL/SQL functions provide an empty LOB to insert into the LOB column.

Using OraBLOB and OraCLOB

OraBLOB and OraCLOB interfaces in OO4O provide 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 the 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"

Retrieving LOBs From the Database

OraBlob, OraClob, and OraBFile objects can be retrieved using an OraDynaset object or a parameter object:

Using an OraDynaset Object

If a table contains a LOB column and a dynaset query selects against that LOB column, then the Value property of the OraField object returns a OraBlob, OraClob, or a OraBFile object. The following example selects LOB columns from the part table. PartDesc and PartImage are OraBlob and OraClob objects that are retrieved from the OraField object.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartDesc = Part.Fields("part_desc").Value 
Set PartImage = Part.Fields("part_image").Value 

Using a Parameter object

If a SQL statement or PL/SQL block has a bind variable of type LOB, you create a OraParameter object using the OraParameters Add method. The Value property of the OraParameter object for that bind variable returns an OraBlob, OraClob, or OraBFile object.

The following example illustrates how to use a LOB data type as a bind variable in a PL/SQL anonymous block. This block selects a LOB column from the database.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
InvDb.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT,ORATYPE_CLOB 
InvDb.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT,ORATYPE_BLOB 
InvDb.ExecuteSQL ("BEGIN select part_desc, part_image into :PARTDESC," & _
             ":PARTIMAGE from part where part_id = 1 for update NOWAIT; END;") & _
             "for update NOWAIT; END;") 
Set PartDesc = InvDb.Parameters("PartDesc").Value 
Set PartImage = InvDb.Parameters("PartImage").Value 

Performance Considerations with LOB Read and Write

When reading and writing LOBs, there are several options that can optimize an application's memory usage and reduce the number of network round-trips.

Single-Piece Operation

The contents of a buffer are read or written to the database in one round-trip.

Multiple-Piece Operation

A small buffer is used for multiple calls to read or write methods. In this mode, the data is streamed, rather than requiring a complete round-trip for each read or write call. This method is quicker than doing several small single-piece operations. It has the restriction that the data must be read and written sequentially, meaning that the offset increases automatically with each read or write. The total amount must be known before it is written, and the operation cannot be aborted before completion.

LOB Buffering Option

The LOB buffering option automatically buffers any read or write operations. A network round-trip occurs only when the FlushBuffer method is called. This is most useful when there are many small writes that occur all across the LOB. This method has significant restrictions.

Writing LOB Data

The Write method of the OraBlob and OraClob objects writes data from a local buffer to a LOB in the database. The CopyFromFile (OraLOB) method writes content of a local file to a LOB in the database.

Any operation that changes the value of a LOB, including the Write method, can only occur when the row the LOB is associated with has been locked. If a LOB field is null, it must first be updated with an empty LOB before a method can write to the LOB field.

LOB data can be written in one piece or in a series of multiple pieces., as described in the following topics:

Single-Piece Write Operation

The entire contents of a buffer can be written in a single piece in one network round-trip. The following example writes 10 KB of data from the local file partimage.dat to part_image column at the offset of 1000.

Dim buffer() as byte 
ReDim buffer(10000) 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
PartImage.Offset = 1000 
FNum = FreeFile 
Open "PartImage.Dat" For Binary As #FNum 
Get #FNum, , buffer 
Part.Edit 
 
amount_written = PartImage.Write(buffer) 
Part.Update 
Close FNum 

The CopyFromFile (OraLOB) method writes data directly to a LOB from a local file. The following code is functionally the same as the previous code:

Part.Edit 
PartImage.CopyFromFile "PartImage.dat" , 10000, 1000 
Part.Update 

Multiple-Piece Write Operation

This mechanism is used when the size of the buffer available is smaller than the total amount of data to be written. The total amount of data to be written is set by using the PollingAmount (OraLOB/BFILE) property.

The Offset (OraLOB/BFILE) property is used only once to set the offset for the first piece Write operation. After the first time, it is automatically increased by the size of the previous piece. The Status (OraLOB/BFILE) property must be checked for success of each piece Write operation. If the Status property returns ORALOB_NEED_DATA, the Write method must be called again. This must continue until the amount specified by the PollingAmount property has been sent.

The piecetype argument of the Write method must be set to ORALOB_FIRST_PIECE for the first piece that is sent, and last piece Write operation ends with setting the piecetype argument to ORALOB_LAST_PIECE. At the end of multiple piece operation, the Status property returns ORALOB_NO_DATA.

The following example writes 102 KB of data in 10 KB chunks to the part_image column from the local file partimage.dat at offset of 1000.

Dim buffer() as byte 
chunksize = 10000 
ReDim buffer(chunksize) 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
 
FNum = FreeFile 
Open "PartImage.Dat" For Binary As #FNum 
PartImage.Offset = 1000 
PartImage.PollingAmount =102000 
remainder = 102000 
Part.Edit 
Get #FNum, , buffer 
amount_written = PartImage.Write(buffer, chunksize, ORALOB_FIRST_PIECE) 
 
While PartImage.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 = PartImage.Write(buffer, chunksize, piecetype) 
Wend 
Close FNum 
Part.Update 

Reading LOB Data

The OraBlob and OraClob Read method reads data to a local buffer from a LOB in the database. The CopyFromFile method reads the contents of a LOB into a local file.

LOB data can be read in one piece or in a series of multiple pieces, as described in the following topics:

Single-Piece Read Operation

The entire contents of a buffer can be read in a single piece in one network round-trip. The following example reads 10 KB of data from the part_image column at an offset of 1000 to the local file image.dat.

Dim buffer as Variant 
Dim buf() As Byte 
chunksize = 10000 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
FNum = FreeFile 
Open "image.dat" For Binary As #FNum 
PartImage.Offset = 1000 
amount_read = PartImage.Read(buffer,10000) 
buf = buffer 
Put #FNum, , buf 
Close FNum 

The CopyToFile (OraLOB/BFILE) method writes data directly to a local file from a LOB. The following code is functionally the same as the previous code:

PartImage.CopyToFile "image.dat" , 10000, 1000 

Multiple-Piece Read Operation

This mechanism is used when the size of the buffer available is smaller than the total amount of data to be read. The total amount of data to be read is set by using the PollingAmount (OraLOB/BFILE) property. The Offset (OraLOB/BFILE) property is used only once to set the offset for the first piece Read operation. After the first time, it is automatically increased by the size of the previous piece.

The Status (OraLOB/BFILE) property must be checked for success for each piece Read operation. If the Status property returns ORALOB_NEED_DATA, the Read method must be called again. This must continue until the amount specified by the PollingAmount property has been read. At the end of multiple piece operations, the Status property returns ORALOB_NO_DATA.

The following example reads 102 KB of data in 10 KB chunks from the part_image column at offset of 1000 to the local file image.dat.

Dim buffer as Variant 
Dim buf() As Byte 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
FNum = FreeFile 
Open "image.dat" For Binary As #FNum 
PartImage.offset = 1000 
PartImage.PollingAmount = 102000 
amount_read = PartImage.Read(buffer, chunksize) 
buf = buffer 
Put #FNum, , buf 
While PartImage.Status = ORALOB_NEED_DATA 
amount_read = PartImage.Read(buffer, chunksize) 
buf = buffer 
Put #FNum, , buf 
Wend 
Close FNum 

Oracle Object Data Types

An object type is a user-defined composite data type created in the database. A column can represent an object type or a row can represent an object type. An instance of the Object type can be stored in the database. This object instance can be fetched to the client side and modified using Oracle Objects for OLE.

There are two types of object instances.

  • OraObject object

    If a column represents an object type, then an instance of this object type is referred to as an embedded instance or a value instance. In OO4O, this type is represented by an OraObject object. For example, an ADDRESS object type is stored as a column in the PERSON table. OraObject objects can be embedded within other structures. An embedded instance or a value instance can also be the attributes of another object instance.

  • OraRef object

    If a row in an object table represents an object type, then the instance of this type is referred to as a referenceable object. In OO4O, this type is represented by an OraRef object. An internally referenceable object has a unique object identifier that is represented by the REF data type. A REF column can be thought of as a pointer to a referenceable object. OO4O applications can retrieve a REF data type from a referenceable object, fetch (pin) the associated referenceable object to the client side, and update (flush) the modified referenceable object to the database.

About the OraObject Interface

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 

See Also:

OraObject Object

Using the OraObject Interface

The following example creates an ADDRESS object type having street, city, state and zip as its attributes and a PERSON table having an ADDRESS object type column. It also inserts data using the ExecuteSQL method of the OraDatabase object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
HRDb.ExecuteSQL("create type ADDRESS as object ( street 
VARCHAR2(200), city varchar2(20), state CHAR(2), zip varchar2(10) )")  
HRDb.ExecuteSQL("create table person (name varchar2(20), age number," & _ 
                 "addr ADDRESS) ") 
HRDb.ExecuteSQL("insert into person values('nasser',40, " & _
                 "address('Wine Blvd', 'Pleasanton', 'CA', '94065'))") 
HRDb.ExecuteSQL("insert into person values('Maha', 25," & _ 
                 "address('Continental Way', 'Belmont', 'CA', '94002'))")
HRDb.ExecuteSQL("insert into person values('chris',30, address('First " & _
                 "Street', 'San Francisco', 'CA' ,'94123'))") 

The following topics discuss manipulating the OraObject interface:

Retrieving an Embedded/Value Instance from the Database

An OraObject object can be retrieved using OO4O using a dynaset or parameter object:

Using a Dynaset Object

If a table contains an object type column and a dynaset query selects against that column, then the Value property of the OraField object returns an OraObject.

The following code selects an ADDRESS column from the person table, and then an Address object is retrieved from the OraField object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Person = hrDb.CreateDynaset("select * from person", 0&) 
set Address = Person.Fields("Addr").Value 

Using a Parameter Object

If a SQL statement or a PL/SQL block has a bind variable of object type, you create an OraParameter object using the OraParameters Add method. The Value property of the OraParameter object for that bind variable returns an OraObject object.

The following example uses an object data type as a bind variable in a PL/SQL anonymous block. This block selects an object column from the database.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "ADDRESS", Null, ORAPARM_OUTPUT, ORATYPE_OBJECT, "ADDRESS"
'execute the sql statement which selects Address from the person_tab 
hrDb.ExecuteSQL ("BEGIN select Addr into :ADDRESS from person where " & _
            "age = 40; end;") 
'retrieve Address object from the OraParameter 
set address = hrDb.Parameters("ADDRESS").Value 

Accessing Attributes of an Embedded/Value Instance

Individual attributes can be accessed by using a subscript or the name of the attribute. The following example illustrates how to access attribute values of an ADDRESS object instance.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Person = hrDb.CreateDynaset("select * from person", 0&) 
set Address = Person.Fields("Addr").Value 
msgbox Address.City 
msgbox Address.Street 
msgbox Address.State 
msgbox Address.Zip 

The following code accesses all of the attribute values:

For I=1 to Address.Count 
   msgbox Address(I) 
Next I 

Modifying Attributes of an Embedded/Value Instance

If the object instance is retrieved using a dynaset object, its attribute values can be modified between a dynaset Edit/Update pair. The following example modifies the street and city attribute values of the ADDRESS object instance.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)
set Person = hrDb.CreateDynaset("select * from person", 0&)
set Address = Person.Fields("Addr").Value
Person.Edit
      Address.Street  =  "Oracle Parkway"
      Address.City = "Redwood shores"
Person.Update

Executing a Member Method of an Oracle Object Instance

Oracle object type member methods are created during type creation. Oracle object instance member methods are executed in OO4O as PL/SQL procedures or functions. Arguments and return values to the member methods should be bound using the OraParameter object. The first argument to the member method should always be the object instance. This object instance can be bound with the ORAPARM_INPUT or ORAPARM_BOTH mode. If the member method modifies the attributes of an object instance and a new object instance needs to be retrieved to the OO4O application, then this object instance must be bound with the ORAPARM_BOTH mode.

For example, if a bank_account object type has open, close, and deposit as member methods, then the schema for the bank_account object type is the following:

CREATE OR REPLACE TYPE bank_account AS OBJECT ( 
    acct_number INTEGER(5), 
    balance REAL, 
    MEMBER PROCEDURE open (amount IN REAL), 
    MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), 
    MEMBER PROCEDURE deposit (SELF IN OUT bank_bccount,num IN 
                        INTEGER, amount IN REAL), 
); 

In OO4O, BankObj is an OraObject object representing a valid bank object instance from the database. To execute the deposit method, the SELF, num, and amount arguments need to be bound using the OraParameter object.

Dim BankObj as OraObject 
assumes that we have valid BankObj 
set BankObj = ..... 
 
'create a OraParameter object for bank_account object and set it to BankObj 
OraDatabase.Parameters.Add "BANK", BankObj, ORAPARM_BOTH, ORATYPE_OBJECT, _
                     "BANK_ACCOUNT" 
 
'create a OraParameter object for num argument and set the value to 100 
OraDatabase.Parameters.Add "ACCOUNT_NO", 100, ORAPARM_INPUT, ORATYPE_NUMBER 
 
'create a OraParameter object for amount argument and set the value to 1200 
OraDatabase.Parameters.Add "AMOUNT", 1200, ORAPARM_OUTPUT, ORATYPE_NUMBER
 
'display the balance from the bank object 
Bankobj.balance 
 
'now execute the PL/SQL block for member method execution 
OraDatabase.ExecuteSQL ("BEGIN BANK_ACCOUNT.DEPOSIT :BANK," & _ 
                    (":ACCOUNT_NO,:AMOUNT); END;") 
 
'get the modified bank object from the parameter 
set Bankobj  = OraDatabase.Parameters("BANK").Value 
 
'display the new balance 
Bankobj.balance 

About the OraRef Interface

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. The OraRef interface is derived from an OraObject interface through the containment mechanism in COM. REF objects are updated and deleted independently of the context from which they originated, such as dynasets. The OraRef interface also encapsulates the functionality for navigating through graphs of objects utilizing the Complex Object Retrieval Capability (COR) in Oracle Call Interface (OCI).

See Also:

"OraRef Object"

Using the OraRef Interface

This section demonstrates the creation of an object table named PERSON_TAB. The object table is based on the object type PERSONOBJ. Each reference to the rows of this object table is stored in an aperson REF type column of the CUSTOMERS table. The following code creates database schemas:

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
HRDb.ExecuteSQL("create type PERSONOBJ  as object ( name varchar2(20), " & _
                 "age number, addr ADDRESS)")
HRDb.ExecuteSQL("create table person_tab of personobj") 
HRDb.ExecuteSQL("insert into person_tab values('nasser',40," & _ 
                  "address('Wine Blvd', 'Pleasanton', 'CA', '94065'))") 
HRDb.ExecuteSQL("insert into person_tab values('Maha', 25, " & _
                  "address('Continental Way', 'Belmont', 'CA', '94002'))") 
HRDb.ExecuteSQL("insert into person_tab values('chris',30, " & _
               "address('First Street', 'San Francisco', 'CA' , '94123'))") 

The following code creates a CUSTOMERS table having an aperson REF column referencing rows of the object table:

HRDb.ExecuteSQL("create table CUSTOMERS (account number, 
aperson REF personobj)") 
 
HRDb.ExecuteSQL("insert into customers values(10, null)") 
HRDb.ExecuteSQL("insert into customers values(20, null)") 
HRDb.ExecuteSQL("insert into customers values(30, null)") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'nasser') where account = 10") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'Maha') where account = 20") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'chris') where account = 30") 

The following topics discuss manipulating the OraRef Interface:

See Also:

OraRef Object

Retrieving a REF from the Database

An OraRef object can be retrieved using OO4O in the following ways:

Using a Dynaset Object

If a table contains a REF type column and a dynaset query selects against that column, then the Value property of the OraField object returns an OraREF. The following example selects an aperson column from the person table, and the aperson object is retrieved from the OraField object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("aperson").Value 
Using an OraParameter Object

If a SQL statement or PL/SQL block has a bind variable of REF type, you create an OraParameter object using the OraParameters Add method. The Value property of the OraParameter object for that bind variable returns an OraREF.

The example illustrates using a REF object data type as a bind variable in a PL/SQL anonymous block. The block selects an object column from the database.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSONOBJ"
 
'execute the sql statement which selects Address from the person_tab 
hrDb.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _ 
                "where account = 10; end;") 
 
'retrieve Person object from the OraParameter 
set Person = hrDb.Parameters("PERSON").Value 

See Also:

OraRef Object

Accessing Attributes of a Referenceable Instance

Before accessing attributes of a referenceable instance, it should be fetched (pinned) on the client side. OO4O implicitly pins the REF value when attribute values are accessed from the OraRef object. After the pin operation, attributes of the referenceable instance are accessed in the same manner as attributes of a value instance represented by the OraObject object.

The following example pins the APERSON REF value (implicitly) and accesses its name and address attributes. Note that accessing the address attribute returns an Address OraObject object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("APERSON").Value 
msgbox Person.Name 
set Address = Person.Addr 
msgbox Address.City 

See Also:

OraRef Object

Modifying Attributes of a Referenceable Instance

Because a referenceable instance is stored in a row of an object table, modifying attributes of referenceable instance requires an object lock. Therefore, rows corresponding to the object instance in an object table should be locked, which can be done by calling the Edit method of the OraRef object. The OraRef Update method releases the object lock.

The following example modifies the age attribute of Person object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("APERSON").Value 
Person.Edit 
Person.Age = 45 
Person.Update 

Oracle Collections

A collection is an ordered group of elements, all of the same type. Each element has a unique subscript, called an index, that determines its position in the collection.

Note:

An OraCollection element index starts at 1.

A collection can be subdivided into the following types:

  • Nested table type

    Viewed as a table stored in the column of a database table. When retrieved, the rows of a nested table are given consecutive subscripts starting at 1, and individual rows are accessed using array-like access.

  • VARRAY type

    Viewed as an array stored in the column of a database table. To reference an element in a VARRAY type, standard subscripting syntax can be used. For example, Grade(3) references the third element in VARRAY Grades.

In Oracle Objects for OLE, an Oracle collection type is represented by the OraCollection interface. The following topics provide more information:

About the OraCollection Interface

The OraCollection interface provides methods for accessing and manipulating Oracle collection types, namely variable-length arrays (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

Retrieving a Collection Type Instance from the Database

A collection type can be retrieved using OO4O in the following ways:

Using a Dynaset Object

If a table contains a collection type column and a dynaset query selects against that column, then the Value property of the OraField object returns an OraCollection object.

The following example selects the ENAMES column from the department table, and an EnameList object is retrieved from the OraField object:

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
Set Dept = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = Dept.Fields("ENAMES").Value 

Using a Parameter Object

If a SQL statement or PL/SQL block has a bind variable of collection type, then you create a OraParameter object using the OraParameters Add method. The Value property of the OraParameter object for that bind variable returns an OraCollection object.

The following example uses a collection data type as a bind variable in a PL/SQL anonymous block and selects a collection type from the database:

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, ORATYPE_VARRAY, "ENAMELIST" 
hrDb.ExecuteSQL ("BEGIN select enames into :ENAMES from department" & _ 
              "where dept_id = 10; END;") 
set EnameList = hrDb.Parameters("ENAMES").Value 

Accessing Collection Elements

Individual element values are accessed by using a subscript. For example, the Value returned by the OraCollection object for subscript 1 is the element value at index 1. The maximum value of the subscript is equal to the total number of elements in the collection including any deleted elements. The OraCollection subscript starts from 1.

The following example code retrieves the Enamelist collection instance and accesses its elements at the first and second index.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
Set Dept = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = Dept.Fields("ENAMES").Value 
msgbox EnameList(1) 
msgbox EnameList(2) 

This code displays all the element values of the EnameList collection.

For I = 1 to EnameList.Size 
   msgbox EnameList(I) 
Next I 

Modifying Collection Elements

If the collection instance is retrieved using a dynaset object, element values can be modified between a dynaset Edit and Update pair. The following example code modifies the second element value of an Enamelist collection instance.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
 
Set OraDynaset = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = OraDynaset.Fields("ENAMES").Value 
 
OraDynaset.Edit 
    EnameList(2) = "Chris" 
OraDynaset.Update 

Creating a VARRAY Collection Type

The example code that follows creates a VARRAY collection type ENAMELIST and a department table having ENAMELIST collection type column.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("Exampledb", "scott/tiger", 0)
hrDb.ExecuteSQL("CREATE TYPE ENAMELIST AS VARRAY(20) OF VARCHAR2(30)") 
hrDb.ExecuteSQL("CREATE TABLE department (dept_id NUMBER(2),name" & _ 
               "VARCHAR2(15),ENAMES ENAMELIST)") 

The following script inserts some collection data into department table:

hrDb.ExecuteSQL("INSERT INTO department VALUES(10, 'ACCOUNTING'," & _ 
               "ENAMELIST('KING','CLARK','MILLER') )") 
hrDb.ExecuteSQL("INSERT INTO department VALUES(20, 'RESEARCH'," & _ 
               "ENAMELIST('JONES','SCOTT','ADAMS','SMITH','FORD') )") 
hrDb.ExecuteSQL("INSERT INTO department VALUES(30, 'SALES'," & _ 
               "ENAMELIST('BLAKE','MARTIN','ALLEN','TURNER','JAMES') )")

Creating a Dynaset from an OraCollection Object

A SELECT query can be issued against instances of the VARRAY and nested table collection types using SQL THE or TABLE operators and individual elements can be accessed as rows. If these collection types have object types for element types, then individual attributes of the object type represents fields of a row.

For example, if an object type X has attributes a, b, and c, and the element type of the collection is object type X, then the SELECT query on this collection returns a, b, and c fields.

In OO4O, read-only dynaset objects can be created from SELECT queries on the collection. Individual elements are accessed using row navigation. If the collection type has an object type as its element type, then attributes of that object type (element) are accessed using the OraField object.

This discussion assumes you have a Course object type and a CourseList nested table collection type with Course as its element type, as described here:

CREATE TYPE Course AS OBJECT ( 
    course_no NUMBER(4), 
    title VARCHAR2(35), 
    credits NUMBER(1) 
); 
CREATE TYPE CourseList AS TABLE OF Course;

In OO4O, CourseList OraCollection represents an instance of the CourseList collection type.

Dim CourseList as OraCollection 

Assume that you have valid a CourseList collection instance:

set CourseList = ...... 

The SQL THE or TABLE operator needs collection type as a bind variable. Create a OraParameter object for the CourseList OraCollection as follows:

OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
            ORATYPE_TABLE, "COURSELIST" 

Create a read-only dynaset based on the CourseList using the SQL THE operator:

Set CourseListDyn = OraDatabase.CreateDynaset("select * from THE (select" & _
           "CAST(:COURSELIST AS COURSELIST) from dual)", ORADYN_READONLY)

You can also create a read-only dynaset based on the CourseList using the SQL TABLE operator, which is available only in OO4O with libraries from release Oracle9i and on:

Set CourseListDyn = OraDatabase.CreateDynaset("select * from" & _
          "TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY) 

'display the course_no field 
msgbox CourseListDyn.Fields("course_no").Value 

'display the title field 
msgbox CourseListDyn.Fields("title").Value 

'move to next row 
OraDynaset.MoveNext 

Example: Creating a Dynaset from an OraCollection Object

The following example illustrates how to create a dynaset from an OraCollection object. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples".

Dim OraSession as OraSession 
Dim OraDatabase as OraDatabase 
Dim OraDynaset as OraDynaset 
Dim CourseList as OraCollection 
Dim Course as OraObject 
Dim CourseListDyn as OraDynaset 
 
'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 a dynaset object from division 
set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&) 
 
'retrieve a Courses column from Division. Here Value property 
'of OraField object 'returns CourseList OraCollection 
set CourseList = OraDynaset.Fields("Courses").Value 
 
'create a input parameter for CourseList for nested table dynaset 
OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
                 ORATYPE_TABLE, "COURSELIST" 
 
'create a read only dynaset based on the CourseList. 
Set CourseListDyn = OraDatabase.CreateDynaset("select * from" & _ 
                 "THE(select CAST(:COURSELIST AS COURSELIST) from dual)", _
                 ORADYN_READONLY) 
 
'dynaset can also be created from Oracle8 collection using the
'following statement 
'Set CourseListDyn = OraDatabase.CreateDynaset("select * from    
'TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY) 

'get the field values of the collection dynaset 
msgbox CourseListDyn.Fields("title").Value 
msgbox CourseListDyn.Fields("course_no").Value 
 
'move the original dynaset to second row 
Oradynaset.MoveNext 
 
'set the new value of CourseList  collection from the second row 
'of main dynaset to the "COURSELIST" parameter 
OraDatabase.Parameters("COURSELIST").Value = CourseList 
 
'refresh the collection dynaset. Now the collection dynaset values are refreshed 
'with new collection value. CourseListDyn.Refresh 
'get the field values of the collection dynaset 
msgbox CourseListDyn.Fields("title").Value 
msgbox CourseListDyn.Fields("course_no").Value 

Advanced Queueing Interfaces

Oracle Objects for OLE provides the OraAQ Automation interface with methods for enqueuing and dequeuing messages. The OraAQMsg object contains the message to be enqueued or dequeued. The message can be a RAW message or any user-defined type.

The following examples illustrate how to enqueue RAW messages from the DBQ queue. Note that the DBQ queue must already be created in the database.

Dim Q as OraAQ 
Dim Msg as OraAQMsg 
set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set empDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
Set Q = empDb.CreateAQ("DBQ") 
Retrieve the message object from the Q object. 
set Msg = Q.AQMsg 
Specify the message value. 
Msg.Value = "This is the first Test message" 
Enqueue the message. 
Q.Enqueue 

The following lines enqueue a high priority message.

Msg.Priority = ORAQMSG_HIGH_PRIORITY 
Msg.Delay = 5 
Msg.Value = "Urgent message" 
Q.Enqueue 

The following example dequeues the RAW messages from Oracle Database and displays the message content.

Q.Dequeue 
MsgBox Msg.value 
Dequeue and display the first high priority message 
Msg.Priority = ORAQMSG_HIGH_PRIORITY 
Q.Dequeue 
MsgBox Msg.value 

See Also:

OraAQ Object

Monitoring Messages

The OraAQ monitor methods (MonitorStart and MonitorStop) provide asynchronous dequeuing through notifications. This is suitable for applications that prefer to process messages in nonblocking mode. Applications can request to be notified on arrival of messages, by supplying an Automation object to the Monitor method. This object implements a method called NotifyMe to receive notifications. Messages can be monitored based on consumer name, message ID, or correlation.

The following sample code demonstrates a simple use of this facility. It illustrates a computerized trading system that executes buy/sell limit orders.

The sample instantiates a queue object for the STOCKS_TO_TRADE queue and monitors messages intended for consumer BROKER_AGENT. STOCKS_TO_TRADE queues messages of the user-defined type TRADEORDER_TYPE. This encapsulates all the information required to initiate a trade order. When messages addressed to the BROKER_AGENT are dequeued, the NotifyMe method of the CallbackClient object is invoked, and a stock trade is performed.

'First instantiate the CallbackClient. The queue monitor 
' will invoke the NotifyMe on this class module.
Public CB_Client As New CallbackClient 
 
  Dim DB As OraDatabase
  Dim Q as OraAQ
  set Q = DB.CreateAQ("STOCKS_TO_TRADE") 

'Notify by calling cbclient::NotifyMe when there are messages
' for consumer '"BROKER_AGENT"
  Q.consumer = "BROKER_AGENT"

'Note that cbclient is a dispatch interface that supports the NotifyMe method.
  Dim s as string
  s = "BROKER_AGENT"
 'Notify the client only when there are messages for "BROKER_AGENT"
  Q.MonitorStart CB_Client, Q, s, 1
'other processing is performed here... 
 
  Q.MonitorStop 
Return 
'Now implement the NotifyMe method of the CallbackClient class module
'and the necessary arguments that will contain the dequeued message
'NotifyMe is the callback interface defined by user. Ctx here is the
'Q object passed in at the time of MontiorStart.
Public sub NotifyMe (ByVal Ctx As Variant, ByVal Msgid As Variant )
  On Error GoTo NotifyMeErr
  Dim tradingSignal as OraAQMsg
  'Tradeorder contains details of the customer order
  Dim tradeorder as OraObject
  If IsNull(Msgid) Then
          MsgBox "No Message"
          'Get Error
          MsgBox OraDatabase.LastServerErrText
  Else
          mvarMsgid = Msgid
          Set tradingSignal = Ctx.AQMsg(1,"STOCK_TYPE","TRADER")
          set tradeorder = tradingSignal.Value

          'Tradeorder is the object of UDT "STOCK_TYPE"Access signal attribute 
          'of tradeorder as tradeorder("signal).Value or tradeorder!signal
          if (tradeorder!signal = "SELL")
            'Sell the stock
             SellStock(tradeorder!NoOfShares, tradeorder!Ticker, _
                    tradeorder!Price,  tradeorder!ValidUntil)
          else if (tradeorder!signal = "BUY")
             'Buy the stock
              BuyStock(tradeorder!NoOfShares,tradeorder!Ticker, _
                   tradeorder!Price,tradeorder!ValidUntil)
          end if
  End If
NotifyMeErr:
  Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method")
End Sub

Database Events

Oracle Database supports detection and run-time publication of database events.

The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications.

Users can enable the publication of the following events:

  • DML events (DELETE, INSERT, UPDATE)

  • DDL events (CREATE, ALTER, DROP)

  • Database events (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

The event publication subsystem is integrated with the AQ publish and subscribe engine.

See Also:

Oracle Database SQL Language Reference for a complete description of triggers for data and system events

Oracle Objects for OLE provides functionality to enable COM users to subscribe to Oracle Database events.

This feature supports asynchronous notification of database events to interested subscribers. Under this model, the client can subscribe to be notified of a database or system event, with each request stored as a subscription.

When the database event of interest fires, the subscriber is notified by the database event handler. The event handler was registered at the time of the event's subscription.

OO4O provides the OraSubscription object that represents the subscription to a database event and the OraSubscriptions collection that maintains a list of OraSubscription objects.

To subscribe to a database event, you must:

  • Create a subscription, based on the database event of interest.

  • Provide a database event handler. The database event handler should be an automation object that implements the NotifyDBEvents method. The NotifyDBEvents method is invoked by OO4O when the subscribed database events are fired.

  • Register the subscription, using the Register method.

Example: Registering an Application for Notification of Database Events

In the following example, an application subscribes for notification of database logon events (such as all logons to the database). When a user logs on to the database, the NotifyDBEvents method of the DBEventsHdlr that was passed in at the time of subscription is invoked. The context-sensitive information and the event-specific information are passed into the NotifyDBEvents method.

The DBEventsHdlr in this example is DBEventCls, which is defined later.

The main application is as follows:

' First instantiate the dbevent handler. The dbevent notification
' will fire the NotifyDBEvents on the callback handler.
 
Public DBEventsHdlr As New DBEventCls
Private Sub Form_Load()
    Dim gOraSession As Object
    Dim gOraSubscriptions As OraSubscriptions 
    Dim gOraDatabase As OraDatabase
 
    'Create the OraSession Object
    Set gOraSession = CreateObject("OracleInProcServer.XOraSession")
 
   'Create the OraDatabase Object by opening a connection to Oracle.
    Set gOraDatabase = gOraSession.DbOpenDatabase                      
             ("ora90.us.oracle.com", "pubsub/pubsub", 
              ORADB_ENLIST_FOR_CALLBACK)
    Set gOraSubscriptions = gOraDatabase.Subscriptions
    gOraSubscriptions.Add "PUBSUB.LOGON:ADMIN", DBEventsHdlr,
             gOraDatabase
    gOraSubscriptions(0).Register
    MsgBox "OK"
End Sub

The database event handler class that defines the NotifyDBEvents method is as follows:

Public countofMsgs as integer
Public Function NotifyDBEvents(Ctx As Variant, Payload As Variant )
    On error goto NotifyMeErr
 
    MsgBox "Retrieved payload " + Payload
   ' do something - here the subscription is unregistered after
   ' receiving 3 notifications
    countofMsgs = countofMsgs + 1
    If countofMsgs > 3 Then
        Ctx.Subscriptions(0).UnRegister
    End If
    Exit Sub
NotifyMeErr:
    Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method")
 
End Sub

See Also:

Application Failover Notifications

Application failover notifications can be used in the event of the failure of one database instance and failover to another instance. Because delay can occur during a failover, the application developer may want to inform the user that a failover is in progress, and request that the user stand by. Additionally, the session on the initial instance may have received some ALTER SESSION commands. These are not automatically replayed on the second instance. Therefore, the developer may want to replay these ALTER SESSION commands on the second instance.

Failover Notification Registration

To address the problems described, OO4O supports application failover notifications. To receive failover notifications, a notification handler must be registered with the MonitorForFailover method of the OraDatabase object. The notification handler must be an automation object (class module in Visual Basic) that implements the OnFailover method. An IDispatch pointer to this automation object must be passed in, along with any client-specific context, at the time of registering for failover notifications.

In the event of failover, the OnFailover method is invoked several times during the course of reestablishing the user's session. The first call to the OnFailover method of the notification handler occurs when the database first detects an instance connection loss. This is intended to allow the application to inform the user of an upcoming delay. If a failover is successful, a second call to the OnFailover method occurs when the connection is reestablished and usable. At this time, the client may want to replay the ALTER SESSION commands and inform the user that a failover has happened.

If a failover is unsuccessful, then the OnFailover method is called to inform the application that the failover will not take place.

An example of failover registration is included as part of the example in the next section.

See Also:

Enabling Failover

To enable failover notifications, the option ORADB_ENLIST_FOR_CALLBACK must be passed into the call to the OpenDatabase method.

Example: Failover Notification

The following sample shows a typical developer-defined OnFailover implementation and demonstrates how to register an application.

'Implement the OnFailover method of the FailoverClient class module and the 
' necessary arguments that will contain the dequeued message. Ctx  here is
' the application-defined context sensitive object that was passed
' in while registering with MonitorForFailover.
' An error of OO4O_FO_ERROR indicates that failover was unsuccessful, but the 
' application can handle the and retry failover by returning  
' a value of OO4O_FO_RETRY 
 
Public Function OnFailover(Ctx As Variant, fo_type As Variant,fo_event _
                     as variant, fo_OraDB as Variant) 
Dim str As String 

OnFailover=0 
str = Switch(fo_type = 1&, "NONE", fo_type = 2&, "SESSION", fo_type = _ 
                  4&, "SELECT") 
If IsNull(str) Then 
   str = "UNKNOWN!" 
End If 
If fo_event= OO4O_FO_ERROR Then 
   MsgBox "Failover error gotten. Retrying " 
   OnFailover = OO4O_FO_RETRY 
   End If 
If fo_event = OO4O_FO_BEGIN Then 
   MsgBox " Failing Over .... with failover type : " & str 
Else 
   MsgBox "Failover Called with event : " & fo_event 
End If 
End Function 

Registering the Application to Receive Failover Notifications

' First instantiate the Failover_Client. The Failover notification 
' will invoke the OnFailover on this class module 
 
Public Failover_Client As New FailoverClient 
Dim OraDatabase As OraDatabase 
Dim OraSession As OraSession 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
' Pass in the entire database name (ie., the entire Tnsnames entry 
' with the domain name)in the opendatabase call 
Set OraDatabase = OraSession.DbOpenDatabase("Exampledb.us.oracle.com", _ 
          "scott/tiger", ORADB_ENLIST_FOR_CALLBACK) 
OraDatabase.MonitorForFailover Failover_Client, OraDatabase

XML Generation

Oracle Objects for OLE support for XML enables you to extract data in XML format from an Oracle database.

Data in XML markup language can be integrated with other software components that support XML. Web servers can provide XML documents along with a style sheet, thus separating the data content from its presentation, and preserving the data in its native form for easy searching.

Using Extensible Stylesheet Language Transformations (XSLT), developers can reformat XML documents received from other businesses into their desired style.

For more information about XML, go to

http://www.w3.org/XML/

XML Generation Example

OO4O renders XML from the contents of any OraDynaset method based on a starting row number and continuing for up to a specified amount of rows. For example:

OO4O Code

Dim XMLString As String 
Dim startrow as Integer 
Dim maxrows as Integer 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 
Set OraDynaset = OraDatabase.CreateDynaset("select EMPNO, ENAME, COMM, JOB " & _
           "from  EMP", 0&) 
startrow = 4 
maxrows = 2 
 
'Output at most 2 rows beginning at row 4 
XMLString = OraDynaset.GetXML(startrow, maxrows) 

XML Output

<?xml version = "1.0"?> 
<ROWSET> 
<ROW id="4"> 
<EMPNO>7566</EMPNO> 
<ENAME>JONES</ENAME> 
<JOB>MANAGER</JOB> 
</ROW> 
<ROW id="5"> 
<EMPNO>7654</EMPNO> 
<ENAME>MARTIN</ENAME> 
<COMM>1400</COMM> 
<JOB>SALESMAN</JOB> 
</ROW> 
</ROWSET> 

The format of the XML can be customized through the OraDynaset and OraField methods:

Dim XMLString As String 
Dim startrow as Integer 
Dim maxrows as Integer 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 
Set OraDynaset = OraDatabase.CreateDynaset("select EMPNO, ENAME, COMM," & _ 
            "JOB from EMP", 0&)  
 
'Change the root tag of the XML document 
OraDynaset.XMLRowsetTag = "ALL_EMPLOYEES" 
 
'Change the row tag of the XML document 
OraDynaset.XMLRowTag = "EMPLOYEE" 
 
'Remove the rowid attribute 
OraDynaset.XMLRowID = "" 
 
'Turn on the null indicator 
OraDynaset.XMLNullIndicator = True 
 
'Change the EMPNO tag name 
Set EmpnoField = OraDynaset.Fields("EMPNO") 
EmpnoField.XMLTagName = "EMP_ID" 
 
'and make it an attribute rather than an element 
EmpnoField.XMLAsAttribute = True 
 
'Change the ENAME tag name 
Set EnameField = OraDynaset.Fields("ENAME") 
EnameField.XMLTagName = "NAME" 
 
'Change the COMM tag name 
Set CommField = OraDynaset.Fields("COMM") 
CommField.XMLTagName = "COMMISSION" 
 
'Change the JOB tag name 
Set JobField = OraDynaset.Fields("JOB") 
JobField.XMLTagName = "JOB_TITLE" 
startrow = 4 
maxrows = 2 
 
'Output at most 2 rows beginning at row 4 
XMLString = OraDynaset.GetXML(startrow, maxrows) 

Output

<?xml version = "1.0"?> 
<ALL_EMPLOYEES> 
<EMPLOYEE EMP_ID="7566"> 
<NAME>JONES</NAME> 
<COMMISSION NULL="TRUE"></COMMISSION> 
<JOB_TITLE>MANAGER</JOB_TITLE> 
</EMPLOYEE> 
<EMPLOYEE EMP_ID="7654"> 
<NAME NULL>MARTIN</NAME> 
<COMMISSION>1400</COMMISSION> 
<JOB_TITLE>SALESMAN</JOB_TITLE> 
</EMPLOYEE> 
</ALL_EMPLOYEES> 

Datetime and Interval Data Types

From Release 9.2.0.4 and later, OO4O provides four new objects that enable developers to access and manipulate the new datetime and interval data types introduced in Oracle9i. Table 4-3 describes the OO4O objects and matching data types.

Table 4-3 Datetime and Interval Data Types

OO4O Objects Oracle Data Types

OraIntervalDS

INTERVAL DAY TO SECOND

OraIntervalYM

INTERVAL YEAR TO MONTH

OraTimeStamp

OraTimeStamp

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

OraTimeStampTZ

TIMESTAMP WITH TIME ZONE


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.

These new data types are not supported as elements in collections such as PL/SQL indexed tables, VARRAYs, or nested tables.

Obtaining Datetime and Interval Data Types

OO4O datetime and interval data types can be obtained using:

  • The Value property of an OraField object in a dynaset.

  • The Value property of an OraParameter object as an input or an output parameter in SQL statements or PL/SQL blocks.

  • An attribute of another object or REF.

  • The following OraSession methods:

    • CreateOraIntervalDS

    • CreateOraIntervalYM

    • CreateOraTimeStamp

    • CreateOraTimeStampTZ

Descriptions of Datetime and Interval Data Types

  • OraTimeStamp object

    Provides methods for operations on Oracle TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE data types. Operations include accessing the datetime values and performing datetime operations.

  • OraTimeStampTZ object

    Provides methods for operations on Oracle TIMESTAMP WITH TIME ZONE data types. Operations include accessing the datetime and time zone values and performing datetime operations.

  • OraIntervalDS object

    Provides methods for operations on the Oracle INTERVAL DAY TO SECOND. This data type represents a period of time in terms of days, hours, minutes, seconds, and nanoseconds.

  • OraIntervalYM object

    Provides methods for operations on the Oracle INTERVAL YEAR TO MONTH. This data type represents a period of time in terms of years and months.

Database Schema Objects

The OraMetaData interface provides access to the schema information of database objects. It is returned by invoking the Describe method of the OraDatabase interface. The Describe method takes the name of a schema object, such as the emp table and returns an OraMetaData object. The OraMetaData object provides methods for dynamically navigating and accessing all the attributes (OraMDAttribute collection) of a schema object described.

The following Visual Basic script shows a simple example of the OraMetaData interface. The sample retrieves and displays several attributes of the emp table.

Dim empMD as OraMetaData 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set empDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 

'Add EMPNO as an Input parameter and set its initial value. 
Set empMd = empDb.Describe("emp")
 
'Get the column attribute collections. 
Set empColumnsMd = empMd("ColumnList").Value 

'Display name, data type, and size of each column in the emp table. 
For I = 0 To empColumnsMd.Count - 1 
   Set ColumnMd = empColumnsMd(I).Value 
   MsgBox ColumnMd("data type").Value 
   MsgBox ColumnMd("Name").Value 
Next I