Skip Headers
Oracle® Objects for OLE Developer's Guide
10g Release 2 (10.2)

Part Number B14309-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

9 Server Objects

This chapter describes the Oracle Objects for OLE Server Objects.

See Also:

This chapter contains these topics:


OraAQ Object

Description

An OraAQ object is instantiated by invoking the CreateAQ method of the OraDatabase interface. It represents a queue that is present in the database.

Remarks

Oracle Objects for OLE provides interfaces for accessing Oracle Database Advanced Queuing (AQ) feature. It makes AQ accessible from popular COM-based development environments such as Visual Basic.

The OraAQ Automation interface provides methods for enqueuing and dequeuing messages (encapsulated in the OraAQMsg object). It also provides a method for monitoring queues for message arrivals.

Client applications provide a Dispatch interface to the monitor. The monitor checks the queue for messages that meet the application criteria. It then invokes the NotifyMe method of the Dispatch interface when these messages are dequeued.

The following diagram illustrates the OO4O AQ Automation objects and their properties.

Description of o4o00016.gif follows
Description of the illustration o4o00016.gif

Properties

Methods

Examples

Example: Enqueuing Messages

Enqueuing messages of type RAW

"Enqueuing Messages of Type RAW"

Enqueuing messages of Oracle object types

"Enqueuing Messages of Oracle Object Types"

Example: Dequeuing messages

NOTE: The following code samples serve as models for dequeuing messages.

A complete AQ sample can be found in \OO4O\VB\SAMPLES\AQ

Dequeuing messages of the RAW type

"Example: Dequeuing Messages of RAW Type"

Dequeuing messages of Oracle object types

"Example: Dequeuing Messages of Oracle Object Types"

Example: Monitoring messages

See "Monitoring Messages" for examples illustrating the use of the MonitorStart and MonitorStop methods.

See Also:


OraAQAgent Object

Description

The OraAQAgent object represents a message recipient and is only valid for queues that allow multiple consumers.

Remarks

An OraAQAgent object can be instantiated by invoking the AQAgent method. For example:

Set agent = qMsg.AQAgent(name)

Methods

None.

Properties

Example

The following Visual Basic example illustrates a simple use of the advanced queuing feature. A message of a user-defined type, MESSAGE_TYPE, is enqueued into a queue, msg_queue, that supports multiple consumers.

Dim q as OraAQ 
Dim qMsg as OraAQMsg 
Dim agent as OraAQAgent 
Set q = OraDatabase.CreateAQ("msg_queue") 
Set qMsg = q.AQMsg(1,"MESSAGE_TYPE") 
 
'To add SCOTT as a recipient for the message, 
Set agent = qMsg.AQAgent("SCOTT") 
 
'To enqueue, 
q.Enqueue 

See Also:


OraAQMsg Object

Description

The OraAQMsg object encapsulates the message to be enqueued or dequeued. The message can be of any user-defined or raw type.

Properties

Methods

See Also:


OraAttribute Object

Description

The OraAttribute object represents an attribute of a Value or REF instance of an OraObject or an OraRef.

Remarks

The OraAttribute object can be accessed from the OraObject or OraRef object by creating a subscript that uses ordinal integers or by using the name attribute.

See the Value (OraAttribute) property for a table that identifies the attribute type and the return value of the Value property of the OraAttribute object:

Properties

Methods

None.

Examples

The following example accesses the attributes of the ADDRESS value instance in the server. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim Address As OraObject 
Dim City As OraAttribute 
Dim State As OraAttribute 
 
'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 person_tab 
Set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)
 
'retrieve an address column from person_tab
'the Value property of OraField object returns Address OraObject 
Set Address = OraDynaset.Fields("Addr").Value 
 
'access the City attribute object 
Set City = Address("City") 
 
' display the value of City attribute Object 
MsgBox City.Value 
 
'access the State attribute object 
Set State = Address("State") 
 
'display the value of State attribute Object 
MsgBox State.Value 

OraBFILE Object

Description

The OraBFile interface in OO4O provides methods for performing operations on the BFILE LOB data type in the database.

Remarks

The BFILE types are large binary data objects stored in operating system files (external) outside of the database tablespaces.

Properties

Methods

Examples

See "Schema Objects Used in LOB Data Type Examples" for schema objects that are used in the OraLOB/BFILE examples.

NOTE: To add the required tables for the following examples, run the lob.sql file in the \OO4O\VB\SAMPLES\LOB directory.

Example: Accessing the BFILE Value

BFILE data can be read using the Read method. The OraBFILE object allows piecewise read operations. Before reading the BFILE content, the BFILE file should be opened using the Open method.

Dim PartColl as OraBFile 
Dim buffer As Variant 
 
'Create a Dynaset containing a BLOB and a CLOB column 
set part = OraDatabase.CreateDynaset ("select * from part",0) 
Set PartColl = part.Fields("part_collateral").Value 
 
'open the bfile for read operation 
PartColl.Open 
 
'read the entire bfile 
amount_read = PartColl.Read(buffer)
 
'close the bfile 
PartColl.Close 

Example: Reading and Inserting BFILEs Using Dynasets

To modify the directory and file names of the BFILE value of an OraBFILE object, first obtain a lock and then use the DirectoryName and FileName properties.

To insert a new row containing a BFILE column, initialize the BFILE column with new directory and file name values using the DirectoryName and FileName properties.

Dim PartColl as OraBFile 
Dim buffer As Variant 
 
'Create a Dynaset containing a BLOB and a CLOB column 
set part = OraDatabase.CreateDynaset ("select * from part",0) 
Set PartColl = part.Fields("part_collateral").Value 
 
'insert a new BFILE in the part_collateral column 
part.AddNew 
 
'Directory objects will be upper-case by default 
    PartColl.DirectoryName = "NEWDIRECTORYNAME"  
    PartColl.FileName = "NewPartCollatoral" 
part.Update 
 
'move to the newly added row 
part.MoveLast 
 
'open the Bfile for read operation 
PartColl.Open 
 
'read the entire bfile 
amount_read = PartColl.Read(buffer)
 
'close the Bfile 
PartColl.Close 

See Also:


OraBLOB, OraCLOB Objects

Description

The OraBLOB and OraCLOB interfaces in OO4O provide methods for performing operations in a database on the large object data types BLOB, CLOB, and NCLOB. In this developer's guide, BLOB, CLOB, and NCLOB data types are also referred to as LOB data types.

