Oracle: Recordsets from Stored Procedures using RE

Oracle: Recordsets from Stored Procedures using RE

--------------------------------------------------------------------------------

Description : For people who might be interested in the new feature of the latest OLE DB providers that allows one to return recordsets from stored procedures via ADO, here are the detailed test steps I used:

1. For testing the Microsoft OLE DB Provider For Oracle version 2.5:

Download the latest version of MDAC 2.5 (mdac_typ.exe) from
http://www.microsoft.com/data/download_250rtm.htm
Install the New MDAC by following all prompts. Restart your computer.
Do the necessary stuff To create a SQL*NET connect String On your NT/IIS server. I was using a Local database so I skipped this step.
Create a PL/SQL package called employees_msft under the scott schema using the code below (my intention was To return all employees In a particular department, using the famous emp table):
CREATE Or Replace PACKAGE employees_msft
As
Type empcur Is REF CURSOR;

PROCEDURE GetEmpRecords(indeptno In NUMBER,
p_errorcode OUT NUMBER,
p_cursor OUT empcur);
End employees_msft;
/

CREATE Or Replace PACKAGE BODY employees_msft
As
PROCEDURE GetEmpRecords(indeptno In NUMBER,
p_errorcode OUT NUMBER,
p_cursor OUT empcur)
Is
BEGIN
p_errorcode := 0;
Open p_cursor For Select * FROM emp WHERE deptno = indeptno ORDER BY
ename;
EXCEPTION
WHEN OTHERS Then
p_errorcode := SQLCODE;
End GetEmpRecords;
End employees_msft;
/

Then create the following ASP file:

<%@LANGUAGE="VBSCript"%>
<% Option Explicit
Response.Buffer = True
%>

<% Dim objConn, objCmd, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRs = Server.CreateObject("ADODB.RecordSet")
Dim strConnection
' Use the correct SQL*NET connect string!!!!
strConnection = "PROVIDER=MSDAORA;DATA SOURCE=beq-local;USER ID=scott;PASSWORD=tiger;"
objConn.Open strConnection
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "employees_msft.getemprecords"
' I used dept 30 as an example
objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "30")
objCmd.Parameters.Append objCmd.CreateParameter("param2", adVarChar, adParamOutput, 10)
Set objRs = objCmd.Execute
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<B>ADO REF CURSOR DEMO Using the Microsoft OLE DB Provider For Oracle2.5</B>
<P>
<TABLE BORDER="1">
<TR>
<TH>Emp No.</TH>
<TH>Emp Name</TH>
</TR>
<% Do While Not objRs.EOF %>
<TR>
<TD><%=objRs.Fields("empno").Value%></TD>
<TD><%=objRs.Fields("ename").Value%></TD>
</TR>
<% objRs.MoveNext
Loop
objRs.Close
Set objRs = Nothing
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

* See the result In your browser!

2. For testing the Oracle Provider For OLE DB version 8.1.5.2.0 Beta:

Download the provider at http://technet.oracle.com/tech/nt/ole_db/
Install Oracle 8.1.5 Client software from the Oracle 8i CD On your IIS server. This Is absolutely necessary For the provider To work, even though you may be connecting To earlier versions of Oracle (I used Oracle 8.0.5.2.1).
Install the provider.
Again create a SQL*NET connect String For your database.
Create the following PL/SQL package under the scott schema:
CREATE Or Replace PACKAGE employees_orcl
As
Type empcur Is REF CURSOR;

PROCEDURE GetEmpRecords(indeptno In NUMBER,
p_cursor OUT empcur, -- Notice the REF CURSOR
-- parameter Is In the middle!
p_errorcode OUT NUMBER);

End employees_orcl;
/

CREATE Or Replace PACKAGE BODY employees_orcl
As
PROCEDURE GetEmpRecords(indeptno In NUMBER,
p_cursor OUT empcur,
p_errorcode OUT NUMBER)
Is
BEGIN
p_errorcode := 0;
Open p_cursor For Select * FROM emp WHERE deptno = indeptno ORDER BY
empno;
EXCEPTION
WHEN OTHERS Then
p_errorcode := SQLCODE;
End GetEmpRecords;
End employees_orcl;
/

* Create the following ASP page:

<%@LANGUAGE="VBSCript"%>
<% Option Explicit
Response.Buffer = True
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<% Dim objConn, objCmd, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRs = Server.CreateObject("ADODB.RecordSet")
Dim strConnection
'Use the correct SQL*NET connect string!
strConnection="PROVIDER=OraOLEDB.Oracle;DATA SOURCE=beq-local;USER
ID=scott;PASSWORD=tiger;PLSQLRSet=1"
objConn.Open strConnection
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "employees_orcl.getemprecords"
objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar,
adParamInput, 10, "30")
objCmd.Parameters.Append objCmd.CreateParameter("param3", adVarChar,
adParamOutput, 10)
Set objRs = objCmd.Execute
%>
<p>
<B>ADO REF CURSOR DEMO Using the Oracle Provider For OLE DB 8.1.5.2.0
Beta</B>
<P>
<TABLE BORDER="1">
<TR>
<TH>Emp No.</TH>
<TH>Emp Name</TH>
</TR>
<% Do While Not objRs.EOF %>
<TR>
<TD><%=objRs.Fields("empno").Value%></TD>
<TD><%=objRs.Fields("ename").Value%></TD>
</TR>
<% objRs.MoveNext
Loop
objRs.Close
Set objRs = Nothing
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

* Again, see the result In the browser!

Folks, I hope you are As excited As I am about the New feature offered by the New OLE DB providers. I know a lot of us had asked For it For so long. They (MSFT And ORCL) finally delivered. Start To use these New providers!

--------------------------------------------------------------------------------