VBA調用帶遊標傳回值的ORACLE預存程序

【解決方案一】：
On Error GoTo 11
Dim hisCNN As ADODB.Connection
Dim hisRST As ADODB.Recordset
Dim sSQL As String
Dim iZDS As Integer
Dim sBT As String
Dim sTEMP As String
Dim hisCMD As New ADODB.Command
Dim Para1 As New ADODB.Parameter
Dim Para2 As New ADODB.Parameter
Dim Para3 As New ADODB.Parameter

Set hisCNN = New ADODB.Connection
hisCNN.CursorLocation = adUseClient

hisCNN.Open ＂Provider=MSDASQL.1;Persist Security Info=False;User ID=i_pathology;pwd=i_pathology;Data Source=aaa＂
MsgBox "串連資料庫成功！"
//aaa是ＯＤＢＣ名

Set hisCMD.ActiveConnection = hisCNN
hisCMD.CommandType = adCmdStoredProc
hisCMD.CommandText = "zhi.F_get_sick_info"

Set Para1 = hisCMD.CreateParameter("as_patient_id", adVarChar, adParamInput, 100, "00")
Para1.Value = "3333333"
hisCMD.Parameters.Append Para1

Set Para2 = hisCMD.CreateParameter("as_io_flag", adVarChar, adParamInput, 100, "1")
Para2.Value = "1"
hisCMD.Parameters.Append Para2

MsgBox "hiscmd"
Set hisRST = hisCMD.Execute

MsgBox "得到記錄集！"

如果把串連改成
Provider=msdaora.1;Data Source=服務名;User ID=i_pathology;Password=i_pathology;
的話，提示　參數數量和類型不對．第三個參數是　遊標，我查了，應該不用賦值的．

【解決方案二】：
PL/SQL 代碼:
CREATE OR REPLACE PACKAGE "SCOTT"."PKG_TEST" AS
       TYPE myrcType IS REF CURSOR;
       FUNCTION get(strbarcode VARCHAR) RETURN myrcType;
END pkg_test;

CREATE OR REPLACE PACKAGE BODY "SCOTT"."PKG_TEST" AS
    FUNCTION get(strbarcode IN VARCHAR) RETURN myrcType IS
      rc myrcType;
    BEGIN
      OPEN rc FOR strbarcode;
      RETURN rc;
    END get;
END pkg_test;
--------------------------------------------------------------------------------------------------------
VB 代碼:
Private Sub Command1_Click()
On Error GoTo cursorErr:
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=oraAny;Extended Properties=PLSQLRSet=1"
    cnn.Open
    
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "{CALL scott.pkg_test.get(?)}"
        .Parameters.Append .CreateParameter("strBarCode", adVarChar, adParamInput, 100, "SELECT * FROM TAB")
    End With
    
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    Set rst.Source = cmd
    rst.Open
    
    MsgBox rst.RecordCount

    Set rst = Nothing
    Set cmd = Nothing
    Exit Sub

cursorErr:
    Set cmd = Nothing
    Set rst1 = Nothing
    MsgBox Err.Description
End Sub