OO4O supports the creation of temporary BLOB or CLOB types that can be manipulated and then bound to SQL statements or PL/SQL blocks, or copied into permanent LOBs.

Remarks

LOB data is accessed using the Read and CopyToFile methods.

LOB data is modified using the Write, Append, Erase, Trim, Copy, CopyFromFile, and CopyFromBFile methods. A row lock must be obtained before modifying the contents of a LOB column in a row. If the LOB column is a field of an OraDynaset object, then the lock is obtained by invoking the Edit method.

None of the LOB operations are allowed on NULL LOBs. To avoid errors, use the IsNull property to detect NULL LOBs. To perform write operations on a LOB that is null, first the LOB column must be initialized with an Empty value.

To insert a new row having a LOB column, first initialize the LOB column with an Empty value by setting the Value property of the OraField or OraParameter object to the keyword Empty and commit the change to the database. The newly updated Empty LOB must be selected again from the database before it can be used. This is done automatically in the case of the OraDynaset object: If a LOB field in an OraDynaset object is set to Empty and the Update method is called, OO4O automatically reselects the Empty LOB into the dynaset making it available for use in subsequent write operations.

There are two modes of operation for read and write operations for LOBs.

  1. Multiple-piece read/write operations

    In this mode, the total amount of data to be read or written is more than the size of the buffer for an individual read/write operation. Rather than make a complete round-trip for each operation, the pieces are streamed. To begin the multiple piece operation, the PollingAmount property is first set to the total amount of data to be read or written. The Offset property is set at this time to specify the initial offset for the first piece read/write operation. The offset is automatically incremented after the first read/write operation, and cannot be changed again until the multiple piece operation has completed. The Status property must be checked for the success of each piecewise operation and the operation must continue until all the pieces are read or written (it cannot be aborted). To start another multiple-piece read/write operation on the same LOB, the PollingAmount property has to be reset to the desired amount. See "Example: Multiple-Piece Read of a LOB".

  2. Single-piece read/write operation

    In this mode, the reading and writing of data occurs in one operation. This mode is enabled when the PollingAmount property is set to 0. See "Example: Single-Piece Read of a LOB".

    The Offset property in both modes of operation is 1-based.

By design, LOBs cannot span transactions started by SELECT .. FOR UPDATE, INSERT, and UPDATE statements. Selecting or modifying LOB values using these SQL statements makes LOBs invalid outside the current transaction. In Oracle Objects for OLE, transactions can be started and ended in the following ways.

  1. Dynaset Edit/Update method

    The Edit method executes the SELECT FOR UPDATE statement to lock the row and start the transaction. The Update method ends the transaction. If the LOB column value is modifed between the Edit and Update pair, OO4O reselects the value of LOB column after the Update call. This is transparent to the user. Note that OO4O does not reselect the LOB value if the LOB is an attribute of an Oracle objects instance or element of an Oracle collection. If the transaction is started by the OraSession/OraDatabase or OraServer object and the LOB data is modified between the Edit and Update methods, OO4O does not reselect the LOB value from the database. LOBs are invalid after committing transactions initiated by OraSession/OraDatabase or OraServer objects.

    See "Example: Dynasets Containing LOBs and Transactions".

  2. Executing an INSERT or UPDATE statement through the ExecuteSQL or CreateSQL method.

    An INSERT or UPDATE statement starts the transaction, and the transaction is implicitly ended by Oracle Objects for OLE (auto-commit). If a statement has a LOB output bind parameter, as in the case of the RETURNING .. INTO clause, then it will become invalid after the ExecuteSQL or CreateSQL method is executed To avoid this, the user must execute these statement between the BeginTrans/CommitTrans pair of OraSession, OraServer or OraDatabase objects.

    See "Example: INSERT or UPDATE Statements with LOBs and Transactions".

See Also:

Properties

Methods

Examples

See "Schema Objects Used in LOB Data Type Examples" for schema objects that are used in the OraLOB and BFILE examples.

Example: Accessing a LOB Value

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim PartImage as OraBlob 
Dim buffer As Variant 
 
'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&) 
 
'execute the select statement 
set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&) 
 
'retrieve photo field from the dynaset 
set PartImage = OraDynaset.Fields("part_image").Value 
 
'read the entire LOB column in one piece into the buffer 
amount_read = PartImage.Read(buffer, 10) 
'use the buffer for internal processing 

Example: Modifying a LOB Value

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim  PartDesc as OraClob 
Dim buffer As String 
 
'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&) 
 
'execute the select statement 
set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&) 
set PartDesc = OraDynaset.Fields("part_desc").Value 
 
'To get a free file number 
FNum = FreeFile 
'Open the file for reading 
Open "partdesc.dat" For Binary As #FNum 
 
'Allocate buffer to the size of file FNum and read the entire file 
buffer = String$(LOF(FNum), 32) 
Get #FNum, , buffer 
 
'lock the row for write operation 
OraDynaset.Edit 
amount_written = PartDesc.Write(buffer) 
 
'commit the operation and release the lock 
OraDynaset.Update 
Close FNum 

Example: Inserting LOBs Using Dynasets

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim Part As OraDynaset 
Dim PartImage as OraBLOB 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
'Create a Dynaset containing a BLOB and a CLOB column 
 
set part = OraDatabase.CreateDynaset ("select * from part",0) 
set PartImage = part.Fields("part_image").Value 
 
'First insert Empty LOB in the part_image column 
part.AddNew 
    part.Fields("part_id").Value = 1234 
    part.Fields("part_image").Value = Empty 
part.Update 
 
'move to the newly added row 
Part.MoveLast 
 
'To get a free file number 
FNum = FreeFile 
 
'Open the file for reading PartImages 
Open "part_picture.gif" For Binary As #FNum 
 
'Re adjust the buffer size to hold entire file data 
 
Redim ImageChunk(LOF(FNum)) 
 
'read the entire file and put it into buffer 
Get #FNum, , ImageChunk 
 
'call dynaset's Edit method to lock the row 
part.Edit 
amount_written = OraBlob.Write(ImageChunk) 
part.Update 
 
'close the file
Close FNum 

Example: Inserting LOBs Using an OraParameter Object

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraBlob As OraBlob 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
Set OraParameters = OraDatabase.Parameters 
OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT 
OraParameters("PartImage").ServerType = ORATYPE_BLOB 
 
