Is there a limeit on the no. of input output parameters in ADO calling Oracle Stored Proc

[adohelp]

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

[Michael McGrath]

Yes,
I have found the same problem. In the work that I do I must return data
using PL/SQL as well. The only work around was to concatenate the data placing
it a large varchar2 data type. On the Client side I would break it apart
again. Since I had to do that I only returned one table array.

Mike


"adohelp" <vivekvipul@hotmail.com> wrote:
>
>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
>
>