Calling a 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();

[RobinS]

This looks like an Oracle error. I'd try posting this to an Oracle
newsgroup.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
"C#_Beginner" <c0*****@allstate.comwrote in message
news:11*********************@a75g2000cwd.googlegro ups.com...

>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();