'BeginTrans needs to be called since LOB locators become 
'invalid after the ExecuteSQL call 
OraSession.BeginTrans 
OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle  Application'," & _
                "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage") 
set PartImage = OraDatabase.Parameters("PARTIMAGE").Value 
 
FNum = FreeFile 
'Open the file for reading PartImages 
Open "part_picture.gif" For Binary As #FNum 
 
'read the file and put it into buffer 
Redim ImageChunk(LOF(FNum)) 
Get #FNum, , ImageChunk 
 
Set OraBlob = OraDatabase.Parameters("PartImage").Value 
amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE) 
 
' commit the transaction and close the file
OraSession.CommitTrans 
Close FNum 

Example: Dynasets Containing LOBs and Transactions

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraBlob As OraBlob 
Dim PartImage as OraBLOB 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb",  "scott/tiger", 0&) 
                                     
'Create a Dynaset containing a BLOB and a CLOB column 
set part = OraDatabase.CreateDynaset ("select * from part " & _
                              "where part_id = 1234",0) 
set PartImage = part.Fields("part_image").Value 
 
'To get a free file number 
FNum = FreeFile 
 
'Open the file for reading PartImages 
Open "c:\part_picture.gif" For Binary As #FNum 
Redim ImageChunk(LOF(FNum)) 
 
'read the file and put it into buffer 
Get #FNum, , ImageChunk 
 
'starts the transaction on OraSession 
OraSession.BeginTrans 
 
'call dynaset's Edit method to lock the row 
part.Edit 
Set OraBlob = PartImage 
amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE) 
part.Update 
 
'ends the transaction 
OraSession.CommitTrans 
 
'the following lines of code will raise error  
'LOB locator cannot span transaction' 
msgbox Partimage.Size 
Close FNum 

Example: INSERT or UPDATE Statements with LOBs and Transactions

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
Set OraParameters = OraDatabase.Parameters 
OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT 
OraParameters("PartImage").ServerType = ORATYPE_BLOB 
 
'Create a Dynaset containing a LOB,column 
OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle Application'," & _ 
                  "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage") 
set PartImage = OraDatabase.Parameters("PARTIMAGE").Value 
 
'the following lines of code will raise error 
'LOB locator cannot span transaction' 
msgbox Partimage.Size 

Example: Using the CopyToFile Method

See "Example:Using the CopyToFile Method".

Example: Using the CopyFromFile Method

See "Example: Using the CopyFromFile Method".

Example: Multiple-Piece Read of a LOB

See "Example: Multiple-Piece Read of a LOB".

Example: Single-Piece Read of a LOB

See "Example: Single-Piece Read of a LOB".

Example: Multiple-Piece Write of a LOB

See "Multiple-Piece Write of a LOB Example".

Example: Single-Piece Write of a LOB

See "Single-Piece Write of a LOB Example".

Example: Passing a Temporary CLOB to a Stored Procedure

See "Example: Passing a Temporary CLOB to a Stored Procedure".


OraClient Object

Description

An OraClient object defines a workstation domain, and all of the OraSession objects of that workstation are listed in the OraSessions collection of the OraClient object.

Remarks

Only one OraClient object can exist for each workstation, and it is created automatically by the system when it is needed.

Properties

Methods


OraCollection Object

Description

The OraCollection interface represents Oracle collection types, such as variable-length arrays (VARRAYs) and nested tables.

Remarks

A collection is an ordered group of elements, all of the same type. For example, the students in a class or the grades for each student in a class. Each element has a unique subscript, called an index, that determines its position in the collection.

The collection type nested table is viewed as a table stored in the column of database tables. When retrieved, rows of a nested table are given consecutive subscripts that start at 1. Individual rows are accessed using an array-like access.

The collection type VARRAY is viewed as an array stored in the column of database tables. To reference an element in a VARRAY data type, standard subscripting syntax can be used. For example, Grade(3) references the third element in the VARRAY data type named Grades.

The OraCollection provides methods for accessing and manipulating an Oracle collection. Implicitly an OraCollection object contains an OLE Automation collection interface for accessing and manipulating (updating and inserting) individual elements of an Oracle collection. Individual elements can be accessed by using a subscript. An OraCollection element index starts at 1.

Element values are retrieved as Variant types. The Variant type of the element depends on the element type of the collection. Element values can be Null and can be set to Null. For elements of type objects and REFs, element values are returned as corresponding OO4O objects for that type. VARRAYs and nested tables do not support the elements of LOBs, VARRAYs, and Nested tables.

Table 9-1 lists the element type and return value of the elements.

Table 9-1 Element Type and Return Value of Elements

Element Type Element Value
Object OraObject
REF OraRef
Date String
Number String
CHAR, VARCHAR2 String
Real Real
Integer Integer

Element values are converted into a Variant SAFEARRAY format using the SafeArray property. Only elements of primitive types are supported. A Variant SAFEARRAY index starts at 0.

The CreateOraObject method on the OraDatabase object returns the OraCollection object. The Oracle collection associated with this OraCollection object is created in the client-side object cache.

For information about creating a dynaset from a collection, see to "Creating a Dynaset from an OraCollection Object".

Properties

Methods

Examples

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" for schema objects that are used in the OraCollection examples.

Example: Accessing Collection Elements

The following example illustrates how to access collection elements.

OraDynaset Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
 
'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 department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
 
'retrieve a Enames column from Department. 
'Here Value property of OraField object returns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
 
'access the first element of EnameList
msgbox  EnameList(1)
 
'move to next to row
OraDynaset.MoveNext
 
'access all the elements of EnameList for the second row
For index = 1 To EnameList.Size
        msgbox EnameList(index)
Next Index    

OraParameter Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameList as OraCollection
 
'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 an  OraParameter object representing EnameList collection bind Variable
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, _
                 ORATYPE_VARRAY,"ENAMELIST"
 
'execute the sql statement which selects ENAMES VARRAY  from the department  table
 
OraDatabase.ExecuteSQL ("BEGIN select enames  into :ENAMES  from department " & _
                   "where  dept_id = 10;  END;")
 
'get the EnameList collection  from OraParameter
set EnameList = OraDatabase.Parameters("ENAMES").Value
 
'access all the elements of EnameList 
For index = 1 To EnameList.Size
        msgbox EnameList(index)
Next Index    

Example: Modifying Collection Elements

The following example illustrates how to modify collection elements.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
 
