Oracle Objects for OLE

Oracle provides us with its own native client software that sits above the Oracle Call Interface, as mentioned earlier, allowing us to communicate with an Oracle database using a COM/OLE component.

Oracle Objects for OLE, or OO4O as it is usually abbreviated to, allows us to execute SQL and PL/SQL statements in a native "pass-through" format. This means we can make use of all Oracle data types as well as additional features, such as  bind variables.

Bind variables are an efficient way to execute the same SQL statement with differing parameters
without Oracle having to re-parse the statement each time . Unfortunately, it won't make that much
of a performance difference to our web page, as we will only execute the statement twice and then
close our database connection. However, this feature is ideal for client/server applications that maintain the database connection until the application is closed. We will be discussing bind variables in the following examples.

Unfortunately, by using OO4O we'll have to forfeit the usual methods found in ADO. However, OO4O does implement the same, or very similar methods, so the learning curve is not that steep.

Version 2.3 of OO4O,which shipped with Oracle8, has the following  object model:

You'll find that the later version, 8.1, as shipped with Oracle8i, has a similar model with a number of extra objects:

The table gives a brief description of each  object:

We are going to create a sample ASP script that executes a SQL statement to return a list of employees for a specific department number using bind variables. This example, which is compatible with both the 2.3 and 8.1 versions of OO4O, will use OraSession, OraDatabase, OraDynaset and OraFields objects, as they are the most commonly used objects in OO4O.

Using Bind Variables

Our script contains simple VBScript function, CreateEmployeeTable, declared at the bottom of the script, to handle the refreshing of the Parameters collection and writing out of the HTML results table each time .

We've made use of two constants to store the department code for the Accounting and Research departments. These are passed into our objDatabase.Parameters object for each department.

Each of the remaining variables defined stores a reference to the OraSession, OraDatabase, and OraDynaset objects respectively. Again we use a variable called varSQL to store our nicely formatted SQL statement:

The only object that we have to create ourselves explicitly is the OraSession object as all other objects are created from other existing objects. We use the familiar Server.CreateObject to create an instance of the OO4O component whose internal ProgID is OracleInProcServer.XOraSession.

The OpenDatabase function returns an OraDatabase object, which in our case is the scott account on the Oracle8_dev service. OpenDatabase is called in the following way:

Where db_name is the service name to connect to, connectstring is the standard Oracle connection format of "username/password", and options is a collection of bit flags to indicate the mode in which the database should be opened. In our case we are passing in 0 to indicate that we want the database to be opened in the default mode, which means that any fields that we do not explicitly set a value for using the AddNew or Edit methods will be set to Null (this will incidentally override any server column default values!), rows will be locked as soon as the Edit method is called, and that non-blocking SQL  functionality will not be used. A non-blocking call provides the same concept as an asynchronous ADO call in which the calling application does not have to wait until the server completes a request before continuing.

Just for our benefit we write out some information about the version of OO4O and the Oracle server that we are connecting to:

varSQL stores the SQL statement to execute using an Oracle bind variable, :deptnoparam. As we hinted at above, using bind variables can improve the performance of data access when you have the same SQL statement to execute, but need to alter a parameter. Each time Oracle executes a statement it has to go through the statement to understand how it should be executed. By using the bind variable, we can get Oracle to parse it only once. Remember, though, that this only exists for the life of your OraDatabase object – which should only be kept around for the life of the script and not in an ASP Session variable .

Before we can tell Oracle about the bind variable we need to add it to the OraDatabase object's Parameters collection  using the Add command, which is called in this way:

The Name argument is a string that represents the name of the parameter to add, and it must match that of the bind variable defined in the SQL statement, Value is a variant, IOType indicates the direction of this parameter:

IOType is much like the adDirection enumerator used in the ADO Command object's Parameters collection to set the direction of SQL parameters.

In our example we passed in a value of 0 as a default because we don't actually have a value to use and a 1 to indicate that is for input use only.

