0

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Taylor Leese
  • 51,004
  • 28
  • 112
  • 141

3 Answers3

1

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
1

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

Crocked
  • 1,192
  • 10
  • 10
0

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.

Dan Story
  • 9,985
  • 1
  • 23
  • 27
  • I'm setting the Value property of the OracleParameters when I create them (for IN parameters). Is that not right? – Taylor Leese Mar 30 '10 at 22:54
  • p1 is a string, p2 is a long, and p3 is a string – Taylor Leese Mar 30 '10 at 22:59
  • Okay. I don't have a copy of Oracle handy to test with, but two things jump to mind from having tinkered with this sort of thing in the past. First, try casting your p2 to a decimal when you assign it to the parameter. Second, try removing the Size parameters for your Varchar2 params. DB type conversion is notoriously finicky and sometimes supplying more information than it wants (or expecting it to perform trivial implicit conversions like long to decimal) can cause hangups. – Dan Story Mar 30 '10 at 23:05
  • Oh, also make sure that your strings are not null when you're pushing them into the parameters. If they are, use DBNull.Value instead. That can also cause trouble. – Dan Story Mar 30 '10 at 23:09
  • I tried both of your suggestions, but unfortunately I'm still getting the same exception. – Taylor Leese Mar 30 '10 at 23:10
  • Regarding null values, when I step through the code with the debugger I can see that they are not null so I don't think that is it. – Taylor Leese Mar 30 '10 at 23:12
  • Can you isolate the input and output parameters? Try dropping the output params from the query (both server and client-side; just declare them in the stored proc and throw away the values) and see if the input params work on their own? – Dan Story Mar 30 '10 at 23:26