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 methods.
For an introduction to OO4O server objects, see "Oracle Objects for OLE In-Process Automation Server" .
This chapter contains these topics:
Server Methods: A to B
Server Methods: C
Server Methods: D to H
Server Methods: I to L
Server Methods: M to S
Server Methods: T to Z
Applies To
Description
Calculates the absolute value of an OraNumber
object.
Usage
OraNumber.Abs
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Adds a parameter to the OraParameters
collection.
Usage
oraparameters.Add Name, Value, IOType, ServerType, ObjectName
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
Name |
The name of the parameter to be added to the parameters collection. This name is issued both for parameter identification and as the placeholder in associated SQL and PL/SQL statements. |
Value |
A Variant specifying the initial value of the parameter. The initial value of the parameter is significant; it defines the data type of the parameter. |
IOType |
An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks. |
ServerType |
Specifies Oracle Database type to which this parameter is to be bound. This is required when binding to BLOB , CLOB , BFILE , OBJECT , REF , NESTED TABLE , or VARRAY . For a list of possible values, see the OraParameter "ServerType Property". |
ObjectName |
A case-sensitive string containing the name of the Object . This is only required if ServerType is ORATYPE_OBJECT , ORATYPE_VARRAY , or ORATYPE_TABLE . ServerType is required for ORATYPE_REF when the REF is used in PL/SQL. |
IOType Settings
The IOType
settings are:
Settings | Values | Description |
---|---|---|
ORAPARM_INPUT |
1 | Used for input variables only |
ORAPARM_OUTPUT |
2 | Used for output variables only |
ORAPARM_BOTH |
3 | Used for variables that are both input and output |
These values can be found in the oraconst.txt
file.
By default, the maximum size of the ORAPARM_OUTPUT
variable for ServerType
VAR
, VARCHAR2
, and ORATYPE_RAW_BIN
is set to 128 bytes. Use the MinimumSize
property to change this value. The minimum size of an ORAPARM_OUTPUT
variable for VAR
and VARCHAR2
must always be greater than the size of the expected data from the database column.
Verify that this value is correct. If you set an incorrect option, such as ORAPARM_BOTH
for the IN
stored procedure parameter type, this can result in errors. ORAPARM_BOTH
is for IN
and OUT
parameters only. It is not used against one stored procedure that has an IN
parameter and another that has an OUT
parameter. For this case, use two parameters. Errors caused this way are rare, if there is a parameter-related error, verify that the IOType
is correct.
The Value
argument can be an Oracle Database 10g object, such as an OraBLOB
. Note that a copy of the object is made at that point in time and the Value
property must be accessed to obtain a new object that refers to the value of the parameter. For example, if IOType
is ORATYPE_BOTH
and an OraBLOB
obtained from a dynaset is passed in as the input value, the Parameter
Value
property needs to be accessed one time after the SQL has been executed to obtain the newly updated output value of the parameter. The object is obtained from the parameter in the same manner as from a dynaset.
The 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 EMPTY
value can be used for BLOB
and CLOB
data types to mean an empty LOB, and the EMPTY
value can be used for OBJECT
, VARRAY
, and NESTED
TABLE
data types to mean an object whose attributes are all Null
.
Remarks
Use parameters to represent SQL bind variables (as opposed to rebuilding the SQL statement). SQL bind variables are useful because you can change a parameter value without having to parse the query again. Use SQL bind variables only as input variables.
You can also use parameters to represent PL/SQL bind variables. You can use PL/SQL bind variables as both input and output variables.
The ORATYPE_RAW_BIN
ServerType
value is used when binding to Oracle Raw
columns. A byte array is used to Put
or Get
values. The maximum allowable size of an ORATYPE_RAW_BIN
bind buffers is 2000 bytes when bound to a column of a table and 32 KB when bound to a stored procedure. For example code, see the samples in the ORACLE_BASE\ORACLE_HOME
\OO4O\VB\Raw
directory.
Examples
This example demonstrates using 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 Dim OraSession As OraSession Dim OraDatabase As OraDatabase '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
Adds an argument to the OraIntervalDS
object.
Usage
OraIntervalDS.Add operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalDS object to be added. |
Remarks
The result of the operation is stored in an OraIntervalDS
object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in the following format: [+/-]Day HH:MI:SSxFF.
If operand
is a numeric value, the value provided should represent the total number of days that the constructed 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") 'Add an interval using a string, which represents 2 days 'and 12 hours, to oraIDS. 'The resulting oraIDS is an interval which represents 4 days oraIDS.Add "2 12:0:0.0"
See Also:
"CreateOraIntervalDS Method"Applies To
Description
Adds an argument to the OraIntervalYM
object.
Usage
OraIntervalYMObj.Add operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalYM object to be added. |
Remarks
The result of the operation is stored in the OraIntervalYM
object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in the following format: [+/-]YEARS-MONTHS.
If operand
is a numeric value, the value provided should represent the total number of years that the constructed 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") 'Add an interval using a string, which represents 2 years 'and 6 months, to oraIYM. 'The resulting oraIYM is an interval which represents 4 years oraIYM.Add "2-6"
See Also:
CreateOraIntervalYM MethodApplies To
Description
Adds a numeric argument to the OraNumber
object.
Usage
OraNumber.Add operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber object, or a numeric value. |
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Adds a subscription to the OraSubscriptions
collection.
Usage
orasubscriptions.Add Name, DbeventsHdl, Ctx
Arguments
The arguments for the method are:
Variants | Description |
---|---|
[in ] Name |
The database event of interest. The appropriate event trigger and AQ queue must be set up prior to this.
The |
[in ] DbeventsHdl |
The database event handler. An IDispatch interface implementing the NotifyDBEvents method, which is invoked when the database event of interest is fired. |
[in ] Ctx |
Context-specific information that the application wants passed to the NotifyDbEvents method when it is invoked. |
Remarks
To register for subscription of a database event, the name identifying the subscription of interest and the name of the dbevent
handler that handles the event must be passed in when the Add
method is called. The queues and event triggers necessary to support the database event must be set up before the subscriptions can be fired.
The dbevent
handler should be an automation object that implements the NotifyDBEvents
method.
NotifyDBEvents Handler
The NotifyDBEvents
method is invoked by Oracle Objects for OLE when database events of interest are fired.
For more detailed information about setting up the queues and triggers for Oracle Database events, see to Triggers on System Events and User Events in Oracle Database Concepts.
The syntax of the method is:
Public Function NotifyDBEvents(ByVal Ctx As Variant, ByVal Payload As Variant
Variants
The variants for the method are:
Variants | Description |
---|---|
[in] Ctx |
Passed into the OraSubscriptions.Add method by the application. Context-sensitive information that the application wants passed on to the dbevent handler. |
[in] Payload |
The payload for this notification.
Database events are fired by setting up event trigger and queues. |
Examples
Example: Registering an Application for Notification of Database Events
In the following example, an application subscribes for notification of database logon events (such as all logons to the database). When a user logs on to the database, the NotifyDBEvents
method of the DBEventsHdlr
that was passed in at the time of subscription is invoked. The context-sensitive information and the event-specific information are passed into the NotifyDBEvents
method.
The DBEventsHdlr
in this example is DBEventCls
, which is defined later.
The main application:
' First instantiate the dbevent handler. The dbevent notification ' will fire the NotifyDBEvents on the callback handler. Public DBEventsHdlr As New DBEventCls Private Sub Form_Load() Dim gOraSession As Object Dim gOraSubscriptions As OraSubscriptions Dim gOraDatabase As OraDatabase 'Create the OraSession Object Set gOraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set gOraDatabase = gOraSession.DbOpenDatabase ("ora90.us.oracle.com", "pubsub/pubsub", ORADB_ENLIST_FOR_CALLBACK) Set gOraSubscriptions = gOraDatabase.Subscriptions gOraSubscriptions.Add "PUBSUB.LOGON:ADMIN", DBEventsHdlr, gOraDatabase gOraSubscriptions(0).Register MsgBox "OK" End Sub
The database event handler class that defines the NotifyDBEvents
method.
Public countofMsgs as integer Public Function NotifyDBEvents(Ctx As Variant, Payload As Variant ) On error goto NotifyMeErr MsgBox "Retrieved payload " + Payload ' do something - here the subscription is unregistered after ' receiving 3 notifications countofMsgs = countofMsgs + 1 If countofMsgs > 3 Then Ctx.Subscriptions(0).UnRegister End If Exit Sub NotifyMeErr: Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method") End Sub
See Also:
"Database Events" for a complete discussion of the concepts involved in this example
Triggers on System Events and User Events in Oracle Database Concepts for detailed information about setting up the queues and triggers for Oracle Database Events
Applies To
Description
Adds an interval that represents an interval from days to seconds, to the OraTimeStamp
or OraTimeStampTZ
object.
Usage
OraTimeStampObj.AddIntervalDS operand OraTimeStampTZObj.AddIntervalDS operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalDS object that represents an interval from days to seconds to be added to the current OraTimeStamp or OraTimeStampTZ object. |
Remarks
The result of adding an interval to the current OraTimeStamp
or OraTimeStampTZ
object is stored in the current object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in the following format: [+/-] Day HH:MI:SSxFF.
If operand
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS
object represents.
Examples
Using OraTimeStamp
Dim OraTimeStamp As OraTimeStamp ... 'Create OraTimeStamp using a string Set OraTimeStamp = OraSession.CreateOraTimeStamp("2000-12-28 00:00:00", _ "YYYY-MM-DD HH:MI:SS") 'Add an interval using numeric value that represents 5 days and 12 hours OraTimeStamp.AddIntervalDS 5.5 'Value should now be "2001-1-2 12:00:00" tsStr = OraTimeStamp.Value
Using OraTimeStampTZ
Dim OraTimeStampTZ As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStamp = OraSession.CreateOraTimeStampTZ("2000-12-28 00:00:00 -07:00", _ "YYYY-MM-DD HH:MI:SS TZH:TZM") 'Add an interval using numeric value that represents 5 days and 12 hours OraTimeStampTZ.AddIntervalDS 5.5 'Value should now be "2001-1-2 12:00:00" tstzStr = OraTimeStampTZ.Value ...
Applies To
Description
Adds an interval that represents an interval from years to months, to the OraTimeStamp
or OraTimeStampTZ
object.
Usage
OraTimeStampObj.AddIntervalYM operand OraTimeStampTZObj.AddIntervalYM operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalYM object that represents an interval from years to months, to be added to the current OraTimeStamp or OraTimeStampTZ object. |
Remarks
The result of adding an interval to the current OraTimeStamp
or OraTimeStampTZ
object is stored in the current object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in following format: [+/-] YEARS-MONTHS.
If operand
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
Examples
Example: Using the OraTimeStamp Object
Dim OraTimeStamp As OraTimeStamp ... 'Create OraTimeStamp using a string Set OraTimeStamp = OraSession.CreateOraTimeStamp("2000-12-28 00:00:00", _ "YYYY-MM-DD HH:MI:SS") 'Add an interval using numeric value that represents 2 years OraTimeStamp.AddIntervalYM 2 'Value should now be "2002-12-28 00:00:00" tsStr = OraTimeStamp.Value ...
Example: Using the OraTimeStampTZ Object
Dim OraTimeStampTZ As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStampTZ =OraSession.CreateOraTimeStampTZ("2000-12-28 00:00:00" & _ "-07:00" "YYYY-MM-DD HH:MI:SS TZH:TZM") 'Add an interval using numeric value that represents 2 years OraTimeStampTZ.AddIntervalYM 2 'Value should now be "2002-12-28 00:00:00" tstzStr = OraTimeStampTZ.Value ...
Applies To
Description
Clears the copy buffer and begins a record insertion operation into the specified dynaset and associated database.
Usage
oradynaset.AddNew oradynaset.DbAddNew
Remarks
When an AddNew
operation is initiated, values of fields present within the dynaset are maintained in a copy buffer and do not reflect the actual contents of the database.
The values of the fields are modified through the OraField
object, and committed with an Update
operation or when database movement occurs, which discards the new row. Field values that have not been explicitly assigned are either set to Null
or allowed to default by way of the Oracle default mechanism, depending on the Column Defaulting mode of the options flag used when the OpenDatabase
method was called. In either case, fields that appear in the database table but not in the dynaset are always defaulted by the Oracle default mechanism.
Internally, records are inserted by the AddNew
method using the "INSERT
into
TABLE
(...)
VALUES
(...)"
SQL statement, and are added to the end of the table.
When adding a row that has object, collection, and REF
columns, these column values should be set to a valid OraObject
, OraCollection
, or OraRef
interface or to the Null
value. The column values can also be set with the automation object returned by the CreateOraObject
method. When adding a row having a BLOB
, CLOB
, or BFILE
column, the column value should be set to a valid OraBLOB
, OraCLOB
, or OraBFILE
interface, Null
, or Empty
. Setting a BLOB
, CLOB
, and BFILE
column to an Empty
value inserts an empty LOB value into the database.
Note:
A call toEdit
, AddNew
, or Delete
methods cancels any outstanding Edit
or AddNew
method calls before proceeding. Any outstanding changes not saved using an Update
method are lost during the cancellation.Examples
This example demonstrates the use of the AddNew
and Update
methods to add a new record to a dynaset. Copy 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 * from emp", 0&) 'Begin an AddNew. OraDynaset.AddNew 'Set the field(column) values. OraDynaset.Fields("EMPNO").Value = "1000" OraDynaset.Fields("ENAME").Value = "WILSON" OraDynaset.Fields("JOB").Value = "SALESMAN" OraDynaset.Fields("MGR").Value = "7698" OraDynaset.Fields("HIREDATE").Value = "19-SEP-92" OraDynaset.Fields("SAL").Value = 2000 OraDynaset.Fields("COMM").Value = 500 OraDynaset.Fields("DEPTNO").Value = 30 'End the AddNew and Update the dynaset. OraDynaset.Update MsgBox "Added one new employee." End Sub
Applies To
Description
Adds an array parameter to the OraParameters
collection.
Usage
oraparamarray.AddTable Name, IOType, ServerType, ArraySize , ElementSize, ObjectName
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
Name |
The name of the parameter to be added to the parameters collection. This name is used both for parameter identification and as the placeholder in associated SQL and PL/SQL statements. |
IOType |
An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks. |
ServerType |
Specifies Oracle Database type to which this array parameter is to be bound. For a list of possible values, see the OraParameter ServerType Property. |
ArraySize |
Defines the number of elements in the parameter array. This parameter is used to calculate the maximum buffer length. |
ElementSize [optional] |
Defines the size of the element. Valid for only character and string type table (array) parameters. The valid size for ElementSize depends on the VarType .
|
ObjectName |
A case-sensitive string containing the name of the Object . This is only required if ServerType is ORATYPE_OBJECT , ORATYPE_VARRAY , or ORATYPE_TABLE . It is required for ORATYPE_REF when the REF is used in PL/SQL. |
IO Type Settings
The IOType
settings are:
Constant | Value | Description |
---|---|---|
ORAPARM_INPUT |
1 | Used for input variables only. |
ORAPARM_OUTPUT |
2 | Used for output variables only. |
ORAPARM_BOTH |
3 | Used for variables that are both input and output. |
Verify that this value is correct. If you set an incorrect option, such as ORAPARM_BOTH
for the stored procedure parameter type IN
, this can result in errors. ORAPARM_BOTH
is for IN
and OUT
parameters only. It is not used against one stored procedure that has an IN
parameter and another that has an OUT
parameter. In this case, use two parameters. Errors caused in this way are rare, but if there are parameter-related errors, verify that the IOType
is correct.
Server Type
See ServerType Property for valid types and note the following:
Note:
External data type ORATYPE_NUMBER
allows decimal precision of 1
to 38
.
The maximum positive number is 0.99999999999999999999
E
+
38
.
The minimum positive number is 0.1
E
-38.
The minimum negative number is -0.99999999999999999999
E
+
38
.
The maximum negative number is 0.1
E
-38
.
ElementSize (Optional)
Valid for character, string, and raw types. The valid size for ElementSize
depends on the VarType
. This represents the length of each individual string or raw array element. These ranges are listed.
VarType | Size |
---|---|
ORATYPE_VARCHAR2 |
Valid range from 1 to 1999 |
ORATYPE_VARCHAR |
Valid range from 1 to 1999 |
ORATYPE_STRING |
Valid range from 1 to 1999 |
ORATYPE_CHAR |
Valid range from 1 to 255 |
ORATYPE_CHARZ |
Valid range from 1 to 255 |
ORATYPE_RAW_BIN |
Valid range from 1 to 4000 (see remarks) |
Remarks
Use parameters to represent SQL bind variables for array insert, update, and delete operations, rather than rebuilding the SQL statement. SQL bind variables are useful because you can change a parameter value without having to parse the query again. Use SQL bind variables only as input variables.
You can also use parameters to represent PL/SQL bind (IN
/OUT
) variables. You can use PL/SQL bind variables as both input and output variables.
The ServerType
value ORATYPE_RAW_BIN
is used when binding to Oracle Raw
columns. A byte array is used to Put
or Get
values. The maximum allowable size of ORATYPE_RAW_BIN
bind buffers is 2000 bytes when bound to a column of a table: the maximum allowable size is 32 KB when bound to a stored procedure. No element (see ElementSize
argument) can be greater than 4000 bytes when binding to stored procedures, 2000 bytes against columns of tables. For example code, see the samples in the ORACLE_BASE\ORACLE_HOME
\OO4O\VB\Raw
directory.
Examples
See "Example: Using OraParamArrays with PL/SQL".
See Also:
ServerType PropertyApplies To
Description
Extends the size of the collection by one and appends the Variant
value at the end of the collection.
Usage
OraCollection.Append element
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] element |
A Variant representing the value to be appended. |
Remarks
If an OraCollection
represents a collection of Object
types or Ref
s, the element argument should represent a valid OraObject
or OraRef
.
Examples
The following example illustrates the Append
method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples".
Example: Append Method for the OraCollection Object Example
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim EnameList as OraCollection 'create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create a dynaset object from department set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&) 'retrieve a Enames column from Department. 'Here Value property of OraField object returns EnameList OraCollection set EnameList = OraDynaset.Fields("Enames").Value 'Append an "Eric" to the collection. 'Before that row level lock should be obtained OraDynaset.Edit EnameList.Append "Eric" OraDynaset.Update
Applies To
Description
Appends the LOB content of the input OraLOB object to the internal LOB value of this instance.
Usage
OraBlob.Append srcBlob OraClob.Append srcClob
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] srcLOB |
A valid object of type OraBLOB or OraCLOB . |
Remarks
Appends the LOB content of input LOB to the end of current LOB value. Obtain either a row-level lock or an object-level lock before calling this method.
Applies To
Description
Appends data from a string to a LONG
or LONG
RAW
field in the copy buffer.
Usage
orafield.AppendChunk(string) orafield.DbAppendChunk(string)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
string |
Data to append to the specified field. |
Remarks
The AppendChunk
method allows the manipulation of data fields that are larger than 64 KB.
Examples
Note:
This example cannot be run as is. It requires a defined form namedfrmChunk
.This example demonstrates the use of the AppendChunk
method to read a file into a LONG
RAW
column of a database. This example expects a valid dynaset named OraDynaset
representing a table with a column named longraw
. Copy this code into the definition section of a form named frmChunk
. Call this procedure with a valid filename
.
Sub AppendChunkExample (FName As String) 'Declare various variables. Dim NumChunks As Integer, RemChunkSize As Integer Dim TotalSize As Long, CurChunk As String Dim I As Integer, FNum As Integer, ChunkSize As Integer 'Set the size of each chunk. ChunkSize = 10240 frmChunk.MousePointer = HOURGLASS 'Begin an add operation. OraDynaset.AddNew 'Clear the LONGRAW field. OraDynaset.Fields("LONGRAW").Value = "" 'Get a free file number. FNum = FreeFile 'Open the file. Open FName For Binary As #FNum 'Get the total size of the file. TotalSize = LOF(FNum) 'Set number of chunks. NumChunks = TotalSize \ ChunkSize 'Set number of remaining bytes. RemChunkSize = TotalSize Mod ChunkSize 'Loop through the file. For I = 0 To NumChunks 'Calculate the new chunk size. If I = NumChunks Then ChunkSize = RemChunkSize End If CurChunk = String$(ChunkSize, 32) 'Read a chunk from the file. Get #FNum, , CurChunk 'Append chunk to LONGRAW field. OraDynaset.Fields("LONGRAW").AppendChunk (CurChunk) Next I 'Complete the add operation and update the database. OraDynaset.Update 'Close the file. Close FNum frmChunk.MousePointer = DEFAULT End Sub
Applies To
Description
Appends data from a byte array to a LONG
or LONG
RAW
field in the copy buffer.
Usage
orafield.AppendChunkByte(ByteArray, numbytes)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
Byte Array |
Data to append to the specified field. |
numbytes |
Number of bytes to copy. |
Remarks
The AppendChunkByte
method allows the manipulation of data fields that are larger than 64 KB.
Examples
Note:
This is an incomplete code sample, provided for your reference. A complete Visual Basic sample calledLONGRAW
that is based on this code sample, is provided in the OO4O samples directory.This sample code demonstrates the use of the AppendChunkByte
method to read a file into a LONG
RAW
column of a database. This code expects a valid dynaset named OraDynaset
representing a table with a column named longraw
.
Sub AppendChunkByteExample (FName As String) 'Declare various variables. Dim NumChunks As Integer, RemChunkSize As Integer Dim TotalSize As Long, CurChunkByte() As Byte Dim I As Integer, FNum As Integer, ChunkSize As Integer 'Set the size of each chunk. ChunkSize = 10240 frmChunk.MousePointer = HOURGLASS 'Begin an add operation. OraDynaset.AddNew 'Clear the LONGRAW field. OraDynaset.Fields("LONGRAW").Value = "" 'Get a free file number. FNum = FreeFile 'Open the file. Open FName For Binary As #FNum 'Get the total size of the file. TotalSize = LOF(FNum) 'Set number of chunks. NumChunks = TotalSize \ ChunkSize 'Set number of remaining bytes. RemChunkSize = TotalSize Mod ChunkSize 'Loop through the file. For I = 0 To NumChunks 'Calculate the new chunk size. If I = NumChunks Then ChunkSize = RemChunkSize End If ReDim CurChunkByte(ChunkSize) 'Read a chunk from the file. Get #FNum, , CurChunkByte 'Append chunk to LONGRAW field. OraDynaset.Fields("LONGRAW").AppendChunkByte (CurChunkByte) Next I 'Complete the add operation and update the database. OraDynaset.Update 'Close the file. Close FNum frmChunk.MousePointer = DEFAULT End Sub
Applies To
Description
Creates an instance of the OraAQAgent
for the specified consumer and adds it to the OraAQAgent
s list of the message.
Usage
Set agent = qMsg.AQAgent(name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] name |
A String up to 30 bytes representing the name of the consumer of the message. |
[in ] [optional] Address |
A 128-byte String representing the protocol specific address of a recipient, such as [schema. ]queue [@dblink ]. |
Remarks
The OraAQAgent
object represents a message recipient and is only valid for queues that allow multiple consumers. Queue subscribers are recipients by default. Use this object to override the default consumers.
An OraAQAgent
object can be instantiated by invoking the AQAgent
method. For example:
Set agent = qMsg.AQAgent(consumer)
The maximum number of agents that a message can support is 10.
The AQAgent
method returns an instance of an OraAQAgent
object.
Note:
Address
is not supported in this release, but is provided for future enhancements.Applies To
Description
Creates an OraAQMsg
for the specified options.
Usage
Set qMsg = Q.AQMsg(msgtype, typename, schema)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] msgtype |
An Integer representing a RAW or user-defined type. Optional for RAW type. Possible values are:
|
[in ] typename |
A String representing the name of the type. Optional for RAW type. Default is 'RAW' . |
[in ] [optional] schema |
A String representing the schema where the type is defined. Default is 'SYS' . |
Remarks
The method could be used as follows:
set QMsg = Q.AQMsg(ORATYPE_OBJECT,"MESSAGE_TYPE","SCOTT") set QMsg = Q.AQMsg
Applies To
Description
Calculates the arc cosine of an OraNumber
object. The result is in radians.
Usage
OraNumber.ArcCos
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method returns an error if the OraNumber
value is less than -1
or greater than 1
.
Applies To
Description
Calculates the arc sine of an OraNumber
object. Result is in radians.
Usage
OraNumber.ArcSin
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method returns an error if the OraNumber
object is less than -1
or greater than 1
.
Applies To
Description
Calculates the arc tangent of an OraNumber
object. Result is in radians.
Usage
OraNumber.ArcTan
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Calculates the arc tangent of two numbers using the operand
provided. The result is in radians.
Usage
OraNumber.ArcTan2 operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method returns an error if operand
is zero.
Applies To
Description
Returns the OraMDAttribute
object at the specified index
.
Usage
Set OraMDAttribute = OraMetaData.Attribute(2) Set OraMDAttribute = OraMetaData.Attribute("AttributeName")
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] index |
An Integer index between 0 and count-1 , or a String representing the name of an attribute. |
Remarks
None.
See Also:
OraMetaData Object for a list of possible attribute namesApplies To
Description
Resets the AutoBind
status of a parameter.
Usage
oraparameter.AutoBindDisable
Remarks
If a parameter has AutoBindDisabled
status, it is not automatically bound to a SQL or PL/SQL statement.
Examples
This example demonstrates the use of the AutoBindDisable
and AutoBindEnable
methods to prevent unnecessary parameter binding while creating various dynasets that use different parameters. Copy 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&) 'Add the job input parameter with initial value MANAGER. OraDatabase.Parameters.Add "job", "MANAGER", 1 'Add the deptno input parameter with initial value 10. OraDatabase.Parameters.Add "deptno", 10, 1 'Disable the deptno parameter for now. OraDatabase.Parameters("deptno").AutoBindDisable 'Create the OraDynaset Object using the job parameter. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _ "where job = :job", 0&) 'Only employees with job=MANAGER will be contained in the dynaset. MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _ "Job=" & OraDynaset.Fields("job").value 'Enable the deptno parameter and disable the job parameter. OraDatabase.Parameters("deptno").AutoBindEnable OraDatabase.Parameters("job").AutoBindDisable 'Create the OraDynaset Object using the deptno parameter. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _ "where deptno = :deptno", 0&) 'Only employees with deptno=10 will be contained in the dynaset. MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," & _ "DeptNo=" & OraDynaset.Fields("deptno").value End Sub
See Also:
AutoBindEnable MethodApplies To
Description
Sets the AutoBind
status of a parameter.
Usage
oraparameter.AutoBindEnable
Remarks
If a parameter has AutoBindEnabled
status, it is automatically bound to a SQL or PL/SQL statement.
Examples
This example demonstrates the use of the AutoBindDisable
and AutoBindEnable
methods to prevent unnecessary parameter binding while creating various dynasets that use different parameters. Copy 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&) 'Add the job input parameter with initial value MANAGER. OraDatabase.Parameters.Add "job", "MANAGER", 1 'Add the deptno input parameter with initial value 10. OraDatabase.Parameters.Add "deptno", 10, 1 'Disable the deptno parameter for now. OraDatabase.Parameters("deptno").AutoBindDisable 'Create the OraDynaset Object using the job parameter. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _ "where job = :job", 0&) 'Only employees with job=MANAGER will be contained in the dynaset. MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," & _ "Job=" & OraDynaset.Fields("job").value 'Enable the deptno parameter and disable the job parameter. OraDatabase.Parameters("deptno").AutoBindEnable OraDatabase.Parameters("job").AutoBindDisable 'Create the OraDynaset Object using the deptno parameter. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _ "where deptno = :deptno", 0&) 'Only employees with deptno=10 will be contained in the dynaset. MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," & _ "DeptNo=" & OraDynaset.Fields("deptno").value End Sub
See Also:
AutoBindDisable MethodApplies To
Description
Begins a database transaction within the specified session.
Usage
oraconnection.BeginTrans oradatabase.BeginTrans orasession.BeginTrans
Remarks
After this method has been called, no database transactions are committed until a CommitTrans
is issued. Alternatively, the session can be rolled back using the Rollback
method. If a transaction has already been started, repeated use of the BeginTrans
method causes an error.
If Update
or Delete
methods fail on a given row in a dynaset in a global transaction after you issue a BeginTrans
, be aware that locks remain on those rows on which you called the Update
or Delete
method. These locks persist until you call a CommitTrans
or Rollback
method.
Note:
If anOraDatabase
object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.Examples
This example demonstrates the use of the BeginTrans
method to group a set of dynaset edits into a single transaction and uses the Rollback
method to cancel those changes. Copy 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 Dim fld As OraField '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 * from emp", 0&) 'Start Transaction processing. OraSession.BeginTrans 'Setup a field object to save object references. Set fld = OraDynaset.Fields("sal") 'Traverse until EOF is reached, setting each employees salary to zero Do Until OraDynaset.EOF = True OraDynaset.Edit fld.value = 0 OraDynaset.Update OraDynaset.MoveNext Loop MsgBox "All salaries set to ZERO." 'Currently, the changes have NOT been committed to the database. 'End Transaction processing. Using RollbackTrans 'means the rollback can be canceled in the Validate event. OraSession.Rollback 'MsgBox "Salary changes rolled back." End Sub
Applies To
OraSQLStmt Object created with the ORASQL_NONBLK
option
Description
Cancels the currently executing SQL operation.
Usage
status = OraSQL.NonBlockingState if status = ORASQL_STILL_EXECUTING OraSQL.CancelEndif
Return Values
ORASQL_SUCCESS(0)
- Any errors are thrown as exceptions.
See Also:
"Asynchronous Processing"Applies To
Description
Unlocks the referenceable object in the database and cancels the object update operation.
Usage
OraRef.CancelEdit
Remarks
Care should be taken before using this method; it cancels any pending transaction on the connection.
Applies To
Description
Calculates the ceiling value of an OraNumber
object.
Usage
OraNumber.Ceil
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Changes the password for a given user.
Usage
OraServer.ChangePassword user_name, current_password, new_password
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] user_name |
A String representing the user for whom the password is changed. |
[in ] current_password |
A String representing the current password for the user. |
[in ] new_password |
A String representing the new password for whom the user account is set. |
Remarks
The OraServer
object should be attached to an Oracle database using the Open
method before to using this method.
This method is useful when a password has expired. In that case, the OpenDatabase
method could return the following error:
ORA-28001 "the password has expired".
See Also:
Applies To
Description
Changes the password for a given user.
Usage
OraSession.ChangePassword database_name, user_name, current_password, new_password
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] database_name |
A String representing the Oracle network specifier used when connecting to a database. |
[in ] user_name |
A String representing the user for whom the password is changed. |
[in ] current_password |
A String representing the current password for the user. |
[in ] new_password |
A String representing the new password for whom the user account is set. |
Remarks
This method is especially useful when a password has expired. In that case, the OpenDatabase
or CreateDatabasePool
method could return the following error:
ORA-28001 "the password has expired".
Examples
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim password as String 'Note: The DBA could expire scott's password by issuing 'ALTER USER SCOTT PASSWORD EXPIRE Set OraSession = CreateObject("OracleInProcServer.XOraSession") password = "tiger" On Error GoTo err: Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/" & password, 0&) End err: 'Check for password expiration error If OraSession.LastServerErr = 28001 Then OraSession.ChangePassword "ExampleDb", "scott", password, "newpass" 'reset our password variable, then try OpenDatabase again password = "newpass" Resume End If End
See Also:
Applies To
Description
Returns a duplicate dynaset of the specified dynaset.
Usage
Set oradynaset2 = oradynaset1.Clone Set oradynaset2 = oradynaset1.DbClone
Remarks
This method creates a duplicate dynaset of the one specified. The original and duplicate dynasets have their own current record. However, the new dynaset is not positioned on any row and has its EOF
and BOF
conditions set to True
. To change this, you must explicitly set a current row on the new duplicate with a Move
or Find
method.
Using the Clone
method has no effect on the original dynaset. You cannot add, update, or remove records from a dynaset clone.
Use the Clone
method to perform an operation on a dynaset that requires multiple current records.
A cloned dynaset does not have all the property settings of the original. The CacheBlock
, CacheSliceSize
, CacheSlicePerBlock
, and FetchLimit
properties are all set to Null
.
Bookmarks of a dynaset and its clone are interchangeable; bookmarks of dynasets created with separate CreateDynaset
methods are not interchangeable.
See Also:
Applies To
Description
Returns the clone of an OraLOB or OraBFILE
object.
Usage
OraBlob1 = OraBlob.Clone OraClob1 = OraClob.Clone OraBfile = OraBfile.Clone
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] OraLOB |
A valid object of type OraBLOB , OraCLOB , or OraBFILE . |
Remarks
This method makes a copy of an OraBLOB
or OraCLOB
object. This copy does not change due to a dynaset move operation or OraSQLStmt
Refresh
operation. No operation that modifies the LOB content of an OraBLOB
or OraCLOB
object can be performed on a clone.
This method makes a copy of Oracle BFILE
locator and returns an OraBFILE
associated with that copy. The copy of an OraBFILE
does not change due to a dynaset move operation or a OraSQLStmt
refresh operation.
Applies To
Description
Returns the clone of an OraCollection
object.
Usage
set OraCollection1 = OraCollection.Clone
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] oraCollection1 |
A valid OraCollection object |
Remarks
This method makes a copy of an Oracle collection and returns an OraCollection
object associated with that copy. This copy of an Oracle collection does not change due to a dynaset move operation or OraSQLStmt
Refresh
operation. An OraCollection
object returned by this method allows operations to access its element values of the underlying Oracle collection and prohibits any operation that modifies its element values.
Applies To
Description
Returns a copy of the OraIntervalDS
object.
Usage
Set OraIntervalDSObjClone = OraIntervalDSObj.Clone
Remarks
Returns a new OraIntervalDS
object with the same value as the original.
Applies To
Description
Returns a copy of the OraIntervalYM
object.
Usage
Set OraIntervalYMObjClone = OraIntervalYMObj.Clone
Remarks
Returns a new OraIntervalYM
object with the same value as the original.
Applies To
Description
Returns a copy of the OraNumber
object .
Usage
Set OraNumber2 = OraNumber.Clone
Remarks
Returns a new OraNumber
object with the same value as the original.
Applies To
Description
Returns the clone of an OraObject
or OraRef
object.
Usage
Set OraObjectClone = OraObject.CloneSet OraRefClone = OraRef.Clone
Remarks
This method makes a copy of a Value
instance or REF
value and returns an OraObject
or OraRef
object associated with that copy. This copy does not change due to a dynaset move operation or OraSQLStmt
refresh operation. An OraObject
object returned by this method allows an operation to access its attribute values of an underlying value instance and disallows any operation to modify its attribute values.
Examples
Before running the sample code, make sure that you have the necessary data types and tables in the database. For the following examples, see "Schema Objects Used in the OraObject and OraRef Examples"
Example: Clone Method for the OraObject Object
The following example shows the use of the Clone
method.
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&) 'retrieve a address column from person_tab. Here Value property of OraField object 'returns Address OraObject set Address = OraDynaset.Fields("Addr").Value 'here Address OraObject points to Address value instance in the server 'for the first row msgbox Address.Street 'move to second row OraDynaset.MoveNext 'here Address OraObject points to Address value instance in the server 'for the second row msgbox Address.Street 'get the clone of Address object. This clone points to the copy of 'the value instance for second row set AddressClone = Address.Clone 'move to third row OraDynaset.MoveNext 'here Address OraObject points to Address value instance in the server 'for third row msgbox Address.Street 'here AddressClone OraObject points to copy of Address value instance ' in the server for second row msgbox AddressClone.Street
Example: Clone Method for the OraRef Object
The following example shows the usage of the Clone
method. Before running the sample code, make sure that you have the necessary data types and tables in the database.
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim Person as OraRef Dim PersonClone 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 'here Person OraRef points to Person Ref value in the server for the first row msgbox Person.Name 'move to second row OraDynaset.MoveNext 'here Person OraRef points to Person Ref value in the server for the second row msgbox Person.Name 'get the clone of Person object. 'This clone points to the copy of the Ref for second row set PersonClone = Person.Clone 'move to third row OraDynaset.MoveNext 'here Person OraRef points to Person Ref value 'in the server for the third row msgbox Person.Name 'here PersonClone OraRef points to Person Ref value 'in the server for the second row msgbox PersonClone.Name
Applies To
Description
Returns a copy of the OraTimeStamp
object.
Usage
Set OraTimeStampObj1 = OraTimeStampObj.Clone
Remarks
Returns a new OraTimeStamp
object with the same value as the current object.
Applies To
Description
Returns a copy of the OraTimeStampTZ
object.
Usage
Set OraTimeStampTZObj1 = OraTimeStampTZObj.Clone
Remarks
Returns a new OraTimeStampTZ
object with the same value as the current object.
Applies To
Description
Does nothing. Added for compatibility with Visual Basic.
Remarks
Neither the OraDatabase
nor the OraDynaset
object supports this method. Once an OraDatabase
or OraDynaset
object has gone out of scope and there are no references to it, the object closes automatically.
See Also:
Applies To
Description
Closes an opened BFILE
data type.
Usage
OraBfile = OraBfile.Close
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] OraBfile |
A valid object of type OraBFILE . |
Remarks
This method only applies to BFILE
s, not LOBs.
Applies To
Description
This method closes all open OraBFILE
objects on this connection.
Usage
OraBfile.CloseAll
Applies To
Description
Ends the current transaction and commits all pending changes to the database.
Usage
oraconnection.CommitTrans oradatabase.CommitTrans orasession.CommitTrans
Remarks
The CommitTrans
method acts differently for these objects:
OraConnection
and OraDatabase
The CommitTrans
method commits all pending transactions for the specified connection. This method has no effect if a transaction has not started. When a sessionwide transaction is in progress, you can use this method to commit the transactions for the specified connection prematurely.
OraSession
The CommitTrans
method commits all transactions present within the session. The CommitTrans
method is valid only when a transaction has been started. If a transaction has not been started, using the CommitTrans
method causes an error.
Note: If an OraDatabase
object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.
Examples
This example demonstrates the use of the BeginTrans
method to group a set of dynaset edits into a single transaction. The CommitTrans
method then accepts the changes. Copy 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 Dim fld As OraField '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 * from emp", 0&) 'Start Transaction processing. OraSession.BeginTrans 'Setup a field object to save object references. Set fld = OraDynaset.Fields("sal") 'Traverse until EOF is reached, setting each employees salary to zero. Do Until OraDynaset.EOF = True OraDynaset.Edit fld.value = 0 OraDynaset.Update OraDynaset.MoveNext Loop MsgBox "All salaries set to ZERO." 'Currently, the changes have NOT been committed 'to the database. 'End Transaction processing. Commit the changes to the database OraSession.CommitTrans MsgBox "Salary changes committed." End Sub
Applies To
Description
Compares the specified portion of the LOB value of an OraBLOB
or OraCLOB
object (or OraBFILE
object) to the LOB value of the input OraBLOB
or OraCLOB
object (or OraBFILE
object).
Usage
IsEqual = OraBlob.Compare srcBlob, amount, Offset, srcOffset IsEqual = OraClob.Compare srcClob, amount, Offset, srcOffset IsEqual = OraBfile.Compare srcBfile, amount, Offset, srcOffset
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] srcLOB |
Input OraBLOB , OraCLOB , or OraBFILE object whose value is to be compared. |
[in ] [optional] amount |
An Integer specifying the number of bytes or characters to compare. The default value of amount is from the Offset to the end of each LOB. |
[in ] [optional] Offset |
An Integer specifying the 1 -based Offset in bytes (OraBLOB or OraBFILE ) or characters (OraCLOB ) in the value of this object. Default value is 1 . |
[in ] [optional] srcOffset |
An Integer specifying the 1 -based Offset in bytes (OraBLOB or OraBFILE ) or characters (OraCLOB ) in the value of the srcLob object. Default value is 1 . |
[out ] IsEqual |
A Boolean representing the result of a compare operation. |
Remarks
The Compare
method returns True
if comparison succeeds; otherwise, it returns False
.
If the amount to be compared causes the comparison to take place beyond the end of one LOB but not beyond the end of the other, the comparison fails. Such a comparison could succeed only if the amount of data from the Offset
to the end is the exactly the same for both LOBs.
This call is currently implemented by executing a PL/SQL block that utilizes DBMS_LOB.INSTR()
.
Applies To
Description
Returns the OraSession
object with the specified name that is associated with the OraClient
object of the specified session.
Usage
Set orasession2 = orasession1.ConnectSession(session_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
session_name |
A String specifying the name of the session. |
Remarks
This method is provided for simplicity and is equivalent to iterating through the OraSessions
collection of the OraClient
object of the current session and searching for a session named session_name
. The OraSessions
collection contains only sessions created through the current application. This means that it is not possible to share sessions across applications, only within applications.
Examples
This example demonstrates the use of the ConnectSession
and CreateNamedSession
methods to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then, press F5.
Sub Form_Load () 'Declare variables Dim dfltsess As OraSession Dim OraSession As OraSession 'Create the default OraSession Object. Set dfltsess = CreateObject("OracleInProcServer.XOraSession") 'Try to connect to "ExampleSession". If it does not exist 'an error is generated. On Error GoTo SetName Set OraSession = dfltsess.ConnectSession("ExampleSession") On Error GoTo 0 'You can specify other processing here, such as creating a ' database and/or dynaset. Exit Sub SetName: 'The session named "ExampleSession" was not found, so create it. Set OraSession = dfltsess.Client.CreateSession("ExampleSession") Resume Next End Sub
Applies To
Description
Copy the rows from the dynaset to the clipboard in text format.
Usage
OraDynaset.CopyToClipboard(NumOfRows, colsep, rowsep)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
NumOfRows |
Number of rows to be copied to the dynaset |
colsep [optional] |
Column separator in the CHAR data type to be inserted between columns |
rowsep [optional] |
Row separator in the CHAR data type to be inserted between rows |
Remarks
This method is used to help transfer data between the Oracle Object for OLE cache (dynaset) and Windows applications, such as Excel or Word. The CopyToClipboard
method copies data starting from the current position of the dynaset up to the last row.
The default column separator is TAB (ASCII 9).
The default row separator is ENTER (ASCII 13).
Examples
The following example copies data from the dynaset to the clipboard. 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&) Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 'Now call CopyToClipboard to copy the entire dynaset OraDynaset.CopyToClipboard -1, chr(9), chr(13) End Sub
Applies To
Description
Copies a portion of the internal LOB value of an input OraBLOB
or OraCLOB
object to internal LOB value of this instance.
Usage
OraBlob.Copy srcBlob, amount, destOffset, srcOffset OraClob.Copy srcClob, amount, destOffset, srcOffset
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] srcLOB |
An OraCLob or OraBLOB object whose value is to be copied. |
[in ] [optional] amount |
An Integer specifying number of bytes or characters to copy. Default value is the size of the BLOB or CLOB value of the srcLOB object. |
[in ] [optional] destOffset |
An Integer specifying the offset in bytes or characters for the value of this object. Default value is 1 . |
[in ] [optional] srcOffset |
An Integer specifying the offset in bytes or characters, for the value of the srcLOB object. Default value is 1 . |
Remarks
Obtain either a row-level lock or object-level lock before calling this method.
Applies To
Description
Loads or copies a portion or all of a local file to the internal LOB value of this object.
Usage
OraBlob.CopyFromFile "blob.bmp" amount, offset, chunksize OraClob.CopyFromFile "clob.txt" amount, offset, chunksize
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] filename |
A string specifying the absolute name and path for the file to be read. |
[in ] [optional] amount |
An Integer specifying the maximum number in bytes to be copied. Default value is total file size. |
[in ] [optional] offset |
An Integer specifying the absolute offset of the BLOB or CLOB value of this object, in bytes for OraBLOB or OraBFILE and characters for OraCLOB . Default value is 1 . |
[in ] [optional] chunksize |
An Integer specifying the size for each read operation, in bytes. If chunksize parameter is not set or 0 , the value of the amount argument is used, which means the entire amount is transferred in one chunk. |
Remarks
Obtain either a row-level lock or object-level lock before calling this method.
The file should be in the same format as the NLS_LANG
setting.
Note:
When manipulating LOBs using LOB methods, such asWrite
and CopyFromFile
, the LOB object is not automatically trimmed if the length of the new data is smaller than the old data. Use the Trim
(OraLOB) method to shrink the LOB object to the size of the new data.Examples
Example: Using the CopyFromFile Method
This example demonstrates the use of the CopyFromFile
method.
Be sure that you have the PART
table in the database with valid LOB data in it. Also, be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartImage as OraBLOB 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Create a Dynaset containing a BLOB and a CLOB column set part = OraDatabase.CreateDynaset ("select * from part where" & _ "part_id = 1234",0) set PartImage = part.Fields("part_image").Value 'copy the entire content of partimage.jpg file to LOBS part.Edit PartImage.CopyFromFile "partimage.jpg" part.Update
See Also:
Trim (OraLOB) MethodApplies To
Description
Copies a portion or all of the LOB value of an OraBFILE
object to the LOB value of this object.
Usage
OraBlob.CopyFromBFile srcBFile, amount, destOffset, srcOffset OraClob.CopyFromBFile srcBFile, amount, destOffset, srcOffset
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] srcBFile |
An OraBFILE object from which the data is to be copied. |
[in ] [optional] amount |
An Integer specifying the maximum number to be copied, in characters for OraCLOB or bytes for OraBLOB or OraBFILE . Default value is the size of BFILE value of the srcBFile object. |
[in ] [optional] destOffset |
An Integer specifying the absolute offset for this instance. Default is 1 . |
[in ] [optional] srcOffset |
An Integer specifying the absolute offset for the BFILE value of the source OraBFILE object. Default is 1 . |
Remarks
Obtain either a row-level lock or object-level lock before calling this method.
For a single-byte character set, the OraBFile
object should be of the same character set as the database.
If the database has a variable width character set, the OraBFile
object passed to the OraClob.CopyFromBFile
method must point to a file that uses the UCS2 character set.
Applies To
Description
Copies a portion or all of the internal LOB value of this object to the local file.
Usage
OraBlob.CopyToFile "blob.bmp" amount,offset,chunksize OraClob.CopyToFile "clob.txt" amount,offset,chunksize OraBfile.CopyToFile "bfile.bmp" amount,offset,chunksize
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] filename |
A String specifying the absolute name and path for which the file is to be written. |
[in ] [optional] amount |
An Integer specifying the maximum amount to be copied, in bytes for OraBLOB /OraBFILE and characters for OraCLOB . Default value is the size of the LOB or BFILE . |
[in ] [optional] offset |
An Integer specifying absolute offset of the LOB or BFILE value of this instance, in bytes for OraBLOB /OraBFILE and characters for OraCLOB . Default value is 1 . |
[in ] [optional] chunksize |
An Integer specifying the size, in bytes, for each write operation. If the chunksize parameter is not set or is 0 , the value of the amount argument is used which means the entire amount is transferred in one chunk. |
Remarks
The file is in the same format as the NLS_LANG
setting.
If the file exists, its contents is overwritten.
Examples
Example:Using the CopyToFile Method
This example demonstrates the use of the CopyToFile
method.
Be sure that you have the PART
table in the database with valid LOB data in it. Also, be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartDesc as OraCLOB 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0& 'Create a Dynaset containing a BLOB and a CLOB column set part = OraDatabase.CreateDynaset ("select * from part where" & _ "part_id = 1234",0) set PartDesc = part.Fields("part_desc").Value 'Copy the entire LOB content to partdesc.txt file PartDesc.CopyToFile "partdesc.txt"
Applies To
Description
Calculates the cosine of an OraNumber
object given in radians.
Usage
OraNumber.Cos
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Creates an instance of the OraAQ
object.
Usage
Set OraAq = OraDatabase.CreateAQ(Qname)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] Qname |
A String representing the name of the queue in the database. |
Remarks
None.
Applies To
Description
Creates a dynaset using custom cache and fetch parameters
Usage
Set oradynaset = oradatabase.CreateCustomDynaset(sql_statement, options, slicesize, perblock, blocks, FetchLimit, FetchSize, SnapShotID)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
sql_statement |
Any valid Oracle SQL SELECT statement. |
slicesize |
Cache slice size. |
perblock |
Cache slices for each block. |
blocks |
Cache maximum number of blocks. |
FetchLimit |
Fetch array size. |
FetchSize |
Fetch array buffer size. |
options |
A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying the constant ORADYN_DEFAULT or the value &H0& gives the following defaults for the dynaset:
|
SnapShotID [optional] |
The ID of a Snapshot obtained from the SnapShot property of an OraDynaset . |
Constants
The following table lists constants and values for the options flag.
Constant | Value | Description |
---|---|---|
ORADYN_DEFAULT |
&H0& |
Accept the default behavior. |
ORADYN_NO_AUTOBIND |
&H1& |
Do not perform automatic binding of database parameters. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Do not remove trailing blanks from character string data retrieved from the database. |
ORADYN_READONLY |
&H4& |
Force dynaset to be read-only. |
ORADYN_NOCACHE |
&H8& |
Do 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_ORAMODE |
&H10& |
Same as Oracle Mode for a database except it affects only the dynaset being created. If database was created in Oracle Mode, the dynaset inherits the property from it (for compatibility). |
ORADYN_NO_REFETCH |
&H20& |
Behaves same as ORADB_NO_REFETCH mode for a database except this mode affects only the dynaset being created. If the database was created in ORADB_NO_REFETCH mode, the dynaset inherits the property for compatibility. |
ORADYN_N_MOVEFIRST |
&H40& |
Does not force a MoveFirst when the dynaset is created. BOF and EOF are both true. |
ORADYN_DIRTY_WRITE |
&H80& |
Update and Delete methods do not check for read consistency. |
These values can be found in the oraconst.txt
file located in:
ORACLE_BASE\ORACLE_HOME
\rdbms\oo4o
Remarks
The SQL statement must be a SELECT
statement or an error is returned. Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested select statements, and remote database references, but in each case you end up with a read-only dynaset.
If you use a complex expression or SQL function on a column, such as "sal + 100"
or "abs(sal)"
, you get an updatable dynaset, but the column associated with the complex expression is not updatable.
Object names generally are not modifed, but in certain cases, they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. If you use spaces in a complex expression, you must refer to the column without the spaces, because the database removes spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the SELECT
statement.
Executing the SQL SELECT
statement generates a commit operation to the database by default. To avoid this, use the BeginTrans
method on the session object before using the CreateDynaset
method.
The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag.
Updatability Conditions
For the dynaset to be updatable, three conditions must be met:
A SQL statement must refer to a simple column list or to the entire column list (*).
The statement must not set the read-only flag of the options argument.
Oracle 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 the Updatable
property of the dynaset returns False
.
This method automatically moves to the first row of the created dynaset.
You can use SQL bind variables in conjunction with the OraParameters
collection.
Examples
This example demonstrates the CreateCustomDynaset
method. 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 using sliceSize as 256,perblock size as 16, no. of 'blocks as 20, fetchLimit as 20,FetchSize as 4096 Set OraDynaset = OraDatabase.CreateCustomDynaset("select empno, " & _ "ename from emp", 0&,256,16,20,20,4096) 'Display the first record. MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & _ OraDynaset.Fields("ename").value End Sub
See Also:
SnapShot PropertyApplies To
Description
Creates a pool of OraDatabase
objects. Only one pool can be created for each OraSession
object.
Usage
CreateDatabasePool (long initialSize, long maxSize, long timeoutValue, BSTR database_name, BSTR connect_string, long options)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
initialSize |
The initial size of the pool. |
maxSize |
The maximum size to which the pool can grow. |
timeoutValue |
If an OraDatabase object in the pool is idle for the timeoutValue value specified, the database connection that it contains is disconnected. The connection is reopened if the pool item is used again. This value is in seconds. |
database_name |
The Oracle network specifier used when connecting the data control to a database. |
connectString |
The user name and password to be used when connecting to an Oracle database. |
options |
A bit flag word used to set the optional modes of the database. If options = 0 , the default mode settings apply. "Constants" shows the available modes. |
Remarks
The OpenDatabase
method of the OraSession
object is used to establish a connection to an Oracle database. This method returns a reference to the OraDatabase
object which is then used for executing SQL statements and PL/SQL blocks. The connection pool in OO4O is a pool of OraDatabase
objects. The pool is created by invoking the CreateDatabasePool
method of the OraSession
interface.
Exceptions are raised by this call if:
A pool already exists.
An error occurs in creating a connection to Oracle Database.
Invalid values for arguments are passed (that is, initialSize
> maxSize
).
The LastServerErr
property of the OraSession
object contains the code for the specific cause of the exception resulting from an Oracle Database error.
One possible connection error that could be returned is:
ORA-28001 "the password has expired"
The user can change the password using the ChangePassword
method.
Applies To
Description
Creates an OraDynaset
object from the specified SQL SELECT
statement and options.
Usage
Set oradynaset = oradatabase.CreateDynaset(sql_statement, options, SnapShotID) Set oradynaset = oradatabase.DbCreateDynaset(sql_statement, options, SnapShotID)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
sql_statement |
A String containing any valid Oracle SQL SELECT statement. |
options |
A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying the constant ORADYN_DEFAULT or the value &H0& gives the following defaults for the dynaset:
|
SnapShotID [optional] |
A ID of the snapshot obtained from the SnapShot property of an OraDynaset object. |
Constants
The following table lists constants and values for the options flag.
Constant | Value | Description |
---|---|---|
ORADYN_DEFAULT |
&H0& |
Accept the default behavior. |
ORADYN_NO_AUTOBIND |
&H1& |
Do not perform automatic binding of database parameters. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Do not remove trailing blanks from character string data retrieved from the database. |
ORADYN_READONLY |
&H4& |
Force dynaset to be read-only. |
ORADYN_NOCACHE |
&H8& |
Do 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_ORAMODE |
&H10& |
Behave the same as Oracle Mode for a database except affect only the dynaset being created. If database was created in Oracle Mode, the dynaset inherits the property from it (for compatibility). |
ORADYN_NO_REFETCH |
&H20& |
Behave the same as ORADB_NO_REFETCH mode for a database except affect only the dynaset being created. If the database was created in ORADB_NO_REFETCH mode, the dynaset inherits the property for compatibility. |
ORADYN_NO_MOVEFIRST |
&H40& |
Does not force a MoveFirst when the dynaset is created. BOF and EOF are both true. |
ORADYN_DIRTY_WRITE |
&H80& |
Update and Delete methods do not check for read consistency. |
These values can be found in the oraconst.txt
file.
Remarks
Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested select statements and remote database references, but in each case, the dynaset is read-only.
If you use a complex expression or SQL function on a column, such as "sal + 100"
or "abs(sal)"
, you get an updatable dynaset, but the column associated with the complex expression is not updatable.
Object names generally are not modifed, but in certain cases they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. Also, if you use spaces in a complex expression, you must refer to the column without the spaces, since the database strips spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the SELECT
statement.
Executing the Update
method generates a commit operation to the database by default. To avoid this, use the BeginTrans
method on the session object before using the CreateDynaset
method.
The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag. For the dynaset to be updatable, these conditions must be met:
A SQL statement must refer to a simple column list or to the entire column list (*).
The 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 the Updatable
property of the dynaset returns False
. This method automatically moves to the first row of the created dynaset. You can use SQL bind variables in conjunction with the OraParameters
collection.
The SnapShotID
option causes a snapshot descriptor to be created for the SQLStmt
object created. This property can later be obtained and used in creation of other SQLStmt
or OraDynaset
objects. Execution snapshots provide the ability to ensure that multiple commands executed in the context of multiple OraDatabase
objects operate on the same consistent snapshot of the committed data in the database.
Examples
This example demonstrates CreateObject
, OpenDatabase
and CreateDynaset
methods. 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&) 'Display the first record. MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & _ OraDynaset.Fields("ename").value End Sub
Applies To
Description
Creates an iterator to scan the elements of a collection.
Usage
OraCollection.CreateIterator
Remarks
This method creates an iterator for scanning the elements of an Oracle collection. Accessing collection elements using the iterator is faster than using an index on the instance of a collection.
Examples
Example: OraCollection Iterator
The following example illustrates the use of an Oracle collection iterator.
Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim CourseList As OraCollection Dim Course As OraObject 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", scott/tiger", 0&) 'Create a dynaset object from division Set OraDynaset = OraDatabase.CreateDynaset("select courses from" & _ "division where name='History'", 0&) 'Retrieve a Courses column from Division. Set CourseList = OraDynaset.Fields("Courses").Value 'Create the iterator CourseList.CreateIterator 'Initialize the iterator to point to the beginning of a collection CourseList.InitIterator 'Call IterNext to read CourseList until the end While CourseList.EOC = False Set Course = CourseList.ElementValue course_no = Course.course_no Title = Course.Title Credits = Course.Credits CourseList.IterNext Wend 'Call IterPrev to read CourseList until the beginning CourseList.IterPrev While CourseList.BOC = False Set Course = CourseList.ElementValue course_no = Course.course_no Title = Course.Title Credits = Course.Credits CourseList.IterPrev Wend
See Also:
Applies To
Description
Creates and returns a new named OraSession
object.
Usage
orasession = orasession.CreateNamedSession(session_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
session_name |
A String specifying the name of the session. |
Remarks
Using this method, you can create named sessions that can be referenced later in the same application as long as the session object referred to is in scope. Once a session has been created, the application can reference it by way of the ConnectSession
method or the OraSessions
collection of their respective OraClient
object. The OraSessions
collection only contains sessions created within the current application. Therefore, it is not possible to share sessions across applications, only within applications.
This method is provided for simplicity and is equivalent to the CreateSession
method of the OraClient
object.
Examples
This example demonstrates the use of ConnectSession
and CreateNamedSession
methods to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then, press F5.
Sub Form_Load () 'Declare variables Dim dfltsess As OraSession Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset 'Create the default OraSession Object. Set dfltsess = CreateObject("OracleInProcServer.XOraSession") 'Try to connect to "ExampleSession". If it does not exist 'an error is generated. On Error GoTo SetName Set OraSession = dfltsess.ConnectSession("ExampleSession") On Error GoTo 0 '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&) 'Display or manipulate data here Exit Sub SetName: 'The session named "ExampleSession" was not found, so create it. Set OraSession = dfltsess.CreateNamedSession("ExampleSession") Resume Next End Sub
Applies To
Description
Creates the OraIntervalDS
object. This OraIntervalDS
represents an Oracle INTERVAL
DAY
TO
SECOND
data type.
Usage
Set OraIntervalDSObj = OraSession.CreateOraIntervalDS value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, an OraIntervalDS , or an OraNumber object. |
Return Values
Remarks
An OraSession
object must be created before an OraIntervalDS
object can be created.
If value
is a Variant
of type String
, it must be in the following format: [+/-] Day HH:MI:SSxFF.
If value
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS
represents.
A Variant
of type OraIntervalDS
can also be passed. A cloned OraIntervalDS
is returned.
Examples
Dim oraIDS as OraIntervalDS Dim oraIDS2 as OraIntervalDS Dim oraNum as OraNumber 'Create an OraIntervalDS using a string which represents 1 days, 2 hours, '3 minutes, 4 seconds and 500000 nanoseconds Set oraIDS = oo4oSession.CreateOraIntervalDS("1 2:3:4.005") 'Create an OraIntervalDS using a numeric value which represents '1 days and 12 hours Set oraIDS = oo4oSession.CreateOraIntervalDS(1.5) 'Create an OraIntervalDS using an OraIntervalDS Set oraIDS2 = oo4oSession.CreateOraIntervalDS(oraIDS)
See Also:
Applies To
Description
Creates the OraIntervalYM
object. This OraIntervalYM
represents an Oracle INTERVAL
YEAR
TO
MONTH
data type.
Usage
Set OraIntervalYMObj = OraSession.CreateOraIntervalYM value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalYM object. |
Return Values
Remarks
An OraSession
object must be created before an OraIntervalYM
object can be created.
If value
is a Variant
of type String
, it must be in the following format: [+/-] YEARS-MONTHS.
If value
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
A Variant
of type OraIntervalYM
can also be passed. A cloned OraIntervalYM
object is returned.
Examples
Dim oraIYM as OraIntervalYM Dim oraIYM2 as OraIntervalYM 'Create an OraIntervalYM using a string which represents 1 year and 2 months Set oraIYM = oo4oSession.CreateOraIntervalYM("1- 2") 'Create an OraIntervalYM using a numeric value which represents '1 year and 6 months Set oraIYM = oo4oSession.CreateOraIntervalYM(1.5) 'Create an OraIntervalYM using an OraIntervalYM Set oraIYM2 = oo4oSession.CreateOraIntervalYM(oraIYM)
See Also:
Applies To
Description
Creates an OraNumber
object. This OraNumber
represents an Oracle NUMBER
data type.
Usage
OraNumber = OraSession.CreateOraNumber(inital_value, format)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
initial_value |
Initial value of OraNumber . A Variant of type OraNumber , string or a numeric value. |
format [optional] |
Format string to be used when displaying OraNumber value. |
Return Value
Remarks
For more information about format strings, see the format property on the OraNumber
object.
Applies To
Description
Creates a value instance or referenceable object in the cache and returns the associated OO4O object.
Usage
OraObject1 = OraDatabase.CreateOraObject(schema_name) OraRef1 = OraDatabase.CreateOraObject(schema_name,table_name) OraCollection1 = OraDatabase.CreateOraObject(schema_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
OraObject1 |
A valid OraObject object representing a newly created value instance. |
OraRef1 |
A valid OraRef object representing a newly created referenceable object. |
OraCollection |
A valid OraCollection object representing a newly created collection instance. |
schema_name |
A String specifying the schema name of the value instance to be created. |
table_name |
A String specifying the table name of the referenceable object to be created. |
Remarks
If the table_name
argument is not specified, it creates a value instance in the client and returns an OraObject
or OraCollection
object. If the table_name
argument is specified, it creates a referenceable object in the database and returns an associated OraRef
object.
Examples
OraObject
and OraRef
object examples are provided. 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".
Example: Creating an OraObject Object
The following example illustrates the use of the CreateOraObject
method to insert a value instance. The row containing ADDRESS
is inserted as a value instance in the database.
Dynaset Example
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim AddressNew as OraObject 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", scott/tiger", 0&) 'create a dynaset object from person_tab set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&) ' create a new Address object in OO4O set AddressNew = OraDatabase.CreateOraObject("ADDRESS") 'initialize the Address object attribute to new value AddressNew.Street = "Oracle Parkway" AddressNew.State = "CA" 'start the dynaset AddNew operation and 'set the Address field to new address value OraDynaset.Addnew OraDynaset.Fields("ADDR").Value = AddressNew OraDynaset.Update
OraParameter Example
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim AddressNew as OraObject 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create an OraParameter object represent Address object bind Variable OraDatabase.Parameters.Add "ADDRESS", Null, ORAPARM_INPUT, _ ORATYPE_OBJECT, "ADDRESS" ' create a new Address object in OO4O set AddressNew = OraDatabase.CreateOraObject("ADDRESS") 'initialize the Address object attribute to new value AddressNew.Street = "Oracle Parkway" AddressNew.State = "CA" 'set the Address to ADDRESS parameter Oradatabase.Parameters("ADDRESS").Value = AddressNew 'execute the sql statement which updates Address in the person_tab OraDatabase.ExecuteSQL ("insert into person_tab values ('Eric',30,:ADDRESS)")
Example: Creating an OraRef Object
The following example illustrates the use of the CreateOraObject
method to insert referenceable objects.
In this example, a new PERSON
is inserted as a referenceable object in the database.
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim Person as OraRef 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'CreteOraObject creates a new referenceable 'object in the PERSON_TAB object table and returns associated OraRef set Person = OraDatabase.CreateOraObject("PERSON","PERSON_TAB") 'modify the attributes of Person Person.Name = "Eric" Person.Age = 35 'Update method inserts modified referenceable object in the PERSON_TAB. Person.Update
Applies To
Description
Creates a new OraTimeStamp
object. This OraTimeStamp
method represents an Oracle TIMESTAMP
or an Oracle TIMESTAMP
WITH
LOCAL
TIME
ZONE
data type.
Usage
Set OraTimeStampObj = OraSession.CreateOraTimeStamp value format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStamp . |
[in ] [optional] format |
TimeStamp format string to be used when displaying or interpreting an OraTimeStamp object as a string. If format is not specified, the TimeStamp string is interpreted using the session TIMESTAMP format (NLS_TIMESTAMP_FORMAT format). |
Return Values
Remarks
An OraSession
object must created before an OraTimeStamp
object can be created.
If value
is a Variant
of type String
, the string format must match the datetime format specified in the format
argument. If format
is not specified, the string format must match the session TIMESTAMP
format (NLS_TIMESTAMP_FORMAT
).
If format
is specified, it is stored in the Format
property of the OraTimeStamp
; otherwise, the session TIMESTAMP
format is stored in the OraTimeStamp
Format
property.
Examples
Dim oraTS as OraTimeStamp Dim oraTS1 as OraTimeStamp Dim date as Date 'Create an OraTimeStamp using a string assuming the session 'TIMESTAMP format is "DD-MON-RR HH.MI.SSXFF AM" Set oraTS = oo4oSession.CreateOraTimeStamp("12-JAN-2003 12.0.0.0 PM") 'Create an OraTimeStamp using a string and a format Set oraTS = oo4oSession.CreateOraTimeStamp("2003-01-12 12:00:00 PM", _ "YYYY-MM-DD HH:MI:SS AM") 'Create an OraTimeStamp using a Date date = #1/12/2003# Set oraTS = oo4oSession.CreateOraTimeStamp(date) 'Create an OraTimeStamp using an OraTimeStamp Set oraTS1 = oo4oSession.CreateOraTimeStamp(oraTS)
See Also:
Applies To
Description
Creates a new OraTimeStampTZ
object. This OraTimeStampTZ
object represents an Oracle TIMESTAMP
WITH
TIME
ZONE
data type.
Usage
Set OraTimeStampTZObj = OraSession.CreateOraTimeStampTZ value format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStampTZ . |
[[in ] [optional] format |
TIMESTAMP WITH TIME ZONE format string to be used when displaying or interpreting an OraTimeStampTZ object as a string. If format is not specified, the TIMESTAMP WITH TIME ZONE string is interpreted using the session TIMESTAMP WITH TIME ZONE format (NLS_TIMESTAMP_TZ_FORMAT format). |
Return Values
Remarks
An OraSession
object must be created before an OraTimeStampTZ
object can be created.
If value
is a Variant
of type String
, the string format must match the datetime format specified in the format argument if format is specified; otherwise, the string format must match the session TIMESTAMP
WITH
TIME
ZONE
format (NLS_TIMESTAMP_TZ_FORMAT
).
If value
is a Variant
of type Date
, the date-time value in the Date
is interpreted as the date-time value in the time zone of the session. The TimeZone
property in the OraTimeStampTZ
object contains the time zone of the session.
If format
is specified, it is stored in the Format
property of the OraTimeStampTZ
object, otherwise the session TIMESTAMP
WITH
TIME
ZONE
format is stored in the Format
property of OraTimeStampTZ
object.
Examples
Dim oraTSZ as OraTimeStampTZ Dim oraTSZ1 as OraTimeStampTZ Dim date as Date 'Create an OraTimeStampTZ using a string assuming the session 'TIMESTAMP WITH TIME ZONE format is "DD-MON-RR HH.MI.SSXFF AM TZH:TZM" Set oraTSZ = oo4oSession.CreateOraTimeStampTZ( "12-JAN-2003" & _ "12.0.0.0 PM -03:00") 'Create an OraTimeStampTZ using a string and a format Set oraTSZ = oo4oSession.CreateOraTimeStampTZ( "2003-01-12" & _ "12:00:00 PM -03:00", "YYYY-MM-DD HH:MI:SS AM TZH:TZM") 'Create an OraTimeStampTZ using a Date date = #1/12/2003# Set oraTSZ = oo4oSession.CreateOraTimeStampTZ(date) 'Create an OraTimeStampTZ using an OraTimeStampTZ Set oraTSZ1 = oo4oSession.CreateOraTimeStampTZ(oraTSZ)
See Also:
Applies To
Deprecated.
For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".
Description
Creates a dynaset from a PL/SQL cursor using custom cache and fetch parameters. The SQL statement should be a stored procedure or anonymous block. The resulting dynaset is read-only. Attempting to set the SQL property results in an error. The dynaset can be refreshed with new parameters.
Usage
set OraDynaset = CreatePlsqlCustomDynaset(SQLStatement, CursorName, options, slicesize, perblock, blocks, FetchLimit, FetchSize)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
SQLStatement |
Any valid Oracle PL/SQL stored procedure or anonymous block. |
CursorName |
Name of the cursor created in the PL/SQL stored procedure. |
options |
A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. |
slicesize |
Cache slice size. |
perblock |
Cache slices for each block. |
blocks |
Cache maximum number of blocks. |
FetchLimit |
Fetch array size. |
FetchSize |
Fetch array buffer size. |
Constants
The options flag values are:
Constant | Value | Description |
---|---|---|
ORADYN_DEFAULT |
&H0& |
Accept the default behavior. |
ORADYN_NO_AUTOBIND |
&H1& |
Do not perform automatic binding of database parameters. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Do not remove trailing blanks from character string data retrieved from the database. |
ORADYN_NOCACHE |
&H8& |
Do 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 use. |
ORADYN_NO_MOVEFIRST |
&H40& |
Do not force a MoveFirst when the dynaset is created. BOF and EOF are both true. |
These values can be found in the oraconst.txt
file.
Remarks
The SQL statement must be a PL/SQL stored procedure with BEGIN
and END
around the call, as if it were executed as an anonymous PL/SQL block; otherwise, an error is returned. The CursorName
argument should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise an error is returned. The cursor created inside the stored procedure should represent a valid SQL SELECT
statement.
You do not need to bind the PL/SQL cursor variable using the OraParameters
Add
method if the stored procedure returns a cursor as an output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters
collection.
This method automatically moves to the first row of the created dynaset.
Specifying ORADYN_READONLY
, ORADYN_ORAMODE
, ORADYN_NO_REFETCH
, ORADYN_DIRTY_WRITE
options have no effect on the dynaset creation.
See Also:
Applies To
Deprecated.
For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".
Description
Creates a dynaset from a PL/SQL cursor. The SQL statement should be a stored procedure or an anonymous block. The resulting dynaset is read-only and attempting to set SQL property results in an error. Dynasets can be refreshed with new parameters similar to dynasets without cursors.
Usage
set OraDynaset = CreatePLSQLDynaset(SQLStatement, CursorName, options)
Arguments
Arguments | Description |
---|---|
SQLStatement |
Any valid Oracle PL/SQL stored procedure or anonymous block. |
CursorName |
Name of the cursor created in the PL/SQL stored procedure. |
options |
A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. |
Constants
The options flag values are:
Constant | Value | Description |
---|---|---|
ORADYN_DEFAULT |
&H0& |
Accept the default behavior. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Do not remove trailing blanks from character string data retrieved from the database. |
ORADYN_NOCACHE |
&H8& |
Do 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& |
Do not force a MoveFirst operation when the dynaset is created. BOF and EOF are both true. |
These values can be found in the oraconst.txt
file.
Remarks
The SQL statement must be a PL/SQL stored procedure with BEGIN
and END
statements around the call, as if it were executed as an anonymous PL/SQL block; otherwise an error is returned. The CursorName
argument should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise, an error is returned. Cursors created inside the stored procedure should represent a valid SQL SELECT
statement.
You do not need to bind the PL/SQL cursor variable using the OraParameters.Add method if the stored procedure returns a cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters
collection.
This method automatically moves to the first row of the created dynaset.
Specifying the ORADYN_READONLY
, ORADYN_ORAMODE
, ORADYN_NO_REFETCH
, or ORADYN_DIRTY_WRITE
options have no effect on the dynaset creation.
Examples
This example demonstrates the use of PL/SQL cursor in the CreatePlsqlDynaset
method and Refresh
method. This example returns a PL/SQL cursor as a dynaset for the different values of the DEPTNO
parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL
) is available in the Oracle database. 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 Deptno parameter OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER ' Create OraDynaset based on "EmpCursor" created in stored procedure. Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData" & _ "(:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&) 'Should display KING MsgBox OraDynaset.Fields("ENAME").Value 'Should display 7839 MsgBox OraDynaset.Fields("EMPNO").Value ' Now set the deptno value to 20 OraDatabase.Parameters("DEPTNO").Value = 20 'Refresh the dynaset OraDynaset.Refresh 'Should display JONES MsgBox OraDynaset.Fields("ENAME").Value 'Should display 7566 MsgBox OraDynaset.Fields("EMPNO").Value 'Remove the parameter. OraDatabase.Parameters.Remove ("DEPTNO") End Sub
Applies To
Description
Creates a new named OraSession
object.
Usage
orasession = oraclient.CreateSession(session_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
session_name |
A String specifying the name of the session. |
Remarks
Use this method to create named sessions that can be referenced later in the same application without having to explicitly save the OraSession
object when it is created. Once a session has been created, the application can reference it by way of the ConnectSession
method or the OraSessions
collection of their respective OraClient
object. The OraSessions
collection only contains sessions created within the current application. This means that it is not possible to share sessions across applications, only within applications.
Examples
This example demonstrates how to create a session object using the CreateSession
method of the client object. Copy and paste this code into the definition section of a form. Then, press F5.
Sub Form_Load () 'Declare variables Dim OraClient As OraClient Dim OraSession As OraSession Dim NamedOraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Get the OraClient object. Set OraClient = OraSession.Client 'Create a named OraSession Object 'Alternatively, you could use the CreateNamedSession 'method of the OraSession Object. Set NamedOraSession = OraClient.CreateSession("ExampleSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = NamedOraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Create the OraDynaset Object. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) End Sub
See Also:
OraSession ObjectApplies To
Description
Executes the SQL statement and creates an OraSQLStmt
object from the specified SQL statement and options.
Usage
Set orasqlstmt = oradatabase.CreateSQL(sql_statement, options)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
sql_statement |
Any valid Oracle SQL statement. |
options |
A bit flag indicating the status of any optional states of the OraSQLStmt object. You can combine one or more options by adding their respective values. |
Constants
The options flag values are:
Constant | Value | Description |
---|---|---|
ORASQL_NO_AUTOBIND |
&H1& |
Do not perform automatic binding of database parameters. |
ORASQL_FAILEXEC |
&H2& |
Raise error and do not create SQL statement object. |
ORASQL_NONBLK |
&H4& |
Execute SQL in a nonblocking state. |
These values can be found in the oraconst.txt
file.
Remarks
The SQL statement can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle Database functions treat carriage returns as null terminators.
You can use PL/SQL bind variables in conjunction with the OraParameters
collection.
Executing the SQL statement generates a commit to the database by default. To avoid this, use the BeginTrans
method on the session object before using the CreateSQL
method.
When executing PL/SQL blocks or calling stored procedures, you must include a BEGIN
and END
statement around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the EXECUTE
command of SQL*Plus and SQL*DBA.
If the ORASQL_FAILEXEC
option is used, an error is raised during SQLstmt
object creation failure (on SQLstmt
object refresh). The SQLstmt
object is not created and cannot be refreshed.
Note:
Use theCreateSQL
method with care, because any SQL statement or PL/SQL block that is executed might cause errors afterward when you use the Edit
method on open dynasets.Data Type
String
Examples
This example demonstrates the use of parameters, the CreateSQL
method, the Refresh
method, and the SQL property for OraSQLStmt
object. 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 OraSqlStmt As OraSQLStmt '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&) OraDatabase.Parameters.Add "EMPNO", 7369, 1 OraDatabase.Parameters("EMPNO").ServerType = 2 'ORATYPE_NUMBER OraDatabase.Parameters.Add "ENAME", 0, 2 OraDatabase.Parameters("ENAME").ServerType = 1 'ORATYPE_VARCHAR2 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName" & _ "(:EMPNO, :ENAME); end;", 0&) 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Should display SMITH MsgBox OraDatabase.Parameters("ENAME").Value 'Change the value of the empno parameter. OraDatabase.Parameters("EMPNO").Value = 7499 'Refresh the sqlstmt OraSqlStmt.Refresh 'Should display ALLEN MsgBox OraDatabase.Parameters("ENAME").Value 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Remove the parameter. OraDatabase.Parameters.Remove ("job") End Sub
See Also:
"Asynchronous Processing" for more information about the ORASQL_NONBLK
option
Applies To
Description
Creates a temporary LOB in the database.
Usage
Set OraBLOB = OraDatabase.CreateTempBLOB(use_caching) Set OraCLOB = OraDatabase.CreateTempCLOB(use_caching)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
use_caching |
A boolean value that specifies whether Oracle Database uses caching when accessing this LOB. The default value is False . |
Remarks
Temporary LOBs are LOBs that do not exist permanently in the database. OO4O programmers commonly use temporary LOBs to pass into stored procedures and functions that have LOB arguments.
Temporary LOBs do not require or take part in transactions. (It is not necessary to acquire a lock before write operations, and rollbacks have no effect on temporary LOBs.)
The use_caching
argument directs Oracle to use caching when accessing the temporary LOB. This is suggested when multiple accesses are expected on a single LOB. Caching is not required for the typical case, where a LOB is created, filled with data, passed to a stored procedure, and then discarded.
Temporary LOBs exist on the database until no more references to the corresponding OraBLOB
or OraCLOB
exist on the client. Note that these references include any OraParameter
or OraParamArray
that contain a temporary OraBLOB
or OraCLOB
object.
Examples
Example: Passing a Temporary CLOB to a Stored Procedure
The following example illustrates the use of the CreateTempClob
method to create a OraCLOB
. The OraCLOB
is then populated with data and passed to a stored procedure which has an argument of type CLOB
.
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraClob as OraClob '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 stored procedure used in this example OraDatabase.ExecuteSQL ("create or replace procedure GetClobSize" & _ "(in_clob IN CLOB, clobsize OUT NUMBER) as Begin clobsize" & _ " := DBMS_LOB.GETLENGTH(in_clob); End;") 'create an OraParameter object to represent Clob bind Variable OraDatabase.Parameters.Add "CLOB", Null, ORAPARM_INPUT, ORATYPE_CLOB 'the size will go into this bind variable OraDatabase.Parameters.Add "CLOBSIZE", Null, ORAPARM_OUTPUT, ORATYPE_NUMBER ' create a temporary CLOB set OraClob = OraDatabase.CreateTempClob 'Populate the OraClob with some data. Note that no row locks are needed. OraClob.Write "This is some test data" 'set the Parameter Value to the temporary Lob OraDatabase.Parameters("CLOB").Value = OraClob 'execute the sql statement which updates Address in the person_tab OraDatabase.ExecuteSQL ("Begin GetClobSize(:CLOB, :CLOBSIZE); end;") 'Display the size MsgBox OraDatabase.Parameters("CLOBSize").Value 'these two lines force the temporary clob to be freed immediately OraDatabase.Parameters.Remove "CLOB" Set OraClob = nothing
Applies To
Description
Deletes the current row of the specified dynaset.
Usage
oradynaset.Delete oradynaset.DbDelete
Remarks
A row must be current before you can use the Delete
method; otherwise, an error occurs.
Note that after you call the Delete
method on a given row in a dynaset in a global transaction (that is, once you issue a BeginTrans
method), locks remain on the selected rows until you call a CommitTrans
or Rollback
method.
Any references to the deleted row produce an error. The deleted row, as well as the next and previous rows, remain current until database movement occurs (using the MoveFirst
, MovePrevious
, MoveNext
, or MoveLast
methods). Once movement occurs, you cannot make the deleted row current again.
You cannot restore deleted records except by using transactions.
Note:
A call to anEdit
, AddNew
, or Delete
method, cancels any outstanding Edit
or AddNew
calls before proceeding. Any outstanding changes not saved using an Update
method are lost during the cancellation.Examples
This example demonstrates the use of the Delete
method to remove records from a database. 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. Only select the employees in Department 10. Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where" & _ "deptno=10", 0&) Do Until OraDynaset.EOF OraDynaset.Delete OraDynaset.MoveNext Loop MsgBox "All employees from department 10 removed." End Sub
Applies To
Description
Deletes an element at given index. This method is available only in an OraCollection
of type ORATYPE_TABLE
(nested table).
Usage
OraCollection.Delete index
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] index |
An Integer specifying the index of the element to be deleted. |
Remarks
The Delete
method creates holes in the client-side nested table. This method returns an error if the element at the given index has already been deleted or if the given index is not valid for the given table.
Examples
The following example illustrates the Delete
method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim CourseList as OraCollection 'create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create a dynaset object from division set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&) 'retrieve a Courses column from Division. 'Here Value property of OraField object returns CourseList OraCollection set CourseList = OraDynaset.Fields("Courses").Value 'Delete the CourseList NestedTable at index 2. 'Before that lock should be obtained OraDynaset.Edit CourseList.Delete 2 OraDynaset.Update
See Also:
Type (OraCollection) PropertyApplies To
Description
Deletes a referenceable object in the database.
Usage
OraRef.Delete
Remarks
Accessing attributes on the deleted instance results in an error.
Examples
The following example illustrates the Delete
method. 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 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 an OraParameter object represent Person object bind Variable OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSON" 'execute the sql statement which selects person 'from the customers table for account = 10 OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _ "where account = 10; END;") 'get the Person object from OraParameter set Person = OraDatabase.Parameters("PERSON").Value 'delete the Person object in the server for modifying its attributes Person.Delete
Applies To
Description
Deletes a collection iterator.
Usage
OraCollection.DeleteIterator
Remarks
None.
Examples
See "Example: OraCollection Iterator"
See Also:
"CreateIterator Method"Applies To
Description
Dequeues a message.
Usage
Q.Dequeue()
Remarks
The message attributes can be accessed with the OraAQMsg
interface contained in this object. On success, this method returns the message identifier as an array of bytes. Otherwise, it returns an empty array (null).
Examples
Note:
The following code sample are models for dequeuing messages.A complete AQ sample can be found in the \OO4O\VB\SAMPLES \AQ
directory.
Example: Dequeuing Messages of RAW Type
'Dequeue the first message available Q.Dequeue Set Msg = Q.QMsg 'Display the message content MsgBox Msg.Value 'Dequeue the first message available without removing it ' from the queue Q.DequeueMode = ORAAQ_DQ_BROWSE 'Dequeue the first message with the correlation identifier ' equal to "RELATIVE_MSG_ID" Q.Navigation = ORAAQ_DQ_FIRST_MSG Q.correlate = "RELATIVE_MESSAGE_ID" Q.Dequeue 'Dequeue the next message with the correlation identifier ' of "RELATIVE_MSG_ID" Q.Navigation = ORAAQ_DQ_NEXT_MSG Q.Dequeue 'Dequeue the first high priority message Msg.Priority = ORAQMSG_HIGH_PRIORITY Q.Dequeue 'Dequeue the message enqueued with message id of Msgid_1 Q.DequeueMsgid = Msgid_1 Q.Dequeue 'Dequeue the message meant for the consumer "ANDY" Q.consumer = "ANDY" Q.Dequeue 'Return immediately if there is no message on the queue Q.wait = ORAAQ_DQ_NOWAIT Q.Dequeue
Example: Dequeuing Messages of Oracle Object Types
Set OraObj = DB.CreateOraObject("MESSAGE_TYPE") Set QMsg = Q.AQMsg(23, "MESSAGE_TYPE","SCOTT") 'Dequeue the first message available without removing it Q.Dequeue OraObj = QMsg.Value 'Display the subject and data MsgBox OraObj("subject").Value & OraObj("Data").Value
Applies To
Description
Describes a schema object. This method returns an instance of the OraMetaData
interface.
Usage
OraMetaDataObj = OraDatabase.Describe(SchemaObjectName)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] SchemaObjectName |
A String representing the name of the schema object to be described. |
Remarks
The following schema object types can be described:
Tables
Views
Procedures
Functions
Packages
Sequences
Collections
(VARRAY
s or nested tables)
Types
Describing any other schema object (for example, a column) or an invalid schema object name raises an error. You should navigate to schema objects not listed here, rather than describing them directly.
This method takes the name of a schema object, such as emp
, and returns a COM Automation object (OraMetaData
). The OraMetaData
object provides methods for dynamically navigating and accessing all the attributes (OraMDAttribute
collection) of a schema object described.
Examples
Simple Describe Example
The following Visual Basic code illustrates a how to use the Describe
method to retrieve and display several attributes of the emp
table.
Set emp = OraDatabase.Describe("emp") 'Display the name of the Tablespace MsgBox emp!tablespace 'Display name and data type of each column in the emp table. Set empColumns = emp!ColumnList Set ColumnList = empColumns.Value for i = 0 to ColumnList.Count - 1 Set Column = ColumnList(i).Value MsgBox "Column: " & Column!Name & " Data Type: " & Column!Data Type Next i
Describing a Table Example
Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim OraMetaData As OraMetaData Dim OraMDAttribute As OraMDAttribute Dim ColumnList As OraMetaData Dim Column As OraMetaData '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&) 'Use Describe to retrieve the metadata object Set OraMetaData = OraDatabase.Describe("EMP") 'Display the type of the metadata MsgBox TypeofMetaData & OraMetaData.Type 'Display the count of attributes belonging to the table MsgBox NumberOfAttributes & OraMetaData.Count 'Attribute can be accessed using the explicit OraMetaData property: Attribute 'The index can be an integer or the attribute name Set OraMDAttribute = OraMetaData.Attribute(0) MsgBox "ObjectID: " & OraMDAttribute.Value 'Since Attribute is the default property of OraMetaData, an attribute can ' be accessed as follows. Here, we use attribute name as an index Set OraMDAttribute = OraMetaData("ObjectID") MsgBox "Name: " & OraMDAttribute.Name MsgBox "Value: " & OraMDAttribute.Value 'Value is the default property of OraMDAttribute, the following shows 'the Value of property "IsClustered" for the table MsgBox "Is Clustered: " & OraMetaData!IsClustered MsgBox "Is Partitioned: " & OraMetaData!IsPartitioned 'Retrieve the Column List Set OraMDAttribute = OraMetaData!ColumnList ' Use IsMDObject property to check whether an attribute's value is an OraMetaData If (OraMDAttribute.IsMDObject()) Then Set ColumnList = OraMDAttribute.Value 'Display the name and data type of each column For I = 0 To ColumnList.Count - 1 Set Column = ColumnList(I).Value ' Each column is again an OraMetaData MsgBox "Column: " & Column!Name & " data type: " & Column!Data Type Next I End If
Example: Describing a User-Defined Type
Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim OraMetaData As OraMetaData Dim OraMDAttribute As OraMDAttribute Dim attrList As OraMetaData Dim attr As OraMetaData 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDB", "scott/tiger",0&) Set OraMetaData = OraDatabase.Describe("ORAMD_ADDRESS") NumAttributes = OraMetaData!NumAttributes NumMethods = OraMetaData!NumMethods MsgBox "The Address type has " & NumAttributes & " attributes" MsgBox "Address Object has " & NumMethods & " methods" 'Retrieve the attribute list of this type object Set attrList = OraMetaData!Attributes.Value 'Display the name and data type of each attribute For I = 0 To attrList.Count - 1 Set attr = attrList(I).Value ' each attr is actually an OraMetaData MsgBox "Attribute Name: " & attr!Name MsgBox "Attribute Type: " & attr!TypeName Next I
Example: Describing Unknown Schema Objects
Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".
Sub RecursiveDescribe(name$, xMD As OraMetaData) Dim xMDAttr As OraMDAttribute For I = 0 To xMD.Count - 1 Set xMDAttr = xMD.Attribute(I) ' If an attribute can be described further, describe it, ' otherwise display its attribute name & value If (xMDAttr.IsMDObject) Then RecursiveDescribe xMDAttr.name, xMDAttr.Value Else MsgBox name & "->" & xMDAttr.name & " = " & xMDAttr.Value End If Next I End Sub Sub Main() 'This example displays all the attributes of any schema object given Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim xMD As OraMetaData Dim x As String 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDB", "scott/tiger", 0&) ' x is any database object, here the EMP table is used as an example x = "EMP" Set xMD = OraDatabase.Describe(x) MsgBox x & " is of the type " & xMD.Type RecursiveDescribe x, xMD End Sub
See Also:
Applies To
Description
The pool is implicitly destroyed if its parent session object is destroyed. It can also be destroyed at any time by invoking the DestroyDatabasePool
method.
Usage
DestroyDatabasePool()
Remarks
An exception is raised by this call if the pool does not exist.
See Also:
CreateDatabasePool MethodApplies To
Description
Disables buffering of LOB operations.
Usage
OraBlob.DisableBuffering OraClob.DisableBuffering
Remarks
This method does not automatically flush the buffers. The FlushBuffer
method should be used to flush any changes before buffering is disabled.
Applies To
Description
Divides the OraIntervalDS
object by a divisor.
Usage
OraIntervalDSObj.Div divisor
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] divisor |
A Variant for type numeric value or an OraNumber object to be used as the divisor. |
Remarks
The result of the operation is stored in the OraIntervalDS
object, overwriting any previous value. There is no return value.
Applies To
Description
Divides the OraIntervalYM
object by a divisor.
Usage
OraIntervalYMObj.Div divisor
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] divisor |
A Variant for type numeric value or an OraNumber object to be used as the divisor. |
Remarks
The result of the operation is stored in the OraIntervalYM
object, overwriting any previous value. There is no return value.
Applies To
Description
Divides an OraNumber
object by a numeric argument.
Usage
OraNumber.Div operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber object, or a numeric value. |
Remarks
The result of the operation is stored in an OraNumber
object . There is no return value.
The operand
must not be equal to zero, or a divide by zero error is raised.
Applies To
Description
Specifies the dynaset cache and fetch parameters for the dynaset created from the PL/SQL cursor.
Usage
oraparameter.DynasetCacheParams SliceSize,perblock, Blocks, FetchLimit,FetchSize
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
SliceSize |
Cache slice size. |
perblock |
Cache slices for each block. |
Blocks |
Cache maximum number of blocks. |
FetchLimit |
Fetch array size. |
FetchSize |
Fetch array buffer size. |
Remarks
This method should be called before executing the PL/SQL procedure containing a cursor variable. By default, the dynaset is created with default cache and fetch parameters specified in the registry.
Applies To
Description
Begins an edit operation on the current row by copying the data to the copy buffer.
Usage
oradynaset.Edit oradynaset.DbEdit
Remarks
The Edit
method causes the locally cached data to be compared to the corresponding row of an Oracle Database. An error is generated if Oracle Database data is not the same as the data currently being browsed. If this operation succeeds, the row is locked using a "SELECT
...
FOR
UPDATE"
statement until the edit is completed with an Update
method or until database movement occurs, which discards any edits in progress. The behavior of the "SELECT
...
FOR
UPDATE"
statement is affected by the Lock
Wait
mode of the options flag used when the OpenDatabase
method was called.
Note:
The cached data is not compared to the database withBLOB
and CLOB
, Object
, REF
, and collection types, and the data is updated regardless (dirty writes).During editing, changes made to fields are kept in a shadowed copy buffer and do not yet reflect the actual contents of the database. However, all references to the row return the newly modified data as long as the edit operation is still in progress.
When data is modified within a data control attached to this dynaset, the Edit
method is invoked automatically upon the next record movement. Thus, this method is required only when modifications are made to field data within code.
Note:
A call to anEdit
, AddNew
, or Delete
method cancels any outstanding Edit
or AddNew
calls before proceeding. Any outstanding changes not saved using an Update
operation are lost during the cancellation.Examples
This example demonstrates the use of the Edit
and Update
methods to update values in a database. 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 * from emp", 0&) 'Traverse until EOF is reached, settingeach employee's salary to zero Do Until OraDynaset.EOF OraDynaset.Edit OraDynaset.Fields("sal").value = 0 OraDynaset.Update OraDynaset.MoveNext Loop MsgBox "All salaries set to ZERO." End Sub
Applies To
Description
Locks a referenceable object in the database.
Usage
OraRef.Edit
Remarks
Call this method before modifying any attributes of an underlying referenceable object of OraRef
or an error is raised. This call makes a network round-trip to lock the object in the database. An error is raised if the object is changed by another user in the database. The object can also be locked during the pin operation using the EditOption
property.
Examples
The following examples update the attributes of the "PERSON"
referenceable object in the database.
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".
Dynaset Example
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 'locks the Person object in the server for modifying its attributes Person.Edit Person.Name = "Eric" Person.Age = 35 'Update method flushes the modified referenceable object in the server Person.Update
Parameter Example
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim Person as OraRef 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create an OraParameter object represent Address object bind Variable OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, _ ORATYPE_REF,"PERSON" 'execute the sql statement which selects person from the customers table OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON" & _ "from customers where account = 10; END;") 'get the Person object from OraParameter set Person = OraDatabase.Parameters("PERSON").Value 'locks the Person object in the server for modifying its attributes Person.Edit Person.Name = "Eric" Person.Age = 35 'Update method flushes the modified referenceable object in the server Person.Update
See Also:
EditOption (OraRef) PropertyApplies To
Description
Returns the current value of the collection element to which the iterator points.
Usage
elem_val = OraCollection.ElementValue
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
elem_val |
A Variant representing element value of the collection. |
ElementType
For elements of type Object
and REF
, element values are returned as corresponding OO4O objects for that type. The following table shows the element type and return value of the elements:
ElementType | Element Value |
---|---|
Object |
OraObject |
REF |
OraRef |
Date |
String |
Number |
String |
CHAR ,VARCHAR2 |
String |
Real |
Real |
Integer |
Integer |
Remarks
Calling this method when the EOC
or BOC
property returns True
raises an error. The Variant
type of the element depends on the element type of the collection.
Examples
Applies To
Description
Enables buffering of LOB operations.
Usage
OraBlob.EnableBuffering OraClob.EnableBuffering
Remarks
When enabled, buffering uses the LOB Buffering subsystem to minimize network round-trips by buffering changes until the FlushBuffer
method is called. This can be beneficial to applications that perform a series of repeated small reads and writes to specific areas of a LOB.
There are many caveats and restrictions for using LOB buffering. These are summarized here, but for complete information, see the Oracle Database Application Developer's Guide - Large Objects.
Restrictions
The following LOB methods cannot be used while buffering is enabled:
Append
Copy
Erase
Size
Trim
CopyFromBFILE
CopyFromFile
CopyToFile
There is currently a 512 KB limit to the amount of a single read/write operation.
Error reporting for buffered operations is delayed until the next database access.
Transactional support is not guaranteed. Users must roll back changes manually if an error occurs.
Do not perform updates to a LOB column that bypasses the buffering system while in the same transaction as a buffer-enabled LOB. Performing an INSERT
statement can cause this.
Only one LOB object is allowed to perform buffered writes to a given LOB. Other LOB objects that point to the same LOB raise an error if they attempt a buffered write.
A LOB object taken from an OraParameter
object raises an error if it is buffer-enabled and bound to an OUT
parameter.
The Clone
method can raise an error for buffer enabled LOBs.
Appending directly to the end of the LOB is allowed, but any write operation whose offset extends beyond the end of the LOB and results in blank padding (for CLOB
) or zero padding (for BLOB
) raises an error.
Applies To
Description
Enqueues the message (OraAQMsg
) contained in this object.
Usage
Msgid = Q.Enqueue
Remarks
On success, this method returns the message identifier as an array of bytes. Otherwise, it returns an empty array (null).
Examples
Note:
The following code samples are models for enqueuing messages, but cannot be run as is.A complete AQ sample can be found in the \OO4O\VB\SAMPLES\AQ
directory.
Enqueuing Messages of Type RAW
'Create an OraAQ object for the queue "DBQ" Dim Q as OraAQ Dim Msg as OraAQMsg Dim OraSession as OraSession Dim DB as OraDatabase Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set DB = OraSession.OpenDatabase("mydb", "scott/tiger" 0&) Set Q = DB.CreateAQ("DBQ") 'Get a reference to the AQMsg object Set Msg = Q.AQMsg Msg.Value = "Enqueue the first message to a RAW queue." 'Enqueue the message Q.Enqueue 'Enqueue another message. Msg.Value = "Another message" Q.Enqueue 'Enqueue a message with non-default properties. Msg.Priority = ORAQMSG_HIGH_PRIORITY Msg.Delay = 5 Msg.Value = "Urgent message" Q.Enqueue Msg.Value = "The visibility option used in the enqueue call" & _ "is ORAAQ_ENQ_IMMEDIATE" Q.Visible = ORAAQ_ENQ_IMMEDIATE Msgid = Q.Enqueue 'Enqueue Ahead of message Msgid_1 Msg.Value = "First Message to test Relative Message id" Msg.Correlation = "RELATIVE_MESSAGE_ID" Msg.delay = ORAAQ_MSG_NO_DELAY Msgid_1 = Q.Enqueue Msg.Value = "Second message to test RELATIVE_MESSAGE_ID is queued" & _ " ahead of the First Message " Q.RelMsgId = Msgid_1 Msgid = Q.Enqueue
Enqueuing Messages of Oracle Object Types
'Prepare the message. MESSAGE_TYPE is a user defined type in the "AQ" schema Set OraMsg = Q.AQMsg(23, "MESSAGE_TYPE","SCOTT") Set OraObj = DB.CreateOraObject("MESSAGE_TYPE") OraObj("subject").Value = "Greetings from OO4O" OraObj("text").Value = "Text of a message originated from OO4O" Msgid = Q.Enqueue
Applies To
Description
Erases the specified portion of the LOB value of this object starting at the specified offset.
Usage
OraBlob.Erase amount, offset OraClob.Erase amount, offset
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] amount |
An Integer specifying the maximum number of characters or bytes to be erased. |
[in ] offset [optional] |
An Integer specifying absolute offset of the LOB value from which to start erasing. Default value is 1. |
Remarks
Obtain either a row-level lock or object-level lock before calling this method. The actual number of characters or bytes and the requested number differ if the end of the LOB value is reached before erasing the requested number of characters or bytes. For BLOB
types, erasing means that zero-byte fillers overwrite the existing LOB value. For CLOB
types, erasing means that spaces overwrite the existing LOB value.
Applies To
Description
Executes a single non-SELECT
SQL statement or a PL/SQL block.
Usage
rowcount = oradatabase.ExecuteSQL(sql_statement) rowcount = oradatabase.DbExecuteSQL(sql_statement)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
sql_statement |
Any valid Oracle non-SELECT SQL statement. |
Remarks
Executes a SQL statement and returns the number of rows processed by that statement.
The sql_statement
argument can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle Database functions treat carriage returns as null terminators.
Executing the SQL statement generates a commit to the database by default. To avoid this, use the BeginTrans
method on the session object before using the ExecuteSQL
method.
You can use PL/SQL bind variables in conjunction with the OraParameters
collection.
When executing PL/SQL blocks or calling stored procedures, you must include a BEGIN
and END
statement around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the EXECUTE
command of SQL*Plus and SQL*DBA.
Note:
TheExecuteSQL
method should be used with care because any SQL statement or PL/SQL block that is executed can adversely affect open dynasets. This is true if the OraDatabase
object used for the ExecuteSQL
method is the same as the one that was used to create the dynaset. Use a different OraDatabase
object if you are unsure.Normal dynaset operations can be adversely affected, if in transactional mode, a database commit is issued. This can happen if a SQL commit statement, a Data Control Language (DCL), or Data Definition Language (DDL) command is issued. DCL and DDL SQL commands, such as CREATE
, DROP
, ALTER
, GRANT
, and REVOKE
always force a commit, which in turn commits everything done before them. See the Oracle Database SQL Reference for more details about DCL, DDL, and transactions.
Data Type
Long
Integer
Examples
Example: ExecuteSQL
This example uses the Add
and Remove
parameter methods, the ServerType
parameter property, and the ExecuteSQL
database method to call the stored procedure GetEmpName
and the stored function GetSal
. Before running the example, run the ORAEXAMP.SQL
file to create GetEmpName
and GetSal
as well as other necessary object types and LOBs in Oracle Database. Then, copy and paste this OO4O code example into the definition section of a form and run the program.
Sub Form_Load () 'Declare variables Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDatabase '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 True
if an element exists at a given index; otherwise, returns. Valid only for OraCollection
of Type ORATYPE_TABLE
.
Usage
exists = OraCollection.Exist index
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[out ] exists |
A Boolean value specifying the existence status of the element. |
[in ] index |
An Integer specifying the index of the element. |
Remarks
None.
Applies To
Description
Calculates e to the power of an OraNumber
object.
Usage
OraNumber.Exp
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Fetches a referenceable object into the cache and returns the associated OraRef
object.
Usage
Set OraRef = OraDatabase.FetchOraRef(hex_value)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
hex_value |
A String containing the hexadecimal value of the REF . |
Remarks
The hex_value
argument can be obtained through the OraRef.HexValue
property or from an XML document generated by the OraDynaset.GetXML
method.
See Also:
Applies To
Description
Returns the number of bytes stored in a LONG
or LONG
RAW
field. Not available at design time and read-only at run time.
Usage
data_size = orafield.FieldSize( ) data_size = orafield.DbFieldSize( )
Remarks
Returns the number of bytes stored in a LONG
or LONG
RAW
field, up to a value of around 64 KB. If the field contains more than 64 KB, then the FieldSize
method returns -1
.
Oracle Database does not return the length of columns that are greater than 64 KB; The only way to determine the length is to retrieve the column. To conserve resources, columns of lengths greater than 64 KB are not retrieved automatically.
Data Type
Long Integer
Applies To
Description
Find the indicated rows in the dynaset that matches the FindClause
. The FindClause
can be any valid WHERE
clause without the WHERE
. If the current FindClause
matches the last clause from the previous find operation, then the current FindClause
is not parsed again.
These methods move the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found, the NoMatch
property is set to True
, and the current row remains the same.
Usage
oradynaset.FindFirst FindClause oradynaset.FindLast FindClause oradynaset.FindNext FindClause oradynaset.FindPrevious FindClause
Remarks
The following types of expressions can be used in the FindClause
:
Simple queries, such as "deptno = 20"
Queries involving complex expressions, such as "sal
+
100
>
1000"
.
SQL function calls, such as "UPPER(ename)
=
'SCOTT'
"
or "NVL(comm,
0)
=
0"
.
Subqueries, such as "deptno
in
(select
deptno
from
dept)"
.
The SQL LIKE
operator does not work in multiple byte languages. Table or synonym DUAL
is required in the user's schema. Date values are retrieved and compared in Visual Basic format, which is the format specified in the Control Panel. Therefore, date comparisons fail if any other format such as the default Oracle format, DD-MON-YYYY is used.
The SQL function TO_CHAR
(date
, fmt
) cannot be used because the first argument must be a date value in native Oracle format, and OO4O only handles 'string
dates'
.
The SQL function TO_DATE
converts a string to a date, but OO4O converts it back to a string in Visual Basic format, as previously described, and the comparison may still fail.
The FindPrevious
and FindLast
methods in a NO_CACHE
dynaset do not work; NoMatch
is set to True
.
Note: To avoid raising an error, check for EOF
or BOF
before calling a Find
method.
Examples
This example demonstrates the use of the FindFirst
, FindNext
, FindPrevious
methods. Copy and paste this code into the definition section of a form. Then, press F5.
Sub Form_Load () Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim OraFields As OraFields Dim FindClause As String Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&) Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where empno" & _ ">= 7654 and empno <= 7844 ", ORADYN_NO_BLANKSTRIP) Set OraFields = OraDynaset.Fields OraDynaset.MoveFirst 'FindClause for job as MANAGER FindClause = "job LIKE '%GER'" OraDynaset.FindFirst FindClause 'NoMatch property set to true , if no rows found If OraDynaset.NoMatch Then MsgBox "Couldn't find rows " else MsgBox OraFields("ename").Value ' Should display BLAKE OraDynaset.FindNext FindClause MsgBox OraFields("ename").Value ' Should display CLARK OraDynaset.FindPrevious FindClause MsgBox OraFields("ename").Value ' Should display BLAKE endif End Sub
Applies To
Description
Calculates the floor, that is, lowest value, of an OraNumber
object.
Usage
OraNumber.Floor
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Flushes, that is, empties, the content of the LOB to the database if LOB buffering has been enabled.
Usage
OraBlob.FlushBuffer OraClob.FlushBuffer
See Also:
EnableBuffering (OraLOB) MethodApplies To
Description
Returns the next available OraDatabase
object from the pool.
Usage
GetDatabaseFromPool(long waitTime)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
waitTime |
The number of milliseconds this call waits for an object to be available, if the pool contains the maximum number of objects and all are used. |
Remarks
To retrieve an OraDatabase
object from the pool, the GetDatabaseFromPool
method is called. This function returns a reference to an OraDatabase
object. If the pool does not contain the maximum number of objects allowed, and all objects in the pool are used, then an additional OraDatabase
object is created implicitly. In addition, if a pool item contains an OraDatabase
object that has been timed out, then a new object is created and returned. The OraDatabase
object obtained from the pool is then marked as in use and is returned to the pool when the object is no longer referenced by the application.
Exceptions are raised by this call if:
The connection pool does not exist.
The pool contains no objects.
A time-out has occurred.
The LastServerErr
property of the OraSession
object contains the code for the specific cause of the exception.
See Also:
CreateDatabasePool MethodApplies To
Description
Returns a string containing the bytes of all or a portion of a LONG
or LONG
RAW
field.
Usage
data_string = orafield.GetChunk(offset, numbytes) data_string = orafield.DbGetChunk(offset, numbytes)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
offset |
The number of bytes of the field to skip before copying data. |
numbytes |
The number of bytes to copy. |
Remarks
The GetChunk
method typically retrieves the specified bytes from the local cache. If data is not found in the cache, then the GetChunk
method requests it from the database. Data from all fields (except the LONG or LONG
RAW
field) in the dynaset are retrieved and compared to the cached values for consistency. If any changes have occurred since the last fetch, then the GetChunk
method stops the operation which causes an error and returns a Null
string.
If a LONG
or LONG
RAW
field is less than 65280 bytes, it is quicker to retrieve the data using the Value
property than using the GetChunk
method. You cannot use the GetChunk
method on a LONG
or LONG
RAW
field for which you have created an alias.
See "Migration from LONG RAW to LOB or BFILE".
Examples
This example demonstrates the use of the GetChunk
method to retrieve a LONG
RAW
column of a database and save it as a file. This example expects a valid dynaset named OraDynaset
representing a table with a column named longraw
. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.
Sub GetChunkExample (FName As String) 'Declare various variables Dim CurSize As Integer, ChunkSize As Long Dim I As Integer, FNum As Integer, CurChunk As String 'Set the size of each chunk ChunkSize = 10240 frmChunk.MousePointer = HOURGLASS 'Get a free file number FNum = FreeFile 'Open the file Open FName For Binary As #FNum I = 0 'Loop through all of the chunks. Oracle does not return the size of columns > ' 64KB. We should loop until the length of our block is less than we asked for. Do CurChunk = OraDynaset.Fields("LONGRAW").GetChunk(I * ChunkSize, ChunkSize) CurSize = Len(CurChunk) 'Get the length of the current chunk. Put #FNum, , CurChunk 'Write chunk to file. I = I + 1 Loop Until CurSize < ChunkSize 'Close the file. Close FNum frmChunk.MousePointer = DEFAULT End Sub
Applies To
Description
Reads the data from the LONG
or LONG
RAW
field into byte array and returns the size of data read.
Usage
Size_read = orafield.GetChunkByte(ByteArray, offset, numbytes)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
ByteArray |
The first element of the ByteArray to hold the data. |
offset |
The number of bytes in the field to skip before copying data. |
numbytes |
The number of bytes to copy. |
Remarks
When possible, the GetChunkByte
method retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In these cases, the GetChunkByte
method requests the necessary data from the database as required. As part of this process, data from all fields (except the Long or LONG
RAW
field) in the dynaset are retrieved and compared with the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then the GetChunkByte
method stops the operation and an error occurs. In the case of an abort, the returned string is Null
.
If a LONG
or LONG
RAW
field is less than 65280 bytes in size, it is quicker to retrieve the data using the Value
property than using the GetChunkByte
method. You cannot use the GetChunkByte
method on a LONG
or LONG
RAW
field for which you have created an alias.
Examples
This example demonstrates the use of the GetChunkByte
method to retrieve a LONG
RAW
column of a database and save it as a file. This example expects a valid dynaset named OraDynaset
representing a table with a column named longraw
. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.
Sub GetChunkByteExample (FName As String) 'Declare various variables Dim CurSize As Integer, ChunkSize As Long Dim I As Integer, FNum As Integer, CurChunk() As Byte 'Set the size of each chunk ChunkSize = 10240 'Redim CurChunk Array ReDim CurChunk(ChunkSize) frmChunk.MousePointer = HOURGLASS 'Get a free file number FNum = FreeFile 'Open the file Open FName For Binary As #FNum I = 0 'Loop through all of the chunks 'Oracle does not return the size of columns > 64KB. We should loop until the 'length of our block is less than we asked for. Do CurSize = OraDynaset.Fields("type_longraw").GetChunkByte(CurChunk(0), I * ChunkSize, ChunkSize) If CurSize > 0 AND CurSize < ChunkSize Then ReDim CurChunk(CurSize) CurSize = OraDynaset.Fields("type_longraw").GetChunkByte(CurChunk(0), I * ChunkSize, CurSize) End If Put #FNum, , CurChunk 'Write chunk to file. I = I + 1 Loop Until CurSize <= 0 'Close the file. Close FNum frmChunk.MousePointer = DEFAULT End Sub
See Also:
"Migration from LONG RAW to LOB or BFILE" for additional informationApplies To
Description
Reads the data from a LONG
or LONG
RAW
field into a Variant
and returns the amount of data read.
Usage
amount_read = orafield.GetChunkByteEx(ByteArray, offset, numbytes)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
ByteArray |
The name of the Variant ByteArray to hold the data. |
offset |
The number of bytes in the field to skip before copying data. |
numbytes |
The number of bytes to copy. |
Remarks
When possible, the GetChunkByteEx
method retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In these cases, the GetChunkByteEx
method requests the necessary data from the database as required. As part of this process, data from all fields (except the LONG
or LONG
RAW
field) in the dynaset are retrieved and compared to the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then the GetChunkByteEx
method aborts the operation with an error.
Because the GetChunkByteEx
method takes in a Variant
as the first parameter, instead of the first element of the ByteArray
as in the GetChunkByte
method, only the GetChunkByteEx
method can be used within an ASP/IIS environment.
If a LONG
or LONG
RAW
field is less than 65280 bytes in size, it is quicker to retrieve the data using the Value
property than using the GetChunkByteEx
method.
See "Migration from LONG RAW to LOB or BFILE".
Examples
Using the GetChunkByteEx Method to Retrieve a LONG RAW Example
This example demonstrates the use of the GetChunkByteEx
method to retrieve a LONG
RAW
column of a database and save it as a file. This example expects a valid dynaset named OraDynaset
representing a table with a column named type_longraw
. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.
Sub GetChunkByteExExample (FName As String) 'Declare various variables Dim bytesread As Integer, ChunkSize As Long , bytearr() as byte Dim I As Integer, FNum As Integer, CurChunk 'Set the size of each chunk ChunkSize = 10240 frmChunk.MousePointer = HOURGLASS 'Get a free file number FNum = FreeFile 'Open the file Open FName For Binary As #FNum I = 0 'Loop through all of the chunks 'Oracle does not return the size of columns > 64KB. 'We should loop until the length of our block is 'less than we asked for. Do bytesread = OraDynaset.Fields("type_longraw").GetChunkByteEx(CurChunk,_ I * ChunkSize, ChunkSize) 'redim byte array redim bytearr(bytesread - 1) bytearr = CurChunk Put #FNum, , bytearr 'Write chunk to file. I = I + 1 Loop Until bytesread < ChunkSize 'Close the file. Close FNum frmChunk.MousePointer = DEFAULT End Sub
Using the GetChunkByteEx Method with Active Server Pages (ASP) Example
'This example is for use with ASP (Active Server Pages) <%@ LANGUAGE = VBScript %> <%Response.ContentType = "image/JPEG"%> <% Dim OraDatabase, Oradynaset Dim Chunksize, BytesRead, CurChunkEx 'This assumes a pool of database connections have been created in the global.asa Set OraDatabase = OraSession.getDatabaseFromPool(10) 'This assumes a table called "art_gallery" and 'displays JPEG images stored in the table Set OraDynaset = OraDatabase.CreateDynaset("select art from art_gallery " & _ "where artist = 'Picasso'", 0) BytesRead = 0 'Reading in 32K chunks ChunkSize= 32768 Do BytesRead = OraDynaset.Fields("picture").GetChunkByteEx(CurChunkEx, _ i * ChunkSize, ChunkSize) if BytesRead > 0 then Response.BinaryWrite CurChunkEx end if Loop Until BytesRead < ChunkSize 'Cleanup, remove all local references Set OraDynaset = Nothing Set Oradatabase = Nothing %>
Applies to
Description
Generates an XML document based on the contents of the dynaset.
Usage
XMLstring = oradynaset.GetXML(startrow, maxrows)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
startrow |
The row identifier indicating from which row to start (see OraDynaset.RowPosition ). The default value of this argument is zero (the first row). |
maxrows |
The maximum number of rows to retrieve (if the end of the record set is reached; fewer rows may be returned). If this argument is omitted, then all rows are returned. |
Remarks
This method returns a string containing the XML document.
The formatting of the output XML can be customized through the XML properties of the OraDynaset
and OraField
objects.
Applies To
Description
Generates an XML document and writes it to a file.
Usage
oradynaset.GetXMLToFile (filename, startrow, maxrows)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
filename |
The file name that the XML is written to. Existing files by the same name are overwritten. |
startrow |
The row identifier indicating from which row to start (see OraDynaset.RowPosition ). The default value of this argument is 0 (the first row). |
maxrows |
The maximum number of rows to retrieve (if the end of the record set is reached; fewer rows may be returned). If this argument is omitted, then all rows are returned. |
Remarks
There is no return value.
The formatting of the XML output can be customized through the XML properties of the OraDynaset
and OraField
objects.
Applies To
Description
Retrieves multiple records of a dynaset object into Variant
safe array.
Usage
Array =OraDynaset.GetRows(num_rows, start, fields )
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
num_rows [optional] |
An Integer representing the number of records to retrieve. Default value is the total number of rows in the dynaset. |
start [optional] |
An Integer representing the starting position of the dynaset from which the GetRows operation begins. Default value is the current position of the dynaset. |
fields [optional] |
A Variant representing a single field name or field position, or an array of field names or array of field position numbers. The GetRows method returns only the data in these fields. |
Remarks
Use the GetRows
method to copy records from a dynaset into a two-dimensional array. The first subscript identifies the field and the second identifies the row number. The Array
variable is automatically dimensioned to the correct size when the GetRows
method returns the data.
Calling the GetRows
method does not change the current row position of the dynaset object.
Examples
The following example retrieves data using the GetRows
method.
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim row, col As Integer Dim fields() As String 'Create the OraSession Object Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle Set OraDatabase = OraSession.OpenDatabase("ExampleDb", _ "scott/tiger", 0&) Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 'The following line executes GetRows to get all records data_array = OraDynaset.GetRows() 'Now display all the data in data_array For row = 0 To UBound(data_array, 2) For col = 0 To UBound(data_array, 1) Debug.Print data_array(col, row) Next col Next row 'The following lines execute GetRows to get the data from 'the ename and empno fields starting at 5 ReDim fields(2) fields(0) = "EMPNO" fields(1) = "ENAME" 'Execute GetRows data_array = OraDynaset.GetRows(, 5, fields) 'Now display all the data in data_array For row = 0 To UBound(data_array, 2) For col = 0 To UBound(data_array, 1) Debug.Print data_array(col, row) Next col Next row
Applies To
Description
Returns the value of a particular element of the array at the specified index.
Usage
OraParamArray.Get_Value(array, index)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] array |
A String representing the name of the array. |
[in ] index |
An Integer representing the index value of the object. |
Remarks
The OraParamArray.Get_Value
method returns the value of the field as a Variant
. The value of data_value
=
oraparameter.Value
sets the contents of the parameter.
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".
The Value
argument can be an Oracle Database 10g object, such as an OraBLOB
object. For Put_Value
, a copy of the object is made at that point in time, and Get_Value
must be accessed to obtain a new object that refers to that index value. For example, if iotype
is ORATYPE_BOTH
and an OraBLOB
object obtained from a dynaset is passed in as the input value, Get_Value
needs to be called after the SQL code has been executed to obtain the newly updated output value of the ParamaterArray
object.
Similar to a dynaset, the object obtained from the ParamaterArray
Get_Value
property refers to the latest value for that ParamaterArray
index. 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 indicate an empty LOB, and for Object
, VARRAY
, and nested table data types to indicate an object whose attributes are all Null
.
This method is not available at design time and is read-only at run time.
When binding to RAW
columns (ServerType
ORATYPE_RAW_BIN
), the value should be a byte array.
Applies To
Description
Calculates the hyperbolic cosine of an OraNumber
object.
Usage
OraNumber.HypCos
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Calculates the hyperbolic sine of an OraNumber
object.
Usage
OraNumber.HypSin
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Calculates the hyperbolic tangent of an OraNumber
object.
Usage
OraNumber.HypTan
Remarks
The result of the operation is stored in an OraNumber
object. There is no return value.
Applies To
Description
Initializes an iterator to scan a collection.
Usage
OraCollection.InitIterator
Remarks
This method initializes an iterator to point to the beginning of a collection. If this method is called for same Oracle Database 10g collection instance, then this method resets the iterator to point back to the beginning of the collection. The OraCollection
object automatically reinitializes the iterator when the underlying collection changes due to a dynaset row navigation or a parameter Refresh
method.
After you call the InitIterator
method, you need to call the IterNext
method or the first element in the collection repeats an extra time.
Examples
See "Example: OraCollection Iterator".
See Also:
Applies To
Description
Checks if the OraIntervalDS
object is equal to an argument.
Usage
isEqual = OraIntervalDSObj.IsEqual value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalDS object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalDS
object is equal to the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: [+/-] Day HH:MI:SSxFF.
If value
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS
object represents.
Applies To
Description
Checks if the OraIntervalYM
object is equal to an argument.
Usage
isEqual = OraIntervalYMObj.IsEqual value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalYM object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalYM
object is equal to the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: [+/-] YEARS-MONTHS.
If value
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
Applies To
Description
Checks if an OraNumber
object is equal to an argument value.
Usage
bool = OraNumber.IsEqual value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
Returns a Boolean value: The value is True
if all values are equal; otherwise, it is False
.
Applies To
Description
Checks if the OraTimeStamp
object is equal to an argument.
Usage
isEqual = OraTimeStampObj.IsEqual value format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in] value |
A Variant of type String , Date , or OraTimeStamp to be compared. |
[in ] [optional] format |
Specifies the TIMESTAMP format string to be used to interpret value when value is of type String . If format is not specified, the value is interpreted using the Format property of the current OraTimeStamp object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStamp
object is equal to the argument; otherwise, it is False
. The IsEqual
method compares all the date-time values stored in the OraTimeStamp
object.
If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current OraTimeStamp
object.
Applies To
Description
Checks if the OraTimeStampTZ
object is equal to an argument.
Usage
isEqual = OraTimeStampTZOb.IsEqual value, format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStampTZ to be compared. |
[in ] [optional] format |
Specifies the TIMESTAMP WITH TIME ZONE format string to be used to interpret value when value is type String . If format is not specified, value is interpreted using the Format property of the current OraTimeStampTZ object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStampTZ
object is equal to the argument; otherwise, it is False
. The IsEqual
method only compares the Coordinated Universal Time (UTC) date-time values stored in the OraTimeStampTZ
object; the time zone information is ignored.
Note:
UTC was formerly known as Greenwich Mean Time.)If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current session OraTimeStampTZ
object.
If value
is of Date
type, the date-time value in Date
is interpreted as the date-time value in the time zone of the session.
Applies To
Description
Checks if the OraIntervalDS
object is greater than an argument.
Usage
isGreater = OraIntervalDSObj.IsGreater value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalDS object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalDS
object is greater than the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: Day [+/-] HH:MI:SSxFF.
If value
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS
object represents.
Applies To
Description
Checks if the OraIntervalYM
object is greater than an argument.
Usage
isGreater = OraIntervalYMObj.IsGreater value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalYM object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalYM
object is greater than the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: [+/-] YEARS-MONTHS.
If value
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
Applies To
Description
Checks if an OraNumber
object is greater than an argument value.
Usage
bool = OraNumber.IsGreater value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , OraNumber object, or a numeric value. |
Remarks
Returns a Boolean value: The value is True
if the OraNumber
object is greater than the argument; otherwise, it is False
.
Applies To
Description
Checks if the OraTimeStamp
object is greater than an argument.
Usage
isGreater = OraTimeStampObj.IsGreater value format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStamp to be compared. |
[in ] [optional] format |
Specifies the TIMESTAMP format string to be used to interpret value when value is of type String . If format is not specified, the value is interpreted using the Format property of the current OraTimeStamp object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStamp
object is greater than the argument; otherwise, it is False
. The IsGreater
method compares all the date-time values stored in the OraTimeStamp
object.
If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current OraTimeStamp
object.
Applies To
Description
Checks if the OraTimeStampTZ object
is greater than an argument.
Usage
isGreater = OraTimeStampTZObj.IsGreater value, format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStampTZ object to be compared. |
[in ] [optional] format |
Specifies the TIMESTAMP WITH TIME ZONE format string to be used to interpret a value when value is type String . If format is not specified, value is interpreted using the Format property of the current OraTimeStampTZ object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStampTZ
object is greater than the argument; otherwise, it is False
. The IsGreater
method only compares the UTC date-time values stored in the OraTimeStampTZ
object; the time zone information is ignored.
If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current OraTimeStampTZ
object.
If value
is of type Date
, the date-time value in Date
is interpreted as the date-time value in the time zone of the session.
Applies To
Description
Checks if the OraIntervalDS
object is less than an argument.
Usage
isLess = OraIntervalDSObj.IsLess value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalDS object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalDS
object is less than the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: [+/-] Day HH:MI:SSxFF.
If value
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS object
represents.
Applies To
Description
Checks if the OraIntervalYM object
is less than an argument.
Usage
isLess = OraIntervalYMObj.IsLess value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , a numeric value, or an OraIntervalYM object to be compared. |
Remarks
Returns a Boolean value: The value is True
if the OraIntervalYM
object is less than the argument; otherwise, it is False
.
If value
is a Variant
of type String
, it must be in the following format: [+/-] YEARS-MONTHS.
If value
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
Applies To
Description
Checks if an OraNumber
object is less than an argument value.
Usage
bool = OraNumber.IsLess value
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , OraNumber object, or a numeric value. |
Remarks
Returns a Boolean value: The value is True
if the OraNumber object is less than the argument; otherwise, it is False
.
Applies To
Description
Checks if the OraTimeStamp
object is less than an argument.
Usage
isLessr = OraTimeStampObj.IsLess value format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStamp . |
[in ] [optional] format |
Specifies the TIMESTAMP format string to be used to interpret value when value is of type String . If format is not specified, the value is interpreted using the Format property of the current OraTimeStamp object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStamp
is less than the argument; otherwise, it is False
. The IsLess
method compares all the date-time values stored in the OraTimeStamp
object.
If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current OraTimeStamp
object.
Applies To
Description
Checks if the OraTimeSTampTZ
object is less than an argument.
Usage
isLess = OraTimeStampTZObj.IsLess value, format
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant of type String , Date , or OraTimeStampTZ . |
[[in ] [optional] format |
Specifies the TIMESTAMP WITH TIME ZONE format string to be used to interpret value when value is type String . If format is not specified, value is interpreted using the Format property of the current OraTimeStampTZ object. |
Remarks
Returns a Boolean value: The value is True
if the OraTimeStampTZ
object is less than the argument; otherwise, it is False
. IsLess
only compares the UTC date-time values stored in the OraTimeStampTZ
object; the time zone information is ignored.
If value
is of type String
, the string format must match the format specified in the format
argument. If format
is not specified, the string format must match the Format
property of the current OraTimeStampTZ
object.
If value
is of type Date
, the date-time value in Date
is interpreted as the date-time value in the time zone of the session.
Applies To
Description
Moves the iterator to point to the next element in the collection.
Usage
OraCollection.IterNext
Remarks
Using an iterator is faster than using an index when accessing collection elements.
If the iterator is pointing to the last element of the collection before to executing this function, then calling this method makes the EOC
property return True
. Also, the iterator is not changed. Check the EOC
property when calling this method repetitively.
Call the IterNext
method after the InitIterator
method, or the first element in the collection is repeated an extra time.
Examples
See "Example: OraCollection Iterator" .
See Also:
Applies To
Description
Moves the iterator to point to the previous element in the collection.
Usage
OraCollection.IterPrev
Remarks
Using an iterator is faster than using an index when accessing collection elements.
If the iterator is pointing to the first element of the collection prior to executing this function, then calling this method makes the BOC
property return True
. Also, the iterator is not changed. Check the BOC
property when calling this method repetitively.
Examples
See "Example: OraCollection Iterator" .
See Also:
Applies To
Description
Clears the LastServerErr
property to a zero value and sets the LastServerErrText
property to Null
for the specified object.
Usage
oradatabase.LastServerErrResetorasession.LastServerErrReset
Remarks
This method allows user programs to better determine which program request generated the Oracle error.
Applies To
Description
Calculates the natural logarithm (base e) of an OraNumber
object.
Usage
OraNumber.Ln
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method raises an error if the OraNumber
object is less than or equal to zero.
Applies To
Description
Calculates the logarithm of operand
using the OraNumber
object as the base.
Usage
OraNumber.Log operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method raises an error if the OraNumber
object or operand
is less than or equal to zero.
Applies To
Description
Returns the position of the nth
occurrence of the pattern starting at the offset.
Usage
position = OraBlob.MatchPos pattern, offset, nth position = OraClob.MatchPos pattern, offset, nth position = OraBFile.MatchPos pattern, offset, nth
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] pattern |
A string for CLOB , or byte array for BLOB or BFILE that is searched for in the LOB. |
[in ] Offset |
The starting position in the LOB or BFILE for the search. |
[in ] nth |
The occurrence number. |
Remarks
This call is currently implemented by executing a PL/SQL block that uses DBMS_LOB.INSTR()
.
Applies To
Description
Gets the modulus from the division of the OraNumber
object by operand
.
Usage
OraNumber.Mod operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
If operand
is equal to zero, an error is raised.
Applies To
Description
Registers the failover notification handler of the application.
Usage
OraDatabase.MonitorForFailover FOSink, FOCtx
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] FOSink |
An IDispatch interface implementing the OnFailover method which is notified in event of a failover. |
[in ] FOCtx |
Context-specific information that the application wants passed into the OnFailover method in the event of a failover. |
Remarks
To receive failover notifications, a notification handler must be registered with the MonitorForFailover
method. The notification handler must be an automation object (a class module in Visual Basic) that implements the OnFailover
method.
The syntax of the method is:
Public Function OnFailover(Ctx As Variant, fo_type As Variant,fo_event as Variant, fo_OraDB as Variant)
Variants | Description |
---|---|
[in] Ctx |
Passed into the MonitorForFailover method by the application. Context-sensitive information that the application wants passed in event of a failover. |
[in] fo_type |
Failover type. This is the type of failover that the client has requested. The values are:
|
[in]
|
Failover event. This indicates the state of the failover. It has several possible values:
|
[in] fo_OraDB |
The OraDatabase object of the user session that is being failed over. Valid only when the fo_event variant is OO4O_FO_REAUTH . |
Examples
Failover Notification Example
See Example: Failover Notification.
See Also:
"Application Failover Notifications"Applies To
Description
Starts a monitor thread for dequeuing the messages specified.
Usage
Q.MonitorStart NotificationHandler, CallbackCtx, MsgFilterVal,MsgFilter
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] NotificationHandler |
An IDispatch interface containing the callback method (NotifyMe ) which should be notified of new messages. |
[in ] CallbackCtx |
Context-specific information that the application wants to pass to the NotifyMe method. This is passed into the NotifyMe method whenever a new message satisfying the user criteria is dequeued. |
[in ] [optional] MsgFilterVal |
A byte array containing a value for the message filter. Ignored if MsgFilter is ORAAQ_ANY . |
[in ] [optional] MsgFilter |
An Integer constant specifying the selection criteria for messages. Possible values for MsgFilter are:
|
Remarks
NotifyMe
is the callback method of the notification object. The syntax of the method is:
Public Sub NotifyMe (ByVal Ctx As Variant, ByVal Msgid As Variant)
Variants | Description |
---|---|
[in] Ctx |
Value passed into the MonitorStart method by the application. Context-sensitive information that the application wants to pass in when messages are dequeued. |
[in] Msgid |
The message ID of the newly dequeued message. The Msgid variant is null when there is an error while monitoring. |
By default, the message is passed into NotifyMe
in Remove
mode. The default dequeue options can be overridden by setting the properties of this instance (OraAQ
).
The MonitorStart
method returns ORAAQ_SUCCESS
or ORAAQ_FAIL
.
See Also:
"Monitoring Messages"Applies To
Description
Stops the monitor thread that was started earlier.
Usage
Q.MonitorStop
Remarks
Does nothing if a monitor is not running.
See Also:
"Monitoring Messages"Applies To
Description
Change the cursor position to the first, last, next, or previous row within the specified dynaset. These move methods move the cursor to the next (previous, and so on) valid row, skipping rows that have been deleted.
Usage
oradynaset.MoveFirst oradynaset.DbMoveFirst
oradynaset.MoveLast oradynaset.DbMoveLast
oradynaset.MovePrevious oradynaset.DbMovePrevious
oradynaset.MoveNext oradynaset.DbMoveNext
Remarks
The data control buttons map (from left to right or from top to bottom) to the MoveFirst
, MovePrevious
, MoveNext
, and MoveLast
methods. The BOF
and EOF
properties are never true when using the data control buttons.
When the first or last record is current, record movement does not occur if you use the MoveFirst
or MoveLast
methods, respectively. You force the query to completion if you use the MoveLast
method on a dynaset.
If you use the MovePrevious
method and the first record is current, there is no current record and BOF
is true. Using the MovePrevious
method again causes an error, although BOF
remains True
. If you use the MoveNext
method and the last record is current, there is no current record and EOF
is true. Using the MoveNext
method again causes an error, although EOF
remains true. Note that when the dynaset is created with the ORADYN_NO_MOVEFIRST
option, BOF
and EOF
are true whether the dynaset is empty or not.
When you open a dynaset, BOF
is False
and the first record is current. If a dynaset is empty, BOF
and EOF
are both true, and there is no current record.
If an Edit
or AddNew
operation is pending and you use one of the Move
methods indirectly by way of the data control, then the Update
method is invoked automatically, although, it can be stopped during the Validate
event.
If an Edit
or AddNew
operation is pending and you use one of the Move
methods directly without the data control, pending Edit
or AddNew
operations cause existing changes to be lost, although no error occurs.
Data is fetched from the database, as necessary, so performing a MoveFirst
operation followed by a MoveNext
operation incrementally builds the mirrored (cached) local set without requiring read-ahead of additional data. However, executing a MoveLast
operation requires that the entire query be evaluated and stored locally.
When a dynaset is attached to a data control, these methods first notify the Validate
event of the data control that record motion is about to occur. The Validate
handler can deny the request for motion, in which case the request is ignored. If the record pointer is successfully moved, then all custom controls attached to the data control are notified automatically of the new record position.
Examples
This example demonstrates record movement within a dynaset using the MoveFirst
, MoveNext
, MoveLast
, MovePrevious
methods. 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&) MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _ OraDynaset.Fields("ename").value 'Move to the next record and display it. OraDynaset.MoveNext MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _ OraDynaset.Fields("ename").value 'Move to the last record and display it. OraDynaset.MoveLast MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _ OraDynaset.Fields("ename").value 'Move to the previous record and display it. OraDynaset.MovePrevious MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _ OraDynaset.Fields("ename").value End Sub
Applies To
Description
Change the cursor position to the specified row within the specified dynaset.
Usage
oradynaset.MovePreviousn offset oradynaset.MoveNextn offset oradynaset.MoveRel offset oradynaset.MoveTo offset
MoveNextn Method
Moves offset records forward.
MovePreviousn Method
Moves offset records backward.
MoveRel Method
Moves offset records relative to the current row. A positive value, represented by a plus (+) sign, moves the cursor down the table, and a negative value moves the cursor up the table.
MoveTo Method
Moves directly to row number offset.
Remarks
EOF
is set when the cursor moves beyond the end of a dynaset using MoveNextn
, MoveRel
, or MoveTo
methods. BOF
is set when the cursor moves beyond the start of a dynaset using MovePreviousn
, MoveRel
, or MoveTo
methods. The MoveNextn
, MovePreviousn
, and MoveTo
methods accept offset as a positive integer only. The MoveRel
methods accepts offset as either a positive or a negative integer.
The MoveTo
rownum
always gets the same row unless the row has been deleted. If the requested row has been deleted, the MoveTo
method moves to the next valid row. The MoveNextn
, MovePreviousn
, MoveRel
, and MoveTo
methods do not take into account deleted rows, so be cautious when using these methods based on relative positions of row numbers.
Data Type
Long
Integer
Examples
This example demonstrates the use of the MovePreviousn
, MoveNextn
, MoveRel
, and MoveTo
methods. Copy and paste this code into the definition section of a form. Then, press F5.
Private Sub Form_Load() Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim OraFields As OraFields Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&) Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where empno" & _ ">=7654 and empno <= 7844 ", ORADYN_NO LANKSTRIP) Set OraFields = OraDynaset.Fields 'Move to 3rd record from the first record OraDynaset.MoveNextn 3 'Should set EOF to true MsgBox OraFields("ename").Value ' Should be display SCOTT If OraDynaset.EOF = True Then MsgBox "End of the record reached" End If 'Move back from the current record by the offset 2 OraDynaset.MovePreviousn 2 MsgBox OraFields("ename").Value ' Should be display BLAKE If OraDynaset.BOF = True Then MsgBox "Start of the record reached" End If 'Move relative in the forward direction OraDynaset.MoveRel 2 MsgBox OraFields("ename").Value ' Should be display SCOTT If OraDynaset.EOF = True Then MsgBox "End of the record reached" End If 'Move relative in the backward direction OraDynaset.MoveRel -2 MsgBox OraFields("ename").Value ' Should be display BLAKE If OraDynaset.BOF = True Then MsgBox "Start of the record reached" End If 'Move to the record position 4 in the current dynaset OraDynaset.MoveTo 4 MsgBox OraFields("ename").Value ' Should be display SCOTT End Sub
Applies To
Description
Multiplies the OraIntervalDS
object by a multiplier.
Usage
OraIntervalDSObj.Mul multiplier
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] multiplier |
A Variant for type numeric value or an OraNumber object to be used as the multiplier. |
Remarks
The result of the operation is stored in the OraIntervalDS
object, overwriting any previous value. There is no return value.
Applies To
Description
Multiplies the OraIntervalYM
object by a multiplier.
Usage
OraIntervalYMObj.Mul multiplier
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] multiplier |
A Variant for type numeric value or an OraNumber object to be used as the multiplier. |
Remarks
The result of the operation is stored in the OraIntervalYM
object, overwriting any previous value. There is no return value.
Applies To
Description
Multiplies the OraNumber
object by operand
.
Usage
OraNumber.Mul operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Negates the OraIntervalDS
object.
Usage
OraIntervalDSObj.Neg
Remarks
The result of the operation is stored in the OraIntervalDS
object, overwriting any previous value. There is no return value.
Applies To
Description
Negates the OraIntervalYM
object.
Usage
OraIntervalYMObj.Neg
Remarks
The result of the operation is stored in the OraIntervalYM
object, overwriting any previous value. There is no return value.
Applies To
Description
Negates an OraNumber
object.
Usage
OraNumber.Neg
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Establishes a connection to an Oracle database.
Usage
OraServer.Open serverAlias
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] serverAlias |
A String containing the Network alias used for connecting to the database. |
Remarks
If no arguments is supplied, this method attaches to a database that was detached previously.
See Also:
Applies To
Description
Opens a BFILE
.
Usage
OraBfile.Open
Remarks
This method should be called before accessing the BFILE
value.
Applies To
Description
Establishes a user session to the database. It creates a new OraDatabase
object using the given database name, connection string, and specified options.
Usage
Set oradatabase = orasession.OpenDatabase(database_name,connect_string, options) Set oradatabase = oraserver.OpenDatabase(connect_string, options)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
database_name |
The Oracle Network specifier used when connecting the data control to a database. |
connect_string |
The user name and password to be used when connecting to an Oracle database. |
options |
A bit flag word used to set the optional modes of the database. If options = 0 , the default mode settings apply. The following table shows the possible modes, which can be combined by adding their respective values. |
Constants
The following table lists constants and values for the options flag.
Constant | Value | Description |
---|---|---|
ORADB_DEFAULT |
&H0& |
Visual Basic Mode (Default):
Field (column) values not explicitly set are set to Nonblocking SQL functionality is not enabled. |
ORADB_ORAMODE |
&H1& |
Oracle Mode:
Lets Oracle Database set the default field (column) values when using the Note: If you use triggers, fetch the data again using the full Oracle Mode. |
ORADB_NOWAIT |
&H2& |
Lock No-Wait Mode:
Does not wait on row locks. When you use the Note: This option only applies to the |
ORADB_NO_REFETCH |
&H4& |
Oracle Mode (No Refetch):
Performs like the Oracle Mode, but does not refetch data to the local cache. This boosts performance. Note: Use the No Refetch mode only when you intend to insert rows without editing them, because database column defaults cause inconsistencies between database data and the local cache. Attempting to edit after inserting in this mode causes a |
ORADB_NONBLK |
&H8& |
Nonblocking Mode:
Turns on Nonblocking mode on SQL statement execution. Nonblocking mode affects the SQL statements processed using the Note: This feature has been deprecated. |
ORADB_ENLIST_IN_MTS |
&H10& |
Enlist in MTS Mode:
Determine whether the |
ORADB_ENLIST_FOR_ CALLLBACK |
&H20& |
Enlist For Callbacks Mode:
Turn on the event notification. This mode has to be enabled to receive Failover Notifications. |
These values can be found in the oraconst.txt
file. For creating a valid database alias, see the Oracle Net Services Administrator's Guide.
Examples of valid connect_string
arguments include:
"scott/tiger"
"system/manager"
"/"
Remarks
An OraConnection
object is created automatically and appears within the OraConnections
collection of the session. Opening a database has the effect of opening a connection but does not perform any SQL actions.
One possible connection error that could be returned is:
ORA-28001 "the password has expired"
The user can change the password using the ChangePassword
method.
Examples
This example demonstrates how to programmatically create a dynaset and all of the underlying objects. Copy and paste this code into the definition section of a form with text boxes named txtEmpNo
and txtEName
. 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&) 'Display the first record. txtEmpNo = OraDynaset.Fields("empno").value txtEName = OraDynaset.Fields("ename").value End Sub
Applies To
Description
Returns the OraField
object based on the original column name used in the SELECT
statement in the dynaset. Not available at design time and read-only at run time.
Usage
set OraField = OraFields.OriginalItem(field_index) set OraField = OraFields.OriginalItem(original_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
field_index |
Field index of the original column name. |
original_name |
Original field name specified in the SQL statement. |
Remarks
This is property is useful when a SQL statement contains 'schema.table.col'
as the Name
of the field, and retrieves the field object specific to that original name.
Examples
The following example shows the use of the OriginalItem
method. 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 Dim OraFields As OraFields 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno," & _ "dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&) 'Get the Field collection object Set OraFields = OraDynaset.Fields 'get the original field object. Returns "scott.emp.deptno" MsgBox OraField.OriginalName Set OraField = OraFields.OriginalItem(1) 'Returns "dept.deptno" MsgBox OraField.OriginalName End Sub
Applies To
Description
Returns the original column name used in the SELECT
statement in the dynaset (as opposed to the name of the field as it appears on the server returned by the Name
property). Not available at design time and read-only at run time.
Usage
field_name = Orafield.OriginalName
Remarks
The orafield.OriginalName
method returns the name of the specified OraField
object. This returns 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. It enables duplicate column names to be referenced. (Duplicate column names can be avoided by using aliases in the SQL statement.)
Examples
The following example shows the use of the OriginalName
property. 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 Dim OraFields As OraFields 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno," & _ "dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&) Set OraFields = OraDynaset.Fields 'Returns "DEPTNO" MsgBox OraFields(0).Name 'Returns "scott.emp.deptno" MsgBox OraFields(0).OriginalName 'Returns "dept.deptno" MsgBox OraFields(1).OriginalName End Sub
Applies To
Description
Raises the OraNumber
object to the power of the operand.
Usage
OraNumber.Power operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Inserts values into the table parameter.
Usage
OraParamArray.Put_Value(value, index)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] value |
A Variant representing the value to insert. |
[in ] index |
An Integer representing the index value of the object. |
Remarks
This method should be used to insert a value before accessing a row in a table. A row does not contain a valid value until a row is assigned a value. Any reference to an unassigned row in the table raises an OLE
Automation
error
.
The value
argument can be an Oracle Database 10g object, such as an OraBLOB
. For Put_Value
, a copy of the object is made at that point in time, and Get_Value
must be accessed to obtain a new object that refers to that index value. For example, if iotype
is ORATYPE_BOTH
and an OraBLOB
obtained from a dynaset is passed in as the input value, Get_Value
needs to be called after the SQL has been executed to obtain the newly updated output value of the ParamaterArray
.
Similar to a dynaset, the object obtained from ParamaterArray
Get_Value
method always refers to the latest value for that ParamaterArray
index. 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 indicate an empty LOB, and for OBJECT
, VARRAY
and NESTED
TABLE
to indicate an object whose attributes are all Null
.
When binding to RAW
columns (ServerType
ORATYPE_RAW_BIN
) value should be a byte array.
Applies To
Description
Reads into a buffer a specified portion of a BLOB
, CLOB
, or BFILE
value. Returns the total amount of data read.
Usage
amount_read = OraBlob.Read buffer, chunksize amount_read = OraClob.Read buffer, chunksize amount_read = OraBfile.Read buffer, chunksize
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[out ] buffer |
Variant of type character array for OraCLOB , Variant of type byte array for OraBLOB , or OraBFILE from which the piece is read. |
[in ] [optional] chunksize |
An Integer specifying the amount to be read. Default value is the size of the LOB. In bytes for OraBLOB or OraBFILE ; characters for OraCLOB . |
[out ] amount_read |
An Integer representing the total amount of data read. In bytes for OraBLOB or OraBFILE ; characters for OraCLOB . |
Remarks
Reads the LOB or BFILE
data from the offset specified by the Offset
property. For multiple piece read operation, the PollingAmount
property must be set to the value of the total amount of data to be read, and the Status
property must be checked for the success of each piece operation.
Note:
When reading a portion of a LOB, it is recommended that you set thePollingAmount
property, rather than using the chunksize
parameter. This avoids the possibility of raising an error if the entire LOB is not read before to executing another LOB method.Examples
Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .
Example: Multiple-Piece Read of a LOB
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim PartImage As OraBlob Dim chunksize As Long Dim AmountRead As Long Dim buffer As Variant Dim buf As String 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&) 'Create the OraDynaset Object. Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&) 'Get OraBlob from OraDynaset Set PartImage = OraDynaset.Fields("part_image").Value 'Set Offset and PollingAmount property for piecewise Read operation PartImage.offset = 1 PartImage.PollingAmount = PartImage.Size chunksize = 50000 'Get a free file number FNum = FreeFile 'Open the file Open "image.dat" For Binary As #FNum 'Do the first read on PartImage, buffer must be a variant AmountRead = PartImage.Read(buffer, chunksize) 'put will not allow Variant type buf = buffer Put #FNum, , buf ' Check for the Status property for polling read operation While PartImage.Status = ORALOB_NEED_DATA AmountRead = PartImage.Read(buffer, chunksize) buf = buffer Put #FNum, , buf Wend Close FNum
Example: Single-Piece Read of a LOB
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartDesc As OraClob Dim AmountRead As Long Dim buffer As Variant Dim buf As String 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Add PartDesc as an Output parameter and set its initial value. OraDatabase.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT OraDatabase.Parameters("PartDesc").ServerType = ORATYPE_CLOB 'Execute the statement returning 'PartDesc' OraDatabase.ExecuteSQL ("BEGIN select part_desc into :PARTDESC from" & _ "part where part_id = 1 for update NOWAIT; END;") 'Get 'PartDesc' from Parameters collection Set PartDesc = OraDatabase.Parameters("PartDesc").Value 'Get a free file number FNum = FreeFile 'Open the file. Open "Desc.Dat" For Binary As #FNum 'Read entire CLOB value, buffer must be a Variant AmountRead = PartDesc.Read(buffer) 'put will not allow Variant type buf = buffer Put #FNum, , buf Close FNum
Applies To
Description
Returns a String
containing the bytes of all or a portion of a LONG
or LONG
RAW
field.
Usage
data_string = orafield.ReadChunk(offset, numbytes, bytesread)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
offset |
The number of bytes in the field to skip before copying data. |
numbytes |
The number of bytes to copy. |
bytesread |
The number of bytes read. |
Remarks
The ReadChunk
method behaves like the GetChunk
method, but it returns the actual number of bytes read in the bytesread
argument.
Applies To
Description
Forces an immediate update of the dynaset given the current Connect
, DatabaseName
, and SQL properties.
Forces an immediate update of the dynaset by reexecuting the SQL statement in the SQL statement object.
Usage
oradynaset.Refresh oradynaset.DbRefresh orasqlstmt.Refresh orasqlstmt.DbRefresh
Remarks
This method cancels all edit operations (Edit
and AddNew
methods), executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Any dynaset objects created before issuing the Refresh
method, including bookmarks, record counts, and field collections, are considered invalid. The OraConnection
and OraSession
objects associated with the previous dynaset remain unchanged.
Performing a refresh operation with this method can be more efficient than refreshing with a data control. This method also lets you execute a modified SQL statement without creating a new dynaset or OraSQLStmt
object.
The preferred refresh methods when changing parameter values are oradynaset.Refresh
or orasqlstmt.Refresh
, because required database operations are minimized (SQL parsing, binding, and so on). This can improve performance when only parameter values have changed.
If you call the Refresh
method after assigning an invalid SQL statement to the SQL property of a dynaset or SQL statement object, these objects remain valid. However, a dynaset in this state does not permit any row or field operations. Bound controls also exhibit unusual behaviors similar to those that occur when the standard Visual Basic data control RecordSource
is set to an invalid SQL statement at run time and then refreshed.
You can regain the normal dynaset and SQL statement operations by refreshing the object with a valid SQL statement. The Refresh
method treats Null
or empty SQL statements as invalid.
Examples
Refresh Method Example (OraDynaset)
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 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
Refresh Method Example (OraSQLStmt)
This example demonstrates the use of parameters, the Refresh
method, and the SQL property for the . object. 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 OraSqlStmt As OraSQLStmt '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&) OraDatabase.Parameters.Add "EMPNO", 7369, 1 OraDatabase.Parameters("EMPNO").ServerType = 2 'ORATYPE_NUMBER OraDatabase.Parameters.Add "ENAME", 0, 2 OraDatabase.Parameters("ENAME").ServerType = 1 'ORATYPE_VARCHAR2 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO," & _ ":ENAME); end;", 0&) 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Should display SMITH MsgBox OraDatabase.Parameters("ENAME").Value 'Change the value of the empno parameter. OraDatabase.Parameters("EMPNO").Value = 7499 'Refresh the dynaset. OraSqlStmt.Refresh 'Should display ALLEN MsgBox OraDatabase.Parameters("ENAME").Value 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Remove the parameter. OraDatabase.Parameters.Remove ("job") End Sub
Applies To
Description
Refreshes the referenceable object from the most current database snapshot.
Usage
OraRef.Refresh
Applies To
Description
Activates the subscription.
Usage
orasubscription.Register
Remarks
When the specified database event is fired, the NotifyDBevents
method of the dbevent
handler that was passed in while creating this subscription is invoked.
Examples
See "Example: Registering an Application for Notification of Database Events" for a complete example.
Applies To
Description
Removes a parameter from the OraParameters
collection.
Usage
oraparameters.Remove(member_name)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
member_name |
A Variant specifying an integer subscript from 0 to Count 1 , or the parameter name. |
Remarks
Instead of repeatedly removing and adding unwanted parameters, use the AutoBindDisable
and AutoBindEnable
methods.
For an OraParameter
of type ORATYPE_CURSOR
, this method destroys the dynaset object associated with the cursor, and clears the local cache temporary files.
Examples
Applies To
Description
Removes a subscription from the OraSubscriptions
collection.
Usage
orasubscriptions.Remove(member)
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
member |
A Variant specifying an integer subscript from 0 to Count , or the subscription name. |
Remarks
This method unregisters (removes) the subscription if it is active, and destroys the subscription associated with it.
Applies To
Description
Removes the OraDatabase
object from the pool.
Usage
OraDatabase.RemoveFromPool
Remarks
This method applies only to those OraDatabase
objects that are retrieved from the pool using the GetDatabaseFromPool
method.
No exceptions or errors are raised if the OraDatabase
object is not a member the pool.
This method is useful for removing OraDatabase
objects from the pool whose connections are no longer valid.
Applies To
Description
Unconditionally rolls back all transactions and clears the transaction mode initiated by BeginTrans
method.
Usage
oraconnection.ResetTransorasession.ResetTrans
Remarks
This method does not generate events or produce errors. Because the ResetTrans
method does not generate events, you cannot cancel the ResetTrans
method in a Validate
event, as you can with a rollback or commit operation.
Note: If an OraDatabase
object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.
Examples
This example demonstrates the use of the BeginTrans
and ResetTrans
methods to group a set of dynaset edits into a single transaction. 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 * from emp", 0&) 'Start Transaction processing. OraDynaset.Session.BeginTrans 'Traverse until EOF is reached, setting each employee's salary to zero. Do Until OraDynaset.EOF OraDynaset.Edit OraDynaset.Fields("sal").value = 0 OraDynaset.Update OraDynaset.MoveNext Loop MsgBox "All salaries set to ZERO." 'Currently, the changes have NOT been committed to the database. 'End Transaction processing. 'Using ResetTrans means the rollback cannot be canceled in the Validate event. OraDynaset.Session.ResetTrans MsgBox "Salary changes rolled back." End Sub
Applies To
Description
Ends the current transaction and rolls back all pending changes to the database.
Usage
oraconnection.Rollback orasession.Rollback oradatabase.Rollback
Remarks
When this method is invoked, all OraDynaset
objects that share the specified session or connection are given the opportunity to cancel the rollback request. If they do not cancel the request, they are advised when the rollback succeeds.
This feature is useful primarily for dynasets that are created as part of an Oracle Data Control operation. For these dynasets, the Validate
event is sent to allow them to cancel the rollback request.
OraConnection
and OraDatabase
:
The Rollback
method rolls back all pending transactions within the specified connection. This method has no effect if a transaction has not begun. When a session-wide transaction is in progress, you can use this call to prematurely roll back the transactions for the specified connection.
OraSession
:
The Rollback
method rolls back all pending transactions within the specified session. The Rollback
method is valid only when a transaction has been started. If a transaction has not been started, the use of the Rollback
method results in an error.
Note:
If anOraDatabase
object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.Examples
This example demonstrates the use of the BeginTrans
and Rollback
methods to group a set of dynaset edits into a single transaction. 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 * from emp", 0&) 'Start Transaction processing. OraDynaset.Session.BeginTrans 'Traverse until EOF is reached, setting each employee's salary to zero. Do Until OraDynaset.EOF OraDynaset.Edit OraDynaset.Fields("sal").value = 0 OraDynaset.Update OraDynaset.MoveNext Loop MsgBox "All salaries set to ZERO." 'Currently, the changes have NOT been committed to the database. 'End Transaction processing. OraDynaset.Session.Rollback MsgBox "Salary changes rolled back." End Sub
Applies To
Description
Rounds the OraNumber
object to the specified decimal place.
Usage
OraNumber.Power decplaces
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] decplaces |
An Integer specifying the number of digits to the right of the decimal point from which to round. Negative values are allowed and signify digits to the left of the decimal point. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Sets an OraNumber
object to Pi.
Usage
OraNumber.SetPi
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Calculates the sine of an OraNumber
object given in radians.
Usage
OraNumber.Sin
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Calculates the square root of an OraNumber
object.
Usage
OraNumber.Sqrt
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
This method returns an error if the OraNumber
object is less than zero.
Applies To
Description
Subtracts an argument from the OraIntervalDS
object.
Usage
OraIntervalDSObj.Sub operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalDS , object to be subtracted. |
Remarks
The result of the operation is stored in the OraIntervalDS
object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in the following format: [+/-] Day HH:MI:SSxFF.
If operand
is a numeric value, the value provided should represent the total number of days that the constructed OraIntervalDS
object represents.
Applies To
Description
Subtracts an argument from the OraIntervalYM
object.
Usage
OraIntervalYMObj.Sub operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , a numeric value, or an OraIntervalYM object to be subtracted. |
Remarks
The result of the operation is stored in the OraIntervalYM
object, overwriting any previous value. There is no return value.
If operand
is a Variant
of type String
, it must be in the following format: [+/-] YEARS-MONTHS.
If operand
is a numeric value, the value provided should represent the total number of years that the constructed OraIntervalYM
object represents.
Applies To
Description
Subtracts a numeric argument from the OraNumber
object.
Usage
OraNumber.Sub operand
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] operand |
A Variant of type String , type OraNumber , or a numeric value. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Calculates the tangent of an OraNumber
object given in radians.
Usage
OraNumber.Tan
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Returns a copy of the Date
type from an OraTimeStamp
or OraTimeStampTZ
object.
Usage
Set date = OraTimeStampObj.ToDate Set date = OraTimeStampTZObj.ToDate
Remarks
This method returns the datetime values in the Date
data type. As a result, the date-time values can be adjusted if they fall outside the range allowed by a VB date.
For an OraTimeStamp
object:
Returns a new Date
object with the same date-time values as the current OraTimeStamp
object, but the nanosecond portion is truncated.
For an OraTimeStampTZ
object:
Returns a new Date
object with the same date-time values as the current OraTimeStampTZ
object, but the nanosecond portion and time zone portion are truncated.
Examples
Using the OraTimeStamp Object
Dim OraTimeStamp As OraTimeStamp ... 'Create OraTimeStamp using a string Set OraTimeStamp = OraSession.CreateOraTimeStamp("1999-APR-29 12:10:23.444 AM", _ "YYYY-MON-DD HH:MI:SS.FF AM") ' returns a Date type with date value set to "1999-APR-29 12:10:23 AM" ' note that the fractional part is dropped Set date = OraTimeStamp.ToDate
Using the OraTimeStampTZ Object
Dim OraTimeStampTZ As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2000-12-28" & _ "12:10:23.444 -07:00", "YYYY-MM-DD HH:MI:SS.FF TZH:TZM") 'returns a Date type with date value set to "2000-12-28 12:10:23" 'note that Time Zone and nanosecond portions are dropped Set date = OraTimeStampTZ.ToDate
Applies To
Description
Returns an OraNumber
object containing a value that represents the total number of days that the OraIntervalDS
object specifies.
Usage
Set OraNumberObj = OraIntervalDSObj.ToOraNumber
Applies To
Description
Returns a copy of the OraTimeStamp
object that has the date-time value in the specified time zone of the current OraTimeStampTZ
object.
Returns a copy of the OraTimeStamp
object from an OraTimeStampTZ
object.
Usage
Set OraTimeStampObj = OraTimeStampTZObj.ToOraTimeStamp
Remarks
Returns a new OraTimeStamp
object that has the date-time values in the specified time zone of the current OraTimeStampTZ
object.
Examples
Dim OraTimeStampTZ As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2000-12-28" & _ "12:10:23.444 -07:00", "YYYY-MM-DD HH:MI:SS.FF TZH:TZM") 'returns a new OraTimeStamp object with date value equal to ' "2000-12-28 12:10:23.444" 'note that Time Zone portion is dropped Set OraTimeStamp = OraTimeStampTZ.ToOraTimeStamp
Applies To
Description
Returns a copy of the OraTimeStamp
object that has the date-time value normalized to the session time zone of the current OraTimeStampTZ
object.
Usage
Set OraTimeStampObj = OraTimeStampTZObj.ToOraTimeStampLTZ
Remarks
Returns a new OraTimeStamp
object that has the date-time values normalized to the session time zone of the current OraTimeStampTZ
object.
Examples
Dim OraTimeStampTZ As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2003-APR-29" & _ "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM") 'Assuming that the Session Time Zone is "-08:00" 'returns a new OraTimeStamp object with date value normalized to 'session Time Zone, "2003-APR-29 11:00:00" Set OraTimeStamp = OraTimeStampTZ.ToOraTimeStampLTZ ...
Applies To
Description
Returns a copy of the OraTimeStampTZ
object from an OraTimeStamp
object.
Usage
Set OraTimeStampTZObj = OraTimeStampObj.ToOraTimeStampTZ
Remarks
Returns a new OraTimeStampTZ
object with the same date-time values as the current OraTimeStamp
object. The time zone information in the returned OraTimeStampTZ
object is set to the session time zone.
Examples
Dim OraTimeStamp As OraTimeStamp ... 'Create OraTimeStamp using a string Set OraTimeStamp = OraSession.CreateOraTimeStamp("1999-APR-29" & _ "12:10:23.444 AM", "YYYY-MON-DD HH:MI:SS.FF AM") ' assuming that the session Time Zone is "-07:00" returns a new ' OraTimeStampTZ object with date value equal to "1999-APR-29 12:10:23 -07:00" Set OraTimeStampTZ = OraTimeStamp.ToOraTimeStampTZ
Applies To
Description
Returns a copy of the OraTimeStampTZ
object that has the date-time value normalized to Coordinated Universal Time (UTC) of the current OraTimeStampTZ
object.
Usage
Set OraTimeStampTZObj1 = OraTimeStampTZObj.ToUniversalTime
Remarks
Returns a new OraTimeStampTZ
object that has the date-time values normalized to the UTC of the current OraTimeStampTZ
object.
Note:
UTC was formerly known as Greenwich Mean Time.Examples
Dim OraTimeStampTZ As OraTimeStampTZ Dim OraTimeStampTZ_UTC As OraTimeStampTZ ... 'Create OraTimeStampTZ using a string Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2003-APR-29 " & _ "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM") 'returns a new OraTimeStampTZ object with date value normalized to 'UTC time, "2003-APR-29 19:00:00 00:00" Set OraTimeStampTZ_UTC = OraTimeStampTZ.ToUniversalTime ...
Applies To
Description
Trims a given number of elements from the end of the collection.
Usage
OraCollection.Trim size
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] size |
An Integer specifying the number of elements to trim. |
Remarks
The elements are removed from the end of the collection. An error is returned if the size is greater than the current size of the collection.
Examples
The following example illustrates the Trim
method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .
Example: Trim Method for the OraCollection Object
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim OraDynaset as OraDynaset Dim EnameList as OraCollection 'create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create a dynaset object from department set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&) 'retrieve a Enames column from Department. 'Here Value property of OraField object returns EnameList OraCollection set EnameList = OraDynaset.Fields("Enames").Value 'display the size of the collection msgbox EnameList.Size 'Trim the EnameList collection by one. Before that row level 'lock should be obtained OraDynaset.Edit EnameList.Trim 1 OraDynaset.Update 'display the new size of the collection msgbox EnameList.Size
Applies To
Description
Trims or truncates the LOB value to shorter length.
Usage
OraBlob.Trim NewLen OraClob.Trim NewLen
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] NewLen |
An Integer specifying the new length of the LOB value; must be less than or equal to the current length. |
Remarks
Either a row-level lock or object-level lock should be obtained before calling this method.
Note:
When manipulating LOBs using LOB methods, such as theWrite
and CopyFromFile
, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the Trim
(OraLOB) method to shrink the LOB object to the size of the new data.Applies To
Description
Truncates an Oracle number at a specified decimal place.
Usage
OraNumber.Trunc decplaces
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
[in ] decplaces |
An Integer specifying the number of digits to the right of the decimal point from which to truncate. Negative values are allowed and signify digits to the left of the decimal point. |
Remarks
The result of the operation is stored in the OraNumber
object. There is no return value.
Applies To
Description
Unregisters this subscription, which turns off notifications on the specific database event.
Usage
orasubscription.UnRegister
Remarks
Unregistering a subscription ensures that the user does not receive notifications related to that subscription or database event in the future. If the user wants to resume notification, then the only option is to re-register the subscription.
Examples
Registering an Application for Notification of Database Events Example
See "Example: Registering an Application for Notification of Database Events".
Applies To
Description
Saves the copy buffer to the specified dynaset.
Usage
oradynaset.Update oradynaset.DbUpdate
Remarks
The Update
method completes an AddNew
or Edit
operation and immediately commits changes to the database unless a BeginTrans
operation is pending for the session.
Once the Update
method is called on a given row in a dynaset in a global transaction (that is, a BeginTrans
operation is issued), locks remain on the selected rows until a CommitTrans
or Rollback
method is called.
The mirrored data image is also updated so that the query does not have to be reevaluated to continue browsing and updating data. The method used for updating the mirror image is subject to the options flag that was passed to the OpenDatabase
method that created the OraDatabase
object of this dynaset.
If this dynaset is attached to a data control, then the Validate
event of the data control code may optionally cancel the update request. If the update completes, then all bound controls associated with the dynaset are notified of the update so they can reflect the data changes automatically.
Examples
This example demonstrates the use of AddNew
and Update
methods to add a new record to a dynaset. 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 * from emp", 0&) 'Begin an AddNew. OraDynaset.AddNew 'Set the field(column) values. OraDynaset.Fields("EMPNO").Value = "1000" OraDynaset.Fields("ENAME").Value = "WILSON" OraDynaset.Fields("JOB").Value = "SALESMAN" OraDynaset.Fields("MGR").Value = "7698" OraDynaset.Fields("HIREDATE").Value = "19-SEP-92" OraDynaset.Fields("SAL").Value = 2000 OraDynaset.Fields("COMM").Value = 500 OraDynaset.Fields("DEPTNO").Value = 30 'End the AddNew and Update the dynaset. OraDynaset.Update End Sub
Applies To
Description
Flushes the modified referenceable object to the database.
Usage
OraRef.Update
Remarks
The Update
method completes the Edit
operation and commits the changes to the database unless a BeginTrans
operation is pending for the session.
Examples
The following example updates the attributes of the PERSON
referenceable object in the database. 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".
Updating Attribute Values: Dynaset Example
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 'locks the Person object in the server for modifying its attributes Person.Edit Person.Name = "Eric" Person.Age = 35 'Update method flushes the modified referenceable object in the server Person.Update
Updating Attribute Values: Parameter Example
Dim OraSession as OraSession Dim OraDatabase as OraDatabase Dim Person as OraRef 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'create an OraParameter object represent Address object bind Variable OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSON" 'execute the sql statement which selects person from the customers table OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _ "where account = 10; END;") 'get the Person object from OraParameter set Person = OraDatabase.Parameters("PERSON").Value 'locks the Person object in the server for modifying its attributes Person.Edit Person.Name = "Eric" Person.Age = 35 'Update method flushes the modified referenceable object in the server Person.Update
Applies To
Description
Writes a buffer into the BLOB
or CLOB
value of this object and returns the total amount of the data written.
Usage
amount_written = OraBlob.Write buffer, chunksize, piece amount_written = OraClob.Write buffer, chunksize, piece
Arguments
The arguments for the method are:
Arguments | Description |
---|---|
in ] buffer |
The character array for an OraCLOB object or byte array for the OraBLOB object from which the piece is written. |
[in ] [optional] chunksize |
An Integer specifying the length of the buffer, in characters for an OraCLOB object and bytes for an OraBLOB or OraBFILE object. Default value is the size of the buffer argument. |
[in ] [optional] piece |
An Integer specifying which piece of the buffer is being written. Possible values include:
|
[out ] amount_written |
An Integer representing the amount written, in characters for an OraCLOB object and bytes for an OraBLOB or OraBFILE object. |
Remarks
Obtain either a row-level lock or object-level lock before calling the Write
method. This method writes the BLOB
or CLOB
data from the offset specified by the Offset
property. For a multiple-piece write operation, the PollingAmount
property can be set to the value of the total amount of data to be written, and the Status
property must be checked for the success of each piece operation. If the total amount is not known, then the PollingAmount
property can be set to 0
and polling still occurs as long as the piece type is not OraLob_piece
.
For the last piece, set the piece argument to ORALOB_LAST_PIECE
. You must write the polling amount in bytes or characters. It is not possible to terminate the Write
operation early if the PollingAmount
property is not zero.
When the OraLOB Pollingamount
= 0
but the piece type on OraLOB Write
is not ORALOB_ONE_PIECE
, polling still occurs. Polling completes when ORALOB_LAST_PIECE
is sent as an argument to a call to the Write
method. This is useful when calling the OraCLOB.Write
method in a variable-width character set, when counting the total amount of characters ahead of time may be costly.
Note:
When manipulating LOBs using LOB methods, such as theWrite
and CopyFromFile
, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the Trim
(OraLOB) method to shrink the LOB object to the size of the new data.Examples
Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .
Multiple-Piece Write of a LOB Example
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset Dim PartDesc As OraClob Dim buffer As String Dim chunksize As Long Dim amount_written As Long 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Create the OraDynaset Object Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&) Set PartDesc = OraDynaset.Fields("part_desc").Value chunksize = 32000 'Re adjust the buffer size buffer = String$(chunksize, 32) FNum = FreeFile 'Open the file. Open "partdesc.dat" For Binary As #FNum 'set the offset and PollingAmount properties for piece wise 'Write operation PartDesc.offset = 1 PartDesc.PollingAmount = LOF(FNum) remainder = LOF(FNum) 'Lock the row for write operation OraDynaset.Edit Get #FNum, , buffer 'Do first write operation amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE) While PartDesc.Status = ORALOB_NEED_DATA remainder = remainder - chunksize If remainder < chunksize Then piecetype = ORALOB_LAST_PIECE chunksize = remainder Else piecetype = ORALOB_NEXT_PIECE End If Get #FNum, , buffer amount_written = PartDesc.Write(buffer, chunksize, piecetype) Wend Close FNum 'call Update method to commit the transaction OraDynaset.Update
Single-Piece Write of a LOB Example
Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim PartImage As OraBlob Dim buffer() As Byte 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 'Add PartDesc as an Output parameter and set its initial value. OraDatabase.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT OraDatabase.Parameters("PartImage").ServerType = ORATYPE_BLOB 'Begin the transaction OraSession.BeginTrans 'Execute the statement returning 'PartDesc' OraDatabase.ExecuteSQL ("BEGIN select part_image into :PARTIMAGE" & _ "from part where part_id = 1 for update NOWAIT; END;") 'Get 'PartDesc' from Parameters collection Set PartImage = OraDatabase.Parameters("PartImage").Value 'Get a free file number FNum = FreeFile 'Open the file. Open "PartImage.Dat" For Binary As #FNum 'Re adjust the buffer size to hold entire file data ReDim buffer(LOF(FNum)) Get #FNum, , buffer 'Do one write operation amount_written = PartImage.Write(buffer) Close FNum MsgBox "Amount written to the LOB data is " & amount_written 'Ends the transaction OraSession.CommitTrans