Oracle cannot store emoji

Question

I have a problem saving emoji to the Oracle database. This is a simple Java program which takes user input from the webpage and save it to the database.

This is what it looks like before I save to DB.

And this is what it looks like when it retrieves it from database.

The database column defined as CLOB so my expectation that it persist text with emoji just fine.

I also tried to save and retrieve emoji with sqlplus and have no luck as well:

SQL> update emoji_table set emoji_column = 'Test ' where emoji_table_id = 123;

1 row updated.

SQL> select emoji_column from emoji_table where emoji_table_id = 123;

EMOJI_TABLE
--------------------------------------------------------------------------------
Test ????

I assume there might be an issue with Oracle character encoding or something like that. Could you please advice what might be wrong and where to look for potential fix.

Thank you

UPDATE #1

As suggested by Wernfried Domscheit I checked my NLS_CHARACTERSET and it appears to be WE8MSWIN1252.

As I understand this is the root cause of the issue, it is not UTF-8, hence doesn't have reserved list of emoji.

The output of the

select DUMP(emoji_column, 1016) from emoji_table where emoji_table_id = 123

is following

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Could you please advice how to workaround?

UPDATE #2

Here is the output of DUMP sql query:

select DUMP(TO_CHAR(emoji_column), 1016) from emoji_table where emoji_table_id = 123

DUMP(TO_CHAR(TEMPLATE),1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=WE8MSWIN1252: 42,6f,64,79,20,bf

As I can see CharacterSet is WE8MSWIN1252 which matches the NLS_CHARACTERSET of the Oracle instance.

Is there a workaround to save emoji with WE8MSWIN1252?

Probably Base64 encoding could be a workaround but I would like to know if there is a better solution.

You need to make your column `NCLOB` to support unicode or change to database itself to support unicode. See [Supporting Multilingual Databases with Unicode](https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm) — Filburt, Oct 05 '18 at 13:09
If you have not seen it already, http://www.dba-oracle.com/t_store_emoticons_table.htm — PM 77-1, Oct 05 '18 at 13:09
What do you get from `select DUMP(emoji_column, 1016) from emoji_table where emoji_table_id = 123`? — Wernfried Domscheit, Oct 05 '18 at 14:25
Please see my update. Actually there would be workaround, see https://stackoverflow.com/questions/47039666/if-we-have-us7ascii-characterset-why-does-it-let-us-store-non-ascii-characters/47051107#47051107 but I really do not recommend it! — Wernfried Domscheit, Oct 05 '18 at 21:02

thatjeffsmith · Answer 1 · 2018-10-05T19:53:15.217

You can totally store emoji's, or any other multibyte character in an Oracle DB.

And see them in SQL Developer.

My DB has a UTF-8 characterset.

My SQL Developer has an emoji-friendly display font.

I have a text field wide enough to store these.

With display font set to 'Segoe UI Emoji

These are stored in a CLOB, but a wide enough VARCHAR2 would work just as well.

If your db charset isn't UTF-8 or similar, you may need to rely on the NCLOB or NVARCH2 data types assuming you have extended character set available.

Wernfried Domscheit · Accepted Answer · 2018-10-05T21:02:36.830

In order to see emoji in sql*plus do following:

Choose a font which is capable to display emojis (see FACE WITH TEARS OF JOY (U+1F602) Font Support)
Set character set of your console to UTF-8
Set NLS_LANG accordingly
Run slqplus

On Windows it would be:

chcp 65001
set NLS_LANG=.AL32UTF8
sqlplus ...

Regarding settings in java, please have a look at Database JDBC Developer's Guide - Globalization Support. ~~Since you get data as ???? I assume the value is properly inserted into database as is U+1F602, which is in UTF-8 F0 9F 98 82, i.e. 4 Bytes.~~

Update:

You cannot store Unicode emojis when your character set is WE8MSWIN1252 which is only single-byte. Either use NCLOB data type or migrate your database to UTF-8, see Character Set Migration

Oracle cannot store emoji

2 Answers2

Linked