Calling an Oracle store procedure using C#

[C#_Beginner]

I was wondering if someone can help me out. I'm getting the following
error:

Message: ORA-06550: line 1, column 7: PLS-00306: wrong number or
types
of arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored


Here is the stored procedure


CREATE OR REPLACE package admin_package
AS


TYPE CHAR_ARRAY IS TABLE OF VARCHAR2(50) INDEX BY
BINARY_INTEGER;


TYPE GRP_IDTABLETYPE IS TABLE OF SEC_GRP.GRP_ID%TYPE
INDEX BY BINARY_INTEGER;
TYPE BRANCHTABLETYPE IS TABLE OF
SEC_CONSOLD.CONSOLIDATED_BRANCH_CODE
%TYPE
INDEX BY BINARY_INTEGER;
TYPE PRODUCERTABLETYPE IS TABLE OF
SEC_CONSOLD.CONSOLIDATED_PRODUCER_CODE%TYPE
INDEX BY BINARY_INTEGER;
TYPE SUBPRODUCERTABLETYPE IS TABLE OF
SEC_CONSOLD.CONSOLIDATED_SUBPRODUCER_CODE%TYPE
INDEX BY BINARY_INTEGER;
TYPE READINDTABLETYPE IS TABLE OF SEC_GRP_RIGHTS.READ_IND
%TYPE
INDEX BY BINARY_INTEGER;
TYPE WRITEINDTABLETYPE IS TABLE OF SEC_GRP_RIGHTS.WRITE_IND
%TYPE
INDEX BY BINARY_INTEGER;
TYPE LEVELINDTABLETYPE IS TABLE OF SEC_GRP_RIGHTS.LEVEL_IND
%TYPE
INDEX BY BINARY_INTEGER;
TYPE GRP_NAMETABLETYPE IS TABLE OF SEC_GRP.GRP_NAME%TYPE
INDEX BY BINARY_INTEGER;


PROCEDURE GET_RIGHTS2 (SGRPID IN SEC_GRP.GRP_ID
%TYPE,
SCONSOLDBRANCH OUT BRANCHTABLETYPE,
SCONSOLDPRODUCER OUT
PRODUCERTABLETYPE,
SCONSOLDSUBPRODUCER OUT
SUBPRODUCERTABLETYPE,
SREADIND OUT READINDTABLETYPE,
SWRITEIND OUT
WRITEINDTABLETYPE,
SLEVELIND OUT
LEVELINDTABLETYPE)
IS
iIndex BINARY_INTEGER := 0;
CURSOR GRCUR (SGRPID IN SEC_GRP.GRP_ID%TYPE) IS
SELECT CONSOLIDATED_BRANCH_CODE,
CONSOLIDATED_PRODUCER_CODE,
CONSOLIDATED_SUBPRODUCER_CODE, READ_IND, WRITE_IND, LEVEL_IND
FROM SEC_GRP_RIGHTS
Where GRP_ID = SGRPID ;
BEGIN
FOR GetRec IN GRCUR(SGRPID)
LOOP
iIndex := iIndex + 1;
SCONSOLDBRANCH(iIndex) :=
GetRec.CONSOLIDATED_BRANCH_CODE;
SCONSOLDPRODUCER(iIndex) :=
GetRec.CONSOLIDATED_PRODUCER_CODE;
SCONSOLDSUBPRODUCER(iIndex) :=
GetRec.CONSOLIDATED_SUBPRODUCER_CODE;
SREADIND(iIndex) := GetRec.READ_IND;
SWRITEIND(iIndex) := GetRec.WRITE_IND;
SLEVELIND(iIndex) := GetRec.LEVEL_IND;
END LOOP;
END GET_RIGHTS2;
END;
/


and here is the C# code


OracleCommand myCMD1 = new OracleCommand();
myCMD1.Connection = Oraclecon1;
myCMD1.CommandText =
"SECURITY_PROC.ADMIN_PACKAGE.GET_RIGHTS2";
myCMD1.CommandType =
CommandType.StoredProcedure;


OracleParameter param1 =
myCMD1.Parameters.Add("SGRPID", null);
OracleParameter param2 =
myCMD1.Parameters.Add("SCONSOLDBRANCH",
null);
OracleParameter param3 =
myCMD1.Parameters.Add("SCONSOLDPRODUCER",
null);
OracleParameter param4 =
myCMD1.Parameters.Add("SCONSOLDSUBPRODUCER", null);
OracleParameter param5 =
myCMD1.Parameters.Add("SREADIND", null);
OracleParameter param6 =
myCMD1.Parameters.Add("SWRITEIND", null);
OracleParameter param7 =
myCMD1.Parameters.Add("SLEVELIND", null);


param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Output;
param3.Direction = ParameterDirection.Output;
param4.Direction = ParameterDirection.Output;
param5.Direction = ParameterDirection.Output;
param6.Direction = ParameterDirection.Output;
param7.Direction = ParameterDirection.Output;


param1.Value = strGrpId;
param1.Size = 8000;
param2.Size = 8000;
param3.Size = 8000;
param4.Size = 8000;
param5.Size = 8000;
param6.Size = 8000;
param7.Size = 8000;


try
{
Oraclecon1.Open();
OracleDataReader myReader =
myCMD1.ExecuteReader();
int x;
int count;
count = 0;
while (myReader.Read())
{
for (x = 0; x <=
myReader.FieldCount - 1; x++)


Response.Write(myReader.GetValue(x));


Console.WriteLine();
count += 1;


}
//OracleDataAdapter da = new
OracleDataAdapter(myCMD);
//da.Fill(ds);
//DG_ShowUserInfo.DataSource = ds;
//DG_ShowUserInfo.DataBind();


}
catch (OracleException ex)
{
string errorMessage = "Code: " +
ex.Code + "<br>" +
"Message: " + ex.Message;


Response.Write(errorMessage);
}
Oraclecon1.Close();

 

[David Browne]

I was wondering if someone can help me out. I'm getting the following
error:

Message: ORA-06550: line 1, column 7: PLS-00306: wrong number or
types
of arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'GET_RIGHTS2' ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'GET_RIGHTS2'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored

This procedure uses PL/SQL collection types. You'll need to use ODP.NET to
call this procedure directly. Here's an article:

Using PL/SQL Associative Arrays
http://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html

And the reference

PL/SQL Associative Array Binding
ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By
Tables) binding.
.. . ..

http://download-east.oracle.com/docs/cd/B19306_01/win.102/b14307/featOraCommand.htm#sthref223


David

 

[C#_Beginner]

This procedure uses PL/SQL collection types. You'll need to use ODP.NET to
call this procedure directly. Here's an article:

Using PL/SQL Associative Arrayshttp://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html

And the reference

PL/SQL Associative Array Binding
ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By
Tables) binding.
. . ..

http://download-east.oracle.com/docs/cd/B19306_01/win.102/b14307/feat...

David- Hide quoted text -

- Show quoted text -

Thanks for the info i figure that part, however i have another
problem. The SP pulls information based on an input parameter, well
sometimes the data returned varies the data may conatin 1 record or 3.
I'm using the ArrayBindSize and setting it up to 3 but i get an error
when the data returned is 1. The error i get is an array out of
bounds.

Can you help.