Oracle has released version 8.1.6 of its own provider, Oracle Provider for OLE DB. This provider has a class name of OraOLEDB.Oracle that is used when defining your ADO connection string. It supports the same set of Oracle data types as Microsoft's OLE DB Provider for Oracle with the additional support for the binary object types BLOB, CLOB, NCLOB, and BFILE, but as with Microsoft's provider, it also does not provide support for the Oracle8i object data types .

This provider gives us pretty much the same level of functionality as Microsoft's, except that it supports the use of Oracle reference cursors so that we can return back an ADO Recordset object from a stored procedure. A reference cursor is a pointer to a memory location that can be passed between different PL/SQL clients, thus allowing query result sets to be passed back and forth between clients.

A reference cursor is a variable type defined using the PL/SQL TYPE  statement within an Oracle package, much like a PL/SQL table:

Here, ref_type_name is the name given to the type and return_type represents a record in the database. You do not have to specify the return type as this could be used as a general catch-all reference cursor. Such non-restrictive types are known as weak, whereas specifying the return type is restrictive, or strong. The following example uses %ROWTYPE to define a strong return type that represents the record structure of the emp table:

So let's jump straight to an example. We'll create a new Oracle package that contains a single procedure, EmployeeSearch, which returns a list of matching employee names. From your SQL editor, execute the following code to create the package specification:

We've created a new type called empcur that returns a weak reference cursor that we use as an output parameter to the EmployeeSearch procedure. Now we need the package body :

This code is very similar to our previous stored procedure, except that we don't need to transfer each column in distinct PL/SQL tables, as the reference cursor, o_EmpCursor, is returned back to the client. The Oracle Provider for OLE DB converts any parameters that reference cursors into an ADO Recordset for us – but only if we add PLSQLRSet=1 to our connection string, which we'll cover next .

The actual ASP is very similar to our previous example so we'll just concentrate on the sections that
are different :

So far it's just the same, except that we define a new variable, objNameParam, that we'll use as an ADO Parameter object to pass in the search name entered.

Here we tell ADO to use the Oracle Provider for OLE DB, OraOLEDB.Oracle, and we set the PLSQLRSet attribute to tell the provider that it should parse the PL/SQL stored procedures to determine if any parameters  return a record set. OraOLEDB can only return one recordset per stored procedure. If you call a stored procedure that returns more than one recordset then OraOLEDB will only return the first argument of a ref cursor type.

If you omit the PLSQLRSet attribute, or you set it to 0, then you'll receive the following Oracle error:

The rest of the code goes as follows:

Although our stored procedure has two parameters, the search name and the reference cursor that is returned, you must not bind the reference cursor as a parameter using the ? attribute when using OraOLEDB, so we've included only one ? character to represent the Name input parameter .

The ADO Parameter object, objNameParam, is created using the Command object's CreateParameter function. CreateParameter is called in the following way:

objNameParam is declared as an adBSTR type because this maps to Oracle's VARCHAR2 data type. Once we've created the Parameter we need to add it to the Command object's Parameters collection using the Append method .

Finally we call the Execute function to return a Recordset object that represents the result set from the o_EmpCursor reference cursor parameter. That's all there is to it. We can then navigate through the Recordset object as usual.

It's worth remembering that if you try to call the stored procedure using the Parameters
collection directly:

you'll get the following runtime error:

Therefore you must use the CreateParameter function.

That wraps up our look at retrieving ADO Recordset objects from Oracle stored procedures. As you've seen, we have two choices: PL/SQL tables with the Microsoft ODBC for Oracle Driver or reference cursors with Oracle's Oracle Provider for OLE DB. On the face of it, the use of PL/SQL tables does appear rather convoluted in comparison to the ease of reference cursors. Both are relatively inefficient in terms of server performance and the Oracle Provider for OLE DB has been regarded as rather buggy. Again, it's your choice; it's difficult to define what each can and can't do. As ever, you should investigate how both methods perform in your own environment, looking at response times along with CPU  and memory usage.