oracle character to number conversion error from .net

Question

I have a procedure in oracle having input fields varchar2 and number. I am adding parameters from my .Net code using Oracle.DataAccess.dll. I am getting an exception like

ORA-06502: PL/SQL: numeric or value error: character to number conversion error\nORA-06512: at line 1

If I run procedure directly from oracle sql developer, it works fine.

code:-

OracleCommand cmd = (OracleCommand)_dbFactory.CreateCommand();
            try
            {
                cmd.Connection = (OracleConnection)_conOracle;
                _conOracle.Open();
                cmd.CommandText = "SERVICE_STATUS";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Clear();



                cmd.Parameters.Add("PANUMBER", OracleDbType.Int32).Value = 10;
                cmd.Parameters.Add("PA_LINK", OracleDbType.Varchar2).Value = "Test";
                cmd.Parameters.Add("PO_MSG", OracleDbType.Varchar2, 4000).Direction = System.Data.ParameterDirection.Output;
                try
                {
                    cmd.ExecuteScalar();
                }
                catch(Exception ex1) {
                    log.Debug("Exception ex1 "+ ex1.Message+" inner--"+ex1.InnerException);
                }

                string isValid = cmd.Parameters["PO_MSG"].Value.ToString();                
                if (isValid == "SUCCESS")
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _conOracle.Close();
            }

procedure


create or replace
PROCEDURE SERVICE_STATUS( panumber number, pa_link varchar2 default NULL, po_msg OUT VARCHAR2 )
 is

BEGIN
   --logic comes here

END;

"Logic comes here" might be responsible. It seems that you're somewhere doing TO_NUMBER on a string that contains something else but digits; for example, TO_NUMBER('12F') will fail. — Littlefoot
Do you still have a problem if you comment out all the --logic comes here but assign po_msg := "Some Test Message";? — Dmitry Bychenko
@Littlefoot i understand, but i passed numeric field and it failed. I have not given a conversion like this. — Ajoe
@DmitryBychenko I dont have permission to edit the procedures. So i couldn't check that. — Ajoe
Try switching OracleDbType.Int32 to OracleDbType.Decimal - Oracle maps a NUMBER without precision to a .NET Decimal not an integer - Also you should be calling ExecuteNonQuery... — Dominic Cotton

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2020-01-23T08:00:21

Whenever you have a procedure with one OUT parameter I would rather prefer to create a function instead.

Anyway, looks like you cannot change that, so take what you have.

~~Try to add the parameter like this:~~

~~cmd.Parameters.Add("PO_MSG", OracleDbType.Varchar2, 4000, null, ParameterDirection.Output); cmd.Parameters["PO_MSG"].DbType = DbType.String;~~

(Only relevant for older version of ODP.NET providers)

The cmd.ExecuteScalar(); seems to be wrong. See documentation:

This method executes the query using the connection, and returns the first column of the first row in the result set returned by the query.

You don't execute a query, you want to invoke a procedure. Use cmd.ExecuteNonQuery();

But the real issue should be the size of your output parameter. Apparently 4000 characters is not sufficient. As opposed to SQL (unless you set MAX_STRING_SIZE = EXTENDED) the max. size of VARCHAR2 datatype is 32767, see PL/SQL Program Limits.

So, change your parameter to

cmd.Parameters.Add("PO_MSG", OracleDbType.Varchar2, 32767).Direction = ParameterDirection.Output;

oracle character to number conversion error from .net

1 Answers