create or replace package pkgsearch as
type mycursor is ref cursor;
procedure acc_search(id in varchar,cur out mycursor);
end pkgsearch;

CREATE OR REPLACE PACKAGE BODY pkgsearch AS
PROCEDURE acc_search (id in varchar,cur out mycursor) IS
BEGIN
OPEN cur FOR SELECT *
FROM account
WHERE accno = 'id';
end acc_search;
end pkgsearch;

dim con as new ADODB.connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim str As String

con.open "dsn=xxxxx;uid=xxxxxx;pwd=xxxxxx"

With cmd
.ActiveConnection = con
.CommandText = "pkgsearch.acc_search"
.CommandType = adCmdStoredProc
Set param = .CreateParameter("id", adVarChar, adParamInput, 10, Text1.Text)
.Parameters.Append param
End With

Set rs = cmd.Execute

Text1.Text = rs.Fields(0).Value
Text2.Text = rs.Fields(1).Value

sql = "exec STORED PROCEDURE HERE"
Set RS = ExecuteQuery(sql)
RS.MoveFirst

While not EOF
DO STUFF
RS.MoveNext
Wend

Public connect As New ADODB.Connection
Public cmd As ADODB.command

    Public Function ConnectToDBOpen() As Boolean
        If (connect.State = adStateOpen) Then
            connect.Close
        End If
        connect.CursorLocation = adUSeClient
        connect.Open "CONNECTION INFO HERE"

        ConnectToDBOpen = True
    End Function


    Public Function DisconnectFromDB()

        connect.Close

    End Function


    Public Function ExecuteQuery(ByVal sqlQuery As String) As ADODB.Recordset
        Dim rsOut As ADODB.Recordset
        Set rsOut = New ADODB.Recordset
        Dim recordsAffected As Long


        If ConnectToDBOpen Then
            Dim command
            Set command = New ADODB.command

            With command
                .ActiveConnection = connect
                .CommandText = sqlQuery
                .CommandType = adCmdText
            End With

            rsOut.Open command.Execute(recordsAffected)

            Set ExecuteQuery = rsOut
            Set command = Nothing
            Call DisconnectFromDB
        End If
    End Function