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.CON SOLIDATED_BRANC H_CODE
%TYPE
INDEX BY BINARY_INTEGER;
TYPE PRODUCERTABLETY PE IS TABLE OF
SEC_CONSOLD.CON SOLIDATED_PRODU CER_CODE%TYPE
INDEX BY BINARY_INTEGER;
TYPE SUBPRODUCERTABL ETYPE IS TABLE OF
SEC_CONSOLD.CON SOLIDATED_SUBPR ODUCER_CODE%TYP E
INDEX BY BINARY_INTEGER;
TYPE READINDTABLETYP E IS TABLE OF SEC_GRP_RIGHTS. READ_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE WRITEINDTABLETY PE IS TABLE OF SEC_GRP_RIGHTS. WRITE_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE LEVELINDTABLETY PE IS TABLE OF SEC_GRP_RIGHTS. LEVEL_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE GRP_NAMETABLETY PE IS TABLE OF SEC_GRP.GRP_NAM E%TYPE
INDEX BY BINARY_INTEGER;

PROCEDURE GET_RIGHTS2 (SGRPID IN SEC_GRP.GRP_ID% TYPE,
SCONSOLDBRANCH OUT BRANCHTABLETYPE ,
SCONSOLDPRODUCE R OUT PRODUCERTABLETY PE,
SCONSOLDSUBPROD UCER OUT SUBPRODUCERTABL ETYPE,
SREADIND OUT READINDTABLETYP E,
SWRITEIND OUT WRITEINDTABLETY PE,
SLEVELIND OUT LEVELINDTABLETY PE)
IS
iIndex BINARY_INTEGER := 0;
CURSOR GRCUR (SGRPID IN SEC_GRP.GRP_ID% TYPE) IS
SELECT CONSOLIDATED_BR ANCH_CODE, CONSOLIDATED_PR ODUCER_CODE,
CONSOLIDATED_SU BPRODUCER_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.CONSOLID ATED_BRANCH_COD E;
SCONSOLDPRODUCE R(iIndex) := GetRec.CONSOLID ATED_PRODUCER_C ODE;
SCONSOLDSUBPROD UCER(iIndex) :=
GetRec.CONSOLID ATED_SUBPRODUCE R_CODE;
SREADIND(iIndex ) := GetRec.READ_IND ;
SWRITEIND(iInde x) := GetRec.WRITE_IN D;
SLEVELIND(iInde x) := GetRec.LEVEL_IN D;
END LOOP;
END GET_RIGHTS2;
END;
/

and here is the C# code

OracleCommand myCMD1 = new OracleCommand() ;
myCMD1.Connecti on = Oraclecon1;
myCMD1.CommandT ext = "SECURITY_PROC. ADMIN_PACKAGE.G ET_RIGHTS2";
myCMD1.CommandT ype = CommandType.Sto redProcedure;
OracleParameter param1 = myCMD1.Paramete rs.Add("SGRPID" , null);
OracleParameter param2 = myCMD1.Paramete rs.Add("SCONSOL DBRANCH",
null);
OracleParameter param3 = myCMD1.Paramete rs.Add("SCONSOL DPRODUCER",
null);
OracleParameter param4 =
myCMD1.Paramete rs.Add("SCONSOL DSUBPRODUCER", null);
OracleParameter param5 = myCMD1.Paramete rs.Add("SREADIN D", null);
OracleParameter param6 = myCMD1.Paramete rs.Add("SWRITEI ND", null);
OracleParameter param7 = myCMD1.Paramete rs.Add("SLEVELI ND", null);

param1.Directio n = ParameterDirect ion.Input;
param2.Directio n = ParameterDirect ion.Output;
param3.Directio n = ParameterDirect ion.Output;
param4.Directio n = ParameterDirect ion.Output;
param5.Directio n = ParameterDirect ion.Output;
param6.Directio n = ParameterDirect ion.Output;
param7.Directio n = ParameterDirect ion.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 ();
OracleDataReade r myReader = myCMD1.ExecuteR eader();
int x;
int count;
count = 0;
while (myReader.Read( ))
{
for (x = 0; x <= myReader.FieldC ount - 1; x++)
Response.Write( myReader.GetVal ue(x));
Console.WriteLi ne();
count += 1;

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

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

Response.Write( errorMessage);
}
Oraclecon1.Clos e();

