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.