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
