This chapter describes basic features of Oracle Objects for OLE.
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.
OraSession
Highest level object for an application. It manages collections of OraDatabase
, OraConnection
, and OraDynaset
objects.
OraServer
Represents a physical connection to a database instance and allows for connection multiplexing
The 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 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.
Once you have obtained an interface, you can use it to establish a user session in an Oracle database by invoking the OpenDatabase
method.
Set EmpDb= OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0)
or
Set EmpDb= OO4OServer.OpenDatabase("Scott/Tiger")
The variable 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 scott
/tiger
for the user name and password.
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.
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
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.
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 theORADYN_NOCACHE option in the CreateDynaset method disables caching. This constant is defined in the oraconst.txt file and can be found in the root directory where OO4O is installed, ORACLE_BASE\ORACLE_HOME \OO4O . |
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 (DML) statements can change data in the database tables. For example, DML statements are used to:
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.
The following example uses the ExecuteSQL
method to execute an UPDATE
statement.
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 CreateSQL
method:
Set sqlStatement = EmpDb.CreateSQL("UPDATE emp SET sal = sal + 1000" & _ "WHERE ename = :ENAME", 0&)
Both the ExecuteSQL
and 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 emp
table.
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) ")
Inserting Multiple Rows Using Parameter Arrays
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 emp
table.
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)")
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.
To retrieve an OraDatabase
object from the pool, call the GetDatabaseFromPool
method. This function returns a reference to an OraDatabase
object.
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.
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
Variable declarations
Assignment 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 FOR
loops
Cursor variables
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.
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
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.
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 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
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 EMPNO
s.
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)
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 BeginTrans
, CommitTrans
, and Rollback
transactional control methods of the OraSession
object allow operations to be grouped into larger transactions.
The 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 CommitTrans
and 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 UPDATE
or DELETE
statement. However, in the case of OO4O, if UPDATE
or 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 CommitTrans
or Rollback
method.
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 INSERT
, UPDATE
, and DELETE
statements). Transactions do not apply to the Data Control Language (DCL) or Data Definition Language (DDL) portions (such as CREATE
, DROP
, and 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:
The OpenDatabase
method of OraSession
uses the ORADB_ENLIST_IN_MTS
option.
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 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)
An OraSQLStmt
object created in nonblocking mode executes in nonblocking mode for the lifetime of the 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 NonBlockingState
property.
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"
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
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 OraDynaset
and OraAQ
, are currently active on the same database session. The only exceptions are OraParameter
and OraObject
objects. These are permitted, as they may be required for the nonblocking execution.