'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 department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
 
'retrieve a Enames column from Department. Here Value property of OraField object
'returns EnameList OraCollection
 
set EnameList = OraDynaset.Fields("Enames").Value
 
'lock the row for editing and set the 2nd element of the EnameList to new value
OraDynaset.Edit
EnameList(2) = "Eric"
OraDynaset.Update

Example: Inserting in a Collection

The following example illustrates how to insert elements into an Oracle collection.

OraDynaset Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameListNew as OraCollection
 
'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 new OraCollection object from the database
set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")
 
'set EnameListNew's element values
EnameListNew(1)  = "Nasser"
EnameListNew(2) =  "Chris"
EnameListNew(3) =  "Gopal"
 
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
 
'start the AddNew operation and insert the EnameListNew collection 
OraDynaset.AddNew
OraDynaset.Fields("dept_id") = 40
OraDynaset.Fields("name") = "DEVELOPMENT"
 
'set the EnameListNew to enames column
OraDynaset.Fields("enames") = EnameListNew
OraDynaset.Update

OraParameter Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameListNew as OraCollection
 
'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 new OraCollection object from the database
set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")
 
'set EnameListNew's element values
EnameListNew(1)  = "Nasser"
EnameListNew(2) =  "Chris"
EnameListNew(3) =  "Gopal"
 
'create an input OraParameter object representing EnameList collection bind 'Variable
 
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_INPUT, ORATYPE_VARRAY, _ 
                "ENAMELIST"
 
'set the ENAMES parameter value to EnameListNew
OraDatabase.Parameters("ENAMES").Value  = EnameListNew
 
'execute the insert sql statement 
OraDatabase.ExecuteSQL ("insert into department values (40,'DEVELOPMENT', " & _ 
              ":ENAMES)")

Example: Collection with Object Type Elements

The following example illustrates the use of an Oracle collection having elements of object type.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim CourseList as OraCollection
Dim Course as OraObject
 
'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
'retrieve the element value of the CourseList at index 1. 
'Here element value is returned as Course OraObject
set Course = CourseList(1)
 
'retrieve course_no and title attribute of the Course
msgbox Course.course_no
msgbox Course.title
 
'move to next row
OraDynaset.MoveNext
 
'now CourseList object represents collection value for the second row 
'and course OraObject 'represents the element value at index 1. 
'retrieve course_no and title attribute of the Course.
msgbox Course.course_no
msgbox Course.title

Example: Creating a SAFEARRAY Variant from a Collection

The following example illustrates how to get and set a SAFEARRAY Variant with an Oracle collection.

Creating SAFEARRAY Variant from a Collection

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
Dim EnameArray as Variant
 
'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 department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
 
'retrieve a Enames column from Department. 
'Here Value property of OraField objectreturns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
 
'get the Variant SAFEARRAY from the collection. 
EnameArray = EnameList.SafeArray
 
'display the individual elements of EnameArray
msgbox EnameArray(0)
msgbox EnameArray(1)
msgbox EnameArray(2)

Setting SAFEARRAY Variant to the Collection

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameList as OraCollection
Dim EnameArray() As String
ReDim EnameArray(3)
 
'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 an Empty uninitialized input OraParameter object 
'represent EnameList collection bind Variable
OraDatabase.Parameters.Add "ENAMES", Empty, ORAPARM_INPUT, _
                            ORATYPE_VARRAY,"ENAMELIST"
 
'get the Empty uninitialized ENAMES parameter value
set EnameList = OraDatabase.Parameters("ENAMES").Value 
 
'initialize the EnameArray
EnameArray(0) = "Nasser"
EnameArray(1) = "Chris"
EnameArray(2) = "Gopal"
 
'set the EnameArray to EnameList's SafeArray
EnameList.SafeArray = EnameArray
 
'execute the insert sql statement 
OraDatabase.ExecuteSQL ("insert into department " & _
                "values (40,'DEVELOPMENT', :ENAMES)")

Example: Creating a Dynaset from a Collection

The following example illustrates how to create a dynaset from an Oracle collection.

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, which requires OO4O v8.1.x later
 
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

Example: Collection Iterator

See "Example: OraCollection Iterator" .


OraConnection Object

Description

An OraConnection object represents a single connection to an Oracle database.

Remarks

An OraConnection object is created automatically whenever an OraDatabase object is instantiated within the session, and it is destroyed automatically whenever all databases using the connection are discarded.

Currently, there is no way to create an OraConnection object explicitly, only by creating an OraDatabase object that requires a connection.

Properties

Methods


OraDatabase Object

Description

An OraDatabase interface represents a user session to an Oracle database and provides methods for SQL and PL/SQL execution.

Remarks

An OraDatabase interface in Oracle8i and higher releases adds additional methods for controlling transactions and creating interfaces representing instances of Oracle object types. Attributes of schema objects can be retrieved using the Describe method of the OraDatabase interface.

In previous releases, an OraDatabase object is created by invoking the OpenDatabase method of an OraSession interface. The network alias, user name, and password are passed as arguments to this method. In Oracle8i and higher releases, invocation of this method results in implicit creation of an OraServer object.

As described in the OraServer interface description, an OraDatabase object can also be created using the OpenDatabase method of the OraServer interface.

Transaction control methods are available at the OraDatabase (user session) level. These methods include:

For example:

MyDatabase.BeginTrans 
MyDatabase.ExecuteSQL("delete from emp where empno = 1234") 
MyDatabase.CommitTrans 

Note:

If the AutoCommit property is set to True, transactions are committed automatically, and you do not need to use the transaction control methods.

Properties

Methods


OraDynaset Object

Description

An OraDynaset object permits browsing and updating of data created from a SQL SELECT statement.

Remarks

An OraDynaset object represents the result set of a SQL SELECT query or a PL/SQL cursor variable returned from a stored procedure or function. It is essentially a client-side scrollable and updatable cursor that allows browsing the set of rows generated by the query it executes. It is created by the CreateDynaset or CreateCustomDynaset method of an OraDatabase interface. An OraDynaset object can be used to scroll result sets that contain instances of relational and object-relational columns such as VARRAYs, nested tables, Objects, REFs, and LOBs and BFILE types.

This object provides transparent mirroring of database operations, such as updates. When data is updated through the Update method, the local mirror image of the query is updated so that the data appears to have been changed without reevaluating the query. The same procedure is used automatically when records are added to the dynaset. Integrity checking is performed to ensure that the mirrored image of the data always matches the actual data present on Oracle Database. This integrity checking is performed only when necessary (such as just before updates occur).

