Hitting again : ORA-01460: unimplemented or unreasonable conversion requested

[Christo Kutrovsky]

I have the latest release of PL/SQL Developer: 5.1.4.730

I am still hitting the very annoing error:
ORA-01460: unimplemented or unreasonable conversion requested

This time, I got mad, and tried to find the cause.

Hitting the error is very random, and I cannot find any cause, other then it happens with bind variables. Sometimes it happens, sometimes it doesn't. When it happens, sometimes closing and reopeing the window (tools -> sessions) will resolve it, sometimes it wont. Sometimes a reconnect will do it, sometimes no.

This time, it did it repeatedly, and I was able to get some info, which I hope will help you.

I have a screen shot, which I would like to email you, it has the error on screen and the cause. Where should I email it to ?

I have the exact query causing it, and I've played arround with it. It is in the tabs of the Sessions window, and the query is:

select 1 from v$sql_workarea
where hash_value = :sql_hash_value
and address = hextoraw(:sql_address)

I tried the following:
- switching the places in the where clause -> same error
- removing "hash_value = :sql_hash_value" -> no error
- removing "address = hextorawsql_address)" -> no error
- removing "hextoraw" -> same error
- adding to_char on :sql_hash_value -> same error
- adding to_char on "hash_value" -> same error

Any other ideas? I still have the session open.

Please update with email, so that I can send you the screen shot.

I hope we nail that thing once and forever.

 

[Marco Kalter]

What were the 2 resulting SQL statements that did not produce the error?

 

[Christo Kutrovsky]

They were:

select 1 from v$sql_workarea
where hash_value = :sql_hash_value

and

select 1 from v$sql_workarea
where address = hextoraw(:sql_address)

Seems that 2 bind variables, with one of them number .. something goes wrong, sometimes.

I assume you don't need the screenshot?

 

[Christo Kutrovsky]

I still have the session open, and still have this error, if you want me to try something now is the time.

 

[Marco Kalter]

I'm a bit puzzled that with 1 bind variable it works okay, and with 2 bind variables it fails. I can't really make sense of that.

 

[Christo Kutrovsky]

Just tried it again.

2 variables = no
1 variable = ok

Keep in mind that a select * from that view returns no rows ...

I also tried with 3 variables = error.
Tried different type of variables = error.

I know it makes no sense .. but here it is .. i am seeing it.

Maybe this will help , the error message appears in the grid as if I selected from a table, and the table had 1 columns with the error message.

 

[DanielF]

I'm not sure if this helps, but I kept running into this error whilst using the oracle XSU and implementing in Delphi.

The cause turned out to be that I was attempting to return a 64 bit integer into a 32 bit integer parameter, which I guess is a little unreasonable.

I am unable to check at the moment the column defs for that view so I can't establish whether this is the problem here.

 

[Christo Kutrovsky]

I have more info !!!!!

I hit again the damn error, this time trying to see the source of a package.

I tried logoff/logon .. did not resolve it. I did "End process" on the pl/sql developer, and start it again, recovered my windowses reconnected and ... hit that error again.

I got mad. I started another session, it worked all fine. Now i got interested.

I got back to my original session, closed all my existing windows (query,and command lines) and loged off/reloged ... (without restarting pl/sql). And a MIRACLE it worked fine !!

Next time it happens, i will try without logoff/long just closing all my windowses in pl/sql developer.

P.S.
Note I had lots of other applications running on my system, is it possibel that this is related to lack of GDI resources ?

 

[Christo Kutrovsky]

Happened again !!!

A logoff/logon is required.

Next time i will try whether closing a few windowses solves it or I need to close all.

 

[Christo Kutrovsky]

I am hitting again this. This time it was on expanding the indexes of a table in the browser.

I had 2 windowses, a SQL window, and a EXPLAIN PLAN window. I right clicked on the table, and used browse, to find the table. Then expanded the table and when I tried to expand the indexes, got the error message.

- Logoff/logon did not help.
- Closing the explain plan window, logoff/logon did not help
- Closing the sql window too, logoff/logon resolved it.

I copied my sql statement in clipboard, and used the same way to browse the table (right clicking and browse) and this time worked fine.

 

[Milan Martak]

Christo,
i hit similar error in past - it happened when i was working against 64b 9i. The 32b 9i worked o.k.
When i set the "Force OCI7 mode..." option, the problem disappears.
I also remember that there was a note on metalink related to this - they recommended to tune the client NLS settings to be as close to the server settings as possible.
Good luck...
M.

 

[Christo Kutrovsky]

But I think it's a PL/SQL developer bug, why would it appear only from time to time, and why would it disappear when closing windowses, and logging on again ?

Thanks for the hint thow, will try it next time it happens.

 

[gulhaugen]

Now, we have the same problem as described previous from time to time. But now I have a bullet proof way of inducing the same error at least.

I do not not if it is the same issue causing it though..

Test with 0, it's fine, with 1, you get the error.

---CODE----
create or replace procedure test(v_with_error number,c out sys_refcursor) is
v_ret varchar2(32767) := 'a';
begin
v_ret := lpad(v_ret,4000+v_with_error);
open c for select v_ret from dual;
end test;
----END CODE--------

 

[Marco Kalter]

This makes sense, because a SQL string is limited to 4000 bytes. A PL/SQL string can be up to 32KB.

 

[Christo Kutrovsky]

I have new information on this.

Seems that the problem is happening ONLY on 9i database with cursor_sharing=force.

I am working with several 9i databases,and only hitting on the ones with cursor_sharing=force.

I will try to alter system it to exact the next time it happens, and if it is possible (i.e. non prod system).

 

[Joachim Rupik]

Try to search in metalink, ora-1460 error is related to many oracle bugs. Maybe you should create a new TAR to solve this problem.

Joachim Rupik

 

[Christo Kutrovsky]

Thanks jrupik, I will have a look.

But it seem strange to me, that disconnecting and reconnecting with PL/SQL developer doesn't solve the problem, unless I close all my sql windows.