Pages Menu
Chitu Okoli chitu.okoli.org

Posted on Jan 25, 2016 in Computers and the Internet

Display advanced Unicode in Oracle SQL Developer

It can be tricky sometimes to display Unicode characters in Oracle SQL Developer. For most Unicode characters, the UNISTR function will do the trick:

  • UNISTR('\25CF') will display ●, UNISTR('\2192')will display → and UNISTR('\2020')will display †.
  • To see the display, you can execute select UNISTR('\2020') from dual; to display †.

However, some Unicode characters in higher code blocks can’t be expressed in four hexadecimal characters. For example, the code for 🍁 (a maple leaf) is U+1F341. Using UNISTR, I would attempt UNISTR('\1F341'). But that gives a gibberish result like: ☐1. With some help from Jeff Smith of Oracle, I finally was able to display advanced Unicode in Oracle SQL Developer. Before explaining the solution, though, I must mention that there are two shortcomings of my solution:

  1. First, this solution only works on Microsoft Windows 7 and later. It depends on the Segoe family of fonts produced by Microsoft (if you have Microsoft Office installed, you might also have that font even for other operating system versions). On other operating systems, such as Linux, if you find an easily accessible font that does the job, please let me know in a comment!
  2. It depends on either the Segoe UI Symbol or Segoe MDL2 Assets fonts, which are both proportional fonts. They are not monospace. So, this is not a permanent solution for everyday programming–it should only be used as a temporary change to print the desired characters. If anyone finds a monospace format that supports extended Unicode characters, please let me know in a comment!

With those limitations understood, here’s my solution:

  1. You need to configure Oracle SQL Developer to display with a font that supports such higher Unicode block characters. If the font cannot display the character, then SQL Developer can’t display it for you.
  2. Change the Code Editor font to Segoe UI Symbol (in Windows 10 and later, use Segoe MDL2 Assets): Tools | Preferences | Code Editor | Fonts:
    Change font to display advanced Unicode in Oracle SQL Developer

    Change font to display advanced Unicode in Oracle SQL Developer

  3. Get the numeric (decimal) code for the character you want. To do this, execute the query select ascii('🍁') from dual;
    • To get the character (such as 🍁) into the query code, you need to somehow copy it and then paste it into the code editor in SQL Developer.
    • The way I do this is to copy the character from one of many sites on the Web that lists Unicode characters. The one I use the most is the Unicode Character Search from FileFormat.info.
    • Once you find the character you want, you can copy the preview character, or else copy the character displayed under Java Data | string.toUpperCase().
  4. select ascii('🍁') from dual; should give you a long number as the query result. For 🍁, the result is 4036988289. Use that number and then execute
    select chr(4036988289) from dual; this should display the character you want. So then, simply use chr(4036988289) (replace the number with the appropriate one for your desired character) in the SQL code to display what you want.

As I mentioned, after doing the queries that required the special Unicode, since the fonts I specified are proportional fonts, you will probably want to change your font back to something monospace (like DialogInput) for your regular everyday programming.

2 Comments

  1. gives a gibberish result like: ☐1.

    even after i made the changes in preferences

    • So, if you followed all the instructions exactly, then what code did you enter that gave you the gibberish result? And what version of Windows are you running?

Discover more from Chitu Okoli

Subscribe now to keep reading and get access to the full archive.

Continue reading