I have been trying to return a recordset from Oracle using Microsoft ODBC
for Oracle and defining each output parameter as a PL/SQL table. I can't
use a ref cursor since I need to manipulate the resultset in the stored procedure.
( can't do it on the front end due to security constraints) I am able to
successfully call the procedure fromn SQl/PLus but when calliong from VB
using ADO 2.7 I get an error

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful
completion

I am able to successfully get the results the procedure if I have a total
of 7 parameters (input and output parameters) but it fails if I have more
than 7 parameters no matter what type of parameter I use

The following is the Package


CREATE OR REPLACE PACKAGE PAYROLL.PBStest
AS

TYPE tbl_POLICY_UNIT_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE tbl_POLICY_SUFFIX_CDE IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
TYPE tbl_POLICY_YEAR IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
TYPE tbl_GROUP_IDN IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
TYPE tbl_policy_idn IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;


PROCEDURE SP_TestPayRoll(
i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,

i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,

i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,

i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,

o_group_idn OUT tbl_GROUP_IDN,
o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
o_policy_year OUT tbl_POLICY_YEAR,
o_policy_idn OUT tbl_policy_idn
);
END PBStest;


CREATE OR REPLACE PACKAGE BODY PAYROLL.pbstest
AS
/************************************************************
Constants
*************************************************************/
c_blank_1 CONSTANT CHAR (1) := ' ';
c_blank_3 CONSTANT CHAR (3) := ' ';
v_split_dte BOOLEAN DEFAULT FALSE;
varrecordcount NUMBER DEFAULT 0;
v_splitdte BOOLEAN DEFAULT FALSE;
PROCEDURE SP_TestPayRoll (
i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,

i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,

i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,

i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,

o_group_idn OUT tbl_GROUP_IDN,
o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
o_policy_year OUT tbl_POLICY_YEAR,
o_policy_idn OUT tbl_policy_idn
)

IS

CURSOR policy_cur
IS
SELECT
pol.group_idn,
pol.policy_unit_num,
pol.policy_year,
pol.policy_idn
FROM pbs_policy_information pol
WHERE pol.policy_unit_num = i_policy_unit_num
AND pol.group_idn = i_group_idn
AND pol.policy_year = i_policy_year;

reccount NUMBER DEFAULT 0;
BEGIN
FOR policyrec IN policy_cur
LOOP
reccount := reccount + 1;
o_group_idn (reccount) := policyrec.group_idn;
o_policy_unit_num (reccount) := policyrec.policy_unit_num;
o_policy_year (reccount) := policyrec.policy_year;

o_policy_idn (reccount) := policyrec.policy_idn;

END LOOP;

END SP_TestPayRoll;
END pbstest;
/

And I am invoking it in the following manner:

Function getPayroll()
Dim cmdPayroll As New ADODB.Command
Dim strCmd As String
Dim iCount As Integer
Dim rsPayroll As Recordset

On Error GoTo ErrHandler:
Const cConnection = "SERVER=myserver;driver={Microsoft ODBC for Oracle};UID=XXXXX;PWD=XXXXXX;"

Dim objConnection As New ADODB.Connection

'* Create the connection object

objConnection.ConnectionString = cConnection
objConnection.CursorLocation = 3 '* adUseClient
objConnection.Open

Const cProcName = "{call payroll.pbstest.sp_testpayroll(?,?,?,?,{resultset
100,o_group_idn,o_policy_unit_num,o_policy_year,o_policy_idn})}"

cmdPayroll.CommandText = cProcName


cmdPayroll.CommandType = adCmdText

cmdPayroll.ActiveConnection = objConnection
'

cmdPayroll.Parameters(0).Type = adVarChar
cmdPayroll.Parameters(0).Direction = 1 '* adParamInput
cmdPayroll.Parameters(0).Value = CStr(" ") '* Set the input param
'

cmdPayroll.Parameters(1).Type = adDouble
cmdPayroll.Parameters(1).Direction = 1 '* adParamInput
cmdPayroll.Parameters(1).Value = 1574316 '* Set the input param '

cmdPayroll.Parameters(2).Type = adVarChar
cmdPayroll.Parameters(2).Direction = 1 '* adParamInput
cmdPayroll.Parameters(2).Value = "2000" '* Set the input param '

cmdPayroll.Parameters(3).Type = adChar
cmdPayroll.Parameters(3).Direction = 1 '* adParamInput
cmdPayroll.Parameters(3).Value = CStr(" ")

Set rsPayroll = cmdPayroll.Execute()
rsPayroll.MoveFirst
Debug.Print rsPayroll.GetString()

Exit Function

ErrHandler:
Dim x As String
x = Err.Description
End Function