Toad World® Forums

ORA-29275 on SQLNav 6.4

Hello,

This occurs since v. 6.3.
I’m getting “ORA-29275: partial multibyte character” at some data regardless the setup in Preferences / General / Session / National Language Support.

I remember that in 6.2.1 it worked the opposite way, i.e. when I had “Display and Edit multi-byte data” enabled it also gave ORA-29275, but when I had “No Support” it worked.

In 5.5.4 it worked just fine as one would expect.

Tested with Oracle client 11gR1 11.1.0.7 against Oracle DB 10gR2 10.2.0.3

Hello Peter,

We could not reproduce the issue here yet.

Could you please provide us more details of the issue:

  • steps to reproduce the error.
  • the table object DDL if that would be possible
  • which column data type you have issues with.
  • Could you please send the screen capture of your login windows in v6.3, and v6.4. In v6.3, please make sure you click the Options button to expand the Option panel. We would like to know the setting of Bytes per Character you use.

Thanks and regards,
Bruce

Hello Peter,

We could not reproduce the issue here yet.

Could you please provide us more details of the issue:

  • steps to reproduce the error.
  • the table object DDL if that would be possible
  • which column data type you have issues with.
  • Could you please send the screen capture of your login windows in v6.3, and v6.4. In v6.3, please make sure you click the Options button to expand the Option panel. We would like to know the setting of Bytes per Character you use.

Thanks and regards,
Bruce

Hello,

  • example of logon screen from 6.4 attached
  • DDL of the table attached as well
    (just this time I tried it against older 9.2.0.8 DB but it doesn’t matter much)
  • database NLS_CHARACTERSET=UTF8

I can say it could be definitely observed with columns password, defaultpsw and pswhistory which are all of type VARCHAR2.

These columns store some encrypted form of data and it actually depends on the data - in some cases it doesn’t occur in some it does.
I can separately provide a sample of test data against which it occurs if You send me Your e-mail address.
UIXLOGINNAMES_ddl.sql (1.47 KB)

Hello,

  • example of logon screen from 6.4 attached
  • DDL of the table attached as well
    (just this time I tried it against older 9.2.0.8 DB but it doesn't matter much)
  • database NLS_CHARACTERSET=UTF8

I can say it could be definitely observed with columns password, defaultpsw and pswhistory which are all of type VARCHAR2.

These columns store some encrypted form of data and it actually depends on the data - in some cases it doesn't occur in some it does.
I can separately provide a sample of test data against which it occurs if You send me Your e-mail address.

Hi,

Thanks for your note.

I have sent you a private message with my email. Please check your Private Message inbox as attached screenshot.

Much appreciated if you could provide a sample of test data that you had issues with.

Thanks and regards,
Bruce

Hi Peter,

In v6.4, Can you please also try to change the ‘Bytes Per Character’ setting in DB the logon window, using ‘3’ or ‘4’ and see if you still get same problem.

Thanks and regards,
Bruce

Hello,

  • no luck at all with National Language Support set to “Display and Edit multi-byte data”

  • but with NLS set to “No Support” I managed to display some rows with any “Bytes per Character” setting except for “Auto detect”. So it worked with value 1, 2, 3, 4, 5, 6, but never with “Auto detect”.

Still, there are some rows which keep giving ORA-29275. I’ll try to locate some of them and send them to You.

EDIT: Sent the promised second set of sample data and note that I managed to display all data in SQLNav 6.4 when manually setting NLS_LANG=american_america.UTF8 , but on SQLNav 5.5.4 there’s no need to do this.

Hi,

I just want to clarify your situation. I assuming that you install different version of SQL Navigator on same machine but side by side.

In 5.5.4 and 6.2.1 set National Language Support to “No Support” will display data without problem but when you change to “Display and Edit multi-byte data”, both version won’t work at all?

In 6.3 or above regardless the setting, it won’t work at all if you set “Bytes per Character” to “Auto detect”. But if you change “Bytes per Character” to 1, 2, 3, 4, 5 or 6, it will returns more rows.

In 6.4 you can managed to display all data, only if you set your registry value NLS_LANG=american_america.UTF8 and National Language Support set to “No Support”?

You expect that SQL Nav 6.3/6.4 should returns all data when National Language Support set to “No Support” without changing “Bytes per Character” or NLS_LANG?

Thanks
Jeff

Hello,

first I’d like to note that we managed to get further with Bruce via email. I sent him one exported table and after some effort he managed to reproduce it (but only at some environments, but at least somewhere).

In 5.5.4 and 6.2.1 set National Language Support to “No Support” will display data without problem but when you change to “Display and Edit multi-byte data”, both version won’t work at all?

Not exactly. 5.5.4 works fine with “Display and Edit multi-byte data” while 6.2.1 works with “No Support” - strange but it’s just like that.

Since 6.3 it doesn’t work pretty much at all. In some cases I managed to get it to work by twiddling with NLS_LANG, Bytes per Character and National Language Support settings but the results weren’t very reliable - It looks like that some combinations get it to work with some data but not all.

I believe it should work in version 6.x with “Display and Edit multi-byte data” and Bytes per Character=Auto - as it works in 5.5.4

I think the exported table that Bruce now has is pretty good selection of data he can use in order to resolve that.

Uhm, tested with just released SQLNav 6.5 but no difference

Hi Peter,

My apology that I have let it slipped through 6.5. Very sorry, bad on my part…:frowning:

I thought we had raised in our CR system and had it addressed for you.

I will keep you posted on the changes in the up coming 6.6 Beta drop.

My apology again.

Thanks and regards,
Bruce

Hi Peter,

With the data you provided, we can see the error only when having the option in Session Preferences / General / Session / National Language Support setting to “Display and Edit multi-byte data”.

We have checked the code in the area of establishing the connection. Changes made to that National Language Support option becomes effective only after restart SQL Navigator, ie. if we changes the options (from Supprt muttibyte to No Support) without closing Navigator, we would still get same error as you reported

We would like to ask for your help again.
In version 6.3 or 6.5 or the beta v6.6 that you might have in your environment. Could you please try to have the option set to ‘No Support’, then restart SQL Navigator, then try to retrieve same data again. Please let us know if you still get same error ORA-29275?

Thanks again for your help.

Regards,
Bruce