How to store special characters in oracle?-CodePudding

I trying to save a string with special characters in oracle database. In this string contains all available characters in keyboard phone. The problem: some characters turned '¿'. I need to store the exact characters I inserted in.

I simulated some cases with the bellow code:

1 - I created a test table with some columns of different type, clob, nclob, varchar2, nvarchar2

create table specialCharacters (
  testColumn1 clob null,
  testColumn2 nclob null,
  testColumn3 varchar2(1000) null,
  testColumn4 nvarchar2(1000) null
);

2 - I wrote a example string with some special characters based on the keyboard fone:

' ¹⅕⅙⅛¹½½⅓¼²⅔'

';,¿‽?`•~|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!'

3 - I inserted this strings in all columns in specialCharacters, I tried of two differents methods in clob and nclob:

insert into specialCharacters values 
  (' ¹⅕⅙⅛¹½½⅓¼²⅔', 
   ' ¹⅕⅙⅛¹½½⅓¼²⅔', 
   ' ¹⅕⅙⅛¹½½⅓¼²⅔', 
   ' ¹⅕⅙⅛¹½½⅓¼²⅔'
  );

insert into specialCharacters values 
  ( ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!',
   ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!',
   ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!',
   ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!'
  );
  
  
insert into specialCharacters values 
  (TO_CLOB(' ¹⅕⅙⅛¹½½⅓¼²⅔'), 
   TO_NCLOB(' ¹⅕⅙⅛¹½½⅓¼²⅔'), 
   ' ¹⅕⅙⅛¹½½⅓¼²⅔', 
   ' ¹⅕⅙⅛¹½½⅓¼²⅔'
  );
  
insert into specialCharacters values 
  ( TO_CLOB(';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!'),
   TO_NCLOB(';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!'),
   ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!',
   ';,¿‽?`•~\|□{}€£¥₩♣︎♧¿◆◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《》¡¿,.#?$!%^@,?!!'
  );

4 - the select result in specialCharacters:

select * from specialCharacters;

Edit

NLS_CHARACTERSET:

NLS_NCHAR_CHARACTERSET:

The function dump in clob columns doesn't appear to work, but in testColumn3 and testColumn4 returned this result:

Finally, I tried to change the character to AL32UTF8, but doesn't work, I think the pattern character already covers the AL32UTF8 character

CodePudding user response：

Can you check the charset on the database, recommended is AL32UTF8. If its different you may probably have to alter the character on your DB. If its live/prod DB backup is advised.

select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET');

Refer this Oracle doc on charset

CodePudding user response：

Never call alter database character set ...! You may corrupt your entire database.

Character set WEMSWIN1252 cannot not store characters like ◇♥︎♡♠︎♤■□●○•°★☆▪︎¤《. You need to use an Unicode character set, preferably UTF-8, aka. AL32UTF8. For proper migration see (see Character Set Migration and/or Oracle Database Migration Assistant for Unicode)