Entering special characters fails in Oracle table

Question

I need to test if my application is reading special characters from the database and displaying them in exactly the same way. For this, I need to populate the database table with all special characters available. However, I am not sure how I can specify the special characters in the sql insert query. Can anyone please guide me to an example where I can insert a special character in the query? For simplicity sake, suppose the table is a City table with Area and Avg_Temperature being the 2 columns. If I need to insert the degree (celcius/farhenheit) symbol in Avg_Temperature column, how should I write the query?

---

*[Edit on 1/9/2012 at 2:50PM EST]*As per Justin Cave's suggestion below, I did following analysis:

Table: create table city(area number, avg_temperature nvarchar2(10));

Date: insert into city values (1100, '10◦C');

Query:
select dump(avg_temperature, 1010) from city where area = 1100;

O/P
DUMP(AVG_TEMPERATURE,1010)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
----------------------------------------------------------
Typ=1 Len=8 CharacterSet=AL16UTF16: 0,49,0,48,0,191,0,67                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

Query
select value$ from sys.props$ where name='NLS_CHARACTERSET';

O/P
VALUE$
----------------
WE8MSWIN1252

Query:
select value$ from sys.props$ where name='NLS_NCHAR_CHARACTERSET';

O/P
----------------
AL16UTF16

It seems that the insert does mess up the special characters as Justin Cave suggested. But I am not able to understand why this is happening? Can anyone please provide related suggestion?

Answer 1

First you need to know what the database character set is. Then you need to know what character set your "client" connection is using. Life is always easier if these are the same.

If your databse is utf-8 and your client is utf-8 then you don't need to do any character escaping you can just use the utf-8 encoding for the desired character.

In your example the degree character is unicode codepoint u+00b0.

In utf-8 this is a two-byte sequence: x'c2', x'b0'.

Answer 2

First you should not store the symbol as part of your column. That requires you to declare the column as VARCHAR which will give you lots of problems in the long run (e.g. you cannot sum() on them, you cannot avg() on them and so on)

You should store the unit in which the temperature was taken in a second column (e.g. 1 = celcius and 2 = fahrenheit) and translate this when displaying the data in the frontend. If you really want to store the symbol, declare the units columns as CHAR(1):

CREATE TABLE readings
(
    area               number(22),
    avg_temperature    number(10,3),
    units              varchar(2)
)

Then you can insert it as follows:

INSERT INTO readings 
 (area, avg_temperature, units)
VALUES 
 (1000, 12.3, '°C');

But again: I would not recommend to store the actual symbol. Store only the code!