ORA-01460: unimplemented or unreasonable conversion requested

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"

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;

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.