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"