11 Server Properties

Address (OraAQAgent) Property

Applies To

OraAQAgent Object

Description

Returns a 128-byte string representing the protocol-specific address of the recipient. The format is: [schema.]queue[@dblink]

Usage

agent_address = qMsg.AQAgent.Address

Data Type

String

ArraySize Property

Applies To

OraParamArray Object

Description

Specifies the array size (that is, number of elements in an array) of an OraParameter string buffer. Not available at design time and read-only at run time.

Usage

OraParamArray.ArraySize

Data Type

Integer

Remarks

You specify the ArraySize during AddTable. See the AddTable method for the OraParamArray object.

AutoCommit Property

Applies To

OraDatabase Object

Description

Returns or sets the AutoCommit property of the OraDatabase object.

Usage

autocommit = OraDatabase.AutoCommit
OraDatabase.AutoCommit = [ True | False 

Data Type

Boolean

Remarks

If the AutoCommit property is set to True, all the data operations that modify data in the database are automatically committed after the statement is executed.

If the AutoCommit property is set to False, you need to use the OraDatabase transaction methods (BeginTrans, CommitTrans, and Rollback) or SQL statements to control transactions.

Examples

The following example shows how to control transactions with SQL statements after setting the AutoCommit property to False.

Dim session As OraSession 
Dim MyDb As OraDatabase 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
Set MyDb = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
MyDb.AutoCommit = False 
MyDb.ExecuteSQL ("update emp set sal = 100000" & _ 
                  "where ename = 'JOHN SMITH' ") 
MyDb.ExecuteSQL ("commit") 

BOC Property

Applies To

OraCollection Object

Description

Indicates True if the collection iterator moves before the first element of a collection.

Usage

boc_flag = OraCollection.BOC

Data Type

boolean

Examples

See "Example: OraCollection Iterator"

BOF Property

Applies To

OraDynaset Object

Description

Returns whether the current record position in a dynaset is before the first record. Not available at design time and read-only at run time.

Usage

bof_status = oradynaset.BOF

Data Type

Integer (Boolean)

Remarks

Returns True if an attempt has been made to move before the first record in the dynaset using the MovePrevious method. Returns False otherwise.

If a recordset is empty, both BOF and EOF return True.

Examples

This example demonstrates the use of the BOF and EOF properties to detect the limits of a record set. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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 the OraDynaset Object
 Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)
 
 'Traverse until EOF is reached
 Do Until OraDynaset.EOF
  OraDynaset.MoveNext
  Loop
 MsgBox "Reached EOF"
 
 'When EOF is True there is no current record.
 'The current recordset position is now AFTER the last record.
 OraDynaset.MoveLast
 
 Do Until OraDynaset.BOF
  OraDynaset.MovePrevious
 Loop
 MsgBox "Reached BOF"
 
 'When BOF is True there is no current record.
 'The current recordset position is now BEFORE
 'AFTER the last record.
 
 OraDynaset.MoveFirst
 'The recordset is now positioned at the first record.
 
End Sub

Bookmark Property

Applies To

OraDynaset Object

Description

Determines the current record of a record set. Not available at design time and read/write at run time.

Usage

row_bookmark = oradynaset.Bookmark
oradynaset.Bookmark = row_bookmark

Data Type

The value is a string of binary data, but can be stored in a variable of String or Variant data type. The length of the string cannot be predicted, so do not use a fixed-length string.

Remarks

The first form returns a Bookmark property for the current row. The second form repositions the Bookmark property to refer to a specific record within the dynaset.

Bookmark objects exist only for the life of the dynaset and are specific to a particular dynaset. They cannot be shared among dynasets. However, Bookmark objects of a dynaset and their clones are interchangeable.

Before attempting to use Bookmark objects, check the BookMarkable property of that dynaset to see if it supports bookmarks.

Examples

This example demonstrates the use of the Bookmark property to return to a previously known record quickly. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraClient As OraClient 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 Dim Bookmark2 As String
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Get the client object.
 Set OraClient = OraSession.Client
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Create the OraDynaset Object.
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
 
 'Move to the second record and display empno.
 OraDynaset.MoveNext
 MsgBox "Second Record, Employee #" & OraDynaset.Fields("EMPNO").value
 Bookmark2 = OraDynaset.Bookmark
 
 'Move to the last record and display empno.
 OraDynaset.MoveLast
 MsgBox "Last Record, Employee #" & OraDynaset.Fields("EMPNO").value
 
 'Move back to the second record using the bookmark.
 
 OraDynaset.Bookmark = Bookmark2
 MsgBox "Second Record, Employee #" & OraDynaset.Fields("EMPNO").value
 
End Sub

BookMarkable Property

Applies To

OraDynaset Object

Description

Indicates whether the specified dynaset can supports Bookmark objects. Not available at design time and read-only at run time.

Usage

if_bookmarkable = oradynaset.Bookmarkable

Data Type

Integer (Boolean)

Remarks

This property returns True unless the No Cache mode was set when the specified dynaset was created; otherwise, it returns False.

CacheBlocks Property

Applies To

OraDynaset Object

Description

Gets or set cache maximum number of blocks.

Usage

set blocks = oradynaset.CacheBlocks 
oradynaset.CacheBlocks = blocks

Data Type

Integer

CacheChanged Property

Applies To

OraDynaset Object

Description

True if cache or fetch parameters have been changed.

Usage

set Changed = oradynaset.CacheChanged

Data Type

Boolean

CacheMaximumSize Property

Applies To

OraDatabase Object

Description

Sets the maximum size (high watermark) for the client-side object cache as a percentage of the optimal size. The default value is 10%.

Usage

Oradatabase.CacheMaximumSize maxsize

Data Type

Long

Remarks

If the memory occupied by the objects currently in the cache exceeds the high watermark (maximum object cache size), then the cache automatically begins to free unmarked objects that have a pin count of zero. The cache continues freeing those objects until memory use in the cache reaches the optimal size, or until it runs out of objects eligible for freeing.

CacheOptimalSize Property

Applies To

OraDatabase Object

Description

Sets the optimal size for the client-side object cache in bytes. The default value is 200 KB.

Usage

Oradatabase.CacheOptimalSize optimalsize

Data Type

Long

Remarks

This parameter increases the client-side object cache size. If the memory occupied by the objects currently in the cache exceeds the high watermark (maximum object cache size), then the cache automatically begins to free unmarked objects that have a pin count of zero. The cache continues freeing those objects until memory use in the cache reaches the optimal size, or until it runs out of objects eligible for freeing. This parameter should be set to an appropriate value so that object cache can accommodate all the fetched object instance from Oracle Database 10g. This is property is useful in performance tuning for accessing an Oracle Database 10g object instance.

CacheSliceSize Property

Applies To

OraDynaset Object

Description

Gets or sets cache slice size.

Usage

set Slicesize = oradynaset.CacheSliceSize 
 oradynaset.CacheSliceSize = Slicesize   

Data Type

Integer

CacheSlicesPerBlock Property

Applies To

OraDynaset Object

Description

Gets or sets cache slices for each block.

Usage

set Perblock = oradynaset.CacheSlicePerBlock 
oradynaset.CacheSlicePerBlock  = Perblock

Data Type

Integer

Client Property

Applies To

OraSession Object

Description

Returns the OraClient object associated with the specified session. Not available at design time and read-only at run time.

Usage

Set oraclient = orasession.Client

Data Type

OLE Object (OraClient)

Remarks

Each computer has only one client object, so this property returns the same object for all sessions on the same computer.

Connect Property

Applies To

OraConnection Object

OraDatabase Object

Description

Returns the user name of the connection string associated with the connection. Not available at design time and read-only at run time.

Usage

connect_string = oraconnection.Connect
connect_string = oradatabase.Connect

Data Type

String

Remarks

• OraConnection.Connect

  Returns the user name of the connection string associated with the connection.

• OraDatabase.Connect

  Returns the user name of the connection string associated with the specified database. It is equivalent to referencing OraDatabase.Connection.Connect.

The password associated with the user name is never returned.

Examples

This example demonstrates the use of the Connect and DatabaseName properties to display the user name and database name to which the user is connected. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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&)
 
 'Display the username and database to which we are connected.
 MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
 
End Sub

Connection Property

Applies To

OraDatabase Object

OraDynaset Object

OraSQLStmt Object

Description

Returns the OraConnection object associated with the specified database, dynaset, or OraSQLStmt object. Not available at design time and read-only at run time.

Usage

Set oraconnection = oradatabase.Connection
Set oraconnection = oradynaset.Connection
Set oraconnection = orasqlstmt.Connection

Data Type

OLE Object (OraConnection)

Remarks

• OraDatabase.Connection

  Returns the connection object associated with the specified database. Each database is associated with one connection object, but many databases can share the same connection object.

• OraDynaset.Connection

  Returns the connection object associated with this dynaset. This is equivalent to referencing oradynaset.Database.Connection.

• OraSQLStmt.Connection

  Returns the connection object associated with this OraSQLStmt object. This is equivalent to referencing orasqlstmt.Database.Connection.

ConnectionOK Property

Applies To

OraDatabase Object

OraConnection Object

Description

Returns a Boolean value indicating the status of the database connection associated with the OraConnection object. A return value of True implies that the connection is alive in the connection object associated with the specified database. If the connection has been dropped, this property returns False.

Not available at design time and read-only at run time.

Usage

ConnectionStat = OraDatabase.ConnectionOK
ConnectionStat = OraDatabase.Connection.ConnectionOK 

Data Type

Boolean

Remarks

• OraDatabase.ConnectionOK

  Returns the connection status of the connection object associated with the specified database. Each database is associated with one connection object, but many databases can share the same connection object.

• OraConnection.ConnectionOK

  Returns the status of the underlying connection to the database. This is equivalent to OraDatabase.OraConnection.ConnectionOK.

Connections Property

Applies To

OraSession Object

Description

Returns the OraConnections collection of the specified session. Not available at design time and read-only at run time.

Usage

Set oraconnections_collection = orasession.Connections

Data Type

OLE Object (OraParameters)

Remarks

