3 Basic Features

Obtaining an OraSession Object

The following script demonstrates how to obtain an OraSession object in Visual Basic. OO4OSession is the object variable that holds an instance of the OraSession object.

Dim OO4OSession as Object 
Set OO4OSession = CreateObject(ÒOracleInProcServer.XOraSession") 

or

Dim OO4OSession as OraSession 
Set OO4OSession = New OraSessionClass 

or

Dim OO4OSession as New OraSessionClass 

The following example demonstrates how to obtain an OraSession object in IIS Active Server Pages.

<OBJECT RUNAT=Server SCOPE=APPLICATION ID=OO4OSession
                PROGID="OracleInProcServer.XOraSession"> 
</OBJECT> 

OracleInProcServer.XOraSession is the version independent program ID for OO4O that the Oracle client installation program registers in the Windows registry. It is the symbolic name for a globally unique identifier (CLSID) that identifies the OO4O component.

Obtaining an OraServer Object

You can also use the OraServer object interface for accessing the OO4O Automation Server.

Dim OO4OServer as Object 
Set OO4OServer = CreateObject("OracleInProcServer.XOraServer") 

Now you can connect to Oracle Database.

Using OraServer for Connection Multiplexing

The OraServer interface allows multiple user sessions to share a physical network connection to the database. This reduces resource usage on the network and the database, and allows for better server scalability. However, execution of commands by multiple user sessions is serialized on the connection. Therefore, this feature is not recommended for use in multithreaded applications in which parallel command execution is needed for performance.

The following code example shows how to use the OraServer interface to establish two user sessions:

Set OO4OServer = CreateObject("OracleInProcServer.XOraServer") 
OO4OServer.Open("ExampleDb") 
Set EmpDb1 = OO4OServer.OpenDatabase("Scott/Tiger") 
Set EmpDb2 = OO4OServer.OpenDatabase("Scott/Tiger") 

You can also obtain user sessions from a previously created pool of objects.

Queries

Queries are statements that retrieve data from a database. A query can return zero, one, or many rows of data. All queries begin with the SQL keyword SELECT, as in the following example:

SELECT ename, empno FROM emp 

In OO4O, SELECT statements such as this are used with the CreateDynaset method of the OraDatabase interface to execute queries. This method returns an OraDynaset object that is then used to access and manipulate the set of rows returned. An OraDynaset object encapsulates the functions of a client-side scrollable (forward and backward) cursor that allows browsing the set of rows returned by the query it executes.

The following code example shows how to connect to the ExampleDb database, execute a query, move through the result set of rows, and displays the column values of each row in a simple message box.

Set OO4OSession = CreateObject(ÒOracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0 ) 
 
' SELECT query described above used in next line 
Set Employees = EmpDb.CreateDynaset("SELECT ename, empno FROM" & _ 
          "emp",ORADYN_NOCACHE) 
While NOT Employees.EOF 
  MsgBox "Name: " & Employees("ENAME").value & "Employee #: " & _
          Employees("EMPNO").value 
Employees.MoveNext 
Wend 

In the previous example, Employees("ENAME") and Employees("EMPNO") return values of the ENAME and the EMPNO columns from the current row in the result set, respectively. An alternative method of accessing the column values is to use the positions of the columns, Employees(0) for the ENAME column and Employee(1) for EMPNO. This method obtains the column value faster than referencing a column by its name.

The Employees.MoveNext statement in the example sets the current row of the result set to the next row. The EOF property of the OraDynaset is set to True if an attempt is made to move past the last row in the result set.

The MoveNext method is one navigational method in the OraDynaset interface. Other methods include MoveFirst, MoveLast, MoveNext, MovePrevious, MoveNextn, MovePreviousn, MoveRel, and MoveTo.

An OraDynaset object also provides methods to update and delete rows retrieved from base tables or views that can be updated. In addition, it provides a way to insert new rows. See "OraDynaset Object".

Queries can also require the program to supply data to the database using input (bind) variables, as in the following example:

SELECT name, empno 
        FROM employees 
        WHERE ename = :ENAME

In the SQL statement, :ENAME is a placeholder for a value that is supplied by the application.

In OO4O, the OraParameter object is used to supply data values for placeholders.

To define a parameter, use the OraParameters collection object. This object is obtained by referencing the Parameters property of an OraDatabase interface. The OraParameters collection provides methods for adding, removing, and obtaining references to OraParameter objects.

The following statement adds an input parameter, ORAPARM_INPUT, to the OraParameters collection contained in the EmpDb object.

EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 

ENAME is the name of the parameter and must be the same as the name of the placeholder in the SQL statement, :ENAME in the sample code. JONES is provided as the initial value, and ORAPARM_INPUT notifies OO4O that it is used as an INPUT parameter.

The following example creates an OraDynaset object that contains only one row for an employee whose name is 'JONES'.

Set OO4OSession = CreateObject(ÒOracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0 ) 
EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 
Set Employees = EmpDb.CreateDynaset("SELECT ename, empno FROM emp" & _
           "WHERE ename = :ENAME",ORADYN_NOCACHE) 

While NOT Employees.EOF 
   MsgBox "Name: " & Employees("ename").value & "Employee #: " & _
            Employees("empno").value 
   Employees.MoveNext 
Wend 

Data Manipulation Language Statements

Data manipulation language (DML) statements can change data in the database tables. For example, DML statements are used to:

• Updating Database Records

• Deleting Rows from a Table

• Inserting New Rows into a Table

The OraDatabase interface in OO4O provides two methods for executing DML statements: ExecuteSQL and CreateSQL. The following discussion describes how these methods can be used to execute various types of DML statements.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 
EmpDb.ExecuteSQL ("UPDATE emp SET sal = sal + 1000 WHERE ename = :ENAME") 

Set sqlStatement = EmpDb.CreateSQL("UPDATE emp SET sal = sal + 1000" & _ 
             "WHERE ename = :ENAME", 0&) 

EmpDb.Parameters("ENAME").Value = "KING" sqlStatement.Refresh 

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 
Set sqlStatement = EmpDb.CreateSQL ("DELETE from emp WHERE ename = :ENAME")

EmpDb.Parameters("ENAME").Value = "KING" sqlStatement.Refresh 

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
EmpDb.ExecuteSQL ("INSERT INTO emp (empno, ename, job, mgr, deptno)" & _ 
           "VALUES (1233,'OERTEL', 'WRITER', 7839, 30) ") 

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("Exampledb", "scott/tiger", 0) 
 
'Creates parameter arrays for the empno, ename, job, and salary columns 
EmpDb.Parameters.AddTable "EMPNO_ARRAY", ORAPARM_INPUT, ORATYPE_NUMBER, 2
EmpDb.Parameters.AddTable "ENAME_ARRAY", ORAPARM_INPUT, ORATYPE_VARCHAR2, 2, 10
EmpDb.Parameters.AddTable "JOB_ARRAY", ORAPARM_INPUT, ORATYPE_VARCHAR2, 2, 9
EmpDb.Parameters.AddTable "MGR_ARRAY", ORAPARM_INPUT, ORATYPE_NUMBER, 2
EmpDb.Parameters.AddTable "DEPT_ARRAY", ORAPARM_INPUT, ORATYPE_VARCHAR2, 2, 10
Set EmpnoArray = EmpDb.Parameters("EMPNO_ARRAY") 
Set EnameArray = EmpDb.Parameters("ENAME_ARRAY") 
Set JobArray = EmpDb.Parameters("JOB_ARRAY") 
Set MgrArray = EmpDb.Parameters("MGR_ARRAY") 
Set DeptArray = EmpDb.Parameters("DEPT_ARRAY") 
 
'Populate the arrays with values 
EmpnoArray(0) = 1234 
EnameArray(0) = "JORDAN" 
JobArray(0) = "SALESMAN" 
MgrArray(0) = 7839 
DeptArray(0) = 30 
EmpnoArray(1) = 1235 
EnameArray(1) = "YOUNG" 
JobArray(1) = "SALESMAN" 
MgrArray(1) = 7839 
DeptArray(1) = 30 
 
'Insert two rows 
EmpDb.ExecuteSQL ("INSERT INTO emp (empno, ename, job, mgr, deptno) VALUES" & _ 
          "(:EMPNO_ARRAY,:ENAME_ARRAY, :JOB_ARRAY,:MGR_ARRAY, :DEPT_ARRAY)") 

Creating the Connection Pool

The connection pool is created by invoking the CreateDatabasePool method of the OraSession interface. An OraDatabase object represents a connection to an Oracle database and contains methods for executing SQL statements and PL/SQL blocks.

Obtaining from and Returning Objects to the Pool

To retrieve an OraDatabase object from the pool, call the GetDatabaseFromPool method. This function returns a reference to an OraDatabase object.

Destroying the Pool

The pool is implicitly destroyed if the parent session object that it belongs to is destroyed. It can also be destroyed at any time by invoking the DestroyDatabasePool method.

Accessing the Pool attributes

The following are the database pool properties. These properties are read-only:

• DbPoolMaxSize - maximum pool size

• DbPoolCurrentSize - current size of the pool

• DbPoolInitialSize - initial size of the pool

Processing Transactions Using the Database from the Connection Pool

The following example shows the recommended way to process transactions:

set Odb = OraSession.GetDatabaseFromPool(0) 
Odb.Connection.BeginTrans 
… 
 
Odb.Connection.CommitTrans 

PL/SQL Integration with Oracle Objects for OLE

Oracle Objects for OLE (OO4O) provides tight integration with PL/SQL stored procedures. OO4O supports PL/SQL stored procedures, PL/SQL tables, PL/SQL, cursors and so on. The PL/SQL bind variables are supported through the OraParameter Add method.

The stored procedure block is executed either through the CreateSQL method or the ExecuteSQL method.

Oracle Objects for OLE can return a cursor created in the stored procedure or anonymous PL/SQL block as a READONLY dynaset object.To do this, you must assign the cursor variable as an OraParameter object of type ORATYPE_CURSOR.

After executing the stored procedure, the Value property of this OraParameter object returns a read-only dynaset object.

This dynaset object can be treated the same as other dynaset objects.

Executing PL/SQL Blocks Using ExecuteSQL and CreateSQL

In OO4O, you can use the ExecuteSQL or CreateSQL methods of the OraDatabase object to execute PL/SQL blocks, as the following example shows:

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
 
'Add EMPNO as an Input parameter and set its initial value. 
EmpDb.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT 
EmpDb.Parameters("EMPNO").ServerType = ORATYPE_NUMBER 
 
'Add ENAME as an Output parameter and set its initial value. 
EmpDb.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT 
EmpDb.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2 
 
'Add SAL as an Output parameter 
EmpDb.Parameters.Add "SAL", 0, ORAPARM_OUTPUT 
EmpDb.Parameters("SAL").ServerType = ORATYPE_NUMBER 
 
'Add COMMISSION as an Output parameter and set its initial value. 
EmpDb.Parameters.Add "COMMISSION", 0, ORAPARM_OUTPUT 
EmpDb.Parameters("COMMISSION").ServerType = ORATYPE_NUMBER 
EmpDb.ExecuteSQL ("BEGIN SELECT ename, sal, comm INTO :ENAME, :SAL," & _ 
             ":COMMISSION FROM emp WHERE empno = :EMPNO; END;") 
 
'display the values of Ename, Sal, Commission parameters 
MsgBox "Name: " & EmpDb.Parameters("ENAME").Value 
MsgBox "Salary " & EmpDb.Parameters("SAL").Value 
MsgBox "Commission: " & EmpDb.Parameters("COMMISSION").Value 

The following example executes a PL/SQL block that calls a stored procedure using the CreateSQL method in OO4O. The procedure takes a department number as input and returns the name and location of the department.

This example is used for creating the stored procedure in the employee database.

CREATE OR REPLACE PACKAGE Department as 
PROCEDURE GetDeptName (inDeptNo IN NUMBER, outDeptName OUT VARCHAR2, 
                   outDeptLoc OUT VARCHAR2); 
END Department;
/
 
CREATE OR REPLACE PACKAGE BODY Department as 
PROCEDURE GetDeptName(inDeptNo IN NUMBER, outDeptName OUT VARCHAR2, 
                 outDeptLoc OUT VARCHAR2) is 
BEGIN 
   SELECT dname, loc into outDeptName, outDeptLoc from DEPT 
      WHERE deptno = inDeptNo; 
   END; 
END Department;
/ 

The following example executes the previously created procedure to get the name and location of the department where deptno is 10.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
empDb.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT 
empDb.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER 
empDb.Parameters.Add "DNAME", 0, ORAPARM_OUTPUT 
empDb.Parameters("DNAME").ServerType = ORATYPE_VARCHAR2 
empDb.Parameters.Add "DLOC", 0, ORAPARM_OUTPUT 
empDb.Parameters("DLOC").ServerType = ORATYPE_VARCHAR2 
Set PlSqlStmt = empDb.CreateSQL("Begin Department.GetDeptname" & _ 
             "(:DEPTNO, :DNAME, :DLOC); end;", 0&) 
 
'Display Department name and location 
MsgBox empDb.Parameters("DNAME").Value & empDb.Parameters("DLOC").Value 

Returning PL/SQL Cursor Variables

PL/SQL cursor variables are mainly used for accessing one or more query result sets from PL/SQL blocks and stored procedures and functions. The OraParameter object in OO4O can be used to hold a PL/SQL cursor variable.

The OraParameter object representing a cursor variable should be of type ORATYPE_CURSOR, and can only be defined as an output variable. After the PL/SQL block is executed, the Value property of the OraParameter object contains a read-only OraDynaset object. This OraDynaset object can be used to scroll through the returned rows.

In some cases, it is better to use the CreateSQL method for executing PL/SQL procedures than the ExecuteSQL method. The Refresh method on the OraSQLStmt object can result in modified PL/SQL cursors. If the CreateSQL method is used, these modified cursors are automatically associated with the existing dynaset object, and no new dynaset object is created.

You cannot set the SQL property of the dynaset object; this raises an error.

You should call the Remove method on the parameter object. This helps in cleaning the dynaset object and local temporary cache files.

The following example contains a stored procedure that gets the cursors for the emp and dept tables and a small application that executes the procedure.

Stored Procedure

CREATE PACKAGE EmpAndDept AS
   cursor emp is select * from emp;
   cursor dept is select * from dept;
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
   PROCEDURE GetEmpAndDeptData (emp_cv OUT EmpCurTyp, 
                              dept_cv OUT DeptCurTyp);
END EmpAndDept;/
 
 
CREATE PACKAGE BODY EmpAndDept AS
   PROCEDURE GetEmpAndDeptData (emp_cv OUT EmpCurTyp, 
                                dept_cv OUT DeptCurTyp) IS
       BEGIN 
         OPEN emp_cv FOR SELECT * FROM emp;
         OPEN dept_cv FOR SELECT * FROM dept; END GetEmpAndDeptData;
END EmpAndDept;
/

Application

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
empDb.Parameters.Add "EMPCUR", 0, ORAPARM_OUTPUT 
empDb.Parameters("EMPCUR").serverType = ORATYPE_CURSOR 
empDb.Parameters.Add "DEPTCUR", 0, ORAPARM_OUTPUT 
empDb.Parameters("DEPTCUR").serverType = ORATYPE_CURSOR 
Set PlSqlStmt = empDb.CreateSql("Begin EmpAndDept.GetEmpAndDeptData (:EMPCUR," & _
             ":DEPTCUR); end;", 0) 
Set EmpDynaset = empDb.Parameters("EmpCur").Value 
Set DeptDynaset = empDb.Parameters("DeptCur").Value 
MsgBox EmpDynaset.Fields("ENAME").Value 
MsgBox DeptDynaset.Fields("DNAME").Value 

Returning PL/SQL Tables

PL/SQL tables are mainly used for accessing arrays of PL/SQL data. The OraParamArray object in OO4O can be used to hold a PL/SQL cursor variable.

The OraParamArray object representing a table variable should be created first the using the AddTable method. Table values are accessed or set using the Get_Value and Put_Value methods of the OraParamArray object.

The PL/SQL procedure GetEmpNamesInArray returns an array of ENAME values for array of EMPNOs.

CREATE PACKAGE EmpNames AS 
    type NUMARRAY is table of NUMBER index by 
          BINARY_INTEGER; --Define EMPNOS array 
    type VCHAR2ARRAY is table of VARCHAR2(10) index by 
          BINARY_INTEGER; --Define ENAMES array 
    PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, 
              inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY); 