During create and refresh operations, the OraDynaset objects automatically bind all relevant enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can simplify dynamic query building and increase the efficiency of multiple queries using the same SQL statement with varying WHERE clauses.

When you use Oracle Objects for OLE, locks are not placed on data until an Edit method is executed. The Edit method attempts to obtain a lock using the "SELECT ... FOR UPDATE" statement on the current record of the dynaset. This is done as late as possible to minimize the time that locks are placed on the records. The Edit method can fail for several reasons:

Properties

Methods


OraField Object

Description

An OraField object represents a single column or data item within a row of a dynaset.

Remarks

An OraField object is accessed indirectly by retrieving a field from the OraFields collection of an OraDynaset object.

If the current row is being updated, then the OraField object represents the currently updated value, although the value may not yet have been committed to the database.

Assignment to the Value property of a field is permitted only if a record is being edited (using the Edit method) or a new record is being added (using the AddNew method). Other attempts to assign data to the Value property of a field results in an error.

Properties

Methods


OraIntervalDS Object

Description

The 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.

Remarks

The OraIntervalDS object is created by the OraSession.CreateOraIntervalDS method or by calling the Clone method on an existing OraIntervalDS object.

An OraIntervalDS object can be bound using the ServerType ORATYPE_INTERVALDS. This allows the binding of a value to a parameter associated with an Oracle INTERVAL DAY TO SECOND data type in a SQL or PL/SQL statement.

When binding a string associated with an INTERVAL DAY TO SECOND data type, the ServerType must be specified to be a string type (for example, ORATYPE_VARCHAR2, ORATYPE_STRING) and the string must be in the format specified by Day HH:MI:SSxFF.

Properties

Methods


OraIntervalYM Object

Description

The 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.

Remarks

The OraIntervalYM object is created by the OraSession.CreateOraIntervalYM method or by calling the Clone method on an existing OraIntervalYM object.

An OraIntervalYM object can be bound using ServerType ORATYPE_INTERVALYM. This allows the binding of a value to a parameter associated with an Oracle INTERVAL YEAR TO MONTH data type in a SQL or PL/SQL statement.

When binding a string associated with an INTERVAL YEAR TO MONTH data type, the ServerType must be specified to be a string type (for example, ORATYPE_VARCHAR2, ORATYPE_STRING), and the string must be in the format specified by YEARS-MONTHS.

Properties

Methods


OraMDAttribute Object

Description

Each OraMDAttribute object describes an individual attribute. It represents an entry to the attribute table of the OraMetaData object. It can be accessed by creating a subscript that uses ordinal integers or by using the name of the attribute.

Remarks

None.

Properties

Methods

None.

Examples

See "Schema Objects Used in OraMetaData Examples" for OraMetaData Schema Definitions used in these examples.

Example: Describing a Table

See "Describing a Table Example".

Example: Describing a User-Defined Type

See "Example: Describing a User-Defined Type".

Example: Describing Unknown Schema Objects

See "Example: Describing Unknown Schema Objects".


OraMetaData Object

Description

The OraMetaData object 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.

An OraMetaData object is a collection of OraMDAttribute objects that represent the description information about a particular schema object in the database. The following table is an example of attributes for a OraMetaData object of type table (ORAMD_TABLE).

Table 9-2 list the ORAMD_TABLE attributes.

Table 9-2 ORAMD_TABLE Attributes

Attribute Name Value Type Description
ObjectID Integer Object ID.
NumCols Integer Number of columns.
ColumnList OraMetaData Column list.
IsTyped Boolean Is the table typed?
IsTemporary Boolean Is the table temporary?
Duration String Duration - can be session, transaction, or null.
DBA Integer Data block address of the segment header.
TableSpace Integer Tablespace in which the table resides.
IsClustered Boolean Is the table clustered?
IsPartitioned Boolean Is the table partitioned?
IsIndexOnly Boolean Is the table index-only?

Remarks

The OraMetaData object can be visualized as a table with three columns:

The OraMDAttribute objects contained in the OraMetaData object can be accessed by creating a subscript that uses ordinal integers or by using the name of the property. Referencing a subscript that is not in the collection (0 to Count-1) results in the return of a NULL OraMDAttribute object.

Properties

Methods

Examples

See "Schema Objects Used in OraMetaData Examples" for OraMetaData schema definitions used in these examples.

The following Visual Basic example illustrates a simple use of this facility. It retrieves and displays several attributes of the emp table.

Set empMD = OraDatabase.Describe("emp") 
 
'Display the name of the Tablespace 
msgbox empMD("tablespace") 
 
'Display name, data type, and size of each column in the emp table. 
Set empColumnsMD = empMD("Columns") 
for I = 1 to empColumns.Count 
   Set ColumnMD = empColumnsMD(I) 
   MsgBox ColumnMD("Name") & ColumnMD("Data Type") & ColumnMD("Length")
Next I

Example: Describing a User-Defined Type

See "Example: Describing a User-Defined Type"

Example: Describing Unknown Schema Objects

See "Example: Describing Unknown Schema Objects"


OraNumber Object

Description

The OraNumber interface provides methods for operations on the Oracle Number data types. This interface exposes a set of math operations that provide greater precision than is available in some programming environments, such as Visual Basic.

Remarks

The OraNumber object can be obtained through the CreateOraNumber method of the OraSession object or by calling the Clone method on an existing OraNumber.

All of the methods of the OraNumber object that take a numeric argument accept a string, another numeric type, such as a long in Visual Basic, or another OraNumber object.

Note:

If a Visual Basic numeric value (or constant) is used as an argument, it is limited to the maximum precision provided by the language.

The OraNumber on which the math operation is called holds the result of the operation (overwriting any previous value). If a Format was specified (through the Format property), the value of an OraNumber must match this format or an error is raised when the Value property is accessed.

Properties

Methods

Example

A scientific calculator example program is included as part on the samples installed with Oracle Objects for OLE. See "Demonstration Schema and Code Examples".


OraObject Object

Description

The OraObject interface is a representation of an Oracle value instance (non-referenceable object instance or embedded objects). Value instances are instances of an Oracle object type stored in the column of a table or attribute of an another Oracle object instance or element of an Oracle collection.

Remarks

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