You can access the connections in this collection by subscripting (using ordinal integer numbers). You can obtain the number of connections in the collection using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count - 1. Out-of-range indexes and invalid names return a Null OraConnection object.

Consumer (OraAQ) Property

Applies To

OraAQ Object

Description

Applicable only for a dequeue operation.

Usage

Q.Consumer = consumer_name 

Data Type

String

Remarks

The value is a string representing the name of the consumer. Only those messages matching the consumer name are accessed.

Examples

Dim DB As OraDatabase 
    Dim Q as OraAQ 
      set Q = DB.CreateAQ("Q_MSG_MULTIPLE") 
      'Dequeue only message meant for ANDY 
       Q.consumer = "ANDY" 
      'other processing...  
       Q.Dequeue

Correlate (OraAQ) Property

Applies To

OraAQ Object

Description

Specifies the identification to look for while dequeuing messages.

Usage

Q.Correlate = "RELATIVE_MESSAGE_ID" 

Data Type

String

Remarks

Applicable only for a dequeue operation.

Correlation (OraAQMsg) Property

Applies To

OraAQMsg Object

Description

Specifies the identification for the message. This can then be used as a means of dequeuing specific messages.

Usage

Msg.Correlation = my_message
 

Data Type

String

Remarks

Applicable only for a message that is being enqueued. Returns any string up to 128 bytes.

See Correlate for dequeuing using this identifier.

Count Property

Applies To

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection

OraSubscriptions Collection

Description

Returns the number of objects in the specified collection. Not available at design time and read-only at run time.

Usage

connection_count = oraconnections.Count
field_count = orafields.Count
parameter_count = oraparameters.Count
session_count = orasessions.Count
subscriptions_count = OraSubscriptions.Count

Data Type

Integer

Remarks

Use this property to determine the number of objects in the specified collection.

Examples

This example demonstrates the use of the Count property to display the number of objects in the OraSessions, OraConnections, and OraFields collections. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraClient As OraClient 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Get the client object.
 Set OraClient = OraSession.Client
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Create the OraDynaset Object.
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
 
 MsgBox "You have " & OraClient.Sessions.Count & " OraSession Object(s)."
 MsgBox "You have " & OraSession.Connections.Count & " OraConnection Object(s)."
 MsgBox "You have " & OraDynaset.Fields.Count & " OraField Object(s)."
 
End Sub

Count (OraMetaData) Property

Applies To

OraMetaData Object

Description

An integer representing the number of OraMDAttribute objects contained in this collection.

Usage

count = OraMetaData.Count

Data Type

Integer

Count (OraObject/Ref) Property

Applies To

OraObject Object

OraRef Object

Description

Returns the number of OraAttribute objects in the collection. This is same as the total number of attributes of the underlying referenceable object of OraRef or underlying value instance of OraObject. Read-only at run time.

Usage

attrcount = OraRef.Count  
attrcount = OraObject.Count 

Data Type

Integer

Remarks

Individual attributes can be accessed by using a subscript or the name of the attribute. The OraObject or OraRef attribute index starts at 1.

Examples

The following example shows the use of the Count property. 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 
 
'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 

Database Property

Applies To

OraDynaset Object

OraSQLStmt Object

Description

Returns the OraDatabase object associated with the specified dynaset or SQL statement object. Not available at design time and read-only at run time.

Usage

Set oradatabase = oradynaset.Database
Set oradatabase = orasqlstmt.Database

Data Type

OLE Object (OraDatabase)

Remarks

The OraDynaset.Database property returns the OraDatabase object from which the specified dynaset was created.

The OraSQLStmt.Database property returns the OraDatabase object from which the specified SQLStmt object was created.

DatabaseName Property

Applies To

OraConnection Object

OraDatabase Object

Description

Returns the name of the database associated with the specified object. Not available at design time and read-only at run time.

Usage

database_name = oraconnection.DatabaseName
database_name = oradatabase.DatabaseName

Data Type

String

Remarks

• oraconnection.DatabaseName

  Returns the name of the database, as specified in the OpenDatabase method.

• oradatabase.DatabaseName

  Returns the database name associated with the connection. It is the same as the referencing oradatabase.Connection.DatabaseName.

Examples

This example demonstrates the use of the Connect and DatabaseName properties to display the user name and database to which you have connected. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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&)
 
 'Display the username and database to which you have connected.
 MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
 
End Sub

Databases Property

Applies To

OraServer Object

Description

Returns a collection interface containing all user sessions that have been established using this object.

Usage

Set myCollection = oraserver.Databases 

Data Type

OLE Object (OraCollection)

