CODEWITHSUNDEEP
c#asp.netoracle-database
Ajoe
Ajoe

Reputation: 1545

oracle character to number conversion error from .net

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;

Upvotes: 0

Views: 961

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

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;

Upvotes: 0

Related Questions