The OraObject attribute index starts at 1. The Count property returns the total number of attributes. Each attribute of the underlying value instance is represented as an OraAttribute object.

Attribute values are retrieved as variants. The Variant type of the attribute depends on the attribute type of the object. Attribute values can be null and can be set to Null. For object types REF, LOB, and collection, attribute values are returned as corresponding OO4O objects for that type.

The CreateOraObject method on the OraDatabase object returns the OraObject object. The value instance associated with this OraObject object is created in the client-side object cache.

For information about executing a member method of a value instance, see "Executing a Member Method of an Oracle Object Instance".

For information about initializing an OraObject object representing a value instance in OO4O or executing a member method of a value instance, see "Instantiating Oracle LOBs, Objects, and Collections".

Properties

Methods

Examples

See "Schema Objects Used in the OraObject and OraRef Examples" for schema descriptions used in examples of OraObject/OraRef objects.

Example: Accessing Attributes of an OraObject Object

The following example accesses the attributes of the ADDRESS value instance in the database.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim Address as OraObject
 
'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 person_tab
set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab",0&)
 
'retrieve a address column from person_tab. Here Value property of OraField  
'object returns Address OraObject 
set Address = OraDynaset.Fields("Addr").Value
 
'access the attribute by dot notation
msgbox Address.Street
 
'access the attribute using '!' notation ( early binding application) 
msgbox Address!Street
 
'access the attribute by index
msgbox Address(1)
 
'access the attribute by name
msgbox Address("Street")
 
'access all the attributes of Address OraObject in the dynaset
Do Until OraDynaset.EOF
    For index = 1 To Address.Count   
        msgbox Address(index)
    Next Index    
OraDynaset.MoveNext
Loop

Example: Updating Attributes of an OraObject Object

The following examples modify the attributes of the ADDRESS value instance in the database.

Dynaset Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim Address as OraObject
 
'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 person_tab
set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)
 
'retrieve a address column from person_tab. 
'Here Value property of OraField object returns Address OraObject 
 
set Address = OraDynaset.Fields("Addr").Value
 
'start the Edit operation and modify the Street attribute
OraDynaset.Edit
Address.Street  =  "Oracle Parkway"
OraDynaset.Update

Parameter Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim Address as OraObject
 
'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 an  OraParameter object represent Address object bind Variable
OraDatabase.Parameters.Add "ADDRESS", Empty, ORAPARM_INPUT, ORATYPE_OBJECT, _
                    "ADDRESS"
 
'get the uninitialized 'Empty' Address object from OraParameter
set Address = OraDatabase.Parameters("ADDRESS").Value
 
'modify the 'Street' attribute of the Address
Address.Street = "Oracle Parkway"
 
'execute the sql statement which updates Address in the person_tab
OraDatabase.ExecuteSQL ("update person_tab set addr = :ADDRESS where age = 40")

Example: Inserting an OraObject Object

The following examples insert a new field (value instance) called ADDRESS in the database.

Dynaset Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim AddressNew as OraObject
 
'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 person_tab
set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)
 
' create a new Address object in OO4O 
set AddressNew = OraDatabase.CreateOraObject("ADDRESS")
 
'initialize the Address object attribute to new value
AddressNew.Street = "Oracle Parkway"
AddressNew.State = "CA"
 
'start the dynaset AddNew operation and set the Address field to new address 
' value
OraDynaset.Addnew
OraDynaset.Fields("ADDR").Value = AddressNew
OraDynaset.Update

OraParameter Example

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim AddressNew as OraObject
 
'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 an  OraParameter object represent Address object bind Variable
OraDatabase.Parameters.Add "ADDRESS", Null, ORAPARM_INPUT, ORATYPE_OBJECT, _ 
                     "ADDRESS"
 
' create a new Address object in OO4O 
set AddressNew = OraDatabase.CreateObject("ADDRESS")
 
'initialize the Address object attribute to new value
AddressNew.Street = "Oracle Parkway"
AddressNew.State = "CA"
 
'set the Address to ADDRESS parameter
Oradatabase.Parameters("ADDRESS").Value = AddressNew
 
'execute the sql statement which updates Address in the person_tab
OraDatabase.ExecuteSQL ("insert into person_tab values (30,'Eric',:ADDRESS))


OraParamArray Object

Description

An OraParamArray object represents an array type bind variable in a SQL statement or PL/SQL block, as opposed to a scalar type bind variable represented by the OraParameter object.

Remarks

OraParamArray objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value.

Implicitly an OraParamArray object contains an OLE automation collection interface for accessing and manipulating individual elements of an array. Individual elements can be accessed using a subscript or the Get_Value method. Individual elements can be modified by using a subscript or the Put_Value method.

Element values are retrieved as Variant types. The Variant type of the element depends on the ServerType of the OraParamArray object. Element values can be null and can be set to Null. For elements of type objects and REFs, element values are returned as corresponding OO4O objects for that type.

You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects descriptions) by using the name of the parameter as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance. Parameters are bound to SQL statements and PL/SQL blocks before execution.

The OraParameters collection is part of the OraDatabase object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (through CreateDynaset, ExecuteSQL, or CreateSQL methods). Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter does not apply to that particular SQL statement or PL/SQL block) are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.

Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle Database), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, use the AutoBindDisable and AutoBindEnable methods.

Properties

Methods

Example

Example: Using OraParamArrays with SQL Statements

The following example shows how to use the OraParamArray object with SQL statements:

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraSqlStmt As OraSQLStmt 
Dim PartNoArray As OraParamArray 
Dim DescArray As OraParamArray 
Dim I As Integer 
 
'Test case for inserting/updating/deleting multiple rows using parameter 
 
' arrays with SQL statements 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger",  0&)  
 
'Create table 
OraDatabase.ExecuteSQL ("create table part_nos(partno number," & _ 
                         "description char(50), primary key(partno))") 
OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22
OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, _ 
                           ORATYPE_CHAR, 10, 50 
Set PartNoArray = OraDatabase.Parameters("PARTNO") 
Set DescArray = OraDatabase.Parameters("DESCRIPTION") 
 
 'Initialize arrays 
For I = 0 To 9 
  achar = "Description" + Str(I) 
  PartNoArray(I) = 1000 + I 
  DescArray(I) = achar 
Next I 
Set OraSqlStmt = OraDatabase.CreateSql("insert into 
part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&) 
 
