Command line switch BPC=1

[Remo Laubacher]

Can someone tell me what the switch BPC does?
We had a problem with ORA-01480 (http://www.allroundautomations.com/ubb/ultimatebb.php?ubb=get_topic;f=3;t=003159). In our case it helped but I couldn't find any information about what it does...
Thanks and kind regards,
Remo

 

[Marco Kalter]

It sets the Bytes Per Character to 1, in case the automatic character size detection fails.

 

[joerg_ids]

What I found is when DB is in UTF8 and CHAR-semantic and column length is VARCHAR2(4000 CHAR) instead of BYTE you get reproducable this error message.
Thing that helped was setting column length to VRACHAR2(2000) or to be sure for UTF8 to VARCHAR2(1333).

 

[bit2]

I'm using BYTE schemantics in an AL32UTF8 database (nls_length_semantics=BYTE in v$parameter) and experienced ORA-01460 ("unimplemented or unreasonable conversion requested") upon opening tables via right-clicking in object browser and selecting "View" or "Edit". I checked the "Support Info" page in PL/SQL Developer and it said that "Character size: 1 byte(s)". Using a bpc=4 parameter with plsqldev.exe helped, the ORA-01460 went away. I assume that "Character size: 1 byte(s)" means PLSD reserves 1 byte per character for storing char values and in UTF8 a character can be 4 bytes long too.

Question: is there any drawback (aside from the extra memory consumption) in continuous use of the bpc=4 parameter with PLSD? I mean even if I connect to a single-byte charset database? It'd be a lot more convenient if I did not have to set BPC depening on what DB I'm connecting to.

One more thing: I'm not sure whether this is the root of the problem, but in "Support Info" both CharSetID and NCharSetID were equal to 0. Maybe they should not be?

I experienced the ORA-01460 issues both in PLSD v7.0.2.1076 and in v7.1.2.1363. I tried the BPC hack only in the latter ... I'm not even sure whether the former has it too or not.

 

[bit2]

Originally posted by bit2:
One more thing: I'm not sure whether this is the root of the problem, but in "Support Info" both CharSetID and NCharSetID were equal to 0. Maybe they should not be?

OK, this is weird. Today on the same PC in the same session (I mean didn't log off or anything) without me having done any changes PLSD reports CharSetID and NCharSetID properly in Support Info. :-o It's driving me crazy.
I really did not touch anything ... I still have two instances of PLSD running from yesterday where Support Info reports zero for both CharSetID and NCharSetID, but if I start new instances of PLSD, they detect charsets correctly and I don't have to set BPC on the command line. Both the client and the database server are all the same, nobody has changed anything. I'd be happy about this if it was sure PLSD would keep working properly in future. But since the cure came so miraculously, I'm a bit scepctical whether this will last ... :-(

 

[Marco Kalter]

Something must have changed on the client or the server. PL/SQL Developer does not make this up by itself.

 

[bit2]

Originally posted by Marco Kalter:
Something must have changed on the client or the server. PL/SQL Developer does not make this up by itself.

Nothing that I could tell. No other user had accessed neither the database, nor the client between the two attempts.

On the other hand it would be extremely useful if PLSD notified the user in case it could not determine what characterset is being used (I mean in case CharSetID=0 or NCharSetID=0). It could raise the attention of the user to possible problems. Silently setting BPC to 1 almost guarantees that user will experience errors.

 

[bit2]

Today I ran into the same problem: CharSetID=0 and NCharSetID=0. It happens with a 9iR2 database (9.2.0.1.0 on a Win2003 server) with NLS_CHARACTERSET=AL32UTF8, NLS_NCHAR_CHARACTERSET=AL16UTF16. The database contains a number of schemas/users. I've created a new one, imported some tables into it from another database using PL/SQL Developer, imported (aka. compiled) some PL/SQL packages ... and all was working fine. Then started up a new instance of PLSD and the CharSetID=0 bug hit me again. I've restarted PLSD, logged off and logged on, nothing seems to help. And I bet that tomorrow morning it'll work again, just as it did the last time. :-(

Is there no way to get some debug info out of PLSD to see at which point and why does it fail to get the characterset from the database?

The strange thing is that if I log in to the database with any other user, then it works (CharSetID got a non-zero value). If I log off from the database (but do not exit PLSD) and log on with the new user, then CharSetID=0.

I've tested whether this happens with all freshly created, new users, but it does not. I've created a "TEST" user, logged on with it and CharSetID was correctly set by PLSD. There must be something else to it. I'll try to create another user that produces the same symptoms with PLSD and hopefully this way I can identify the point of failure.

 

[bit2]

I've got the answer to the CharSetID=0. I bet you would never guess it. ;-) Some very analytic mind (Sherlock H.?) 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. 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.

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 tables 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
6. import 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 is ... but only as long it has a single row in it ... no more, no less)
b.) always expect the unexpected

I doubt you'd change any line of code in PLSD only to come up for such a special case ... but you could easily fix it by changing every reference to the DUAL table to SYS.DUAL (thus specifying the schema too). This way it could not use a DUAL table in the given schema that we are logging into ... not even by accident.

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

 

[MjH]

It sets the Bytes Per Character to 1, in case the automatic character size detection fails.

We've run into this problem (ORA-01480) with Version 7.1.5.1398 in a Unicode database when attempting to update CLOB columns. Using the BPC=1 parameter does appear to prevent the problem, but is there some potential for problems with the data if the column being updated does contain muli-byte characters?

Thanks, Maury

 

[Marco Kalter]

For Unicode data you should be fine.