Rex Jones Rex Jones - 4 months ago 35
C# Question

Stored procedure in Oracle using Select Command and Variables giving error

I am trying to figure out oracle stored procedure as I am new to Oracle 11G

Here is my MS SQL Stored procedure

Create procedure LoadLoginData
@username varchar(50),
@password varchar(50)
as
begin
select * from Employee_table where
username = @username and password = @password
end


when I used Oracle Scratch Editor
it gives me this output

CREATE OR REPLACE PROCEDURE LoadLoginData
(
v_username IN VARCHAR2 DEFAULT NULL ,
v_password IN VARCHAR2 DEFAULT NULL ,
cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN
OPEN cv_1 FOR
SELECT *
FROM employee_table
WHERE Username = v_username
AND PASSWORD = v_password ;
END;


when I am calling in C#
it throws me this exception


"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'LOADLOGINDATA' ORA-06550: line 1, column 7:"


Any idea how to figure this out?

My C# code

OracleConnection ocon = new OracleConnection(orastr);
OracleCommand ocmd = new OracleCommand("LoadLoginData", ocon);
ocmd.CommandType = CommandType.StoredProcedure;
ocon.Open();

try
{
ocmd.Parameters.Add("Username", nBo.username);
ocmd.Parameters.Add("Password", nBo.password);
OracleDataAdapter oda = new OracleDataAdapter(ocmd);
DataSet ds = new DataSet();
oda.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
catch (Exception ex)
{

throw ex;
}
finally
{
ocon.Dispose();
ocon.Close();
nBo = null;
}

Answer

Your Procedure has 2 Input param and 1 output param....your C# code has 2 input params but no output param

In Java level, I would have handled it in this below manner :

callableStatement.registerOutParameter(3, OracleTypes.CURSOR);

In C#, please look for the same thing (how to handle)...I suggest

ocmd.Parameters.Add("cv_1", OracleDbType.RefCursor, ParameterDirection.Output);