'Update the newly created part_nos table 
For I = 0 To 9 
  achar = "Description" + Str(1000 + I) 
  DescArray(I) = achar 
Next I 
 
'Update table 
Set OraSqlStmt = OraDatabase.CreateSql("update part_nos set DESCRIPTION" & _ 
                           "=:DESCRIPTION where PARTNO = :PARTNO", 0&) 
 
'Deleting rows 
Set OraSqlStmt = OraDatabase.CreateSql("delete  from  part_nos where" & _  
                           "DESCRIPTION=: Description ", 0&) 
 
'Drop the table 
OraDatabase.ExecuteSQL ("drop table part_nos") 

Example: Using OraParamArrays with PL/SQL

The following is an example using OraParamArray objects with PL/SQL. The Employee PL/SQL package can be set up with the ORAEXAMP.SQL script. See "Demonstration Schema and Code Examples".

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim EmpnoArray As OraParamArray 
Dim EnameArray As OraParamArray 
 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&) 
OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT 
OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER,3, 22
OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, _ 
                                 ORATYPE_VARCHAR2, 3, 10 
Set EmpnoArray = OraDatabase.Parameters("EMPNOS") 
Set EnameArray = OraDatabase.Parameters("ENAMES") 
 
'Initialize the newly created input parameter table EMPNOS 
EmpnoArray(0) = 7698 
EmpnoArray(1) = 7782 
EmpnoArray(2) = 7654 
 
'Execute the PLSQL package 
OraDatabase.ExecuteSQL ("Begin Employee.GetEmpNamesInArray(:ArraySize," & _
                         ":EMPNOS, :ENAMES); End;") 
'Print out Enames 
MsgBox EnameArray(0) 
MsgBox EnameArray(1) 
MsgBox EnameArray(2) 

OraParameter Object

Description

An OraParameter object represents a bind variable in a SQL statement or PL/SQL block.

Remarks

OraParameter objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the object descriptions), by using the parameter name as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance.

Parameters are bound to SQL statements and PL/SQL blocks before execution. In the case of a SQL SELECT statement, binding occurs before dynaset creation.

The OraParameters collection is part of the OraDatabase object. Therefore, all parameters are available to any SQL statement or PL/SQL block executed within the database (through the CreateDynaset or ExecuteSQL methods).

Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter does not apply to that particular SQL statement or PL/SQL block), are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.

Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle Database), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, use the AutoBindDisable and AutoBindEnable methods.

By default, the maximum size of the ORAPARM_OUTPUT variable for ServerType CHAR and VARCHAR2 is set to 127 bytes. Use the MinimumSize property to change this value. The minimum size of an ORAPARM_OUTPUT variable for CHAR, VARCHAR2, and ORATYPE_RAW_BIN must always be greater than the size of the expected data from the database column.

ServerType ORATYPE_RAW_BIN is used when binding to Oracle Raw columns. A byte array is used to put or get values. The maximum allowable size of ORATYPE_RAW_BIN bind buffers is 2000 bytes when bound to a column of a table, 32 KB when bound to a stored procedure. For example code, see the samples in the ORACLE_BASE\ORACLE_HOME\OO4O\VB\Raw directory.

Properties

Methods


OraRef Object

Description

The OraRef interface represents an Oracle REF (reference) as well as a referenceable object (standalone instance).

Remarks

An Oracle REF is an identifier to a referenceable object. Referenceable objects are stored in rows of an object table. By pinning a REF object, referenceable objects are fetched to the client side. An OraRef object implicitly pins the underlying REF when the attributes of a referenceable object are accessed for the first time. The OraRef also encapsulates the functionality for an object navigational operation utilizing the Complex Object Retrieval Capability (COR).

Attributes of a referenceable object represented by the OraRef object are accessed in the same manner as attributes of an value instance represented by the OraObject interface. When pinned, OraRef contains an OraObject interface through the containment mechanism in COM. At run time, the OraRef interface can be typecast to the OraObject interface.

OraRef provides methods for update and delete operations on a referenceable object, independent of the context from which they originated, such as dynasets, parameters, and so on.

An object-level lock should be obtained before modifying the attributes of a referenceable object. This is done though the Edit method of the OraRefobject.

The CreateOraObject method on the OraDatabase object creates a new referenceable object in the database and returns information associated with the OraRef Object. The CreateOraObject and Update methods pair inserts a new referenceable object in the database.

For information about initializing an OraRef object representing a referenceable object in OO4O or executing a member method of a referenceable object, see "Instantiating Oracle LOBs, Objects, and Collections".

Properties

Methods

Examples

Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples" for schema descriptions used in examples of OraObject/OraRef.

Example: Pinning Ref Values

The following example pins the attributes of the PERSON referenceable object in the database.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim Person as OraRef
 
'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 customers
set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)
 
'retrieve a aperson column from customers. Here Value property of 
' OraField object returns Person OraRef
set Person = OraDynaset.Fields("aperson").Value
 
'access the attribute of person. This operation pins the Person ref 
'value and fetches the Person referenceable object to the client. 
msgbox Person.Name

Example: Accessing Attribute Values

The following example accesses the attributes of the PERSON referenceable object in the database.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim Person as OraRef
Dim Address as OraObject
 
'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 customers
set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)
 
'retrieve a aperson column from customers. Here Value property of OraField 
'object returns Person OraRef
set Person = OraDynaset.Fields("aperson").Value
 
'access the attribute by dot notation. 
msgbox Person.Name
 
'access the attribute using '!' notation ( early binding application)
msgbox Person!Name
 
'access the attribute by index
msgbox Person(1)
 
'access the attribute by name
msgbox Person("Name")
 
'access Addr attribute . This returns Address OraObject.
set Address = Person.Addr

Example: Updating Attribute Values

The following example updates the attributes of the PERSON referenceable object in the database.

Dynaset Example

See "Updating Attribute Values: Dynaset Example".

Parameter Example

See "Updating Attribute Values: Parameter Example".

Example: Inserting Referenceable Objects

The following example inserts the new PERSON referenceable object in the database.

Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim Person  as OraRef
 
'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&)
 
'CreateOraObject   creates a new referenceable object in the PERSON_TAB object 
'table and returns associated OraRef
set Person = OraDatabase.CreateOraObject("PERSON","PERSON_TAB")
 
'modify the attributes of Person
Person.Name = "Eric"
Person.Age = 35
 
