|Oracle® Objects for OLE Developer's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter contains these topics:
Oracle Objects for OLE enables client applications to connect to Oracle databases, execute commands, and access and manipulate the results returned. While some flexibility exists in the order in which specific tasks can be performed, every application using OO4O Automation objects performs the following basic steps:
Disconnect from the servers and free the OO4O objects used
To connect to an Oracle database with the OO4O Automation Server, you must first create an instance of the server. In Visual Basic (VB), this is usually done by calling the
CreateObject method, although the
NEW keyword can also be used.
You can use the Visual Basic
CreateObject method with either of the following two OO4O server objects. The interfaces of these objects can provide access to OO4O and enable a connection to Oracle Database.
Highest level object for an application. It manages collections of
Represents a physical connection to a database instance and allows for connection multiplexing
CreateObject method uses the
ID of the component and object as arguments.
The following script demonstrates how to obtain an
OraSession object in Visual Basic.
OO4OSession is the object variable that holds an instance of the
Dim OO4OSession as Object Set OO4OSession = CreateObject(ÒOracleInProcServer.XOraSession")
Dim OO4OSession as OraSession Set OO4OSession = New OraSessionClass
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.
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.
See Also:"Connecting to Oracle Database"
Once you have obtained an interface, you can use it to establish a user session in an Oracle database by invoking the
Set EmpDb= OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0)
Set EmpDb= OO4OServer.OpenDatabase("Scott/Tiger")
EmpDb represents a user session. It holds an
OraDatabase interface and can be used to send commands to Oracle Database using
ExampleDb for the network connection alias and
tiger for the user name and password.
See Also:OpenDatabase Method
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.
Commands that can be sent to Oracle databases using OO4O Automation objects are divided into the following categories:
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
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.
Note:Caching result sets on the client's local disk can be disabled if backward scrollability is not a requirement. This is strongly recommended and can provide significant performance improvements. Passing the
ORADYN_NOCACHEoption in the
CreateDynasetmethod disables caching. This constant is defined in the
oraconst.txtfile and can be found in the root directory where OO4O is installed,
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,
"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
EMPNO. This method obtains the column value faster than referencing a column by its name.
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.
MoveNext method is one navigational method in the
OraDynaset interface. Other methods include
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
The following statement adds an input parameter,
ORAPARM_INPUT, to the
OraParameters collection contained in the
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
The following example creates an
OraDynaset object that contains only one row for an employee whose name is
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 (DML) statements can change data in the database tables. For example, DML statements are used to:
OraDatabase interface in OO4O provides two methods for executing DML statements:
CreateSQL. The following discussion describes how these methods can be used to execute various types of DML statements.
The following example uses the
ExecuteSQL method to execute an
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")
Another way to execute the
UPDATE statement is to use the
Set sqlStatement = EmpDb.CreateSQL("UPDATE emp SET sal = sal + 1000" & _ "WHERE ename = :ENAME", 0&)
CreateSQL methods execute the UPDATE statement provided. The difference is that the
CreateSQL method returns a reference to an
OraSQLStmt interface, in addition to executing the statement. This interface can later be used to execute the same query using the
Refresh method. Because the query has already been parsed by the database, subsequent execution of the same query results in faster execution, especially if bind parameters are used.
For example, to increase the salary of an employee named
KING by 1000, change the value of the placeholder, and refresh the
sqlStatement object as follows:
EmpDb.Parameters("ENAME").Value = "KING" sqlStatement.Refresh
For DML statements that are frequently executed, using parameters with
OraSqlStmt objects is more efficient than using the
ExecuteSql statement repeatedly. When the
Refresh method of the
OraSQLStmt is executed, the statement no longer needs to be parsed by the database. In application servers, such as Web servers, where the same queries are frequently executed with different parameter values, this can lead to significant savings in Oracle Database processing.
The following example uses the
CreateSQL method to delete rows from the
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")
To delete another row from the
emp table, the value of the parameter is changed, and the
sqlStatement object is refreshed.
EmpDb.Parameters("ENAME").Value = "KING" sqlStatement.Refresh
The following example adds a new row into the table.
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) ")
You can use parameter arrays to fetch, update, insert, or delete multiple rows in a table. Using parameter arrays for manipulating multiple rows is more efficient than executing multiple statements that operate on individual rows.
The following example demonstrates how the
AddTable method of the
OraDatabase interface is used to create parameter arrays. The arrays are then populated with values, and used as placeholders in the execution of an
INSERT statement that inserts two rows into the
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)")
See Also:AddTable Method
OO4O is thread-safe and can be used effectively in multithreaded applications and environments such as the Microsoft Internet Information Server (IIS). OO4O supports both the free and apartment threading models in COM/DCOM.
Access to OO4O object attributes is serialized when used with multiple threads of execution. To achieve maximum concurrency in query execution in a multithreaded application with OO4O, avoid sharing objects in multiple threads.
Avoid using commit and rollback operations on a session object that is shared among multiple threads because all connections associated with that session are committed or rolled back. To perform commit and rollback operations on a session object, create a unique session object for each database object used.
The connection pool in OO4O is a pool of
OraDatabase objects. An OO4O connection pool is a group of (possibly) already connected
OraDatabase objects. For applications that require constant connections and disconnections to the database, such as ASP Web applications, using a connection pool results in enhanced performance.
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.
See Also:CreateDatabasePool Method
To retrieve an
OraDatabase object from the pool, call the
GetDatabaseFromPool method. This function returns a reference to an
See Also:GetDatabaseFromPool Method
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
See Also:DestroyDatabasePool Method
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
OO4O, linked with clients from releases 8.1.6 or higher, supports detection of lost connections.
Applications can verify the status of the database connection by invoking the
ConnectionOK property of the
OraDatabase object. The
OraSession.GetDatabaseFromPool method now verifies the connection before returning the
OraDatabase to the application.
If the connection is lost, the
GetDatabaseFromPool method drops the lost connection and fetches a new connection.
Dim MyDatabase As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set MyDatabase = MySession.OpenDatabase("ora90", "scott/tiger", 0&) ' Other code ... ' Check if the database connection has not timed out if MyDatabase.ConnectionOK MsgBox " The database connection is valid" endif
PL/SQL is the Oracle procedural extension to the SQL language. PL/SQL processes complicated tasks that simple queries and SQL data manipulation language statements cannot perform. Without PL/SQL, Oracle Database would have to process SQL statements one at a time. Each SQL statement results in another call to the database and consequently higher performance overhead. In a networked environment, the overhead can be significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic. However, with PL/SQL, an entire block of statements can be sent to a database at one time. This can greatly reduce communication between an application and a database.
PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. These include:
One or more SQL statements
Procedural control statements (
IF...THEN...ELSE statements and loops)
Exception handling statements
Calls to other Oracle stored procedures and stored functions
Special PL/SQL features such as records, tables, and cursor
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
The stored procedure block is executed either through the
CreateSQL method or the
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
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.
In OO4O, you can use the
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
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
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.
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.
Note:PL/SQL stored procedures that contain cursors as table parameters are not supported.
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
dept tables and a small application that executes the 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; /
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
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.
OraParamArray object representing a table variable should be created first the using the
AddTable method. Table values are accessed or set using the
Put_Value methods of the
The PL/SQL procedure
GetEmpNamesInArray returns an array of
ENAME values for array of
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
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)
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")
A transaction is a logical unit of work that comprises one or more SQL statements executed by a single user. A typical example is transferring money from one bank account to another. Two operations take place:
Money is taken out of one account.
Money is put into the other account.
These operations need to be performed together. If one operation was completed but not the other (for example, if the network connection went down), the bank's books would not balance correctly.
Normally, when you execute an update method on a dynaset, the changes are committed to the database immediately. Each operation is treated as a distinct transaction. The
Rollback transactional control methods of the
OraSession object allow operations to be grouped into larger transactions.
BeginTrans method tells the session that you are starting a group of operations. The
CommitTrans method makes the entire group of operations permanent. The
Rollback method cancels the entire group. The
Rollback methods end the transaction, and the program returns to normal operation: one transaction for each operation. Experienced Oracle Database users should note the following differences between the operation of Oracle Objects for OLE and many Oracle Database tools:
Oracle Database tools, such as SQL*Plus, execute as if the
BeginTrans method was called when the tool was started. This means that updates are not committed immediately; they are held until a commit or rollback is executed.
SQL*Plus starts a new transaction every time a commit or rollback is executed.
SQL*Plus does not take a row lock in the case of a failed
DELETE statement. However, in the case of OO4O, if
DELETE methods fail on a given row in a dynaset in a global transaction (such as cases in which you issued a
BeginTrans method), be aware that locks remain on those rows. These locks persist until you call a
If you are connected to more than one database and use the transaction methods, be aware that Oracle Objects for OLE commits each database separately. This is not the same as the two-phase commit that Oracle Database provides. If your application needs to guarantee data integrity across databases, connect to a single database and then access additional databases by way of the Oracle Database link feature. This method gives you the benefit of the Oracle Database two-phase commit. Consult your Oracle Database documentation for more information about two-phase commit, database links, and distributed transactions.
Transactions apply only to the Data Manipulation Language (DML) portion of the SQL language (such as
DELETE statements). Transactions do not apply to the Data Control Language (DCL) or Data Definition Language (DDL) portions (such as
ALTER statements) of the SQL language. DCL and DDL commands always force a commit, which in turn commits everything done previously.
Oracle database transactions initiated in Oracle Objects for OLE (OO4O) automatically participate in global transactions coordinated by the Microsoft Distributed Transaction Coordinator (DTC) in the Microsoft Transaction Server (MTS), if all the following conditions are true:
OpenDatabase method of
OraSession uses the
OO4O determines that it is running in the context of a global transaction in MTS.
Oracle Service for Microsoft Transaction Server is installed and running.
In OO4O Automation, you can execute commands using asynchronous processing. This enables you to execute SQL statements and PL/SQL blocks in nonblocking mode. Nonblocking mode is an option of the
See Also:CreateSQL Method
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)
OraSQLStmt object created in nonblocking mode executes in nonblocking mode for the lifetime of the object.
See Also:OraSQLStmt Object
This section contains the following topics:
To determine the status of an
OraSQLStmt object executing asynchronously, applications need to poll the
NonBlockingState property. The
NonBlockingState property returns
ORASQL_STILL_EXECUTING if execution is still pending or
ORASQL_SUCCESS if execution has completed successfully.
Any failures are thrown as exceptions.
On successful completion, the output parameters, if any, are placed in the bound parameter buffers. The application can then access the parameters as in the blocking case.
The following example demonstrates the usage of the
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"
See Also:NonBlockingState Property
You can cancel a nonblocking operation that is underway by calling the
Cancel method on the
OraSQLStmt object that is executing the asynchronous call.
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
See Also:Cancel Method
Multiple queries can be executed in asynchronous mode. In this example, while the first connection is executing a non-blocking call, the second connection executes a SQL statement in blocking mode.
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&)
The following are limitations on nonblocking mode:
When a nonblocking operation is running on an
OraSQLStmt object, you cannot change the properties or attributes of this object, as it can affect the execution that is in progress.
You cannot create an
OraSQLStmt object in nonblocking mode if there are other objects that are already instantiated on the connection. In other words, creating an
OraSQLStmt object to execute in nonblocking mode only succeeds if no other objects, such as
OraAQ, are currently active on the same database session. The only exceptions are
OraObject objects. These are permitted, as they may be required for the nonblocking execution.