Asp Oracle存储过程返回结果集的代码

对于 Oracle 这个庞然大物，Asp使用起来，确实是捉襟见肘的 。 尤其是要返回结果集(Recordset)的情况，更是让很多人犯难。 经过摸索和实践，我把自己的解决方法，写在下面： 说明： 我的Oracle客户端的版本是 oracle 9i， 安装client端的时候，不能用默认安装，一定要自定义， 然后选择所有 OLEDB 相关的内容，都装上，否则到下面的 Provider 的时候，会找不到。 

 代码如下:<%@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 Dim rs 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 Set rs = Nothing Set cmdOra = Nothing End With DisconnectFromOracleServer End Sub If Connect2OracleServer Then OutputResult Else Response.Write(Err.Description) End If %>下面是 Oracle 的 sql 脚本 --------------------------------------SQL Script---------------------------------- --建包-----------------------------------

代码如下: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 --------------------------------------------------- --建包体----------------------------------- 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