Day (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Gets and sets the Day attribute of an OraTimeStamp object.

Usage

day= OraTimeStampObj.Day
OraTimeStampObj.Day= day

Arguments

Data Type

Integer

Day (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Day attribute of an OraTimeStampTZ object.

Usage

day= OraTimeStampTZObj.Day
OraTimeStampTZObj.Day= day

Arguments

Data Type

Integer

Days Property

Applies To

OraIntervalDS Object

Description

Gets and sets the Days attribute of an OraIntervalDS object

Usage

days = OraIntervalDSObj.Days
OraIntervalDSObj.Days = days

Arguments

Data Type

Integer

DbPoolCurrentSize Property

Applies To

OraSession Object

Description

Contains the number of currently active database objects in the pool. It is a read-only property.

Usage

curr_size = OraSession.DbPoolCurrentSize

Data Type

Integer

Remarks

An active database object in the pool that contains a live connection to the database.

DbPoolInitialSize Property

Applies To

OraSession Object

Description

Contains the initial size of the pool. It is a read-only property.

Usage

init_size = OraSession.DbPoolInitialSize

Data Type

Integer

DbPoolMaxSize Property

Applies To

OraSession Object

Description

Contains the maximum pool size. It is a read-only property.

Usage

max_size = OraSession.DbPoolMaxSize

Data Type

Integer

Delay (OraAQMsg) Property

Applies To

OraAQMsg Object

Description

Specifies the number of seconds to delay this enqueued message. Set this property to delay the immediate consumption of the message.

Usage

Msg.Delay = seconds

Data Type

Integer

Remarks

Applicable only for a message that is enqueued.

This delay represents the number of seconds after which the message is available for dequeuing.

Possible values are:

• Any valid positive integer.

• ORAAQ_MSG_NO_DELAY

  Default is 0 seconds. The message is available immediately.

DequeueMode (OraAQ) Property

Applies To

OraAQ Object

Description

Specifies the locking behavior associated with the dequeue operation.

Usage

Q.DequeueMode = locking_mode 

Data Type

Integer

Remarks

Possible values are:

• ORAAQ_DQ_BROWSE ( 1)

  Does not lock the message when dequeuing.

• ORAAQ_DQ_LOCKED ( 2)

  Reads and obtains a write lock on the message.

• ORAAQ_DQ_REMOVE (3 )(Default)

  Reads the message, and updates or deletes it.

DequeueMsgId (OraAQ) Property

Applies To

OraAQ Object

Description

Returns an array of raw bytes, specifying the message identifier of the message to be dequeued.

Usage

Q.DequeueMsgid = msg_id

Data Type

String

Remarks

Applicable only for a dequeue operation.

DirectoryName Property

Applies To

OraBFILE Object

Description

Gets or sets the directory alias name.

Usage

diralias = OraBFile.DirectoryName 
OraBFile.DirectoryName = diralias 

Arguments

Data Type

String

Remarks

This String is case-sensitive.

DynasetOption Property

Applies To

OraParameter Object

Description

Specifies the dynaset option for a dynaset created from the PL/SQL cursor.

Usage

oraparameter.DynasetOption = dyn_opts

Remarks

This property should be called before executing a PL/SQL procedure containing a cursor variable. By default, the dynaset is created with the ORADYN_READONLY option.

The possible values are:

EditMode Property

Applies To

OraDynaset Object

Description

Returns the editing state for the current row. Not available at design time and read-only at run time.

Usage

edit_mode = oradynaset.EditMode

Data Type

Integer

Remarks

The EditMode property values are:

These values are located in the ORACLE_BASE\ORACLE_HOME\oo4o\oraconst.txt file and are intended to match similar constants in the Visual Basic constant.txt file.

This property is affected only by the Edit, AddNew, and Update methods.

EditOption (OraRef) Property

Applies To

OraRef Object

Description

Specifies whether the object is to be locked during the pin operation.

Usage

edit_option = OraRef.EditOption
OraRef.EditOption = edit_option

Arguments

Data Type

Integer

Remarks

This property should be called before a pin operation on a Ref value, before accessing an attribute for the first time on the OraRef object. This option is useful if the object attributes are modified immediately after the pin operation. Locking the object instance during the pin operation saves the round-trip to the database during the Edit (OraRef) operation.

Possible values of edit_option are:

Examples

The following example shows the usage of the EditOption property. 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 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
 
'set the ORAREF_EXCLUSIVE_LOCK EditOption on the Person object. 
Person.EditOption = ORAREF_EXCLUSIVE_LOCK 
 
'pin the Person Ref. This operation also locks the underlying 
'referenceable 'object in the server
MsgBox Person.Name

'call Edit method on Person OraRef. 
'This method does not make any network round-trip

Person.Edit
Person.Name = "Eric"
Person.Age = 35
Person.Update

ElementType Property

Applies To

OraCollection Object

Description

An integer code representing the server type of an element. This property is read-only at run time.

Usage

elem_type = OraCollection.ElementType

Data Type

Integer

Remarks

The codes correspond to the Oracle external data types. The following Oracle element data types are supported:

EOC Property

Applies To

OraCollection Object

Description

Returns True if the collection iterator moves past the last element of a collection.

Usage

eoc_flag = OraCollection.EOC

Data Type

Boolean

Examples

See "Example: OraCollection Iterator" .

EOF Property

Applies To

OraDynaset Object

Description

Indicates whether the current record position in a dynaset is after the last record. Not available at design time and read-only at run time.

Usage

eof_status = oradynaset.EOF

Data Type

Integer (Boolean)

Remarks

Returns True if an attempt has been made to move after the last record in the dynaset using the MoveNext method. Otherwise, returns False.

If a recordset is empty, both BOF and EOF return True.

Examples

This example demonstrates the use of the BOF and EOF properties to detect the limits of a recordset. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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 the OraDynaset Object
 Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)
 
 'Traverse until EOF is reached
 Do Until OraDynaset.EOF
  OraDynaset.MoveNext
  Loop
 MsgBox "Reached EOF"
 
 'When EOF is True there is no current record. The current recordset
 ' position is now AFTER the last record
 OraDynaset.MoveLast
 
 Do Until OraDynaset.BOF
  OraDynaset.MovePrevious
 Loop
 MsgBox "Reached BOF"
 
 'When BOF is True there is no current record. The current recordset 
 'position is now BEFORE AFTER the last record.
 
 OraDynaset.MoveFirst
 
 'The recordset is now positioned at the first record.
 
End Sub

ExceptionQueue Property

Applies To

OraAQMsg Object

Description

Specifies the name of the queue to which message should be moved if it cannot be processed successfully.

Usage

Msg.ExceptionQueue queue_name

Data Type

String

Remarks

Applicable only for a message that is being enqueued.

Possible values are:

• A String containing a valid queue name

• Null (Default)

A message is moved to the exception queue if the number of dequeue attempts has expired or has exceeded max_retries specified in the DBMS_AQADM.CREATE_QUEUE command.

Exists Property

Applies To

OraBFILE Object

Description

Returns True if the OraBFILE points to a BFILE that exists on the database.

Usage

exists = OraBFile.Exists

Data Type

Boolean

Remarks

Read privileges on the directory where the BFILE is located are required to use this property. The operating system-specific permissions must have been set for the directory to make sure that the user can read the directory.

Appropriate privileges must be set up in the database previously. For example, to ensure that a user (scott) can read a directory (BfileDirectory) through the Exists property, the following SQL statement must be executed:

GRANT READ ON DIRECTORY BfileDirectory TO scott;

Expiration (OraAQMsg) Property

Applies To

OraAQMsg Object

Description

Specifies, in seconds, the time for which the message is available for dequeuing.

Usage

Msg.Expiration = seconds

Data Type

Integer

Remarks

This property is an offset from the delay. It is applicable only for a message that is being enqueued.

Possible Values are:

• Any integer.

• ORAAQ_MSG_NO_XPIRE (0)

  Default 0 - The message will never expire.

FetchLimit Property

Applies To

OraDynaset Object

Description

Gets or sets the array size of the fetch.

Usage

set Limit = oradynaset.FetchLimit
oradynaset.FetchLimit = Limit

Data Type

Integer

FetchSize Property

Applies To

OraDynaset Object

Description

Gets or sets the array buffer size of the fetch.

Usage

set Size = oradynaset.FetchSize
oradynaset.FetchSize = Size

Data Type

Integer

FieldIndex Property

Applies To

OraDynaset Object

Description

Returns the index of the field indicated by the field_name argument.

Usage

set index = oradynaset.FieldIndex(field_name)

Arguments

Data Type

Integer

Remarks

Accessing fields of a dynaset using an index is more efficient than accessing them by name. If you need to access a particular field many times, use this method to translate its name into its index.

FieldName Property

Applies To

OraDynaset Object

Description

Returns the field name in the SELECT statement in the dynaset.

Usage

set field_name = oradynaset.FieldName(index)

Arguments

Data Type

String

FieldOriginalName Property

Applies To

OraDynaset Object

Description

Gets the original field name used in the SELECT statement in the dynaset.

Usage

set field_name = oradynaset.FieldOriginalName(index)

Arguments

Data Type

String

Remarks

The FieldOriginalName property returns a string containing the original column name specified in the SQL statement during dynaset creation. This property is useful when a SQL statement contains SCHEMA.TABLE.COL as the name of the field. This enables duplicate column names to be referenced. Another way to avoid duplicate columns is to specify an alias in the SQL statement.

FieldOriginalNameIndex Property

Applies To

OraDynaset Object

Description

Returns the index of the field indicated by the original field name used in the SQL SELECT statement.

Usage

set index = oradynaset.FieldOriginalNameIndex(name)

Arguments

Data Type

Integer

Remarks

Accessing fields of a dynaset by index is more efficient than accessing them by name. If you need to access a particular field many times, use this method to translate its original name into its index.

Fields Property

Applies To

OraDynaset Object

Description

Returns the collection of fields for the current row. Not available at design time and read-only at run time.

Usage

Set orafields_collection = oradynaset.Fields

Data Type

OLE Object (OraFields)

Remarks

You can access the fields in this collection by subscripting (using ordinal integer numbers) or by using a string denoting the field (column) name. You can obtain the count of the number of fields using the Count property on the returned collection. A subscript that is not within the collection (0 to Count - 1) results in the return of a Null OraField object.

FileName Property

Applies To

OraBFILE Object

Description

Gets or sets a filename. Read and write at run time.

Usage

filename  = OraBFile.FileName 
OraBFile.FileName = filename 

Arguments

Data Type

String

Remarks

This string can be case-sensitive depending on the database operating system.

Filter Property

Remarks

The OraDynaset object does not support this property. Refine your record selection by using a SQL WHERE clause or by using SQL parameters.

Format (OraNumber) Property

Applies To

OraNumber Object

Description

The format string used in OraNumber operations. For details about format strings, see Oracle Database SQL Quick Reference. Read and write at run time.

Usage

OraNumber.Format = formatstring  
formatstring = OraNumber.Format  

Arguments

Data Type

String

Remarks

An error is returned if the format string is set to an invalid value. To reset the format to the default, set it to an empty string.

Format (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the TIMESTAMP format used to display the OraTimeStamp object as a string.

Usage

format = OraTimeStampObj.Format
OraTimeStampObj.Format = format

Arguments

Data Type

String

Remarks

If Format is Null, the session TIMESTAMP format is used to display the OraTimeStamp object as a string.

Format (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the TIMESTAMP WITH TIME ZONE format used to display the OraTimeStampTZ object as a string.

Usage

format = OraTimeStampTZObj.Format
OraTimeStampTZObj.Format = format

Arguments

Data Type

String

Remarks

If Format is Null, the session TIMESTAMP WITH TIME ZONE format is used to display the OraTimeStampTZ object as a string.

HexValue (OraRef) Property

Applies To

OraRef Object

Description

Returns the hexidecimal value of the REF.

Usage

hexstring = OraRef.HexValue 

Remarks

The hexidecimal value of the REF can be used by the OraDatabase.FetchOraRef method.

Hour (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the Hour attribute of an OraTimeStamp object.

Usage

hour = OraTimeStampObj.Hour
OraTimeStampObj.Hour = hour

Arguments

Data Type

Integer

Hour (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Hour attribute of an OraTimeStampTZ object.

Usage

hour = OraTimeStampTZObj.Hour
OraTimeStampTZObj.Hour = hour

Arguments

Data Type

Integer

Hours Property

Applies To

OraIntervalDS Object

Description

Gets and sets the Hours attribute of an OraIntervalDS object.

Usage

hours = OraIntervalDSObj.Hours
OraIntervalDSObj.Hours = hours

Arguments

Data Type

Integer

IsLocator (OraCollection) Property

Applies To

OraCollection Object

Description

Returns True if the collection instance of the OraCollection object is locator-based; otherwise, returns False.

Usage

islocator = OraCollection.IsLocator

Data Type

Integer (Boolean)

IsMDObject Property

Applies To

OraMDAttribute Object

Description

Returns True if the Value property is another OraMetaData object; otherwise, the property is False.

Usage

isobject = OraMDAttribute.IsMDObject

Data Type

Boolean

IsNull (OraCollection) Property

Applies To

OraCollection Object

Description

Returns True if the collection value of the OraCollection object is Null.

Usage

isnull = OraObject.IsNull

Data Type

Integer (Boolean)

Remarks

Accessing elements of a Null collection results in an error. The IsNull property should be checked before accessing elements of an underlying collection.

IsNull (OraLOB/BFILE) Property

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Returns True if the LOB or BFILE refers to a Null value in the database; otherwise, returns False. This property is read-only.

Usage

IsNull = OraBfile.IsNull
IsNull = OraBlob.IsNull 
IsNull = OraClob.IsNull

Data Type

Boolean

Remarks

Some LOB or BFILE properties and methods are not valid when a LOB or BFILE is Null. This property makes it possible to check for Null values and avoid these errors.

IsNull (OraObject) Property

Applies To

OraObject Object

Description

Returns True if underlying value instance of the OraObject object is Null. Read-only at run time.

Usage

isnull = OraObject.IsNull

Data Type

Integer (Boolean)

Remarks

Accessing attributes of a Null value instance results in an error. The IsNull property can be checked before accessing attributes of an underlying value instance.

Examples

The following example shows the use of the IsNull property. 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 AddressClone 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&)
 
' insert a Null Address value instance in the table
OraDynaset.AddNew
OraDynaset.Fields("Name").value = "Eric"
OraDynaset.Fields("Addr").Value  = Null
OraDynaset.update
 
'move to the newly added value instance
OraDynaset.MoveLast
 
'retrieve a address column from person_tab. This Address object points to Null
' value instance
set Address = OraDynaset.Fields("Addr").Value
 
'try to access attributes of Address. the following line will result an error 
msgbox Address.Street    '---------ERROR------------'
 
'use the IsNull property to check the nullstatus
If Address.IsNull = False Then
    MsgBox Address!Street
End if

IsOpen (OraBFILE) Property

Applies To

OraBFILE Object

Description

Returns True if the OraBFILE object is open.

Usage

IsOpen = OraBFile.IsOpen

Data Type

Boolean

Remarks

The openness of an object OraBFILE is local to this OraBFILE object. If two OraBFILE objects point to the same BFILE in the database, and one OraBFILE object calls the Open method and the other does not, one OraBFILE object will return True for the IsOpen property. The other will return False.

IsRefNull (OraRef) Property

Applies To

OraRef Object

Description

Returns True if the underlying Ref value of the OraRef object is Null.

Usage

isnull = OraRef.IsRefNull

Data Type

Integer (Boolean)

Remarks

Accessing the attributes of a Null Ref value results in an error. The IsRefNull property should be checked before accessing attributes of an underlying referenceable object. This property is read-only at run time.

LastErrorText Property

Applies To

OraParamArray Object

Description

Gets the last error message. Not available at design time and read-only at run time.

Usage

OraParamArray.LastErrorText

Data Type

String

LastModified Property

Applies To

OraDynaset Object

Description

Returns the Bookmark object of the row that was last modified by an Edit or an AddNew operation. Not available at design time and read-only at run time.

Usage

last_modified_bookmark = oradynaset.LastModified

Data Type

The value is a string of binary data, but can be stored in a variable of String or Variant data type. The length of the string cannot be predicted, so do not use a fixed-length string.

Remarks

Use this property to make the last modified record the current record.

LastServerErr Property

Applies To

OraDatabase Object

OraSession Object

Description

Returns the last nonzero error code generated by an Oracle database function for the specified object. Not available at design time and read-only at run time.

Usage

error_number = oradatabase.LastServerErr
error_number = orasession.LastServerErr

Data Type

Long Integer

Remarks

This property represents the last nonzero return value from an Oracle Call Interface (OCI) database function, or zero if no error has occurred since the last LastServerErrReset request. For efficiency, only nonzero return values are returned; therefore, a nonzero value does not necessarily indicate that the most recently called OCI database function generated the error (because zero return values are not returned by way of the LastServerErr method).

• Orasession.LastServerErr

  Returns all errors related to connections, such as errors on OpenDatabase, BeginTrans, CommitTrans, Rollback, and ResetTrans method.

• Oradatabase.LastServerErr

  Returns all errors related to an Oracle cursor, such as errors on dynasets and from ExecuteSQL method.

Examples

This example demonstrates the use of the CreateDynaset method and the LastServerErr and LastServerErrText properties to determine whether an Oracle error has occurred, and to display the error message, respectively. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Set up an error handler.
 On Error GoTo errhandler 
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Attempt to Create the OraDynaset Object.
 'Notice that the FROM keyword is missing from the SQL statement.
 Set OraDynaset = OraDatabase.CreateDynaset("select * emp", 0&)
 
Exit Sub
 
errhandler: 
 
 'Check to see if an Oracle error has occurred.
 If OraDatabase.LastServerErr <> 0 Then
  MsgBox OraDatabase.LastServerErrText
 Else 'Must be some non-Oracle error
  MsgBox "VB:" & Err & " " & Error(Err)
 End If
 
 Exit Sub
 
End Sub

LastServerErrPos Property

Applies To

OraDatabase Object

Description

Returns the position at which a parsing error occurred in a SQL statement. Not available at design time and read-only at run time.

Usage

error_pos = oradatabase.LastServerErrPos

Data Type

Integer

Remarks

The LastServerErrPos property returns 0 if no SQL statements have been parsed; -1 if the last parse was successful; and >= 0 if the last parse failed. Parsing is done on SQL statements before execution (using the CreateDynaset or ExecuteSQL method).

LastServerErrText Property

Applies To

OraDatabase Object

OraSession Object

Description

Returns the textual message associated with the current LastServerErr property of the specified object. Not available at design time and read-only at run time.

Usage

error_text = orasession.LastServerErrText
error_text = oradatabase.LastServerErrText

Data Type

String

Remarks

The returned value indicates one of three possible states:

1. If Null is returned, an Oracle Call Interface (OCI) database function has not returned an error since the most recent LastServerErrReset property.

2. If a non-Null value is returned, an OCI function has returned an error code; the returned string is the associated message.

3. If the message is empty, then no additional information was available.

Examples

This example demonstrates the use of the CreateDynaset method and the LastServerErr and LastServerErrText properties to determine whether an Oracle error has occurred and to display the error message. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Set up an error handler.
 On Error GoTo errhandler 
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Attempt to Create the OraDynaset Object.
 'Notice that the FROM keyword is missing from the SQL statement.
 Set OraDynaset = OraDatabase.CreateDynaset("select * emp", 0&)
 
Exit Sub
 
errhandler:
 
 'Check to see if an Oracle error has occurred.
 If OraDatabase.LastServerErr <> 0 Then
  MsgBox OraDatabase.LastServerErrText
 Else 'Must be some non-Oracle error.
  MsgBox "VB:" & Err & " " & Error(Err)
 End If
 
 Exit Sub
 
End Sub

MaxSize (OraCollection) Property

Applies To

OraCollection Object

Description

Returns the maximum size of the collection.

Usage

max_size  = OraCollection.MaxSize

Data Type

Integer

Remarks

For an OraCollection object of type ORATYPE_TABLE, this property returns the current size of the collection including deleted elements. For an OraCollection object of type ORATYPE_VARRAY, the property returns the maximum size of the collection.

MinimumSize Property

Applies To

OraParameter Object

OraParamArray Object

Description

Returns the minimum size of an OraParameter or OraParamArray string buffer or ByteArray (for ORATYPE_RAW_BIN). For OraParamArray objects, the minimum size property is read-only at run time. For OraParameter objects, the minimum size is read/write at run time.

Usage

oraparameter.MinimumSize 
oraparamarray.MinimumSize

Data Type

Integer

Remarks

This property gets the minimum number of characters or bytes to be allocated for each element of the array. For OraParamArray objects, the size is specified using the AddTable method.

Examples

Note: This example needs the following to be run: a PL/SQL procedure called EmployeeLong with a GetEmpName procedure that uses a table with the column name ENAME_LONG that returns a long ename of approximately 200 characters.

Sub Form_Load ()
 
' Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
' Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
' Create the OraDatabase Object.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
' Add EMPNO as an Input/Output parameter and set its initial value.
OraDatabase.Parameters.Add "EMPNO", 9999, ORAPARM_INPUT
 
' Add ENAME as an Output parameter and set its initial value.
OraDatabase.Parameters.Add "ENAME_LONG", "foo", ORAPARM_OUTPUT
OraDatabase.Parameters("ENAME_LONG").MinimumSize = 201 
 'Since we require to hold a value of more than 128 bytes
 
' Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME_LONG.
OraDatabase.ExecuteSQL ("Begin EmployeeLong.GetEmpName (:EMPNO," & _
            "NAME_LONG); end;")

Minute (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the Minute attribute of an OraTimeStamp object.

Usage

minute = OraTimeStampObj.Minute
OraTimeStampObj.Minute = minute

Arguments

Data Type

Integer

Minute (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Minute attribute of an OraTimeStampTZ object.

Usage

minute = OraTimeStampTZObj.Minute
OraTimeStampTZObj.Minute = minute

Arguments

Data Type

Integer

Minutes Property

Applies To

OraIntervalDS Object

Description

Gets and sets the Minutes attribute of an OraIntervalDS object.

Usage

minutes = OraIntervalDSObj.Minutes
OraIntervalDSObj.Minutes = minutes

Arguments

Data Type

Integer

Month (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the Month attribute of an OraTimeStamp object.

Usage

month = OraTimeStampObj.Month
OraTimeStampObj.Month = month

Arguments

Data Type

Integer

Month (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Month attribute of an OraTimeStampTZ object.

Usage

month = OraTimeStampTZObj.Month
OraTimeStampTZObj.Month = month

Arguments

Data Type

Integer

Months Property

Applies To

OraIntervalYM Object

Description

Gets and sets the Months attribute of an OraIntervalYM object.

Usage

months = OraIntervalYMObj.Months
OraIntervalYMObj.Months = months

Arguments

Data Type

Integer

Name Property

Applies To

OraClient Object

OraField Object

OraParameter Object

OraSession Object

OraParamArray Object

OraServer Object

OraSubscription Object

Description

Returns the name used to identify the given object. Not available at design time and read-only at run time.

Usage

client_name = oraclient.Name
field_name = orafield.Name
parameter_name = oraparameter.Name 
paramarray_name = oraparamarray.Name
session_name = orasession.Name 
server_name = oraserver.Name
subscription_name = orasubscription.Name

Data Type

String

Remarks

• oraclient.Name

  Returns the name of the specified OraClient object. This value is always local.

• orafield.Name

  Returns the name of the specified OraField object. If this is a true database field (not an alias), this use returns the name of the field as it appears in the database. If a SQL statement was executed that contains, for example, calculated select list items or column aliases, then the name is the actual text provided in the SQL SELECT statement.

• oraparameter.Name

  Returns the name of the specified OraParameter object. In addition to identifying the parameter within a parameters collection, the parameter name is also used to match placeholders within SQL and PL/SQL statements for the purposes of parameter binding.

• oraparamarray.Name

  Returns the name of the specified OraParamArray object. In addition to identifying the parameter within a parameters collection, the parameter name is also used to match placeholders within SQL and PL/SQL statements for the purposes of parameter binding.

• orasession.Name

  Returns the name of the specified OraSession object. For automatically created sessions, this is the name assigned by the system (usually a hexadecimal number). For user-created sessions, this is the name originally provided in the CreateSession method. Once created, a session name cannot be changed.

• oraserver.Name

  Returns the name of the physical connection of the specified OraServer object.

• orasubscription.Name

  Returns the name used to represent the subscription. Name here refers to the subscription name in the form of the string 'SCHEMA.QUEUE' if the registration is for a single consumer queue and 'SCHEMA.QUEUE:CONSUMER_NAME' if the registration is for a multiple consumer queue.

Name (AQAgent) Property

Applies To

OraAQAgent Object

Description

Returns a 30-byte string representing the name of agent.

Usage

agent_name = qMsg.AQAgent.Name

Data Type

String

Name (OraAttribute) Property

Applies To

OraAttribute Object

Description

A String containing the name of the attribute.

Usage

name  = OraAttribute.Name

Data Type

String

Remarks

Read-only at run time.

Name (OraMDAttribute) Property

Applies To

OraMDAttribute Object

Description

A String containing the name of the attribute.

Usage

name = OraMDAttribute.Name

Data Type

String

Nanosecond(OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the Nanosecond attribute of an OraTimeStamp object.

Usage

nanosecond = OraTimeStampObj.NanosecondOraTimeStampObj.Nanosecond= nanosecond

Arguments

Data Type

Integer

Nanonsecond (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Nanosecond attribute of an OraTimeStampTZ object.

Usage

nanosecond = OraTimeStampTZObj.NanosecondOraTimeStampTZObj.Nanosecond= nanosecond

Arguments

Data Type

Integer

Nanonseconds Property

Applies To

OraIntervalYM Object

Description

Gets and sets the Nanoseconds attribute of an OraIntervalDS object.

Usage

nanoseconds = OraIntervalDSObj.NanosecondsOraIntervalDSObj.Nanoseconds = nanoseconds

Arguments

Data Type

Integer

Navigation (OraAQ) Property

Applies To

OraAQ Object

Description

Specifies the position of the message that will be retrieved.

Usage

Q.Navigation = position

Data Type

Integer

Remarks

Possible values are:

• ORAAQ_DQ_FIRST_MSG (1)

  Retrieves the first message that is available and matches the search criteria.

• ORAAQ_DQ_NEXT_TRANS (2)

  Skips the remainder of the current transaction group, if any, and retrieves the first message of the next transaction group. Used only if message grouping is enabled for the queue.

• ORAAQ_DQ_NEXT_MSG (3) (Default)

  Retrieves the next message that is available and matches the search criteria.

NoMatch Property

Applies To

OraDynaset Object using the Address (OraAQAgent) Property Property

Description

Returns True if the last call to the FindFirst, FindLast, FindNext, or FindPrevious method failed.

Usage

Set nomatch_status = oradynaset.NoMatch

Data Type

Boolean

NonBlockingState Property

Applies To

"OraSQLStmt Object" created with ORASQL_NONBLK option.

Description

Returns the status of the currently executing SQL as follows:

• ORASQL_STILL_EXECUTING

  If operation is still underway.

• ORASQL_SUCCESS

  If operation has completed successfully.

Any failures are thrown as exceptions.

The application can access the output parameters, if any, as in the blocking case, after successful execution of the SQL statement.

Usage

status = OraSQL.NonBlockingState 
if status = ORASQL_STILL_EXECUTING 
    MsgBox "Still in execution" 
else 
    MsgBox "Execution completed successfully" 

Return Values

ORASQL_STILL_EXECUTING(-3123) or ORASQL_SUCCESS(0)

Errors are thrown as exceptions.

Offset (OraLOB/BFILE) Property

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Gets or sets the 1-based offset into the LOB or BFILE for the next Read or Write operation. This property is read/write at run time.

Usage

offsetbytes = OraBFile.Offset 
OraBFile.Offset = offsetbytes 
 
offsetbytes = OraBlob.Offset 
OraBlob.Offset = offsetbytes 
 
offsetchars = OraClob.Offset 
OraClob.Offset = offsetchars 

Data Type

Integer

Remarks

This value is expressed in bytes for OraBLOB and OraBFILE or characters for the OraCLOB object. The default value is 1. Setting this value to 0 raises an error. When the PollingAmount property is not 0 (polling is enabled), the Offset property can only be set before the first Read or Write operation, or after the current polling operation has completed.

OIPVersionNumber Property

Applies To

OraSession Object

Description

Returns the version number of Oracle Object for OLE. Not available at design time and read-only at run time.

Usage

version_number = orasession.OIPVersionNumber

Data Type

String

Remarks

This property returns a unique identifier for each release of Oracle Object for OLE.

Options Property

Applies To

OraDatabase Object

OraDynaset Object

OraSQLStmt Object

Description

Returns the options flag originally passed to the specified object. Not available at design time and read-only at run time.

Usage

options = oradatabase.Options
options = oradynaset.Options 
options = orasqlstmt.Options

Data Type

Long Integer

Remarks

See the OpenDatabase method for a description of the possible values of oradatabase.Options.

See the CreateDynaset method for a description of the possible values of oradynaset.Options.

See the CreateSQL method for a description of the possible values of orasqlstmt.Options

OraIDataType Property

Applies To

OraField Object

Description

Returns the Oracle internal data type code for the field specified. Not available at design time and read-only at run time.

Usage

field_idatatype = orafield.OraIDataType

Data Type

Long Integer

Remarks

The following Oracle Internal data types are returned.

These values can be found in the ORACLE_BASE\ORACLE_HOME\oo4o\oraconst.txt file.

OraMaxDSize Property

Applies To

OraField Object

Description

Returns the Oracle maximum display size for the field specified. Not available at design time and read-only at run time.

Usage

field_maxdisplaysize = orafield.OraMaxDSize

Data Type

Long Integer

Remarks

This value is meaningful only when the value is returned as a character string, especially when using functions such as SUBSTR or TO_CHAR to modify the representation of the column.

OraMaxSize Property

Applies To

OraField Object

Description

Returns the Oracle maximum column size as stored in the Oracle data dictionary. Not available at design time and read-only at run time.

Usage

field_maxsize = orafield.OraMaxSize

Data Type

Long Integer

Remarks

The return value is dependent on the Oracle internal data type. The following values will be returned:

OraNullOK Property

Applies To

OraField Object

Description

Indicates whether or not Null values are permitted for this column. Not available at design time and read-only at run time.

Usage

field_nullok = orafield.OraNullOK

Data Type

Integer (Boolean)

Remarks

This property returns True if Null values are permitted, otherwise, it returns False.

OraPrecision Property

Applies To

OraField Object

Description

Returns the precision of a numeric column. Not available at design time and read-only at run time.

Usage

field_precision = orafield.OraPrecision

Data Type

Long Integer

Remarks

This value is meaningful only when the value returned is numeric. Precision is the total number of digits of a number.

OraScale Property

Applies To

OraField Object

Description

Returns the scale of a numeric column. Not available at design time and read-only at run time.

Usage

field_scale = orafield.OraScale

Data Type

Long Integer

Remarks

This value is meaningful only when the value returned is numeric. The SQL types REAL, DOUBLE PRECISION, FLOAT, and FLOAT(N) return a scale of -127.

Parameters Property

Applies To

OraDatabase Object

Description

Returns the OraParameters collection of the specified database. Not available at design time and read-only at run time.

Usage

Set oraparameters_collection = oradatabase.Parameters

Data Type

OLE Object (OraParameters)

Remarks

You can access the parameters in this collection by subscripting (using ordinal integer numbers) or by using the name the parameter that was given at its creation. You can obtain the number of parameters in the collection using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count-1. Out-of-range indexes and invalid names return a Null OraParameter object.

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

PinOption (OraRef) Property

Applies To

OraRef Object

Description

Gets and sets the Pin option for the referenceable object during the pin operation.

Usage

pin_option = OraRef.PinOptionOraRef.PinOption = pin_option

Arguments

Data Type

Integer (Boolean)

Remarks

Possible values returned by the pin_option property are:

Examples

The following example shows the usage of the PinOption property. 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 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
 
'set the ORAREF_READ_LATEST read option on the Person object. 
Person.PinOption = ORAREF_READ_LATEST 
 
'pin the Person Ref and get the latest copy of referenceable 
'object for Ref from the database
MsgBox Person.Name
MsgBox Person.Age

PollingAmount Property

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Gets or sets the total amount to be read or written for multiple chunk Read and Write operations (polling). A value of zero means that polling is not used. This property is read/write at run time.

Usage

pollamountbytes = OraBFile.PollingAmount 
OraBfile.PollingAmount = pollamountbytes 
 
pollamountbytes = OraBlob.PollingAmount 
OraBlob.PollingAmount = pollamountbytes 
 
pollamountchars= OraClob.PollingAmount 
OraClob.PollingAmount = pollamountchars 

Data Type

Integer

Remarks

This value is expressed in bytes for the OraBLOB and OraBFILE objects, or characters for the OraCLOB object. It is set before beginning a multiple-chunk read or write operation. After it is set, a series of Read or Write operations must be issued until the LOB Status property no longer returns ORALOB_NEED_DATA .This occurs when the PollingAmount bytes or characters have been read. Attempting to do other LOB operations before the end of the polling operation results in an error.

Priority (OraAQMsg) Property

Applies To

OraAQMsg Object

Description

Specifies the priority of the message.

Usage

Msg.Priority = msg_priority 

Data Type

Integer

Remarks

A smaller number indicates higher priority.

Possible Values are:

• Any integer including negative numbers.

• ORAAQ_NORMAL (Default): 0

• ORAAQ_HIGH : -10

• ORAAQ_LOW : 10

This property can be set while enqueuing and can then be used for priority-based dequeuing.

RDMSVersion Property

Applies To

OraDatabase Object

Description

Returns the database version.

Usage

Set Version = oradatabase.RDBMSVersion

Data Type

String

RecordCount Property

Applies To

OraDynaset Object

OraSQLStmt Object

Description

• OraDynaset

  Returns the total number of records in the dynaset.

• OraSQLStmt

  Returns the number of records processed in an insert, update, or delete statement, even when there is a failure executing the SQL statement.

Not available at design time and read-only at run time.

Usage

record_count = oradynaset.RecordCountrecord_count = orasqlstmt.RecordCount

Data Type

Long Integer

Remarks

Referencing this property requires that the entire result table be fetched immediately from an Oracle database to determine the count of records. Due to the potentially severe performance impact of this, the user should avoid using this property and instead execute an additional query using the COUNT(*) clause, and use the SnapshotID property to guarantee time consistency. For an example, see the SnapShot property.

Referencing this property while using the ORADYN_NOCACHE option of the CreateDynaset method causes an implicit MoveLast operation and makes the current record the last record in the dynaset.

Examples

RecordCount Example (OraDynaset)

This example demonstrates the use of the RecordCount property to determine the number of records retrieved with a SELECT statement and OraDynaset. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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 the dynaset.
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
 
 'Display the number of records. Note that this causes
 'all records to be fetched to ensure an accurate count.
 
 MsgBox OraDynaset.RecordCount & " records retrieved."
 
 
End Sub

Record Count Example (OraSQLStmt)

The following example shows the number of records inserted into the database after using an INSERT statement with OraSQLStmt.

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim OraSqlStmt As OraSQLStmt 
Dim OraPArray1 As OraParamArray 
Dim OraPArray2 As OraParamArray
Dim I As Integer  
  
On Error GoTo ERR_array_sql 
  
'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 
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
  MsgBox "Error" 
End If  
 
Set OraPArray1 = OraDatabase.Parameters("PARTNO") 
Set OraPArray2 = OraDatabase.Parameters("DESCRIPTION") 
 
'Initialize arrays 
For I = 0 To 9 
  achar = "Description" + Str(I) 
  OraPArray1.put_Value 1000 + I, I 
  OraPArray2.put_Value achar, I 
Next I  
 
Set OraSqlStmt = OraDatabase.CreateSql("insert into" & _  
          "part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&) 
If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then  
    MsgBox "Error" 
End If 
MsgBox "# of records inserted : " & OraSqlStmt.RecordCount 
  
Exit Sub 
ERR_array_sql: 
 
    MsgBox Err.Description 
 
 

RelMsgId (OraAQ) Property

Applies To

OraAQ Object

Description

Specifies that the message of this queue object is enqueued ahead of the message specified by the message ID.

Usage

OraAq.RelMsgid = msg_id 

Data Type

String

Remarks

This method is applicable only for an enqueue operation.

Possible values include:

• Any valid message identifier, specified by an array of bytes.

• ORAAQ_NULL_MSGID (Default): No message identifier specified.

Setting this property invokes enqueue with the ORAAQ_ENQ_BEFORE option. Set this property to ORAAQ_NULL_MSGID to place the message on top of the queue.

RowPosition Property

Applies To

OraDynaset Object

Description

Returns the row number of the current row in the dynaset. Not available in design time and read-only in run time.

Usage

rownum = OraDynaset.RowPosition

Data Type

Integer

SafeArray (OraCollection) Property

Applies To

OraCollection Object

Description

Gets or sets the element values from the Variant SAFEARRAY.

Usage

SafeArray = OraCollection.SafeArrayOraCollection.SafeArray = SafeArray

Arguments

Data Type

A Variant representing a SafeArray format.

Remarks

This property is only valid for simple scalar elements types, such as VARCHAR2 and NUMBER. This property raises an error for element type LOBS, Objects, Refs, and so on.

The Variant SAFEARRAY index starts at 0. When converting to SAFEARRAY format, the OraCollection object converts its element value to its corresponding SAFEARRAY Variant type. The following table explains collection element types and their corresponding SAFEARRAY Variant types:

For setting a SAFEARRAY to a collection, OraCollection converts the SAFEARRAY elements to its nearest collection element type.

Second (OraTimeStamp) Property

Applies To

OraTimeStamp Object

Description

Returns or sets the Second attribute of an OraTimeStamp object.

Usage

second = OraTimeStampObj.SecondOraTimeStampObj.Second= second

Arguments

Data Type

Integer

Second (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the Second attribute of an OraTimeStampTZ object.

Usage

second = OraTimeStampTZObj.SecondOraTimeStampTZObj.Second= second

Arguments

Data Type

Integer

Seconds Property

Applies To

OraIntervalDS Object

Description

Gets and sets the Seconds attribute of an OraIntervalDS object.

Usage

seconds = OraIntervalDSObj.SecondsOraIntervalDSObj.Seconds = seconds

Arguments

Data Type

Integer

Server Property

Applies To

OraDatabase Object

Description

Returns the OraServer object to which this object is attached.

Usage

Set oraserver = oradatabase.Server

Data Type

OLE Object (OraServer)

ServerType Property

Applies To

OraParameter Object

OraParamArray Object

Description

Specifies the Oracle external type of a SQL or PL/SQL bind variable. Not available at design time and read/write at run time.

Read-only for the OraParamArray object. Specify the ServerType property during the AddTable method.

Usage

oraparameter.ServerType = oracle_type

Data Type

Integer

Remarks

Used to specify the external data type of SQL or PL/SQL (in/out) bind variables. This is necessary because no local parsing of the SQL statement or PL/SQL block is done to match the data types of placeholders in the SQL statement or PL/SQL block.

After an OraParameter object has been set to ServerType BLOB, CLOB, BFILE, OBJECT, REF, VARRAY, or NESTED TABLE, it cannot be changed to any other ServerType property.

The following Oracle external data types are supported.

These values can be found in the ORACLE_BASE\ORACLE_HOME\oo4o\oraconst.txt file.

Examples

This example demonstrates the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a stored procedure and function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Add EMPNO as an Input/Output parameter and set its initial value.
 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER
 
 'Add ENAME as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2
 
 'Add SAL as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER
 
 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
 OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
 'Display the employee number and name.
 
 'Execute the Stored Function Employee.GetSal to retrieve SAL.
 ' This Stored Function can be found in the file ORAEXAMP.SQL.
 OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin" & _ 
                ":SAL:=Employee.GetEmpSal (:EMPNO); end;")
 
 'Display the employee name, number and salary.
 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value
 
 'Remove the Parameters.
 OraDatabase.Parameters.Remove "EMPNO"
 OraDatabase.Parameters.Remove "ENAME"
 OraDatabase.Parameters.Remove "SAL"
 
End Sub

Session Property

Applies To

OraCollection Object

OraDatabase Object

OraDynaset Object

OraSQLStmt Object

OraServer Object

Description

Returns the OraSession object associated with the specified object. Not available at design time and read-only at run time.

Usage

Set orasession = oraconnection.Session
Set orasession = oradatabase.Session
Set orasession = oradynaset.Session 
Set orasession = orasqlstmt.Session
Set orasession = oraserver.Session

Data Type

OLE Object (OraSession)

Remarks

• oraconnection.Session

  Returns the OraSession object in which this OraConnection object resides.

• oradatabase.Session

  Returns the OraSession object associated with this OraDatabase object. Each database is a part of one session, which is, by default, the session associated with the application.

• oradynaset.Session

  Returns the OraSession object associated with this OraDynaset object.

• orasqlstmt.Session

  Returns the OraSession object associated with this OraSQLStmt object.

Sessions Property

Applies To

OraClient Object

Description

Returns the collection of all sessions for the specified OraClient object. Not available at design time and read-only at run time.

Usage

Set orasessions_collection = oraclient.Sessions

Data Type

OLE Object (OraSessions)

Remarks

You can access a session in this collection by subscripting (using ordinal numbers) or by using the name the session was given at its creation. You can obtain the total number of sessions in the collection by using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count-1. Out-of-range indexes and invalid names return a Null OraSession object.

Size Property

Applies To

OraField Object

Description

Returns the number of characters or bytes of the Variant associated with the returned value of this field. Not available at design time and read-only at run time.

Usage

field_size = orafield.Size

Data Type

Long Integer

Remarks

This property returns 0 for LONG or LONG RAW fields. Use the FieldSize method to determine the length of LONG or LONG RAW fields.

Size (OraCollection) Property

Applies To

OraCollection Object

Description

Returns the current size of the given collection. Read-only at run time.

Usage

coll_size = OraCollection.Size

Data Type

Integer

Remarks

For an OraCollection object of type ORATYPE_TABLE, this property returns the current size of the collection including deleted elements.

Size (OraLOB and OraBFILE) Property

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Returns the number of bytes in OraBLOB and OraBFILE objects or the number of characters in an OraCLOB object. Read-only.

Usage

bytes = OraBFile.Size
 
bytes = OraBlob.Size 
 
chars = OraClob.Size

SnapShot Property

Applies To

OraDynaset Object

Description

Returns the SnapshotID.

Read and write at run time.

Usage

SnapshotID = OraDynaset.Snapshot

Remarks

The SnapshotID represents the snapshot from which this dynaset was created. It can be thought of as a timestamp. It can be passed into other CreateDynaset method calls to cause them to be created using data from the same point in time as the original dynaset.

The Snapshot property can be set with the value of another Snapshot. That new snapshot is used during the next Refresh operation when the query is reexecuted. The Snapshot property always returns the SnapshotID on which this OraDynaset object was based, not any other SnapshotID set through the snapshot property.

The SnapshotID becomes invalid after a certain amount of time; that amount of time is dependent on the amount of activity and the configuration of the database. When this happens, you get a Snapshot too old error message. For more information about snapshots, see the Oracle Database Concepts.

This SnapshotID represents the point in time when this dynaset was created. Changes to this dynaset (Edit, Delete, and AddNew operations) is not reflected in additional dynasets created using this SnapshotID because they occurred after that point in time.

SnapshotID objects are only meaningful for SELECT statements where the tables referenced are real database tables, as opposed to pseudo tables such as DUAL.

One valuable use of the SnapshotID is to calculate the number of rows in a table without using the RecordCount property which causes every row to be fetched. See "Example: Counting Rows in a Dynaset".

Data Type

Object

Examples

Example: Using the SnapShot Property

This example shows the use of the SnapShot property.

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset1 As OraDynaset 
Dim OraDynaset2 As OraDynaset 
Dim SnapshotID as SnapshotID 
 
 '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&) 
 
'ALLEN's JOB is initially SALESMAN 
OraDatabase.ExecuteSql("Update EMP set JOB = 'SALESMAN' where ENAME = 'ALLEN'") 
  
 'Create initial OraDynaset Object. 
 Set OraDynaset1 = OraDatabase.CreateDynaset("select empno, ename," & _ 
            "job from emp", 0&) 
MsgBox "OraDynaset1 -- Value of JOB is " & OraDynaset1.Fields("JOB").Value 
 
'Change Allen's JOB 
OraDatabase.ExecuteSql("Update EMP set JOB = 'CLERK' where ENAME = 'ALLEN'") 
 
'This SnapshotID represents the point in time in which OraDynaset1 was created
Set SnapshotID = OraDynaset1.Snapshot 
 
'Create OraDynaset2 from the same point in time as OraDynaset1 
Set OraDynaset2 = OraDatabase.CreateDynaset("select JOB from EMP" & _ 
               "where ENAME = 'ALLEN'", 0&, SnapshotID) 

MsgBox "OraDynaset2 -- Value of JOB from point of time of OraDynaset1 is " & _
                OraDynaset2.Fields("JOB").Value 
 
'We set the snapshot to NULL which will get us current point in time. 
OraDynaset2.Snapshot = Null 
 
'We refresh it and it will get us the data from the current point in time  
OraDynaset2.Refresh 
MsgBox "OraDynaset2 -- Value of JOB from current point of time is " & _
                OraDynaset2.Fields("JOB").Value 
 
'And back again to the old point in time -- 
OraDynaset2.Snapshot = SnapshotID 
OraDynaset2.Refresh 
MsgBox "OraDynaset2 -- Value of JOB from point of time of OraDynaset1 is " & _
              OraDynaset2.Fields("JOB").Value 
 

Example: Counting Rows in a Dynaset

This example counts the number of rows in a dynaset without using the RecordCount property, which fetches every row. Note that the record count this returns cannot take into account any AddNew or Delete operations, making the information meaningful only immediately after the dynaset is created

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim OraDynCount As OraDynaset 
Dim SnapshotID as SnapshotID 
 
 '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 the Dynaset 
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 
Set SnapshotID = OraDynaset.Snapshot 
 
'Use the snapshot for count query to guarantee the same point in time 
Set OraDynCount = OraDatabase.CreateDynaset("select count(*) NUMROWS" & _
         "from emp", 0&, SnapshotID)
MsgBox "Number of rows in the table is " & OraDynCount.Fields("NUMROWS").Value 

Sort Property

Remarks

The OraDynaset object does not support this property. Sort your record set by using a SQL ORDER BY clause.

SQL Property

Applies To

OraDynaset Object

OraSQLStmt Object

Description

Returns or sets the SQL statement used to create the specified dynaset or OraSQLStmt object. Not available at design time and read/write at run time.

Usage

SQL_statement  = oradynaset.SQL
SQL_statement  = orasqlstmt.SQL
 
oradynaset.SQL = SQL_statement 
orasqlstmt.SQL = SQL_statement

Data Type

String

Remarks

The first use returns the contents of the SQL statement buffer, and the second use sets the contents of the SQL statement buffer.

The SQL statement buffer initially contains the SQL statement used to create the dynaset or OraSQLStmt object. The contents of the SQL statement buffer are executed whenever the Refresh method is issued.

Examples

This example demonstrates the use of parameters, the Refresh method, and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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 parameter with an initial value.
 OraDatabase.Parameters.Add "job", "MANAGER", 1
 
 'Create the OraDynaset Object.
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where " & _
               "job=:job", 0&)
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraDynaset.SQL
 
 'Currently, OraDynaset only contains employees whose job is MANAGER.
 
 'Change the value of the job parameter.
 OraDatabase.Parameters("job").Value = "SALESMAN"
 
 'Refresh the dynaset.
 OraDynaset.Refresh
 
 'Currently, OraDynaset only contains employees whose 'job is SALESMAN.
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraDynaset.SQL
 
 'Remove the parameter.
 OraDatabase.Parameters.Remove ("job")
 
 End Sub

Status Property

Applies To

OraParameter Object

OraParamArray Object

Description

Returns an integer indicating the status of the specified parameter. Not available at design time and read-only at run time.

Usage

parameter_status = oraparameter.Statusparameter_status = oraparamarray.Status

Data Type

Integer

Remarks

The Status property is interpreted as a series of bits, each providing information about the parameter. Parameters can be bound only if they are enabled, and can be enabled only if they are auto-enabled.

The parameter Status property bit values are:

These values are located in the ORACLE_BASE\ORACLE_HOME\oo4o\oraconst.txt file.

Examples

This example demonstrates the use of parameters and the ExecuteSQL method to call a stored procedure (located in ORAEXAMP.SQL). After calling the stored procedure, the Status property of each parameter is checked. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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&)
 
 'Add EMPNO as an Input parameter and set its initial value.
 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
 
 'Add ENAME as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
 
 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
 ' This Stored Procedure is located in the file ORAEXAMP.SQL.
 OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
 
 If OraDatabase.Parameters("EMPNO").Status & ORAPSTAT_INPUT Then
  MsgBox "Parameter EMPNO used for input."
 End If
 
 If OraDatabase.Parameters("ENAME").Status & ORAPSTAT_OUTPUT Then
  MsgBox "Parameter ENAME used for output."
 End If
 
'Display the employee number and name.
 MsgBox OraDatabase.Parameters("EMPNO").value
 MsgBox OraDatabase.Parameters("ENAME").value
 
 'Remove the Parameters.
 OraDatabase.Parameters.Remove "EMPNO"
 OraDatabase.Parameters.Remove "ENAME"
 
End Sub

Status (OraLOB/BFILE) Property

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Returns the status of the current polling operation.

Read-only.

Usage

status = OraBFile.Status 
status  = OraBlob.Status 
status = OraClob.Status 

Data Type

Integer

Remarks

This value only has meaning when the PollingAmount property is not zero, and a Read operation has occurred. Possible return values are:

• ORALOB_NEED_DATA

  There is more data to be read or written.

• ORALOB_NODATA

  There is no data to be read or written, usually due to an error condition.

• ORALOB_SUCCESS LOB

  The data was read or written successfully.

Subscriptions Property

Applies To

OraDatabase Object

Description

Returns the OraSubscriptions collection of the specified database. Not available at design time and read-only at run time.

Usage

Set orasubscriptions_collection = oradatabase.Subscriptions

Data Type

OLE Object (OraSubscriptions)

Remarks

You can access the subscriptions in this collection by subscripting (using ordinal integer numbers). You can obtain the number of subscriptions in the collection using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count-1. Out-of-range indexes return a Null OraSubscription object.

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

Examples

See "Example: Registering an Application for Notification of Database Events" for a complete example.

TableName (OraRef) Property

Applies To

OraRef Object

Description

A String containing the name of the object table in which the underlying referenceable object resides.

Usage

table_name = OraRef.TableName

Data Type

String

Remarks

This property is read-only.

TableSize (OraCollection) Property

Applies To

OraCollection Object

Description

Returns the current size of the given collection. Read-only at run time.

Usage

table_size = OraCollection.TableSize

Data Type

Integer

Remarks

For an OraCollection object of type ORATYPE_TABLE, it returns the current size of the collection, excluding deleted elements.

TimeZone (OraTimeStampTZ) Property

Applies To

OraTimeStampTZ Object

Description

Returns or sets the time zone information of an OraTimeStampTZ object.

Usage

timezone = OraTimeStampTZObj.TimeZoneOraTimeStampTZObj.TimeZone= timezone

Arguments

Data Type

String

Remarks

Setting the TimeZone property does not change the Coordinated Universal Time (UTC) datetime values stored in the OraTimeStampTZ object. However, the local datetime values in the specified time zone can change.

The following table shows the UTC datetime values that correspond to the datetime and time zone values of the OraTimeStampTZ object in the example.

Setting the TimeZone property to -08:00 changes the datetime values in the specified time zone of the OraTimeStampTZ object, but does not change the UTC datetime values.

Examples

Dim OraTSTZ as OraTimeStampTZ 
Dim OraTSTZ_new as OraTimeStampTZ 
Dim OraTSTZStr as String 
Dim OraTSTZStr_new as String 
Set OraTSTZ = oo4oSession.CreateOraTimeStampTZ( "2003-APR-29" & _
       "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM")
 
'Change Time Zone to "-08:00" 
Set OraTSTZ_new = OraTSTZ.Clone 
oraTSTZ_new.TimeZone = "-08:00" 
 
'OraTSTZStr has value as (29-APR-03 12.00.00.000000000 PM -07:00) 
OraTSTZStr = OraTSTZ.value 
'OraTSTZStr_new has value as (29-APR-03 11.00.00.000000000 PM -08:00) 
OraTSTZStr_new = OraTSTZ_new.value 

TotalDays Property

Applies To

OraIntervalDS Object

Description

Gets and sets the total number of days that this OraIntervalDS object represents.

Usage

totalDays = OraIntervalDSObj.TotalDays
OraIntervalDSObj.TotalDays = totalDays

Arguments

Data Type

Double

Examples

Dim oraIDS   as OraIntervalDS 
'Create an OraIntervalDS using a string which represents 1 day and 12 hours 
Set oraIDS = oo4oSession.CreateOraIntervalDS("1 12:0:0.0") 
 
'totalDays is set to 1.5 which represents an interval of 1.5 days
totalDays = oraIDS.TotalDays 

TotalYears Property

Applies To

OraIntervalYM Object

Description

Gets and sets the total number of years that this OraIntervalYM object represents.

Usage

totalYears = OraIntervalYMObj.TotalYearsOraIntervalYMObj.TotalYears= totalYears

Arguments

Data Type

Double

Examples

Dim oraIYM   as OraIntervalYM 
 
'Create an OraIntervalYM using a string which represents 1 year and 6 months
Set oraIYM = oo4oSession.CreateOraIntervalYM("1-6") 
'totalYears is set to 1.5 which represents an interval of 1.5 years 
totalYears = oraIYM.TotalYears

Transactions Property

Applies To

OraDynaset Object

Description

Indicates whether or not the given dynaset can support transaction processing. Not available at design time and read-only at run time.

Usage

if_transactions = oradynaset.Transactions

Data Type

Integer (Boolean)

Remarks

This property always returns True.

Truncated Property

Applies To

OraField Object

Description

Indicates whether or not a field value was truncated when fetched. Not available at design time and read-only at run time.

Usage

field_status = orafield.Truncated

Data Type

Integer (Boolean)

Remarks

This property returns True if truncated data is returned; otherwise, it returns False. Truncation can only occur for LONG or LONG RAW fields. Use this property to decide whether more data needs to be retrieved from an Oracle database using the GetChunk method.

Type Property

Applies To

OraField Object

OraParameter Object

OraParamArray Object

Description

Returns the Variant type of the specified object. Not available at design time and read-only at run time.

Usage

data_type = orafield.Type
data_type = oraparameter.Type 
data_type = oraparamarray.Type

Data Type

Integer

Remarks

• orafield.Type

  Returns the Variant data type (see Visual Basic documentation) associated with the returned value of this field.

• oraparameter.Type

  Returns an integer indicating the Variant data type that is actually bound to the SQL statement. This may differ from the Variant data type of oraparameter.Value, because internal conversions may be necessary to obtain a data type common to both Visual Basic and Oracle Database.

Users can expect the following mapping from Oracle internal data types:

These values are located in the ORACLE_BASE\ORACLE_HOME\oo4o\oraconst.txt file and are intended to match similar constants in the Visual Basic file datacons.txt file.

Note that fields of type DATE are returned in the default Visual Basic format as specified in the Control Panel, even though the default Oracle date format is "DD-MMM-YY".

Note that columns defined as NUMBER instead of NUMBER(precision, scale) are, by definition, floating point numbers with a precision of 38. This means that the Type property returns a type of ORADB_TEXT for these columns.

Type (OraAttribute) Property

Applies To

OraAttribute Object

Description

A integer code representing the type of this attribute.

Usage

typecode = OraAttribute.Type

Data Type

Integer

Remarks

These integer codes correspond to external data types in Oracle Call Interface (OCI). See Oracle data types.

Type (OraCollection) Property

Applies To

OraCollection Object

Description

Returns the type code of the collection.

Usage

coll_type = OraCollection.Type

Data Type

Integer

Remarks

This property returns one of the following values:

Type (OraMetaData) Property

Applies To

OraMetaData Object

Description

Returns type of the schema object described by the OraMetaData object.

Usage

type = OraMetaData.Type

Remarks

The possible values include the following constants:

TypeName (OraObject and OraRef) Property

Applies To

OraObject Object

OraRef Object

Description

Specifies a String containing the name of the user-defined type of the object.

Usage

typename = OraRef.TypeName
typename = OraObject.TypeName

Data Type

String

Remarks

This property is read-only at run time.

Updatable Property

Applies To

OraDynaset Object

Description

Returns whether or not the specified dynaset is updatable. Not available at design time and read-only at run time.

Usage

if_updatable = oradynaset.Updatable

Data Type

Integer (Boolean)

Remarks

Returns True if the rows in the specified dynaset can be updated; otherwise, it returns False.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the read-only flag of the CreateDynaset method.

To be updatable, three conditions must be met:

1. The SQL statement must refer to a simple column list or to the entire column list (*).

2. The SQL statement must not set the read-only flag of the options argument.

3. Oracle Database must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and this property returns False.

Examples

This example demonstrates the use of the Updatable method. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset 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 an updatable dynaset using a simple query.
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
 
 Call IsDynUpdatable(OraDynaset)
 
 'Create a non-updatable dynaset using column aliases.
 Set OraDynaset = OraDatabase.CreateDynaset("select ename EmployeeName," & _
                  "empno EmployeeNumber, sal Salary from emp", 0&)
 Call IsDynUpdatable(OraDynaset)
 
 'Create a non-updatable dynaset using a join.
 Set OraDynaset = OraDatabase.CreateDynaset("select ename, emp.deptno," & _ 
           "loc from emp, dept where emp.deptno = dept.deptno", 0&)
 Call IsDynUpdatable(OraDynaset)
 
End Sub
 
Sub IsDynUpdatable (odyn As OraDynaset)
 
 'Check to see if the dynaset is updatable.
 If odyn.Updatable = True Then
  MsgBox "Created an UPDATABLE dynaset from: '" & odyn.SQL & "'"
 Else
  MsgBox "Created a READ-ONLY dynaset from: '" & odyn.SQL & "'"
 End If
 
End Sub

Value Property

Applies To

OraField Object

OraParameter Object

Description

Returns or sets the value of the given object. Not available at design time and read/write at run time.

Usage

orafield.Value = data_value
data_value = orafield.Value
 
oraparameter.Value = data_value
data_value = oraparameter.Value

Data Type

Variant

Remarks

• Orafield.Value

  Returns the value of the field as a Variant.

  data_value = orafield.Value sets the contents of the field. Fields can contain Null values. You can test the Value property with the Visual Basic function IsNull() to determine whether the value is null upon return. You can also assign Null to the Value property whenever the current record is editable. Field values are cached locally as the data is retrieved from the database. However, in the case of a LONG or LONG RAW fields, some data may not be retrieved and stored locally. In these cases, data is retrieved as required using the methods described in the GetChunk field method. The maximum size of a LONG or LONG RAW field that can be retrieved directly through the Value property is approximately 64 KB. You must retrieve data fields larger than 64 KB indirectly, using the GetChunk method.

• OraParameter.Value

  Returns the value of the parameter as a Variant.

  data_value = oraparameter.Value sets the contents of the parameter. Note that changing the Variant data type of the value can have significant impact on the processing of associated SQL and PL/SQL statements.

Note that fields of type DATE are returned in the default Visual Basic format of "MM/DD/YY" even though the default Oracle date format is "DD-MMM-YY".

The Value argument can be an Oracle Database 10g object, such as an OraBLOB.

Similar to a dynaset, the object obtained from parameter Value property always refers to the latest value of the Parameter. The Visual Basic value Null can also be passed as a value. The Visual Basic value EMPTY can be used for BLOB and CLOB to mean an empty LOB, and for OBJECT, VARRAY, and NESTED TABLE to mean an object whose attributes are all Null.

Value (OraAttribute) Property

Applies To

OraAttribute Object

Description

Gets or sets the value of the attribute. This value could be an instance of an OraObject, OraRef, or OraCollection object, or any of the supported scalar types, such as Integer or Float.

Usage

attr_value = OraAttribute.Value  OraAttribute.Value = attr_value

Data Type

Variant

Remarks

This is the default property for this object.

The Value property of the OraAttribute object returns the value of the attribute as a Variant. The Variant type of the attribute depends on the attribute type of the attribute. Attribute values can be Null and can be set to Null. For attribute of type objects, REF, LOB and Collection, attribute values are returned as corresponding OO4O objects for that type.

The following table identifies the attribute type and the return value of the Value property of the OraAttribute object:

Value (OraAQMsg) Property

Applies To

OraAQMsg Object

Description

Returns or sets the value of the given object.

Usage

Msg.Value = my_string 
set Msg.Value = OraObj 
 
my_string = Msg.Value 
Set OraObj = Msg.Value 

Data Type

String

Remarks

The Value property represents the actual message for RAW as well as user-defined types.

This property is not available at design time and read/write at run time.

Examples

'To set the value for a message of Raw type 
OraAQMsg.Value = "This is a test message" 
myString = "Another way of setting the message" 
OraAQMsg.Value = myString 
 
'To set the value for a message of user-defined type  
Dim OraObj as OraObject 
OraObj("subject").Value = txtdesc 
OraObj("text").Value = txtmsg 
set OraAQMsg.Value = OraObj 
 
'To get the value from a message of raw type 
myString = OraAQMsg.Value 
 
'To get the value from a message of object type(user-defined type) 
Set OraObj = OraMsg.Value 
txtdesc = OraObj("subject").Value 
txtmsg = OraObj("text").Value

Value (OraIntervalDS) Property

Applies To

OraIntervalDS Object

Description

When read, the Value property provides a string representation of the value of the OraIntervalDS object using the format [+/-]Day HH:MI:SSxFF. When set, the Value property accepts a Variant of type String, a numeric value, or an OraIntervalDS object.

Usage

string = OraIntervalDSObj.Value OraIntervalDSObj.Value = value

Arguments

Data Type

Variant

Remarks

If the value set is a Variant of type String, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If the value set is a numeric value, the value provided should represent the total number of days that the OraIntervalDS object represents.

Examples

Dim oraIDS as OraIntervalDS 
 
'Create an OraIntervalDS using a string which represents 1 day and 12 hours 
Set oraIDS = oo4oSession.CreateOraIntervalDS("1 12:0:0.0") 
 
'get the OraIntervalDS.Value return a string for the Value
' property, idsStr is set to "01 12:00:00.000000" 
idsStr = oraIDS.Value 
 
'can also return a string for the Value property as follows 
idsStr = oraIDS 
 
'set the OraIntervalDS.Value using a string which represents 1 days and 12 hours
oraIDS.Value = "1 12:0:0.0" 
 
'set the OraIntervalDS.Value using a numeric value which represents 
'1 days and 12 hours 
oraIDS.Value = 1.5 

Value (OraIntervalYM) Property

Applies To

OraIntervalYM Object

Description

When read, the Value property provides a string representation of the value of the OraIntervalYM object using the format YEARS-MONTHS.

When set, the Value property accepts a Variant of type String, a numeric value, or an OraIntervalYM object.

Usage

string = OraIntervalYMObj.ValueOraIntervalYMObj.Value= value

Arguments

Data Type

String

Remarks

If the value set is a Variant of type String, it must be in following format: [+/-] YEARS-MONTHS.

If the value set is a numeric value, the value provided should represent the total number of years that the OraIntervalYM object represents.

Examples

Dim oraIYM as OraIntervalYM 
 
'Create an OraIntervalYM using a string which represents 1 year and 6 months 
Set oraIYM = oo4oSession.CreateOraIntervalYM("1-6") 
 
'get the OraIntervalYM.Value return a string for the Value property,
' iymStr is set to "01-06" 
iymStr = oraIYM.Value 
 
'can also return a string for the Value property as follows 
iymStr = oraIYM 

'set the OraIntervalDS.Value using a string which represents 1 year and 6 months
oraIYM.Value = "1-6" 
 
'set the OraIntervalYM.Value using a numeric value which represents 
'1 years and 6 months 
oraIYM.Value = 1.5 

Value (OraMDAttribute) Property

Applies To

OraMDAttribute Object

Description

A String containing the value of the attribute.

Usage

value = OraMDAttribute.Value

Data Type

String

Remarks

This is the default property.

Value (OraNumber) Property

Applies To

OraNumber Object

Description

When read, the Value property provides a string representation of the value of the OraNumber object using the current format string. When set, the Value property accepts a Variant of type String, OraNumber, or a numeric value. Read and write at run time.

Usage

string = OraNumber.Value  
OraNumber.Value = variantval 

Arguments

Data Type

Variant

Remarks

If the Value property is set to a numeric type, such as a LONG, it is limited to the maximum precision Visual Basic provides for numerical values.

If the current format cannot be applied successfully to the value, an error is raised. An error is also raised if this property is set to a Variant value that cannot be converted to a number, such as a string of nonnumeric characters.

Value (OraTimeStamp) Property