This chapter describes the Oracle Objects for OLE Server Objects.
This chapter contains these topics:
An OraAQ
object is instantiated by invoking the CreateAQ
method of the OraDatabase
interface. It represents a queue that is present in the database.
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.
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:
|
The OraAQAgent
object represents a message recipient and is only valid for queues that allow multiple consumers.
An OraAQAgent
object can be instantiated by invoking the AQAgent
method. For example:
Set agent = qMsg.AQAgent(name)
None.
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:
|
The OraAQMsg
object encapsulates the message to be enqueued or dequeued. The message can be of any user-defined or raw type.
See Also:
|
The OraAttribute
object represents an attribute of a Value
or REF
instance of an OraObject
or an OraRef
.
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:
None.
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
The OraBFile
interface in OO4O provides methods for performing operations on the BFILE
LOB data type in the database.
The BFILE
types are large binary data objects stored in operating system files (external) outside of the database tablespaces.
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:
|
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.
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.
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".
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.
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.
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:
|
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".
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.
Only one OraClient
object can exist for each workstation, and it is created automatically by the system when it is needed.
The OraCollection
interface represents Oracle collection types, such as variable-length arrays (VARRAY
s) and nested tables.
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 REF
s, element values are returned as corresponding OO4O objects for that type. VARRAY
s and nested tables do not support the elements of LOBs, VARRAY
s, 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".
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
An OraConnection
object represents a single connection to an Oracle database.
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.
An OraDatabase
interface represents a user session to an Oracle database and provides methods for SQL and PL/SQL execution.
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:
BeginTrans
CommitTrans
Rollback
For example:
MyDatabase.BeginTrans MyDatabase.ExecuteSQL("delete from emp where empno = 1234") MyDatabase.CommitTrans
Note: If theAutoCommit property is set to True , transactions are committed automatically, and you do not need to use the transaction control methods. |
An OraDynaset
object permits browsing and updating of data created from a SQL SELECT
statement.
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 VARRAY
s, nested tables, Object
s, REF
s, 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:
The SQL query violates the Oracle SQL update rules; for example, using calculated columns or table joins.
The user does not have the privileges needed to obtain a lock.
The record has been locked already by another user. Note that the OpenDatabase
method has an option so that you can decide whether to wait on locks.
An OraField
object represents a single column or data item within a row of a dynaset.
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.
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.
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.
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.
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.
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.
None.
None.
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".
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? |
The OraMetaData
object can be visualized as a table with three columns:
Metadata attribute name
Metadata attribute value
Flag specifying whether the Value
is another OraMetaData
object
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.
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"
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.
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.
A scientific calculator example program is included as part on the samples installed with Oracle Objects for OLE. See "Demonstration Schema and Code Examples".
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.
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".
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))
See Also:
|
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.
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 REF
s, 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.
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)
An OraParameter
object represents a bind variable in a SQL statement or PL/SQL block.
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.
The OraRef
interface represents an Oracle REF
(reference) as well as a referenceable object (standalone instance).
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 OraRef
object.
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".
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
.
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
The OraServer
interface represents a physical network connection to an Oracle database.
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.
An OraSession
object manages collections of OraDatabase
, OraConnection
, and OraDynaset
objects used within an application.
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.
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
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.
An OraSubscription
object that represents the subscription to a database event.
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.
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.
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
.
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.
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
.
The OraConnections
collection maintains a list of OraConnection
objects. The list is not modifiable; you cannot add to or remove from this collection.
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.
None.
The OraFields
collection maintains a list of the OraField
objects. The list is not modifiable; you cannot add to or remove from this collection.
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.
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.
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.
The OraSessions
collection maintains a list of OraSession
objects. The list is not modifiable; you cannot add to or remove from this collection.
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.
None.
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.
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.