'Update method inserts modified referenceable object in the PERSON_TAB.
Person.Update


OraServer Object

Description

The OraServer interface represents a physical network connection to an Oracle database.

Remarks

The OraServer interface exposes the connection multiplexing feature provided in the Oracle Call Interface. After an OraServer object is created, multiple user sessions (OraDatabase) can be attached to it by invoking the OpenDatabase method. This feature is particularly useful for application components, such as Internet Information Server (IIS), that use Oracle Objects for OLE in n-tier distributed environments. The use of connection multiplexing when accessing Oracle databases with a large number of user sessions active can help reduce server processing and resource requirements while improving the database scalability.

As illustrated in Figure 9-1, the OraServer interface contains a connection to an Oracle database and provides a method (OpenDatabase) for creating user sessions (OraDatabase objects) on the database connection it contains.

Figure 9-1 OraServer to Oracle Database Relationship

Connection from the OraServer to the Oracle Server
Description of the illustration o4o00015.gif

Properties

Methods


OraSession Object

Description

An OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset objects used within an application.

Remarks

Typically, a single OraSession object is created for each application, but you can create named OraSession objects for shared use within and between applications.

The OraSession object is the highest level object for an application. OraSession and OraServer objects are the only objects created by the CreateObject Visual Basic or Visual Basic for Applications APIs and not by an Oracle Objects for OLE method.

Properties

Methods

Examples

The following code fragments show how to create an OraSession object:

Dim oo4oSession as Object
Set oo4oSession = CreateObject("OracleInProcServer.XOraSession")
 

or

Dim oo4oSession as New OraSessionClass 

or

Dim oo4oSession as OraSession 
Set oo4oSession = New OraSessionClass


OraSQLStmt Object

Description

An OraSQLStmt object represents a single SQL statement. Use the CreateSQL method to create the OraSQLStmt object from an OraDatabase object.

During create and refresh operations, OraSQLStmt objects automatically bind all relevant, enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can improve the performance of SQL statement execution without parsing the SQL statement again.

Properties

Methods


OraSubscription Object

Description

An OraSubscription object that represents the subscription to a database event.

Remarks

OraSubscription objects are created, accessed, and removed indirectly through the OraSubscriptions collection of an OraDatabase object. Each subscription has a name that associates with an Oracle database event.

The OraSubscriptions collection is part of the OraDatabase object.

Properties

Methods


OraTimeStamp Object

Description

The OraTimeStamp object represents the Oracle TIMESTAMP and Oracle TIMESTAMP WITH LOCAL TIME ZONE data types and provides methods for operations on these two Oracle data types. The OraTimeStamp represents a date-time value that stores the following information: year, day, hour, minute, second, and nanosecond.

Remarks

The OraTimeStamp object is created by the OraSession.OraCreateTimeStamp method or by calling the Clone method on an existing OraTimeStamp object.

An OraTimeStamp object can be bound using ServerType ORATYPE_TIMESTAMP or ORATYPE_TIMESTAMPLTZ. This allows the binding of a value to a parameter associated with an Oracle TIMESTAMP or an Oracle TIMESTAMP WITH LOCAL TIME ZONE data type in a SQL or PL/SQL statement respectively.

When binding a string associated with a TIMESTAMP or a TIMESTAMP WITH LOCAL TIME ZONE data types, the ServerType must be specified to be a string type (for example, ORATYPE_VARCHAR2, ORATYPE_STRING) and the string must be in the format specified by the NLS_TIMESTAMP_FORMAT.

Properties

Methods


OraTimeStampTZ Object

Description

The OraTimeStampTZ object represents an Oracle TIMESTAMP WITH TIME ZONE data type and provides methods for operations on this Oracle data type. The OraTimeStampTZ represents a date-time value in a specific time zone that stores the following information: year, day, hour, minute, second, nanosecond, and the time zone.

Remarks

The OraTimeStampTZ object is created by the OraSession.OraCreateTimeStampTZ method or by calling the Clone method on an existing OraTimeStampTZ object.

An OraTimeStampTZ object can be bound using ServerType ORATYPE_TIMESTAMPTZ. This allows the binding of a value to a parameter associated with an Oracle TIMESTAMP WITH TIME ZONE data type in a SQL or PL/SQL statement.

When binding a string associated with an TIMESTAMP WITH TIME ZONE data type, the ServerType must be specified to be a string type (for example, ORATYPE_VARCHAR2, ORATYPE_STRING) and the string must be in the format specified by NLS_TIMESTAMP_TZ_FORMAT.

Properties

Methods


OraConnections Collection

Description

The OraConnections collection maintains a list of OraConnection objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraConnection objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraConnection objects in the collection by using the Count property. Referencing a subscript that is not within the collection (0 to Count-1) results in the return of a NULL OraConnection object.

Properties

Methods

None.


OraFields Collection

Description

The OraFields collection maintains a list of the OraField objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraField objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraField objects in the collection by using the Count property. Referencing a subscript that is not within the collection (0 to Count-1) results in the return of a null OraField object.

Properties

Methods

See Also:

OraField Object

OraParameters Collection

Description

The OraParameters collection maintains a list of OraParameter objects. Unlike the other collection objects, this list is modifiable; you can add to and remove from the collection.

Remarks

You can access the OraParameter objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraParameter objects in the collection by using the Count property. Referencing a subscript that is not within the collection (0 to Count-1) results in the return of a null OraParameter object.

In addition to accessing the OraParameter objects of the collection, you can use the collection to create and destroy parameters by using the Add and Remove methods, respectively.

Properties

Methods


OraSessions Collection

Description

The OraSessions collection maintains a list of OraSession objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraSession objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraSession objects in the collection by using the Count property. Referencing a subscript that is not within the collection (0 to Count-1) results in the return of a null OraSession object.

Properties

Methods

None.


OraSubscriptions Collection

Description

The OraSubscriptions collection maintains a list of OraSubscription objects, which represent the subscription to a database event. Unlike the other collection objects, this list is modifiable; you can add to and remove from the collection.

Remarks

You can access the OraSubscription objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraSubscription objects in the collection by using the Count property. Referencing a subscript that is not within the collection (0 to Count-1) results in the return of a null OraSubscription object.

In addition to accessing the OraSubscription objects of the collection, you can use the collection to create and destroy subscriptions by using the Add and Remove methods, respectively.

Properties

Methods