| Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
|
|
View PDF |
| Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
|
|
View PDF |
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 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)
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.
