Oracle8 and Oracle8i

Reference Cursors

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:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

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:

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;

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:

CREATE OR REPLACE PACKAGE Employee_RefCur_pkg
AS
   TYPE empcur IS REF CURSOR;
   PROCEDURE EmployeeSearch(i_EName     IN  VARCHAR2,
                           o_EmpCursor OUT empcur);
END Employee_RefCur_pkg;

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 :

CREATE OR REPLACE PACKAGE BODY Employee_RefCur_pkg
AS 
   PROCEDURE EmployeeSearch(i_EName     IN  VARCHAR2,
                            o_EmpCursor OUT empcur)
   IS
   BEGIN
      OPEN o_EmpCursor FOR       
         SELECT emp.empno, emp.ename, emp.job,
             emp.sal, dept.dname, dept.loc
         FROM emp, dept
         WHERE ename LIKE '%' || i_EName || '%'
            AND emp.deptno = dept.deptno
         ORDER BY UPPER(emp.ename);
   END EmployeeSearch;
END Employee_RefCur_pkg;

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 .


Let's have a look at the results page that calls this stored procedure:

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

<%
Dim strSearchName
Dim objConnection
Dim objCommand
Dim objRecordSet
Dim objNameParam
Dim varEmpNo
strSearchName = Request.Form("txtSearchName")
If strSearchName = "" Then strSearchName = "%"
Set objConnection = Server.CreateObject("ADODB.Connection")

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.

With objConnection
  .ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                      "Data Source=Oracle8i_dev;" & _
                      "User ID=scott;" & _
                      "Password=tiger;" & _
                      "PLSQLRSet=1;"
  .Open
  Response.Write "ADO Provider=" & .Provider & "<P>"
End With

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:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLOYEESEARCH' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The rest of the code goes as follows:

Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
   Set .ActiveConnection = objConnection
   .CommandText = "{call Employee_RefCur_pkg.EmployeeSearch(?)}"
   Set objNameParam = .CreateParameter("SearchName", adBSTR, _
                                        adParamInput, , strSearchName)
   .Parameters.Append objNameParam
   Set objRecordSet = .Execute()
End With

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:

Set parameter = command.CreateParameter(Name, Type, Direction, Size, Value)

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:

   .CommandText = "{call Employee_RefCur_pkg.EmployeeSearch(?)}"
   .Parameters(0).Type = adBSTR
   .Parameters(0).Direction = adParamInput
   .Parameters(0).Value = strSearchName

you'll get the following runtime error:

The provider cannot derive parameter info and SetParameterInfo has not been called

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.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The greatest performance improvement of all is when a system goes from not-working to working.” - John Ousterhout