CODEWITHSUNDEEP
databaseoracle-databasecharacter-encoding
m_beta
m_beta

Reputation: 162

How to properly insert some special characters like ¤ to Oracle database

I have an Oracle database. I have to execute some insert scripts that fill a NVARCHAR2 column. The insert statements include some special character like ¤

Insert into myTable (column1, column2) values(1, 'ABC-XYZ-SET-00985203-INS-01_2_10 | (100-BASE¤BASE ART¤3) | (100-SHELL¤SHELL ART¤1) |');

When I run the query all the special character is replaced with ¿ symbol

Database NLS parameters are as follows:

NLS_NCHAR_CHARACTERSET        UTF8                                                            
NLS_CHARACTERSET              WE8ISO8859P1

I've the special character multiple times repeated in a string. What should I do for inserting the character I mentioned above ¤ appropriately?

Upvotes: 0

Views: 1434

Answers (2)

Jon Heller
Jon Heller

Reputation: 36798

The safest way to insert non-ASCII characters might be to use the UNISTR function. Using the Unicode encoding value instead of the actual character is less convenient, but also less likely to be misinterpreted by whatever future programs run your code.

Insert into myTable (column1, column2) values
(
    1,
    'ABC-XYZ-SET-00985203-INS-01_2_10 | (100-BASE'||unistr('\00A4')||'BASE ART'||unistr('\00A4')||
    '3) | (100-SHELL'||unistr('\00A4')||'SHELL ART'||unistr('\00A4')||'1) |'
);

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

First of all, you need to set also NLS_LANG on your client that supports unicode characters. Then you can use N' nchar literals: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-192417E8-A79D-4A1D-9879-68272D925707

Insert into myTable (column1, column2) 
values(1, N'ABC-XYZ-SET-00985203-INS-01_2_10 | (100-BASE¤BASE ART¤3) | (100-SHELL¤SHELL ART¤1) |');

Notice N before `

Upvotes: 1

Related Questions