Oracle Stored Procedure - Wrong Number of Parms Or Wrong DataTypes

[Guest]

Hello, I am trying to learn how to use stored procedures within the .Net
environment.

I have been successful in calling a stored procedure with a "REFCursor", and
NO input parameters, but when I attempt to call a stored procedure with a
single input parameter and an output parameter with a type of "REFCursor", I
always get the same error - wrong number of parameters or wrong datatypes. I
do not believe either is true, but what can I say, I must be missing
something.

Here is my Oracle Procedure (I have successfully executed it SQL+)

PROCEDURE QRY_PRODUCT_CODES( i_User_ID IN INT,
o_Rows OUT t_cursor)
IS
BEGIN
IF i_User_ID is null THEN
OPEN o_Rows FOR
select op.Product_id,
op.name
from TST_OPS_product op, TST_OPS_product_family opf
where op.FAMILY_ID = opf.FAMILY_ID
and op.MCC_Sort_ord is not null
and ( op.prod_mgr1_id = i_User_ID
or op.prod_mgr2_id = i_User_ID
or op.grp_mgr_id = i_User_ID
or op.PDM_Contact1_ID = i_User_ID)
order by op.Name;
ELSE
OPEN o_Rows FOR
select op.Product_id,
op.name
from TST_OPS_product op, TST_OPS_product_family opf
where op.FAMILY_ID = opf.FAMILY_ID
and op.MCC_Sort_ord is not null
order by op.Name;
END IF;
END QRY_PRODUCT_CODES;

Here is the code that I use to create my parameters....

parm = new Oracle.DataAccess.Client.OracleParameter("o_Rows",
Oracle.DataAccess.Client.OracleDbType.RefCursor);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);

parm2 = new Oracle.DataAccess.Client.OracleParameter("i_User_ID",
Oracle.DataAccess.Client.OracleDbType.Int32);
parm2.Direction = ParameterDirection.Input;
parm2.Value = 53;
cmd.Parameters.Add(parm2)

I have tried all kinds of "OracleDbType" for the "i_User_ID", such as int16,
single, double, decimal....but not luck, same error.

Why does it not like my parameters?

 

[David Browne]

Hello, I am trying to learn how to use stored procedures within the .Net
environment.

I have been successful in calling a stored procedure with a "REFCursor", and
NO input parameters, but when I attempt to call a stored procedure with a
single input parameter and an output parameter with a type of "REFCursor", I
always get the same error - wrong number of parameters or wrong datatypes. I
do not believe either is true, but what can I say, I must be missing
something. .. . .

By default oracle parameters are bound by position. Either switch the order
or run

cmd.BindByName = true;


David