END EmpNames; /
 
 
CREATE PACKAGE BODY EmpNames AS 
    PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, 
              inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY) is 
    BEGIN 
    FOR I in 1..ArraySize loop 
        SELECT ENAME into outEmpNames(I) from EMP 
                       WHERE EMPNO = inEmpNos(I); 
    END LOOP; 
END; 
 
END EmpNames; /
 

The following example executes the previous procedure to get the ename table.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set Empdb = OO4OSession.OpenDatabase("Exampledb", "scott/tiger", 0) 
Empdb.Parameters.Add "ArraySize", 3, ORAPARM_INPUT 
Empdb.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER, 3, 22 
Empdb.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3, 10
Set EmpnoArray = Empdb.Parameters("EMPNOS") 
Set EnameArray = Empdb.Parameters("ENAMES") 
 
'Initialize the newly created input parameter table EMPNOS 
EmpnoArray(0) = 7698 
EmpnoArray(1) = 7782 
EmpnoArray(2) = 7654 
Empdb.ExecuteSQL ("Begin EmpNames.GetEmpNamesInArray(:ArraySize," & _ 
              ":EMPNOS, :ENAMES); End;") 
MsgBox EnameArray(0) 
MsgBox EnameArray(1) 
MsgBox EnameArray(2) 

