oracle数据库中的系统自带表情,Oracle无法存储表情符号-CSDN博客

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.

解决方案

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