8

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

Noam
  • 4,472
  • 5
  • 30
  • 47
  • You can't control the SQL but you execute it anyway? So you just take whatever the user gives you? Sorry, maybe I'm misunderstanding the situation. – tbone Jun 14 '12 at 16:49
  • This is an application that is migrated automatically from one language to another. Most of the SQL is self generated on the fly. Some of it is embedded in the code. – Noam Jun 15 '12 at 06:22
  • My gues it must me something in the .Net driver or application. It is e.g. no problem at all running SQL statements with embedded newlines through JDBC –  Jun 15 '12 at 08:50
  • It's not every query. It's this specific query. I run thousands of other queries, and the are all fine. – Noam Jun 15 '12 at 10:16
  • I think it has something to do with bind variables, odp.net and database link. – Noam Jun 15 '12 at 10:17

4 Answers4

20

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"

Kiran
  • 3,151
  • 2
  • 20
  • 16
7

Finally I found the answer!!!

After investigating and reflecting into the code I found that by changing the Direction of the Parameter to input output - the problem was resolved.

p.Direction = ParameterDirection.InputOutput;
Noam
  • 4,472
  • 5
  • 30
  • 47
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.

Noam
  • 4,472
  • 5
  • 30
  • 47
0

The accepted answer didn't work for me. However, after reading the attached links, I applied the following – although it does involve editing the SQL.

In my case, I knew the maximum left of the bind variable (the length reducing after the first call is what causes the issue). So I padded the .NET string, and added a TRIM in the SQL. Following your example:

c.CommandText = "\r\nSelect c1, c2, c3, rowid \r\nFrom someSpecificTable \r\nWhere c3 = TRIM(:p0)";
...
p.Value = "007".PadRight(10);
AdamRossWalker
  • 294
  • 1
  • 10