10 Server Methods

Abs Method

Applies To

OraNumber Object

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.

Add Method

Applies To

OraParameters Collection

Description

Adds a parameter to the OraParameters collection.

Usage

oraparameters.Add Name, Value, IOType, ServerType, ObjectName 

Arguments

The arguments for the method are:

IOType Settings

The IOType settings are:

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 

Add (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Adds an argument to the OraIntervalDS object.

Usage

OraIntervalDS.Add operand

Arguments

The arguments for the method are:

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" 

Add (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Adds an argument to the OraIntervalYM object.

Usage

OraIntervalYMObj.Add operand

Arguments

The arguments for the method are:

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" 

Add (OraNumber) Method

Applies To

OraNumber Object

Description

Adds a numeric argument to the OraNumber object.

Usage

OraNumber.Add operand 

Arguments

The arguments for the method are:

Remarks

The result of the operation is stored in an OraNumber object. There is no return value.

Add (OraSubscriptions Collection) Method

Applies To

OraSubscriptions Collection

Description

Adds a subscription to the OraSubscriptions collection.

Usage

orasubscriptions.Add Name, DbeventsHdl, Ctx 

Arguments

The arguments for the method are:

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:

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

AddIntervalDS Method

Applies To

OraTimeStamp Object

OraTimeStampTZ Object

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:

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

AddIntervalYM Method

Applies To

OraTimeStamp Object

OraTimeStampTZ Object

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:

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

AddNew Method

Applies To

OraDynaset Object

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.

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

AddTable Method

Applies To

OraParameters Collection

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:

IO Type Settings

The IOType settings are:

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.

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

Append (OraCollection) Method

Applies To

OraCollection Object

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:

Remarks

If an OraCollection represents a collection of Object types or Refs, 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

Append (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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:

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.

AppendChunk Method

Applies To

OraField Object

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:

Remarks

The AppendChunk method allows the manipulation of data fields that are larger than 64 KB.

Examples

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

AppendChunkByte Method

Applies To

OraField Object

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:

Remarks

The AppendChunkByte method allows the manipulation of data fields that are larger than 64 KB.

Examples

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

AQAgent (OraAQMsg) Method

Applies To

OraAQMsg Object

Description

Creates an instance of the OraAQAgent for the specified consumer and adds it to the OraAQAgents list of the message.

Usage

Set agent = qMsg.AQAgent(name)

Arguments

The arguments for the method are:

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.

AQMsg (OraAQ) Method

Applies To

OraAQ Object

Description

Creates an OraAQMsg for the specified options.

Usage

Set qMsg = Q.AQMsg(msgtype, typename, schema)

Arguments

The arguments for the method are:

Remarks

The method could be used as follows:

set QMsg = Q.AQMsg(ORATYPE_OBJECT,"MESSAGE_TYPE","SCOTT") 
set QMsg = Q.AQMsg

ArcCos (OraNumber) Method

Applies To

OraNumber Object

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.

ArcSin (OraNumber) Method

Applies To

OraNumber Object

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.

ArcTan (OraNumber) Method

Applies To

OraNumber Object

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.

ArcTan2 (OraNumber) Method

Applies To

OraNumber Object

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:

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.

Attribute (OraMetaData) Method

Applies To

OraMetaData Object

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:

Remarks

None.

AutoBindDisable Method

Applies To

OraParameter Object

OraParamArray Object

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

AutoBindEnable Method

Applies To

OraParameter Object

OraParamArray Object

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

BeginTrans Method

Applies To

OraConnection Object

OraDatabase Object

OraSession Object

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.

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

Cancel Method

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.

CancelEdit (OraRef) Method

Applies To

OraRef Object

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.

Ceil (OraNumber) Method

Applies To

OraNumber Object

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.

ChangePassword (OraServer) Method

Applies To

OraServer Object

Description

Changes the password for a given user.

Usage

OraServer.ChangePassword user_name, current_password, new_password 

Arguments

The arguments for the method are:

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

ChangePassword (OraSession) Method

Applies To

OraSession Object

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:

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 

Clone Method

Applies To

OraDynaset Object

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.

Clone (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

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:

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.

Clone (OraCollection) Method

Applies To

OraCollection Object

Description

Returns the clone of an OraCollection object.

Usage

set OraCollection1 = OraCollection.Clone

Arguments

The arguments for the method are:

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.

Clone (OraIntervalDS) Method

Applies To

OraIntervalDS Object

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.

Clone (OraIntervalYM) Method

Applies To

OraIntervalYM Object

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.

Clone (OraNumber) Method

Applies To

OraNumber Object

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.

Clone (OraObject/Ref) Method

Applies To

OraObject Object

OraRef Object

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

Clone (OraTimeStamp) Method

Applies To

OraTimeStamp Object

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.

Clone (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

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.

Close Method

Applies To

OraDatabase Object

OraDynaset Object

OraSQLStmt Object

OraServer Object

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.

Close (OraBFILE) Method

Applies To

OraBFILE Object

Description

Closes an opened BFILE data type.

Usage

OraBfile = OraBfile.Close

Arguments

The arguments for the method are:

Remarks

This method only applies to BFILEs, not LOBs.

CloseAll (OraBFILE) Method

Applies To

OraBFILE Object

Description

This method closes all open OraBFILE objects on this connection.

Usage

OraBfile.CloseAll

CommitTrans Method

Applies To

OraConnection Object

OraDatabase Object

OraSession Object

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

Compare (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

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:

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().

ConnectSession Method

Applies To

OraSession Object

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:

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

CopyToClipboard Method

Applies To

OraDynaset Object

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:

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

Copy (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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:

Remarks

Obtain either a row-level lock or object-level lock before calling this method.

CopyFromFile (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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:

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.

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 

CopyFromBFILE (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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:

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.

CopyToFile (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

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:

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" 

Cos (OraNumber) Method

Applies To

OraNumber Object

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.

CreateAQ Method

Applies To

OraDatabase Object

Description

Creates an instance of the OraAQ object.

Usage

Set OraAq = OraDatabase.CreateAQ(Qname)

Arguments

The arguments for the method are:

Remarks

None.

CreateCustomDynaset Method

Applies To

OraDatabase Object

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:

Constants

The following table lists constants and values for the options flag.

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

CreateDatabasePool Method

Applies To

OraSession Object

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:

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.

CreateDynaset Method

Applies To

OraDatabase Object

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:

Constants

The following table lists constants and values for the options flag.

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

CreateIterator Method

Applies To

OraCollection Object

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

CreateNamedSession Method

Applies To

OraSession Object

Description

Creates and returns a new named OraSession object.

Usage

orasession = orasession.CreateNamedSession(session_name)

Arguments

The arguments for the method are:

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

CreateOraIntervalDS Method

Applies To

OraSession Object

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:

Return Values

OraIntervalDS Object

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) 

CreateOraIntervalYM Method

Applies To

OraSession Object

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:

Return Values

OraIntervalYM Object

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) 

CreateOraNumber Method

Applies To

OraSession Object

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:

Return Value

OraNumber Object

Remarks

For more information about format strings, see the format property on the OraNumber object.

CreateOraObject (OraDatabase) Method

Applies To

OraDatabase Object

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:

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

CreateOraTimeStamp Method

Applies To

OraSession Object

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:

Return Values

OraTimeStamp Object

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) 

CreateOraTimeStampTZ Method

Applies To

OraSession Object

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:

Return Values

OraTimeStampTZ Object

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) 

CreatePLSQLCustomDynaset Method

Applies To

OraDatabase Object

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:

Constants

The options flag values are:

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.

CreatePLSQLDynaset Method

Applies To

OraDatabase Object

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

Constants

The options flag values are:

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

CreateSession Method

Applies To

OraClient Object

Description

Creates a new named OraSession object.

Usage

orasession = oraclient.CreateSession(session_name)

Arguments

The arguments for the method are:

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

CreateSQL Method

Applies To

OraDatabase Object

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:

Constants

The options flag values are:

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.

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

CreateTempBLOB/CLOB Method

Applies To

OraDatabase Object

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:

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 

Delete Method

Applies To

OraDynaset Object

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.

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

Delete (OraCollection) Method

Applies To

OraCollection Object

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:

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

Delete (OraRef) Method

Applies To

OraRef Object

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

DeleteIterator Method

Applies To

OraCollection Object

Description

Deletes a collection iterator.

Usage

OraCollection.DeleteIterator

Remarks

None.

Examples

See "Example: OraCollection Iterator"

Dequeue (OraAQ) Method

Applies To

OraAQ Object

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

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

Describe Method

Applies To

OraDatabase Object

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:

Remarks

The following schema object types can be described:

• Tables

• Views

• Procedures

• Functions

• Packages

• Sequences

• Collections (VARRAYs 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 

DestroyDatabasePool Method

Applies To

OraSession Object

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.

DisableBuffering (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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.

Div (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Divides the OraIntervalDS object by a divisor.

Usage

OraIntervalDSObj.Div divisor

Arguments

The arguments for the method are:

Remarks

The result of the operation is stored in the OraIntervalDS object, overwriting any previous value. There is no return value.

Div (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Divides the OraIntervalYM object by a divisor.

Usage

OraIntervalYMObj.Div divisor

Arguments

The arguments for the method are:

Remarks

The result of the operation is stored in the OraIntervalYM object, overwriting any previous value. There is no return value.

Div (OraNumber) Method

Applies To

OraNumber Object

Description

Divides an OraNumber object by a numeric argument.

Usage

OraNumber.Div operand 

Arguments

The arguments for the method are:

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.

DynasetCacheParams Method

Applies To

OraParameter Object

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:

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.

Edit Method

Applies To

OraDynaset Object

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.

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.

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

Edit (OraRef) Method

Applies To

OraRef Object

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

ElementValue Method

Applies To

OraCollection Object

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:

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:

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

See "Example: OraCollection Iterator"

EnableBuffering (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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 SecureFiles and Large Objects Developer's Guide.

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.

Enqueue (OraAQ) Method

Applies To

OraAQ Object

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

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

Erase (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

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:

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.

ExecuteSQL Method

Applies To

OraDatabase Object

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:

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.

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 Language 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

Exist (OraCollection) Method

Applies To

OraCollection Object

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:

Remarks

None.

Exp (OraNumber) Method

Applies To

OraNumber Object

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.

FetchOraRef Method

Applies To

OraDatabase Object

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:

Remarks

The hex_value argument can be obtained through the OraRef.HexValue property or from an XML document generated by the OraDynaset.GetXML method.

FieldSize Method

Applies To

OraField Object

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

FindFirst, FindLast, FindNext, and FindPrevious Methods

Applies To

OraDynaset Object

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

Floor (OraNumber) Method

Applies To

OraNumber Object

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.

FlushBuffer (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Flushes, that is, empties, the content of the LOB to the database if LOB buffering has been enabled.

Usage

OraBlob.FlushBuffer OraClob.FlushBuffer 

GetDatabaseFromPool Method

Applies To

OraSession Object

Description

Returns the next available OraDatabase object from the pool.

Usage

GetDatabaseFromPool(long waitTime)

Arguments

The arguments for the method are:

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.

GetChunk Method

Applies To

OraField Object

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:

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

GetChunkByte Method

Applies To

OraField Object

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:

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

GetChunkByteEx Method

Applies To

OraField Object

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:

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 
%> 

GetXML Method

Applies to

OraDynaset Object

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:

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.

GetXMLToFile Method

Applies To

OraDynaset Object

Description

Generates an XML document and writes it to a file.

Usage

oradynaset.GetXMLToFile (filename, startrow, maxrows) 

Arguments

The arguments for the method are:

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.

GetRows Method

Applies To

OraDynaset Object

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:

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

Get_Value Method

Applies To

OraParamArray Object

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:

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.

HypCos (OraNumber) Method

Applies To

OraNumber Object

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.

HypSin (OraNumber) Method

Applies To

OraNumber Object

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.

HypTan (OraNumber) Method

Applies To

OraNumber Object

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.

InitIterator Method

Applies To

OraCollection Object

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

IsEqual (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the OraIntervalDS object is equal to an argument.

Usage

isEqual = OraIntervalDSObj.IsEqual value

Arguments

The arguments for the method are:

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.

IsEqual (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the OraIntervalYM object is equal to an argument.

Usage

isEqual = OraIntervalYMObj.IsEqual value

Arguments

The arguments for the method are:

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.

IsEqual (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an OraNumber object is equal to an argument value.

Usage

bool  = OraNumber.IsEqual value 

Arguments

The arguments for the method are:

Remarks

Returns a Boolean value: The value is True if all values are equal; otherwise, it is False.

IsEqual (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the OraTimeStamp object is equal to an argument.

Usage

isEqual = OraTimeStampObj.IsEqual value format

Arguments

The arguments for the method are:

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.

IsEqual (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the OraTimeStampTZ object is equal to an argument.

Usage

isEqual = OraTimeStampTZOb.IsEqual value, format

Arguments

The arguments for the method are:

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.

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.

IsGreater (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the OraIntervalDS object is greater than an argument.

Usage

isGreater = OraIntervalDSObj.IsGreater value

Arguments

The arguments for the method are:

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.

IsGreater (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the OraIntervalYM object is greater than an argument.

Usage

isGreater = OraIntervalYMObj.IsGreater value

Arguments

The arguments for the method are:

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.

IsGreater (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an OraNumber object is greater than an argument value.

Usage

bool  = OraNumber.IsGreater value 

Arguments

The arguments for the method are:

Remarks

Returns a Boolean value: The value is True if the OraNumber object is greater than the argument; otherwise, it is False.

IsGreater (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the OraTimeStamp object is greater than an argument.

Usage

isGreater = OraTimeStampObj.IsGreater value format

Arguments

The arguments for the method are:

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.

IsGreater (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the OraTimeStampTZ object is greater than an argument.

Usage

isGreater = OraTimeStampTZObj.IsGreater value, format

Arguments

The arguments for the method are:

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.

IsLess (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the OraIntervalDS object is less than an argument.

Usage

isLess = OraIntervalDSObj.IsLess value

Arguments

The arguments for the method are:

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.

IsLess (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the OraIntervalYM object is less than an argument.

Usage

isLess  = OraIntervalYMObj.IsLess value

Arguments

The arguments for the method are:

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.

IsLess (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an OraNumber object is less than an argument value.

Usage

bool  = OraNumber.IsLess value

Arguments

The arguments for the method are:

Remarks

Returns a Boolean value: The value is True if the OraNumber object is less than the argument; otherwise, it is False.

IsLess (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the OraTimeStamp object is less than an argument.

Usage

isLessr = OraTimeStampObj.IsLess value format

Arguments

The arguments for the method are:

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.

IsLess (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the OraTimeSTampTZ object is less than an argument.

Usage

isLess = OraTimeStampTZObj.IsLess value, format

Arguments

The arguments for the method are:

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.

IterNext Method

Applies To

OraCollection Object

Description

Moves the iterator to point to the next element in the collection.