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.
Comments