How to work around ORA-29275: partial multibyte character when using chr function

If you need to check what is actually stored in a database table, meaning the internal representation, you can use the DUMP function.

It will return a varcar2 value containing the data type code, length in bytes, and internal representation

SQL> select dump ('ø',1010) from dual;

DUMP('Ø',1010)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: 195,184

So the type above is 96, representing a VARCHAR2 or NVARCHAR datatype, the length is 2 bytes, the characterset of the database is AL32UTF8 and the decimal values of each byte is 195 and 184.

For single-byte character set, it would be easy to check which character the decimal represented under the database's characterset; simply use the chr function and pass the decimal value as an argument.

However with a multibyte character set, which one of the returned decimal functions should you choose?

SQL> select chr(195) from dual;

ERROR:
ORA-29275: partial multibyte character

A workaround is to dump the character to hexadecimal format instead of decimal, by using the argument 1016 as the second argument to the function:

SQL>  select dump ('ø',1016) from dual;

DUMP('Ø',1016)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: c3,b8

The c3b8 hexadecimal value corresponds to the decimal value of 50104. Pass this value to the chr function and you get the result you're looking for:

SQL> select chr(50104) from dual;

CHR(50
------
ø

A brilliant converter between decimal and hexadecimal numbers can be found here

The documentation for the dump function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions055.htm#SQLRF00635

The documentation for the chr function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions026.htm#SQLRF00616

The datatype table is listed here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci03typ.htm#i422575