This chapter describes advanced Oracle Objects for OLE features.This chapter contains these topics:
Oracle Objects for OLE provides support for accessing and manipulating instances of REFs, value instances, variable-length arrays (VARRAY
s), 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
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).
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.
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.
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:
Table 4-2 lists the four LOB data types and their corresponding OO4O interfaces.
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. BFILE s 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.
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"
OraBlob
, OraClob
, and OraBFile
objects can be retrieved using an OraDynaset
object or a parameter 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
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
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.
The contents of a buffer are read or written to the database in one round-trip.
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.
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:
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
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
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:
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
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
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.
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
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:
An OraObject
object can be retrieved using OO4O using a dynaset or parameter 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
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
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
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
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
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).
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:
An OraRef
object can be retrieved using OO4O in the following ways:
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
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
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
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
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: AnOraCollection 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:
The OraCollection
interface provides methods for accessing and manipulating Oracle collection types, namely variable-length arrays (VARRAY
s) 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
A collection type can be retrieved using OO4O in the following ways:
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
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
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
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
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') )")
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
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
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
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 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
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.
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:
|
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
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
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>
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
|
TIMESTAMP
|
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, VARRAY
s, or nested tables.
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
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.
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