Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
|
|
View PDF |
This chapter describes the Oracle Objects for OLE Server properties.
For an introduction to Server Objects, see "Oracle Objects for OLE In-Process Automation Server" .
This chapter contains these topics:
Server Properties: A to F
Server Properties: E to L
Server Properties: M to O
Server Properties: P to T
Server Properties: U to Z
Applies To
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
Applies To
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.
See Also:
AddTable MethodApplies To
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")
Applies To
Description
Indicates True
if the collection iterator moves before the first element of a collection.
Usage
boc_flag = OraCollection.BOC
Data Type
boolean
Examples
Applies To
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
Applies To
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
Applies To
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
.
See Also:
Applies To
Description
Gets or set cache maximum number of blocks.
Usage
set blocks = oradynaset.CacheBlocks oradynaset.CacheBlocks = blocks
Data Type
Integer
Applies To
Description
True
if cache or fetch parameters have been changed.
Usage
set Changed = oradynaset.CacheChanged
Data Type
Boolean
Applies To
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.
Applies To
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.
Applies To
Description
Gets or sets cache slice size.
Usage
set Slicesize = oradynaset.CacheSliceSize oradynaset.CacheSliceSize = Slicesize
Data Type
Integer
Applies To
Description
Gets or sets cache slices for each block.
Usage
set Perblock = oradynaset.CacheSlicePerBlock oradynaset.CacheSlicePerBlock = Perblock
Data Type
Integer
Applies To
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.
See Also:
OraClient ObjectApplies To
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
See Also:
Applies To
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
.
See Also:
OraConnection ObjectApplies To
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
.
Applies To
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.
Applies To
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
Applies To
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.
Applies To
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.
See Also:
Correlate (OraAQ) PropertyApplies To
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
See Also:
Applies To
Description
An integer representing the number of OraMDAttribute
objects contained in this collection.
Usage
count = OraMetaData.Count
Data Type
Integer
See Also:
OraMDAttribute ObjectApplies To
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
See Also:
OraAttribute ObjectApplies To
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.
See Also:
Applies To
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
Applies To
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
)
See Also:
OraCollection ObjectApplies To
Description
Gets and sets the Day
attribute of an OraTimeStamp
object.
Usage
day= OraTimeStampObj.Day OraTimeStampObj.Day= day
Arguments
Arguments | Description |
---|---|
[in ] day |
The Day attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Day
attribute of an OraTimeStampTZ
object.
Usage
day= OraTimeStampTZObj.Day OraTimeStampTZObj.Day= day
Arguments
Arguments | Description |
---|---|
[in ] day |
The Day attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Days
attribute of an OraIntervalDS
object
Usage
days = OraIntervalDSObj.Days OraIntervalDSObj.Days = days
Arguments
Arguments | Description |
---|---|
[in ] days |
An Integer specifying the value of the Days attribute of the OraIntervalDS object. |
Data Type
Integer
Applies To
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.
Applies To
Description
Contains the initial size of the pool. It is a read-only property.
Usage
init_size = OraSession.DbPoolInitialSize
Data Type
Integer
Applies To
Description
Contains the maximum pool size. It is a read-only property.
Usage
max_size = OraSession.DbPoolMaxSize
Data Type
Integer
Applies To
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.
Applies To
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.
Applies To
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.
Applies To
Description
Gets or sets the directory alias name.
Usage
diralias = OraBFile.DirectoryName OraBFile.DirectoryName = diralias
Arguments
Arguments | Description |
---|---|
[in ] [out ] diralias |
A String specifying the directory name to be retrieved or set. |
Data Type
String
Remarks
This String
is case-sensitive.
Applies To
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:
Possible Values | Value | Description |
---|---|---|
ORADYN_DEFAULT |
&H0& |
Accepts the default behavior. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Does not remove trailing blanks from character string data retrieved from the database. |
ORADYN_NOCACHE |
&H8& |
Does not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage. |
ORADYN_NO_MOVEFIRST |
&H40& |
Does not force a MoveFirst operation when a dynaset is created. BOF and EOF are both True . |
Applies To
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:
Constant | Value | Description |
---|---|---|
ORADATA_EDITNONE |
0 |
No editing in progress. |
ORADATA_EDITMODE |
1 |
Editing is in progress on an existing row. |
ORADATA_EDITADD |
2 |
A new record is being added and the copy buffer does not currently represent an actual row in the database. |
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.
See Also:
Applies To
Description
Specifies whether the object is to be locked during the pin operation.
Usage
edit_option = OraRef.EditOption OraRef.EditOption = edit_option
Arguments
Arguments | Description |
---|---|
[in ] [out ] edit_option |
An Integer representing the edit option. |
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:
Constant | Value | Description |
---|---|---|
ORAREF_NO_LOCK |
1 |
Does not lock the object in the database (default). |
ORAREF_EXCLUSIVE_LOCK |
2 |
Maintains an exclusive lock on the object in the database. |
ORAREF_NOWAIT_LOCK |
3 |
Maintains an exclusive lock on the object in the database with the nowait option. |
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
See Also:
Edit (OraRef) MethodApplies To
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:
Constant | Value | External Data Type |
---|---|---|
ORATYPE_VARCHAR2 |
1 |
VARCHAR2 |
ORATYPE_NUMBER |
2 |
NUMBER |
ORATYPE_SINT |
3 |
SIGNED INTEGER |
ORATYPE_FLOAT |
4 |
FLOAT |
ORATYPE_VARCHAR |
9 |
VARCHAR |
ORATYPE_DATE |
12 |
DATE |
ORATYPE_UINT |
68 |
UNSIGNED INTEGER |
ORATYPE_CHAR |
96 |
CHAR |
ORATYPE_CHARZ |
97 |
Null Terminated CHAR |
ORATYPE_BFLOAT |
100 |
BINARY_FLOAT |
ORATYPE_BDOUBLE |
101 |
BINARY_DOUBLE |
ORATYPE_OBJECT |
108 |
Object |
ORATYPE_REF |
110 |
Ref |
Applies To
Description
Returns True
if the collection iterator moves past the last element of a collection.
Usage
eoc_flag = OraCollection.EOC
Data Type
Boolean
Examples
Applies To
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
Applies To
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.
Applies To
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;
Applies To
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.
Applies To
Description
Gets or sets the array size of the fetch.
Usage
set Limit = oradynaset.FetchLimit oradynaset.FetchLimit = Limit
Data Type
Integer
Applies To
Description
Gets or sets the array buffer size of the fetch.
Usage
set Size = oradynaset.FetchSize oradynaset.FetchSize = Size
Data Type
Integer
Applies To
Description
Returns the index of the field indicated by the field_name
argument.
Usage
set index = oradynaset.FieldIndex(field_name)
Arguments
Arguments | Description |
---|---|
[in ] field_name |
The name of the field as it appears in the SQL statement that the dynaset used most recently. |
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.
Applies To
Description
Returns the field name in the SELECT
statement in the dynaset.
Usage
set field_name = oradynaset.FieldName(index)
Arguments
Arguments | Description |
---|---|
[in ] index |
Index of the name of the field as it appears in the SQL statement. |
Data Type
String
Applies To
Description
Gets the original field name used in the SELECT
statement in the dynaset.
Usage
set field_name = oradynaset.FieldOriginalName(index)
Arguments
Arguments | Description |
---|---|
[in ] index |
An Integer specifying the field index of the original field name as it appears in the SQL statement. |
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.
Applies To
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
Arguments | Description |
---|---|
[in ] name |
The original name of the field as it appears in the SQL statement. |
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.
Applies To
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.
See Also:
Applies To
Description
Gets or sets a filename. Read and write at run time.
Usage
filename = OraBFile.FileName OraBFile.FileName = filename
Arguments
Arguments | Description |
---|---|
[in ] [out ] filename |
A String specifying the directory name to be retrieved or set. |
Data Type
String
Remarks
This string can be case-sensitive depending on the database operating system.
See Also:
OraField ObjectRemarks
The OraDynaset
object does not support this property. Refine your record selection by using a SQL WHERE
clause or by using SQL parameters.
Applies To
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
Arguments | Description |
---|---|
[in ] formatstring |
A format string used in OraNumber operations. |
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.
See Also:
Oracle Database SQL Quick ReferenceApplies To
Description
Returns or sets the TIMESTAMP
format used to display the OraTimeStamp
object as a string.
Usage
format = OraTimeStampObj.Format OraTimeStampObj.Format = format
Arguments
Arguments | Description |
---|---|
[in ] format |
The format used to display an OraTimeStamp object as a string. |
Data Type
String
Remarks
If Format
is Null
, the session TIMESTAMP
format is used to display the OraTimeStamp
object as a string.
Applies To
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
Arguments | Description |
---|---|
[in ] format |
The format used to display an OraTimeStampTZ object as a string. |
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.
Applies To
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.
See Also:
FetchOraRef MethodApplies To
Description
Returns or sets the Hour
attribute of an OraTimeStamp
object.
Usage
hour = OraTimeStampObj.Hour OraTimeStampObj.Hour = hour
Arguments
Arguments | Description |
---|---|
[in ] hour |
The Hour attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Hour
attribute of an OraTimeStampTZ
object.
Usage
hour = OraTimeStampTZObj.Hour OraTimeStampTZObj.Hour = hour
Arguments
Arguments | Description |
---|---|
[in ] hour |
The Hour attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Hours
attribute of an OraIntervalDS
object.
Usage
hours = OraIntervalDSObj.Hours OraIntervalDSObj.Hours = hours
Arguments
Arguments | Description |
---|---|
[in ] hours |
An Integer specifying the value of the Hours attribute of the OraIntervalDS object. |
Data Type
Integer
Applies To
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)
Applies To
Description
Returns True
if the Value
property is another OraMetaData
object; otherwise, the property is False
.
Usage
isobject = OraMDAttribute.IsMDObject
Data Type
Boolean
Applies To
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.
Applies To
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.
Applies To
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
Applies To
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
.
See Also:
Open (OraBFILE) MethodApplies To
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.
Applies To
Description
Gets the last error message. Not available at design time and read-only at run time.
Usage
OraParamArray.LastErrorText
Data Type
String
Applies To
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.
See Also:
Applies To
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
Applies To
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).
Applies To
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:
If Null
is returned, an Oracle Call Interface (OCI) database function has not returned an error since the most recent LastServerErrReset
property.
If a non-Null
value is returned, an OCI function has returned an error code; the returned string is the associated message.
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
Applies To
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.
Applies To
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;")
See Also:
Applies To
Description
Returns or sets the Minute
attribute of an OraTimeStamp
object.
Usage
minute = OraTimeStampObj.Minute OraTimeStampObj.Minute = minute
Arguments
Arguments | Description |
---|---|
[in ] minute |
The Minute attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Minute
attribute of an OraTimeStampTZ
object.
Usage
minute = OraTimeStampTZObj.Minute OraTimeStampTZObj.Minute = minute
Arguments
Arguments | Description |
---|---|
[in ] minute |
The Minute attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Minutes
attribute of an OraIntervalDS
object.
Usage
minutes = OraIntervalDSObj.Minutes OraIntervalDSObj.Minutes = minutes
Arguments
Arguments | Description |
---|---|
[in ] minutes |
An Integer specifying the value of the Minutes attribute of the OraIntervalDS object. |
Data Type
Integer
Applies To
Description
Returns or sets the Month
attribute of an OraTimeStamp
object.
Usage
month = OraTimeStampObj.Month OraTimeStampObj.Month = month
Arguments
Arguments | Description |
---|---|
[in ] month |
The Month attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Month
attribute of an OraTimeStampTZ
object.
Usage
month = OraTimeStampTZObj.Month OraTimeStampTZObj.Month = month
Arguments
Arguments | Description |
---|---|
[in ] month |
The Month attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Months
attribute of an OraIntervalYM
object.
Usage
months = OraIntervalYMObj.Months OraIntervalYMObj.Months = months
Arguments
Arguments | Description |
---|---|
[in ] month |
An Integer specifying the value of the Months attribute of the OraIntervalYM object. |
Data Type
Integer
Applies To
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.
See Also:
Oracle Database Concepts for more information about Oracle Database events
Applies To
Description
Returns a 30-byte string representing the name of agent.
Usage
agent_name = qMsg.AQAgent.Name
Data Type
String
Applies To
Description
A String
containing the name of the attribute.
Usage
name = OraAttribute.Name
Data Type
String
Remarks
Read-only at run time.
See Also:
OraAttribute ObjectApplies To
Description
A String
containing the name of the attribute.
Usage
name = OraMDAttribute.Name
Data Type
String
Applies To
Description
Returns or sets the Nanosecond
attribute of an OraTimeStamp
object.
Usage
nanosecond = OraTimeStampObj.NanosecondOraTimeStampObj.Nanosecond= nanosecond
Arguments
Arguments | Description |
---|---|
[in ] nanosecond |
The Nanosecond attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Nanosecond
attribute of an OraTimeStampTZ
object.
Usage
nanosecond = OraTimeStampTZObj.NanosecondOraTimeStampTZObj.Nanosecond= nanosecond
Arguments
Arguments | Description |
---|---|
[in ] nanosecond |
The Nanosecond attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Nanoseconds
attribute of an OraIntervalDS
object.
Usage
nanoseconds = OraIntervalDSObj.NanosecondsOraIntervalDSObj.Nanoseconds = nanoseconds
Arguments
Arguments | Description |
---|---|
[in ] nanoseconds |
An Integer specifying the value of the Nanoseconds attribute of the OraIntervalDS object. |
Data Type
Integer
Applies To
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.
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
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.
See Also:
Applies To
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.
See Also:
PollingAmount PropertyApplies To
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.
Applies To
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
Applies To
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.
Constant | Value | Internal Data Type |
---|---|---|
ORATYPE_VARCHAR2 |
1 |
VARCHAR2 |
ORATYPE_NUMBER |
2 |
NUMBER |
ORATYPE_LONG |
8 |
LONG |
ORATYPE_DATE |
12 |
DATE |
ORATYPE_RAW |
23 |
RAW |
ORATYPE_LONGRAW |
24 |
LONG RAW |
ORATYPE_CHAR |
96 |
CHAR |
ORATYPE_BFLOAT |
100 |
BINARY_FLOAT |
ORATYPE_BDOUBLE |
101 |
BINARY_DOUBLE |
ORATYPE_MLSLABEL |
105 |
MLSLABEL |
ORATYPE_OBJECT |
108 |
OBJECT |
ORATYPE_REF |
110 |
REF |
ORATYPE_CLOB |
112 |
CLOB |
ORATYPE_BLOB |
113 |
BLOB |
ORATYPE_BFILE |
114 |
BFILE |
ORATYPE_TIMESTAMP |
187 |
TIMESTAMP |
ORATYPE_TIMESTAMPTZ |
188 |
TIMESTAMP WITH TIME ZONE |
ORATYPE_INTERVALYM |
189 |
INTERVAL YEAR TO MONTH |
ORATYPE_INTERVALDS |
190 | INTERVAL DAY TO SECOND |
ORATYPE_TIMESTAMPLTZ |
232 |
TIMESTAMP WITH LOCAL TIME ZONE |
ORATYPE_VARRAY |
247 |
VARRAY |
ORATYPE_TABLE |
248 |
NESTED TABLE |
These values can be found in the ORACLE_BASE\ORACLE_HOME
\oo4o\oraconst.txt
file.
Applies To
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.
Applies To
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:
Oracle Column Type | Value |
---|---|
CHAR , VARCHAR2 , RAW |
Length of the column in the table |
NUMBER |
22 (the internal length) |
DATE |
7 (the internal length) |
LONG , LONG RAW |
0 |
ROWID |
System dependent |
Functions returning internal data type 1 , such as TO_CHAR() |
Same as orafield.MaxDSize |
Applies To
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
.
Applies To
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.
Applies To
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
.
Applies To
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.
Applies To
Description
Gets and sets the Pin
option for the referenceable object during the pin operation.
Usage
pin_option = OraRef.PinOptionOraRef.PinOption = pin_option
Arguments
Arguments | Description |
---|---|
[in ] PinOption |
An Integer representing the Pin option. |
Data Type
Integer
(Boolean)
Remarks
Possible values returned by the pin_option
property are:
Constant | Value | Description |
---|---|---|
ORAREF_READ_ANY |
3 |
If the object is already in the object cache, returns it, otherwise, retrieves it from the database(default). |
ORAREF_READ_RECENT |
4 |
If the object is retrieved into the cache during a transaction, returns it from the cache, otherwise retrieves the object from the database. |
ORAREF_READ_LATEST |
5 | Always retrieves the latest values from the database. |
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
Applies To
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.
Applies To
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.
Applies To
Description
Returns the database version.
Usage
Set Version = oradatabase.RDBMSVersion
Data Type
String
Applies To
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
Applies To
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.
Applies To
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
See Also:
OraField ObjectApplies To
Description
Gets or sets the element values from the Variant
SAFEARRAY
.
Usage
SafeArray = OraCollection.SafeArrayOraCollection.SafeArray = SafeArray
Arguments
Arguments | Description |
---|---|
SafeArray |
A Variant representing SafeArray format. |
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:
Collection Element Type | SAFEARRAY of |
---|---|
Date |
String |
Number |
String |
CHAR , VARCHAR2 |
String |
Real |
Real |
Integer |
Integer |
For setting a SAFEARRAY
to a collection, OraCollection
converts the SAFEARRAY
elements to its nearest collection element type.
Applies To
Description
Returns or sets the Second
attribute of an OraTimeStamp
object.
Usage
second = OraTimeStampObj.SecondOraTimeStampObj.Second= second
Arguments
Arguments | Description |
---|---|
[in ] second |
The Second attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Second
attribute of an OraTimeStampTZ
object.
Usage
second = OraTimeStampTZObj.SecondOraTimeStampTZObj.Second= second
Arguments
Arguments | Description |
---|---|
[in ] second |
The Second attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Seconds
attribute of an OraIntervalDS
object.
Usage
seconds = OraIntervalDSObj.SecondsOraIntervalDSObj.Seconds = seconds
Arguments
Arguments | Description |
---|---|
[in ] seconds |
An Integer specifying the value of the Seconds attribute of the OraIntervalDS object. |
Data Type
Integer
Applies To
Description
Returns the OraServer
object to which this object is attached.
Usage
Set oraserver = oradatabase.Server
Data Type
OLE Object (OraServer
)
See Also:
OraServer ObjectApplies To
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.
Constant | Value | Internal Data Type |
---|---|---|
ORATYPE_VARCHAR2 |
1 |
VARCHAR2 |
ORATYPE_NUMBER |
2 |
NUMBER |
ORATYPE_SINT |
3 |
SIGNED INTEGER |
ORATYPE_FLOAT |
4 |
FLOAT |
ORATYPE_STRING |
5 |
Null Terminated STRING |
ORATYPE_LONG |
8 |
LONG |
ORATYPE_VARCHAR |
9 |
VARCHAR |
ORATYPE_DATE |
12 |
DATE |
ORATYPE_RAW |
23 |
RAW |
ORATYPE_LONGRAW |
24 |
LONG RAW |
ORATYPE_UINT |
68 |
UNSIGNED INTEGER |
ORATYPE_CHAR |
96 |
CHAR |
ORATYPE_CHARZ |
97 |
Null Terminated CHAR |
ORATYPE_BFLOAT |
100 |
BINARY_FLOAT |
ORATYPE_BDOUBLE |
101 |
BINARY_DOUBLE |
ORATYPE_CURSOR |
102 |
PLSQL CURSOR |
ORATYPE_MLSLABEL |
105 |
MLSLABEL |
ORATYPE_OBJECT |
108 |
OBJECT |
ORATYPE_REF |
110 |
REF |
ORATYPE_CLOB |
112 |
CLOB |
ORATYPE_BLOB |
113 |
BLOB |
ORATYPE_BFILE |
114 |
BFILE |
ORATYPE_TIMESTAMP |
187 |
TIMESTAMP |
ORATYPE_TIMESTAMPTZ |
188 |
TIMESTAMP WITH TIMEZONE |
ORATYPE_INTERVALYM |
189 |
INTERVAL YEAR TO MONTH |
ORATYPE_INTERVALDS |
190 |
INTERVAL DAY TO SECOND |
ORATYPE_TIMESTAMPLTZ |
232 |
TIMESTAMP WITH LOCAL TIME ZONE |
ORATYPE_VARRAY |
247 |
VARRAY |
ORATYPE_TABLE |
248 |
NESTED TABLE |
ORATYPE_RAW_BIN |
2000 |
RAW |
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
Applies To
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.
See Also:
Applies To
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.
Applies To
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.
Applies To
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.
See Also:
OraField ObjectApplies To
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
Applies To
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
Remarks
The OraDynaset
object does not support this property. Sort your record set by using a SQL ORDER
BY
clause.
See Also:
Applies To
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
See Also:
Refresh MethodApplies To
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:
Constant | Value | Description |
---|---|---|
ORAPSTAT_INPUT |
&H1& |
Parameter can be used for input. |
ORAPSTAT_OUTPUT |
&H2& |
Parameter can be used for output. |
ORAPSTAT_AUTOENABLE |
&H4& |
Parameter is AutoBindEnabled. |
ORAPSTAT_ENABLE |
&H8& |
Parameter is Enabled. This bit is always set. |
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
Applies To
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.
See Also:
PollingAmount PropertyApplies To
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.
Applies To
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.
Applies To
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.
Applies To
Description
Returns or sets the time zone information of an OraTimeStampTZ
object.
Usage
timezone = OraTimeStampTZObj.TimeZoneOraTimeStampTZObj.TimeZone= timezone
Arguments
Arguments | Description |
---|---|
[in ] timezone |
The time zone attribute of an OraTimeStampTZ object. |
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.
Properties | OraTSTZ Object Values | UTC Date Time Values of the OraTSTZ Object |
---|---|---|
Year |
2003 |
2003 |
Month |
4 |
4 |
Day |
29 |
29 |
Hour |
12 |
19 |
Minute , Second , Nanosecond |
0 |
0 |
TimeZone |
-07:00 |
00:00 |
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.
Properties | New OraTSTZ Object Values | UTC Date Time Values of the New OraTSTZ Object |
---|---|---|
Year |
2003 |
2003 |
Month |
4 |
4 |
Day |
29 |
29 |
Hour |
11 |
19 |
Minute , Second , Nanosecond |
0 |
0 |
TimeZone |
-08:00 |
00:00 |
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
Applies To
Description
Gets and sets the total number of days that this OraIntervalDS
object represents.
Usage
totalDays = OraIntervalDSObj.TotalDays OraIntervalDSObj.TotalDays = totalDays
Arguments
Arguments | Description |
---|---|
[in ] totalDays |
A Variant type of any numeric value or an OraNumber object specifying the OraIntervalDS object as the total number of days. |
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
See Also:
OraNumber ObjectApplies To
Description
Gets and sets the total number of years that this OraIntervalYM
object represents.
Usage
totalYears = OraIntervalYMObj.TotalYearsOraIntervalYMObj.TotalYears= totalYears
Arguments
Arguments | Description |
---|---|
[in ] totalYears |
A Variant type of any numeric value specifying the OraIntervalYM object as the total number of years. |
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
Applies To
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
.
Applies To
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.
See Also:
Applies To
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:
Oracle Data Type | Constant | Value | Data Type |
---|---|---|---|
BINARY_DOUBLE |
ORADB_DOUBLE |
7 |
Double |
BINARY_FLOAT |
ORADB_SINGLE |
6 |
Single |
BLOB |
ORADB_OBJECT |
9 |
OraBLOB |
CHAR |
ORADB_TEXT |
10 |
String |
CLOB |
ORADB_OBJECT |
9 |
OraCLOB |
DATE |
ORADB_DATE |
8 |
Variant |
DATE |
ORADB_DATE |
8 |
Date |
INTERVAL DAY TO SECOND |
ORADB_OBJECT |
9 |
OraIntervalDS |
INTERVAL YEAR TO MONTH |
ORADB_OBJECT |
9 |
OraIntervalYM |
LONG |
ORADB_MEMO |
12 |
String |
LONG RAW |
ORADB_LONGBINARY |
11 |
String |
NESTED TABLE |
ORADB_OBJECT |
9 |
OraBFILE |
NUMBER (1-4, 0) |
ORADB_INTEGER |
3 |
Integer |
NUMBER (5-9, 0) |
ORADB_LONG |
4 |
Long Integer |
NUMBER (10-15, 0) |
ORADB_DOUBLE |
7 |
Double |
NUMBER (16-38, 0) |
ORADB_TEXT |
10 |
String |
NUMBER (1-15, n) |
ORADB_DOUBLE |
7 |
Double |
NUMBER (16-38, n) |
ORADB_TEXT |
10 |
String |
RAW |
ORADB_LONGBINARY |
11 |
String |
REF |
ORADB_OBJECT |
9 |
OraCollection |
TIMESTAMP |
ORADB_OBJECT |
9 |
OraTimeStamp |
TIMESTAMP WITH LOCAL TIME ZONE |
ORADB_OBJECT |
9 |
OraTimeStamp |
TIMESTAMP WITH TIME ZONE |
ORADB_OBJECT |
9 |
OraTimeStampTZ |
VARRAY |
ORADB_OBJECT |
9 |
OraCollection |
VARCHAR2 |
ORADB_TEXT |
10 |
String |
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.
See Also:
Value PropertyApplies To
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.
See Also:
"Oracle Data Types"Applies To
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:
Constant | Value | Description |
---|---|---|
ORATYPE_VARRAY |
247 |
Collection is VARRAY type. |
ORATYPE_TABLE |
248 |
Collection is nested table type. |
Applies To
Description
Returns type of the schema object described by the OraMetaData
object.
Usage
type = OraMetaData.Type
Remarks
The possible values include the following constants:
Constants | Value |
---|---|
ORAMD_TABLE |
1 |
ORAMD_VIEW |
2 |
ORAMD_COLUMN |
3 |
ORAMD_COLUMN_LIST |
4 |
ORAMD_TYPE |
5 |
ORAMD_TYPE_ATTR |
6 |
ORAMD_TYPE_ATTR_LIST |
7 |
ORAMD_TYPE_METHOD |
8 |
ORAMD_TYPE_METHOD_LIST |
9 |
ORAMD_TYPE_ARG |
10 |
ORAMD_TYPE_RESULT |
11 |
ORAMD_PROC |
12 |
ORAMD_FUNC |
13 |
ORAMD_ARG |
14 |
ORAMD_ARG_LIST |
15 |
ORAMD_PACKAGE |
16 |
ORAMD_SUBPROG_LIST |
17 |
ORAMD_COLLECTION |
18 |
ORAMD_SYNONYM |
19 |
ORAMD_SEQENCE |
20 |
ORAMD_SCHEMA |
21 |
ORAMD_OBJECT_LIST |
22 |
ORAMD_OBJECT_LIST |
23 |
ORAMD_DATABASE |
24 |
Note:
If this version of theOraMetaData
object is used on Oracle Database release 8.1 or later, values higher than 24 are possible if the database is enhanced to introduce new schema types.See Also:
ORAMD_TABLE AttributesApplies To
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.
Applies To
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:
The SQL statement must refer to a simple column list or to the entire column list (*).
The SQL statement must not set the read-only flag of the options argument.
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
See Also:
RecordSource Property of Data Control
Applies To
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
.
See Also:
Applies To
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:
Element Type | Element Value |
---|---|
Object |
OraObject |
REF |
OraRef |
VARRAY , Nested Table |
OraCollection |
BLOB |
OraBLOB |
CLOB |
OraCLOB |
BFILE |
OraBFILE |
Date |
String |
Number |
String |
CHAR ,VARCHAR2 |
String |
Real |
Real |
Integer |
Integer |
Applies To
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
Applies To
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
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalDS object. |
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
See Also:
CreateOraIntervalDS MethodApplies To
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
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalYM object. |
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
See Also:
CreateOraIntervalYM MethodApplies To
Description
A String containing the value of the attribute.
Usage
value = OraMDAttribute.Value
Data Type
String
Remarks
This is the default property.
Applies To
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
Arguments | Description |
---|---|
[in ] variantval |
A Variant of type String , OraNumber , or a numeric value. |
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.
Applies To
Description
When read, the Value
property provides a string representation of the value of the OraTimeStamp
object. If the Format
property is not null, the output string format is in the format specified by the Format
property; otherwise, the output string format is in the session TIMESTAMP
format (NLS_TIMESTAMP_FORMAT
). When set, the Value
property accepts a Variant
of type String
, Date
, or OraTimeStamp
.
Usage
string = OraTimeStampObj.ValueOraTimeStampObj.Value= value
Arguments
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStamp . |
Data Type
String
Remarks
If the value is of type String
and Format
is not null, the string format must match the Format
property. If the Format
property is null, the string format must match the session TIMESTAMP
format.
Examples
... Set OraTimeStamp = OraSession.CreateOraTimeStamp("1999-APR-29 " & _ "12:10:23.444 AM", "YYYY-MON-DD HH:MI:SS.FF AM") 'returns a string for the Value property tsStr = OraTimeStamp.Value 'set OraTimeStamp.Value using a string OraTimeStamp.Value = "1999-APR-29 12:10:23.444 AM"
See Also:
CreateOraTimeStamp MethodApplies To
Description
When read, the Value
property provides a string representation of the value of the OraTimeStampTZ
object. If the Format
property is not null, the output string format is in the format specified by the Format
property; otherwise, the output string format is in the session TIMESTAMP
WITH
TIME
ZONE
format (NLS_TIMESTAMP_TZ_FORMAT
). When set, the Value
property accepts a Variant
of type String
, Date
, or OraTimeStampTZ
.
Usage
string = OraTimeStampTZObj.ValueOraTimeStampObjTZ.Value= value
Arguments
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStampTZ . |
Data Type
String
Remarks
If the Variant
is of type String
and the Format
property is not null, the string format must match the Format
property. If the Format
property is null, the string format must match the session TIMESTAMP
WITH
TIME
ZONE
format.
If the Variant
is of type Date
, the date-time value in Date
is interpreted as the date-time value in the session time zone. The time zone information in the OraTimeStampTZ
object contains the session time zone.
Examples
Dim OraTimeStampTZ As OraTimeStampTZ ... Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2003-APR-29" & _ "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM") 'returns a string for the Value property tstzStr = OraTimeStampTZ.Value ... 'set OraTimeStampTZ.Value using a string OraTimeStampTZ.Value = "2003-APR-29 12:00:00 -07:00"
See Also:
CreateOraTimeStampTZ MethodApplies To
Description
Returns a String containing user-assigned version of the type of underlying value instance.
Usage
version = OraRef.Version version = OraObject.Version
Data Type
String
Remarks
This property is read-only at run time.
Applies To
Description
Specifies the transactional behavior of the enqueue request.
Usage
Q.Visible = transaction_mode
Data Type
Integer
Remarks
This property is applicable only for an enqueue operation.
Possible values are:
ORAAQ_ENQ_IMMEDIATE
(1
)
The enqueue operation constitutes a transaction of its own. Set this property to make the message visible immediately after the enqueue operation.
ORAAQ_ENQ_ON_COMMIT
(2
) (Default)
The enqueue is part of the current transaction, and the message is visible only after the transaction commits.
Examples
Msg.Value = "The visibility option used in the enqueue call is " & _ "ORAAQ_ENQ_IMMEDIATE" Q.Visible = ORAAQ_ENQ_IMMEDIATE Q.Enqueue
Applies To
Description
Specifies the wait time (in seconds), if there is currently no message available.
Usage
Q.Wait = seconds
Data Type
Integer
Remarks
Applicable only for a dequeue operation.
Possible values are:
ORAAQ_DQ_WAIT_FOREVER
(-1
) (Default)
Waits forever.
ORAAQ_DQ_NOWAIT
(0
)
Does not wait.
Applies To
Description
Gets and sets a Boolean value that indicates whether this field name is given as an attribute. If the value is False
, the field name is given as an element. Readable and writable at run time.
Usage
OraField.XMLAsAttribute = True
Remarks
The default value for this property is False
.
Fields of type BLOB
, CLOB
, BFILE
, Object
, VARRAY
, Nested
Table
, Long
or LongRaw
cannot be XML attributes.
Applies To
Description
Gets and sets the attribute name that replaces id
(as in <TYPENAME_ITEM
id
= "1">
) in the rendering of collection items that occurs when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
oradynaset.XMLCollID = "NEWID"
Remarks
The default value for this property is id
. If this property is set to Null
or an empty String
(""
), the collectionid
attribute is omitted. The attribute name must be valid or an error is raised. The case is preserved.
See Also:
Applies To
Description
Gets or sets a string value in the encoding tag of the generated XML document.
Usage
OraDynaset.XMLEncodingTag = "SHIFT_JIS"
Remarks
This property is useful when the XML document generated by OO4O is converted to a different character set encoding before it is stored or parsed. This might occur if the property is to be loaded into a database or stored in a file system.
This property only sets the encoding tag value; it does not change the actual encoding of the document. The document generated by the GetXML
method in Visual Basic is encoded in UCS2. The documents generated by the GetXMLToFile
method use the same character set as the current NLS_LANG
setting.
If this property is set to an empty String, the default encoding tags are used. To omit the tag entirely, use OraDynaset.XMLOmitEncodingTag
.
No validity checking of the chosen encoding is done.
Applies To
Description
Gets and sets a Boolean value that indicates whether a null indicator attribute is used in the case of Null
field values. If the property is False
, tags with Null
values are omitted. Readable and writable at run time.
Usage
oradynaset.XMLNullIndicator = True
Remarks
The default value for this property is False
.
Applies To
Description
Gets or sets a Boolean value that determines if the encoding tag should be omitted.
Usage
OraDynaset.XMLOmitEncodingTag = True
Remarks
The default value is False
.
If this property is set to False
, the value of the XMLEncodingTag
property is used in the encoding tag.
See Also:
XMLEncodingTag PropertyApplies To
Description
Gets and sets the attribute name that replaces id
(as in <ROW
id=
"1">
) in the rendering of XML that occurs when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
oradynaset.XMLRowID = "NEWID"
Remarks
The default value for this property is id
. If this property is set to Null
or an empty string (""
), the rowid
attribute is omitted. The attribute name must be valid or an error is raised. The case is preserved.
See Also:
Applies To
Description
Gets or sets the tag name that replaces the rowset tag <ROWSET>
in the rendering of XML that occurs when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
oradynaset.XMLRowSetTag = "NEWROWSET"
Remarks
The default value for this property is ROWSET
. The tag name must be valid or an error is raised. The case is preserved. This tag is the root, unless schema metadata is requested with the document.
See Also:
Applies To
Description
Gets and sets the tag name that replaces <ROW>
in the rendering of XML that occurs when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
oradynaset.XMLRowTag = "NEWROW"
Remarks
The default value for this property is ROW
. If this property is set to Null
or an empty string (""
), the <ROW>
tag is omitted. The tag name must be valid or an error is raised. The case is preserved.
See Also:
Applies To
Description
Gets and sets the tag name that is used for this field in the rendering of XML that occurs when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
orafield.XMLTagName = "EmployeeName"
Remarks
The default value for this property is the value of the Name
property. If this property is set to Null
or an empty string (""
), this field is omitted. The name must be valid or an error is raised. The case is preserved.
See Also:
Applies To
Description
Gets and sets a Boolean value that indicates whether tag and attribute names are uppercase when GetXML
or GetXMLToFile
methods are called. Readable and writable at run time.
Usage
oradynaset.XMLUpperCase = True
Remarks
The default value for this property is False
. If this property is set to True
, all of the tag and attribute names are in upper case. This method should be called only after all custom tag or attribute names have been set by the user.
See Also:
Applies To
Description
Returns or sets the Year
attribute of an OraTimeStamp
object.
Usage
year = OraTimeStampObj.YearOraTimeStampObj.Year = year
Arguments
Arguments | Description |
---|---|
[in ] year |
The Year attribute of an OraTimeStamp object. |
Data Type
Integer
Applies To
Description
Returns or sets the Year
attribute of an OraTimeStampTZ
object.
Usage
year = OraTimeStampObjTZ.Year OraTimeStampObjTZ.Year = year
Arguments
Arguments | Description |
---|---|
[in ] year |
The Year attribute of an OraTimeStampTZ object. |
Data Type
Integer
Applies To
Description
Gets and sets the Years
attribute of an OraIntervalYM
object.
Usage
years = OraIntervalYMObj.YearsOraIntervalYMObj.Years = years
Arguments
Arguments | Description |
---|---|
[in ] years |
An Integer specifying the value of the Years attribute of the OraIntervalYM object. |
Data Type
Integer