Exception when calling stored procedure: ORA-01460 - unimplemented or unreasonable conversion requested

Question

I'm trying to call a stored procedure using ADO .NET and I'm getting the following error:

ORA-01460 - unimplemented or unreasonable conversion requested

The stored procedure I'm trying to call has the following parameters:

param1 IN VARCHAR2,
param2 IN NUMBER,
param3 IN VARCHAR2,
param4 OUT NUMBER,
param5 OUT NUMBER,
param6 OUT NUMBER,
param7 OUT VARCHAR2

Below is the C# code I'm using to call the stored procedure:

    OracleCommand command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "MY_PROC";

    OracleParameter param1 = new OracleParameter() { ParameterName = "param1", Direction = ParameterDirection.Input,
        Value = p1, OracleDbType = OracleDbType.Varchar2, Size = p1.Length };
    OracleParameter param2 = new OracleParameter() { ParameterName = "param2", Direction = ParameterDirection.Input,
        Value = p2, OracleDbType = OracleDbType.Decimal };
    OracleParameter param3 = new OracleParameter() { ParameterName = "param3", Direction = ParameterDirection.Input,
        Value = p3, OracleDbType = OracleDbType.Varchar2, Size = p3.Length };

    OracleParameter param4 = new OracleParameter() { ParameterName = "param4", Direction = ParameterDirection.Output,
        OracleDbType = OracleDbType.Decimal };
    OracleParameter param5 = new OracleParameter() { ParameterName = "param5", Direction = ParameterDirection.Output,
        OracleDbType = OracleDbType.Decimal};
    OracleParameter param6 = new OracleParameter() { ParameterName = "param6", Direction = ParameterDirection.Output,
        OracleDbType = OracleDbType.Decimal };
    OracleParameter param7 = new OracleParameter() { ParameterName = "param7", Direction = ParameterDirection.Output,
        OracleDbType = OracleDbType.Varchar2, Size = 32767 };

    command.Parameters.Add(param1);
    command.Parameters.Add(param2);
    command.Parameters.Add(param3);
    command.Parameters.Add(param4);
    command.Parameters.Add(param5);
    command.Parameters.Add(param6);
    command.Parameters.Add(param7);

    command.ExecuteNonQuery();

Any ideas what I'm doing wrong?

Answer 1

Where are you pushing the values into the parameters?

Edit: Sorry, bad question. Rather, what are the values you're pushing into the parameters coming from? The ADO.NET implementation doesn't check the type of the object you push into the parameter on the client side; the server is responsible for verifying that the object type meshes with the DB parameter type you've given it. The conversion error can be caused by declaring a parameter as, say, OracleDbType.Decimal and then pushing a string into it by accident.

Answer 2

Not sure if it is relevant but SQL VARCHAR2 values are limited to 4000 (though PL/SQL can cope with 32 thousand)

You could try amending "Size = 32767" to something smaller (eg 500) and see if that works.

Also look into the sizes of the strings you are passing in. If one of them is 50000 characters, that might be the problem.

Answer 3

Hi, what oracle client are you using. There is an oracle issue relating to binds which gives this same error message. If i remember correctly the issue is with all clients from 10.2.0.3 to to 11.1.0.7 that can give this error.

I had an application that worked fine with 10.2.0.1 and suddenly with 11.1.0.7 client it got the above error.

Switching to 11.2.0.1 oracle client fixed the issue.

However in your case I would first check do the NLS settings of your client match the database (or are at least compatible)

Theres no guarantee it's the same issue but you can double check it at least.

//Sorry just saw it's already fixed but the info may be useful to someone else sometime

Cheers, Crocked