How to store ® in oracle table?

Question

I need to insert value into an Oracle table 'RR® 5'?

I created a table column varchar2 data type. But when I am displaying the data it is showing: 'RR?? 5'

Any idea, please.

Answer 1

First of all you have to check if your database runs on character encoding which supports your special characters.

Execute this query to check.

SELECT * 
FROM V$NLS_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Character sets like AL32UTF8, UTF8, WE8ISO8859P1 are able to store special characters ®. In order to check whether your characters are supported you can run this command

SELECT 
    ASCIISTR(CAST(UNISTR('\00C2') AS VARCHAR2(4))) AS VARCHAR_SUPPORT, 
    ASCIISTR(CAST(UNISTR('\00C2') AS NVARCHAR2(4))) AS NVARCHAR_SUPPORT
FROM dual;


VARCHAR_SUPPORT   NVARCHAR_SUPPORT                        
----------------  ------------------------
\00C2             \00C2                                   

1 row selected.

If your result value (00C2) is equal to your input value then your characters are supported.

Suppose your database supports the characters (otherwise the game ends here)

Solution 1

Run this command from any SQL editor or from SQL*plus

INSERT INTO THE_TABLE VALUES (UNISTR('RR\00C2\00AE 5'));

Depending on your actual requirements this can be a very tedious work.

Solution 2

Use SQL*plus command. For this you have to set NLS_LANG environment variable according to code page of cmd

C:\>chcp
Active code page: 850

C:\>set NLS_LANG=.WE8PC850

C:\>sqlplus user/pwd@DB

SQL> INSERT INTO THE_TABLE VALUES ('®');

When you work on Linux/Unix the chcp equivalent is locale charmap or echo $LANG. You can also change current code page, e.g. chcp 65001 for UTF-8

Solution 3a

Create a SQL script containing the same INSERT command as above, and save it with local encoding (typically Windows CP1252, many times misnamed as ANSI)

c:\>set NLS_LANG=.WE8MSWIN1252

c:\>sqlplus user/pwd@DB @Special_Char_CP1252.sql

Solution 3b

Create a SQL script and save it with UTF-8 which is supported by most modern editors. Note: SQL scripts must not contain a BOM (Byte Order Mark), otherwise SQL*plus will raise an error. I found this tiny command line tool to add and remove BOM from arbitary text files: UTF BOM Utils

c:\>set NLS_LANG=.AL32UTF8

c:\>sqlplus user/pwd@DB @Special_Char_UTF8.sql

Note for solutions above: you can skip the set NLS_LANG command in case your registry is set accordingly. Check with this command

C:\>REM for x64 Oracle Client

c:\>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ /s /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1
    NLS_LANG    REG_SZ    AMERICAN_SWITZERLAND.AL32UTF8

End of search: 1 match(es) found.    

C:\>REM for x86 Oracle Client

c:\>reg query HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ /s /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1
    NLS_LANG    REG_SZ    AMERICAN_SWITZERLAND.AL32UTF8

End of search: 1 match(es) found.

C:\>

Solution 4

Run your INSERT statements from a more sophisticated editor like TOAD or SQL Developer. Ensure that NLS_LANG value in Registry, resp. your system environment variable matches the file save options of the editor.

Some external resources which may help you:

• Oracle documentation NLS_LANG Settings in MS-DOS Mode and Batch Mode

• List of Code pages: Code page

• Find a Unicode Codepoint: Codepoints