Executing Data Definition Language Statements

Data Definition Language (DDL) statements manage schema objects in the database. DDL statements create new tables, drop old tables, and establish other schema objects. They also control access to schema objects. For example:

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
EmpDb.ExecuteSQL("create table employees (name VARCHAR2(20)," & _
          "ssn VARCHAR2(12), empno NUMBER(6), mgr NUMBER(6), salary NUMBER(6)") 

EmpDb.ExecuteSQL("GRANT UPDATE, INSERT, DELETE ON employees TO donna") 
EmpDb.ExecuteSQL("REVOKE UPDATE ON employees FROM jamie") 

DDL statements also allow you to work with objects in Oracle Database, for example:

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)  
EmpDb.ExecuteSQL("create type person_t as object (name VARCHAR2(30)," & _
             "ssn VARCHAR2(12),address VARCHAR2(50))") 
EmpDb.ExecuteSQL("create table person_tab OF person_t") 

Nonblocking Mode

In nonblocking mode, control is returned to the application immediately even if the execution is not complete. This allows the application to execute other tasks that are not dependent on the results of the last execution.

To enable nonblocking mode, pass in the ORASQL_NONBLK option to the CreateSQL method while creating the OraSQLStmt object. If this mode is not specified, the OraSQLStmt object executes in blocking mode (default behavior).

