ORA-01460: requested unpaid or unreasonable conversion

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 have no control over the select operations that I get, this IS table is from a database link.

The funny thing is that if I add the following code immediately before ExecuteReader, it will work fine.

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

Unfortunately, this is not a good solution in my case, since I cannot control SQL nore, can I change it this way.

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

I know that ORA-02063, which comes after showing something about the connection with the database, but I looked at the table, and this is not a synonym from any database_link, nor do I think that \ r \ n should influence the link to the database.

I tried to execute the request without the associated parameters, and it really worked - but again, bad practice to do it in general terms.

The problem is that a competing non-.NET tool works and therefore is not a common problem.

I also could not reproduce the problem in my own environment, it is a customer database and website. I use the instant client 11.1.6.20, and also tested it with the instant client 11.2.3.0

The db value is 10, and the db link to the oracle v8 database

Any help would be appreciated

Answer 1

Finally, I found the answer !!!

After researching and reflecting the code, I found that by changing the direction of the parameter to the input signal, the problem was solved.

 p.Direction = ParameterDirection.InputOutput; 

Answer 2

This problem can be recreated with simple steps forward. That is, any SQL query that has a string literal in where where, longer than 4000 characters, gives the error "ORA-01704: string literal is too long"

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

Answer 3

After much research, I learned that all this is due to the fact that we linked the parameters that are used from ODP.NET and the targeting of tables from DBLINK to the Oracle V8 server.

As soon as I fixed the related options, it all worked.

This was some time ago, but I think it has something to do with changing the length of the string lines sent to the related parameter. It seems that he ignored the size property, so if in the first request I sent a string of length 10 and in the second query I sent a string of length 12, I will get this error.

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

and a 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.

Answer 4

The accepted answer did not work for me. However, after reading the attached links, I applied the following - although this is really related to editing SQL.

In my case, I knew the maximum left side of the bind variable (decreasing the length after the first call is the cause of the problem). So I added a .NET string and added TRIM to 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);