Stored procedure in Oracle using Select Command and Variables giving error

Question

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 I get 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 call the Oracle procedure in C# it throws 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;
        }

The error isn't in your stored procedure, it is in your call to the stored procedure. What SQL are you using to call it? — Mark Wagoner
What is the type of "nBo.username" or "nBo.password" ? Can you try the same removing " DEFAULT NULL " from the oracle procedure ? — Surajit Biswas
@SurajitBiswas I tried as you suggested and yet gives the same error — Rex Jones

Surajit Biswas Surajit Biswas · Accepted Answer · 2016-08-02T11:51:33

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

Stored procedure in Oracle using Select Command and Variables giving error

1 Answers