Why am I getting a Oracle numeric or value error

Tried the following and it worked for me.
====================================================================================

create or replace PROCEDURE VerifyCounterparty(
    v_EmailAddress IN VARCHAR,
    v_Password     IN VARCHAR2,
    v_Count OUT NUMBER,
    v_AccountLocked OUT NUMBER,
    v_TradesCompanyId OUT NUMBER,
    v_FirstName OUT VARCHAR2)
IS
BEGIN
  SELECT COUNT(firstname) firstname,
    ACCOUNTLOCKED,
    TRADESCOMPANYID,
    FIRSTNAME
  INTO v_Count,
    v_AccountLocked,
    v_TradesCompanyId,
    v_FirstName
  FROM T_USER
  WHERE EMAILADDRESS = v_EmailAddress
  AND PASSWORD       = v_Password GROUP BY ACCOUNTLOCKED, TRADESCOMPANYID, FIRSTNAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  v_Count           := 0;
  v_AccountLocked   := 0;
  v_TradesCompanyId := 0;
END VerifyCounterparty;
/


create table t_user(
    firstname       varchar2(10),
    accountlocked   number,
    tradescompanyid number)
/        
alter table t_user add(password varchar2(10))
/
alter table t_user add(emailaddress varchar2(10))    
/

SQL> declare
  2      v_email                 varchar2(10);
  3      v_pass                  varchar2(10);
  4      v_count                 varchar2(10);
  5      v_accountlocked         varchar2(10);
  6      v_tradescompanyid       varchar2(10);
  7      v_firstname             varchar2(10);
  8  begin
  9      verifycounterparty(v_email,v_pass,v_count,v_accountlocked,v_tradescompanyid, v_firstname) ;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
==================================================================================================

For grins change the alias for the count to something other than firstname (the name of a column).

Can you describe table t_user  ?
Are accountlocked and tradescompanyid defined as number ?

t-user-table.sql
T-USER.txt

when I run the proc in the server explorer in debug mode and put the input values manually, it seems to work ok, but when I run it by pushing the oracleParameter{} object in asp.net it bombs giving me the error.

Sounds like you have a data type incompatibility between your .Net code and Oracle code.

Check for 'empty' parameters.  Depending on your data provider it might be messing with how Oracle treats empty strings and nulls.

Can you post your .Net code?


Also,
Did you 'scrub' that data you posted?  I would think real names/emails and phone numbers are probably sensitive information.

the parameters aren't empty. The result i get back when I run the proc in debug mode gives me values back for all params. By the way, it's dummy data.

i think whats going on is that .net is expecting all data coming back is the same data type because when I run this wthout the firstname parms it runs fine.

I would need to see your code and how the parameters are defined.  I can guess that might be the case if you are using an array or parameters.

BAL:

List<string> parmOutput = new List<string>();

        OracleParameter[] parms = new OracleParameter[6] { 
                new OracleParameter("v_EmailAddress",username), 
                new OracleParameter("v_Password", password),
                new OracleParameter("v_Count", OracleDbType.Int32),
                new OracleParameter("v_AccountLocked", OracleDbType.Int32),
                new OracleParameter("v_TradesCompanyId", OracleDbType.Int32),
                new OracleParameter("v_FirstName", OracleDbType.Varchar2)
            };

        parms[0].Direction = ParameterDirection.Input;
        parms[1].OracleDbType = OracleDbType.NVarchar2;
        parms[1].Direction = ParameterDirection.Input;
        parms[2].Direction = ParameterDirection.Output;
        parms[3].Direction = ParameterDirection.Output;
        parms[4].Direction = ParameterDirection.Output;
        parms[5].Direction = ParameterDirection.Output;


        parmOutput = dataAccessOPR.returnNonQuery("CALL_OPTION_AVAILABILITY.VerifyCounterparty", parms);

Select allOpen in new window

DAL:

public List<string> returnNonQuery(String procName, OracleParameter[] parms)
    {
        List<string> output = new List<string>();

        try
        {
            cmd = new OracleCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parm in parms) { cmd.Parameters.Add(parm); }
            con.Open();
            cmd.ExecuteNonQuery();

            for (int i = 0; i < cmd.Parameters.Count; i++)
            {
                if (cmd.Parameters[i].Direction == ParameterDirection.Output)
                {
                    output.Add(cmd.Parameters[i].Value.ToString());
                }
            }

        }
        catch (OracleException oe)
        {
            throw oe;
        }
        finally
        {
            connClose();
        }

        return output;

    }

Select allOpen in new window

I was asking out resident .Net expert about the array of parameters and the first thing that jumped out at him is the first parameter of your stored procedure:

See if this is a simple fix:

change:
 v_EmailAddress IN VARCHAR,

to:
 v_EmailAddress IN VARCHAR2,


I just know enough .Net to be dangerous.  Nothing jumps out at me but I don't use parameter arrays so it is a little foreign to me.

I'm checking the docs to see if I can figure out what might be causing that error.   It almost has to be a data type conversion error somewhere.

Did all this work before you added first name?

yes, everything worked before I added the first name parameter.