Session Browser Error ORA -12703 when osuser with umlaut

Toad for Oracle - Beta
1

I have a problem with Session Browser in 13.2.0.78: I get error Ora-12703: this character set conversion is not supported.
I think that I found the reason:
In 13.1 the session browser sql contains:
TO_SINGLE_BYTE (s.OSUSER) AS OSUSER
in 13.2 ther is
CONVERT (s.OSUSER, ‘US7ASCII’, ‘UTF8’) AS OSUSER
We have osuser with umlaut and tis result in error -12703

2

Thanks for the details. I am glad you found this in beta. With that change I was trying to work around a problem of some users still getting a “ORA-29275: partial multibyte character”

What is your DB Character set?

select value from sys.nls_database_parameters
where parameter = ‘NLS_CHARACTERSET’

3

Our DB Character set is WE8ISO8859P1.

4

OK, I’ll set one up here and try to reproduce it. Thanks

5

As I mentioned, the problem only occurs when ther is a umlaut in osuser on client side.
I hope you can reproduce this case.

6

I just set up an Oracle 11.2 database. WE8ISO8859P1 was not one of the choices but WE8ISO8859P15 was so I went with the (I’ll admit, I don’t know what the difference is between the two). I tried this query and didn’t get an error. Does it give you an error?

select ‘ä ö ü’ plain,
TO_SINGLE_BYTE(‘ä ö ü’) single_byte,
CONVERT(‘ä ö ü’,‘US7ASCII’,‘UTF8’) converted
from dual

7

Hi John,

P15 gets you the Euro symbol. I’m 99% certain that’s the only difference.

Cheers,
Norm. [TeamT] <— but you knew that!

8

PS. I think on the DBCA there’s an option to ‘only show approved charactersets’ or similar. Untick that box and you should get we8iso8859p1 in the drop down list.

Typing from memory.

Cheers,
Norm. [TeamT]

9

Thanks Norm.

@mayerhofer, I’ve changed back to TO_SINGLE_BYTE in the next beta. If anyone needs CONVERT instead, I leave a way to configure that in Toad.ini.

10

Thank you very much for solving my problem.

11

Still there was a problem in:
substr(convert(s.ACTION, ‘US7ASCII’, ‘UTF8’), 1, 64) as ACTION.

DB charset CL8MSWIN1251.

12

oops, you’re right, I missed that one. I’ll change it.

By the way, here is how will work going forward (beginning 13.2, next beta):

If database character set is unicode, there is no conversion. Otherwise, we’ll use to_single_byte around fields that might contain non-ascii characters (username, osuser, machine, action)

To use CONVERT instead of TO_SINGLE_BYTE, add these lines to Toad.ini in the [SETTINGS] section.

SessionBrowserConvert=1
SessionBrowserConvertFrom=UTF8
SessionBrowserConvertTo=US7ASCII

SessionBrowserConvertFrom and SessionBrowserConvertTo can have whatever values are appropriate for you in the CONVERT() function, described here: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CONVERT.html#GUID-C8BA0657-61C8-4964-A4CB-9292390853F6

13

Hi,

Looks like you done right thing when switched from CONVERT to TO_SINGLE_BYTE for OSUSER and MACHINE, but for ACTION field you forgot to do it.
Please check:

Select  round(bitand(s.ownerid, 65535)) parent_session_sid, round(bitand(s.ownerid,16711680)/65536) parent_session_instid, s.INST_ID, rawtohex(s.SADDR) as SADDR, s.SID, s.SERIAL#, s.AUDSID, rawtohex(s.PADDR) as PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, TO_SINGLE_BYTE(s.OSUSER) as OSUSER, s.PROCESS, TO_SINGLE_BYTE(s.MACHINE) as MACHINE, s.PORT, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH,

substr(convert(s.ACTION, ‘US7ASCII’, ‘UTF8’), 1, 64) as ACTION

, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.TOP_LEVEL_CALL#, s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID, s.SQL_TRANSLATION_PROFILE_ID, s.PGA_TUNABLE_MEM, s.CON_ID, s.EXTERNAL_NAME
    FROM GV$SESSION S
     WHERE  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') ) 
    order by STATUS, OWNERID
14

Thanks for reporting. It’s fixed for next build. :smiley: