Home

ORA-01460 in PL/SQL Developer

2007, September 27 - 10:29 — müzso
It has happened several times since I work with PL/SQL Developer that opening a table (I mean not the data rows of the table, but the table structure) for view or edit resulted in an ORA-01460 ("unimplemented or unreasonable conversion requested") error message. This used to happen only with unicode databases (UTF8 or AL32UTF8).

It seems that sometimes PL/SQL Developer (PLSD) has trouble in telling the characterset of the database. In recent versions of the tool (7.0.2 did not have it, but 7.1.2 already does) you can check the number of bytes reserved for a single character in PLSD by opening "Support Info" in Help. At the bottom in the "Character Sets" section you'll see a "Character size" value, this tells the number of bytes per character. In case of UTF8 databases (where characterset is UTF8 or AL32UTF8) a character can consume 4 bytes at most. If PLSD could successfully determine the characterset of the DB, then you'll see a non-zero value in "Support Info" in the CharSetID and NCharSetID rows. If PLSD failed to get the characterset properly, then these two IDs will be zero and "Character size" will be 1 byte.

You can tell PLSD manually to use a specific character size with an undocumented commandline switch: BPC (stands for "bytes per character"). So starting PLSD with plsqldev.exe BPC=4 will set the character size to 4 bytes and this fixed the ORA-01460 errors for me.

Unfortunately I could not create a reproducable example for the CharSetID=0 issue. One day it works, another day not (and I could not tell the difference between the two cases).

It's a good question whether using BPC=4 all the time has any drawbacks. Shocked Eg. if I use the switch while connecting to a database with a single-byte characterset?

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
2007, September 28 - 17:00 — müzso

CharSetID=0 issue fixed :-)

I've got the answer to the CharSetID=0 issue. Smiling I bet you would never guess it. Eye-wink Some very analytic mind (Sherlock Holmes?) would be needed to guess the correct answer without having access to my database.

To cut it short: our application had a local "DUAL" table in it's schema (ok ... I can already hear ... "what a dumb ass would create a DUAL table in any other schema than SYS?" ... but I can assure you there was a reason Smiling ). PL/SQL Developer get's the characterset most probably by a "SELECT USERENV('LANGUAGE') FROM DUAL" or similiar query and if the "DUAL" table has no rows in it, the query would return no rows either. Smiling

All my characterset problems with PLSD came from this. Here's how I used to make a copy of the application (in a schema):
1. export table (data) from source schema
2. export table definitions from source schema
3. create new user (dest. schema)
4. create the empty tables in dest. schema
5. disable all constraints on all tables, move indexes to their own tablespace
6. import table data
7. enable all constraints on all tables

The problem is between step 4. and 6. In step 4 an empty DUAL table was created in the application schema and every new connection in PLSD before step 6 resulted in a CharSetID=0 since the DUAL table was empty. And I did the table imports with such a new connection too ... thus the imported unicode data in the dest. schema became crap.

Conclusion:
a.) some "dumb" guy thought a DUAL table in the application schema would be OK (and in fact it was ... but only as long it had a single row in it ... no more, no less ...). We didn't consider that database tools (such as PL/SQL Developer or even Oracle's EXP/IMP might take use of the SYS.DUAL table without a fully qualified reference ... including the schema).
b.) always expect the unexpected Smiling

PLSD could be "fixed" by changing all references to the DUAL table to SYS.DUAL ... but I'd not be surprised if this special case was not to be covered by the developers. Smiling Anyway ... I still think that some notification would be needed for the user to know that something is wrong. Of course she/he will figure it out pretty quickly Smiling, but the solution is far from trivial.

P.S.: I've dropped that DUAL table quickly Smiling since it was only there for some Oracle 8i hack and we've migrated to 9i long ago.

2009, January 14 - 22:12 — helen (not verified)

I had same issue

In PL/SQL developer, when I tried to view table structure or procedure/package, it kept pop up the ora-01460 error.

Solution:
=======
I set the NLS_LANG to be same as DB character set userenv('LANGUAGE') . The problem is solved.

2009, January 18 - 12:37 — müzso

Re: I had same issue

Not really. You just took sure that every non-USASCII character coming from your PL/SQL Developer to the database will be crap (not translated). Setting the client-side NLS_LANG to match the DB's characterset means that there'll be no characterset translation in the communication between the client and the server. However if your DB's characterset is some sort of a unicode (eg. UTF-8), it's quite sure that it'll cause problems since PL/SQL Developer sends it's data in some single-byte characterset. A characterset translation is vital in this case.

For me the problems with the ORA-01460 errors occured even if I set the client-side NLS_LANG to the proper value (that matched the Windows characterset setting). And the reason for the errors was (as I described previously) that PL/SQL Developer could not determine the database characterset correctly due to an empty DUAL table in an Oracle schema that I was connecting to.

Syndicate content