For the behemoth Oracle, Asp is really stretched to use. Especially when it comes to returning a result set (Recordset), it makes many people have difficulties. After exploration and practice, I wrote my own solution below:
illustrate:
The version of my Oracle client is oracle 9i. When installing the client, you cannot use the default installation. You must customize it, and then select all OLEDB related content and install it. Otherwise, you will not find it when you go to the Provider below.
Copy the code code as follows:
<%@Language=VBSCRIPT CodePage=936 LCID=2052%>
<%Option Explicit%>
<!-- #include file=../adovbs.inc -->
<%
Dim cnOra
Function Connect2OracleServer
Dim conStr
conStr = Provider=MSDAORA.Oracle;Data Source=xx;User Id=?;Password=?
Set cnOra = Server.CreateObject(ADODB.Connection)
cnOra.CursorLocation = adUseClient '=3
On Error Resume Next
cnOra.Open conStr
Connect2OracleServer = (Err.Number = 0)
End Function
Sub DisconnectFromOracleServer
If Not cnOra is Nothing Then
If cnOra.State = 1 Then
cnOra.Close
End If
Set cnOra = Nothing
End If
End Sub
Sub Echo(str)
Response.Write(str)
End Sub
Sub OutputResult
Dim cmdOra
Dimrs
Set cmdOra = Server.CreateObject(ADODB.Command)
With cmdOra
.CommandType = adCmdText '=1
.CommandText = {call PKG_TEST.GetItem(?,?)}
.Parameters.Append cmdOra.CreateParameter(p1, adNumeric, adParamInput, 10, 1)
.Parameters.Append cmdOra.CreateParameter(p2, adVarChar, adParamInput, 10, xx)
.ActiveConnection = cnOra
Set rs = cmdOra.Execute
If Not rs.Eof Then
While Not rs.Eof
Echo rs(0)
Echo --
Echo rs(1)
Echo <br>
rs.MoveNext
Wend
rs.Close
End If
Setrs=Nothing
Set cmdOra = Nothing
End With
DisconnectFromOracleServer
End Sub
If Connect2OracleServer Then
OutputResult
Else
Response.Write(Err.Description)
End If
%>
The following is Oracle's sql script
----------------------------------SQL Script---------- --------------------------
--Build package---------------------------------
Copy the code code as follows:
Create Or Replace Package PKG_TEST
IS
TYPE rfcTest IS REF CURSOR;
PROCEDURE GETITEM
( p1 IN NUMBER,
p2 IN VARCHAR2,
p3 OUT rfcTest
);
END; -- Package Specification PKG_TEST
-------------------------------------------------- -
--Building package body----------------------------------
Create Or Replace Package Body PKG_TEST
IS
PROCEDURE GETITEM
( p1 IN NUMBER,
p2 IN VARCHAR2,
p3 OUT rfcTest
)
IS
BEGIN
OPEN p3 FOR
SELECT * FROM tablename WHERE id = p1 AND name=p2 AND rownum < 10;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END;
END; -- Package Body PKG_TEST