ORA-01460: unimplemented or unreasonable conversion requested

Question

When I run the following .Net code:

using (var c = Shared.DataSources.BSS1.CreateCommand())
{
    c.CommandText = "\r\nSelect c1, c2, c3, rowid \r\nFrom someSpecificTable \r\nWhere c3 = :p0";
    var p = c.CreateParameter() as Oracle.DataAccess.Client.OracleParameter;
    c.Parameters.Add(p);
    p.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
    p.DbType = System.Data.DbType.AnsiString;
    p.Size = 20;
    p.Value = "007";
    p.ParameterName = ":p0";
    using (var r = c.ExecuteReader())
    {
        r.Read();
    }
}

I get the following error:

ORA-01460: unimplemented or unreasonable conversion requested
ORA-02063: preceding line from XXX

This is not my database, and I don't have control over the select statements that I get, that table IS from a database link.

The funny thing is that if I add the following code just before the ExecuteReader it runs fine.

c.CommandText = c.CommandText.Replace("\r\n", " ");

Unfortunately that is not a good solution in my case as I can't control to SQL nore can I change it that way.

As for the table itself, the columns are: c1 Number(5) c2 varchar2(40) c3 varchar2(20).

I know that ORA-02063 that comes after indicate something about a database link, but I looked in the synonim table and it didn't come from any database_link, and also I don't think that \r\n should affect database link.

I tried running the query without bound parameters, and it did work - but again bad practice to do so in a general term.

The trouble is that a competing tool that is not .Net based, is working and thus it's not a general problem.

I also couldn't reproduce the problem in my own environment, this is a customer database and site. I am using instant client 11.1.6.20 and also tested it with instant client 11.2.3.0

The db is 10 and the db link is to an oracle v8 database

Any help would be appreciated

Answer 1

This problem can be recreated with straight forward steps. That is, any SQL query having a string literal, in where clause, more than 4000 characters in length gives an error "ORA-01704: string literal too long"

But, when the same query is executed through JDBC it gives "ORA-01460: unimplemented or unreasonable conversion requested"

Answer 2

After much investigation I found out that it's all about the fact that we have bound parameters that are used from ODP.NET and targeting tables from a DBLINK to a V8 Oracle server.

Once I eliminated the bound parameters it all worked.

It was a while back, but I think it's had something to do with varying string lengths of the strings sent to the bound parameter. It seems that it ignored the size property, so if in the first query I sent a string with length 10 and in the second string I sent a string with length 12, I'll get that error.

I also found the oracle articles about it : https://community.oracle.com/thread/2460796?tstart=0

and the patch for it: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=745005.1

But - I found a fix in my code that actually solved it - see my next answer.

Hope this helps anyone.