'Create the statement in NON-BLOCKING mode 
OraSQL = Oradb.CreateSQL("delete from emp",ORASQL_NONBLK) 

An OraSQLStmt object created in nonblocking mode executes in nonblocking mode for the lifetime of the object.

This section contains the following topics:

• Checking the Status of a Nonblocking Operation

• Canceling a Nonblocking Operation

• Executing Multiple Queries in Asynchronous Mode

• Limitations on Nonblocking

Dim OraDatabase as OraDatabase 
Dim OraStmt as OraSQLStmt 
Dim stat as long 
Dim OraSess as OraSession 
Set OraSess = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase =OraSess.OpenDatabase("ExampleDb", "scott/tiger", 0) 
 
'execute the select statement with NONBLOCKING mode on 
set OraStmt = OraDatabase.CreateSQL ("update emp set sal = sal + 1000", _
            ORASQL_NONBLK)
 
'Check if the call has completed 
stat = OraStmt.NonBlockingState 
while stat = ORASQL_STILL_EXECUTING 
MsgBox "Asynchronous Operation under progress" 
stat = OraStmt.NonBlockingState 
wend 
MsgBox "Asynchronous Operation completed successfully" 

Dim OraDatabase as OraDatabase 
Dim OraStmt as OraSQLStmt 
Dim stat as long 
Dim OraSess as OraSession 
Set OraSess = CreateObject("OracleInProcServer.XOraSession") 
Set OraDatabase =OraSess.OpenDatabase("ExampleDb", "scott/tiger", 0) 
 
