vb 调用 Oracle 函数返回数据集的例子

　　 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