Now it's just a case of calling the OraDatabase.CreateDynaset to  retrieve an OraDynaset object. CreateDynaset is called in this way:

Where sql_statement is the SQL to execute and options contains a bit flag of settings to define how the OraDynaset object behaves, such as whether it is updateable, or to cache data on the client. In our case we're passing in the hex value &H4 to indicate that it should be opened in read-only mode as we only want to display some data.

Behind the scenes, Oracle parses the statement ready for execution. It doesn't actually fetch any data until we set the deptnoparam parameter's Value property in OraDatabase.Parameters collection and ask OO4O to refresh the dynaset using OraDynaset.Refresh:

Here we can see our VBScript procedure CreateEmployeeTable being  called to set the value for each of the department number parameters to create a nicely formatted table, as defined below:

As always, we close down all of our objects as soon as possible in order to save server resources. Now to the CreateEmployeeTable function:

CreateEmployeeTable is passed the required department code, which it binds to the original SQL statement's bind variable deptnoparam. Each time you specify a new value for a bind variable, you must call the Refresh method to fetch the new data.

Calling Refresh cancels all record edit operations that may have been pending through the Edit and AddNew methods, executes the SQL statement, and then moves to the first row of the resulting dynaset.

So now we create the TABLE tag and loop through the records until we come to the end of file, EOF, exactly as we would with the ADO Recordset.EOF property.

The Fields collection returns a named list of columns for the current record, which we use to create a new table row for each record. We haven't shown it, but as with an ADO Recordset object, the Fields property is the default value, and for a Field object, the Value is the default so varOraDynaset.Fields ("sal").Value is equal to varOraDynaset("sal"). For better performance you should use the latter.

As with ADO, the MoveNext method moves to the next record.

Getting Meta Data

For our final look at OO4O we will use the OraMetaData object found in version 8.1 to retrieve a list of attributes for the emp table within the scott schema. As we said earlier, OraMetaData can retrieve all sorts of information about a schema, by calling the OraDatabase object's Describe("schema_name") function to return an OraMetaData object. The OraMetaData object returns a collection of OraMDAttribute objects that actually describe the data found and contains the following methods and properties :

To make things slightly complicated the OraMDAttribute object has a property called IsMDObject  that returns True if the Value property contains yet another OraMetaData object. This allows you to recursively search through a hierarchy of OraMetaData objects. If it returns False then Value contains a string representation of the item .

The following code produces the screenshot shown above. We start off with the usual header:

The cTableName constant contains the name of the table that we want to describe. As usual we are using the OraSession object to hold a reference to OO4O and OraDatabase to connect to our Oracle8i server:

objOraMetaData is used to store our top-level OraMetaData object returned by the Describe function and objOraMDAttribute stores the item name "ColumnList" from the objOraMetaData object, which represents the list of columns in the emp table. The actual Value for objOraMDAttribute is stored in objColumnList.

We connect to the Oracle8i_dev service and call the OraDatabase object's Describe function  to return our first OraMetaData object to objOraMetaData for the emp table. objOraMetaData will contain a collection of OraMDAttribute items, so we pass in ColumnList to retrieve the list of column names.

Even though it's not strictly necessary with this example, we check the IsMDObject property to see if the Value property contains another objMetaData object. In our case, it will always be True, since we asked for the list of column names, which is another objMetaData object.

To make the code easier to read and run quicker we transfer the Value property into a new variable objColumnList:

Now it's just a case of moving through the zero-based collection of column details and writing out the value for each item. We finish off by shutting down our objects:

That covers our introduction into the common objects you'll come across in OO4O. OO4O offers a rather flexible approach to connecting to an Oracle database and also provides us with additional PL/SQL functionality not available through ADO, such as the use of input arrays for stored procedures.

So, which one should you use in your ASP applications?  Unfortunately, there is no simple answer. Each method claims to be faster than the next whilst providing support for additional functionality. It really does pay to try each of the methods in your own environment before committing to any particular one.