'execute the select statement with NONBLOCKING mode on 
set OraStmt = OraDatabase.CreateSQL ("update emp set sal = sal + 1000", _ 
         ORASQL_NONBLK)
 
'Check if the call has completed 
stat = OraStmt.NonBlockingState 
if stat = ORASQL_STILL_EXECUTING 
MsgBox "Cancelling the asynchronous operation that is underway" 
OraStmt.Cancel 
End if 

Dim OraSess as OraSession 
Dim OraServ as OraServer 
Dim OraDb1 as OraDatabase 
Dim OraDb2 as OraDatabase 
Dim OraStmtnonblk as OraSQLStmt 
Dim OraStmtblk as OraSQLStmt 
Dim stat as long 
set OraSess = CreateObject("OracleInProcServer.XOraSession") 
set OraDb1 = OraSess.OpenDatabase("exampledb","scott/tiger",0&) 
Set OraServ = CreateObject("OracleInProcServer.XOraServer") 
set OraDb2 = OraServ.OpenDatabase("Exampledb","scott/tiger",0&) 
 
'execute the select statement with NONBLOCKING mode on 
set OraStmtnonblk = OraDb1.CreateSQL ("update emp set sal = sal + 1000", _
             ORASQL_NONBLK) 
 
'Check if the call has completed 
stat = OraStmt.NonBlockingState 
while stat = ORASQL_STILL_EXECUTING 
  MsgBox "Asynchronous Operation under progress" 
  stat = OraStmt.NonBlockingState 
wend 
  MsgBox "Asynchronous Operation completed successfully" 
 
'execute on the second connection in BLOCKING mode 
set OraStmtblk = OraDb2.CreateSQL ("update emp set sal = sal + 500",0&)