[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#_Beginne r" <c0*****@allsta te.comwrote in message
news:11******** *************@a 75g2000cwd.goog legroups.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.CON SOLIDATED_BRANC H_CODE
%TYPE
INDEX BY BINARY_INTEGER;
TYPE PRODUCERTABLETY PE IS TABLE OF
SEC_CONSOLD.CON SOLIDATED_PRODU CER_CODE%TYPE
INDEX BY BINARY_INTEGER;
TYPE SUBPRODUCERTABL ETYPE IS TABLE OF
SEC_CONSOLD.CON SOLIDATED_SUBPR ODUCER_CODE%TYP E
INDEX BY BINARY_INTEGER;
TYPE READINDTABLETYP E IS TABLE OF SEC_GRP_RIGHTS. READ_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE WRITEINDTABLETY PE IS TABLE OF SEC_GRP_RIGHTS. WRITE_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE LEVELINDTABLETY PE IS TABLE OF SEC_GRP_RIGHTS. LEVEL_IND%TYPE
INDEX BY BINARY_INTEGER;
TYPE GRP_NAMETABLETY PE IS TABLE OF SEC_GRP.GRP_NAM E%TYPE
INDEX BY BINARY_INTEGER;

PROCEDURE GET_RIGHTS2 (SGRPID IN SEC_GRP.GRP_ID% TYPE,
SCONSOLDBRANCH OUT BRANCHTABLETYPE ,
SCONSOLDPRODUCE R OUT PRODUCERTABLETY PE,
SCONSOLDSUBPROD UCER OUT SUBPRODUCERTABL ETYPE,
SREADIND OUT READINDTABLETYP E,
SWRITEIND OUT WRITEINDTABLETY PE,
SLEVELIND OUT LEVELINDTABLETY PE)
IS
iIndex BINARY_INTEGER := 0;
CURSOR GRCUR (SGRPID IN SEC_GRP.GRP_ID% TYPE) IS
SELECT CONSOLIDATED_BR ANCH_CODE, CONSOLIDATED_PR ODUCER_CODE,
CONSOLIDATED_SU BPRODUCER_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.CONSOLID ATED_BRANCH_COD E;
SCONSOLDPRODUCE R(iIndex) := GetRec.CONSOLID ATED_PRODUCER_C ODE;
SCONSOLDSUBPROD UCER(iIndex) :=
GetRec.CONSOLID ATED_SUBPRODUCE R_CODE;
SREADIND(iIndex ) := GetRec.READ_IND ;
SWRITEIND(iInde x) := GetRec.WRITE_IN D;
SLEVELIND(iInde x) := GetRec.LEVEL_IN D;
END LOOP;
END GET_RIGHTS2;
END;
/

and here is the C# code

OracleCommand myCMD1 = new OracleCommand() ;
myCMD1.Connecti on = Oraclecon1;
myCMD1.CommandT ext = "SECURITY_PROC. ADMIN_PACKAGE.G ET_RIGHTS2";
myCMD1.CommandT ype = CommandType.Sto redProcedure;
OracleParameter param1 = myCMD1.Paramete rs.Add("SGRPID" , null);
OracleParameter param2 = myCMD1.Paramete rs.Add("SCONSOL DBRANCH",
null);
OracleParameter param3 = myCMD1.Paramete rs.Add("SCONSOL DPRODUCER",
null);
OracleParameter param4 =
myCMD1.Paramete rs.Add("SCONSOL DSUBPRODUCER", null);
OracleParameter param5 = myCMD1.Paramete rs.Add("SREADIN D", null);
OracleParameter param6 = myCMD1.Paramete rs.Add("SWRITEI ND", null);
OracleParameter param7 = myCMD1.Paramete rs.Add("SLEVELI ND", null);

param1.Directio n = ParameterDirect ion.Input;
param2.Directio n = ParameterDirect ion.Output;
param3.Directio n = ParameterDirect ion.Output;
param4.Directio n = ParameterDirect ion.Output;
param5.Directio n = ParameterDirect ion.Output;
param6.Directio n = ParameterDirect ion.Output;
param7.Directio n = ParameterDirect ion.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 ();
OracleDataReade r myReader = myCMD1.ExecuteR eader();
int x;
int count;
count = 0;
while (myReader.Read( ))
{
for (x = 0; x <= myReader.FieldC ount - 1; x++)
Response.Write( myReader.GetVal ue(x));
Console.WriteLi ne();
count += 1;

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

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

Response.Write( errorMessage);
}
Oraclecon1.Clos e();