對於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