Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert/Update into NVARCHAR2 column in win1252 DB

David WiltonMay 16 2013 — edited May 16 2013
Hi, I'm having a problem inserting utf8 data into utf8 nvarchar column

We are on oracle db 11gR2 (11.2.0.3.0)
Our db characterset is WE8MSWIN1252
Our nchar db characterset is UTF8

I created a Nvarchar2 column but I am unable to insert utf8 characters into this column from sql or plsql without losing most of the characters to upside down question marks. I can copy paste directly into say Toad and save without issues.

Here is an example of my problem:

select * from nls_database_parameters;

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET UTF8
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.3.0

create table test_nchar (my_col nvarchar2(100));
insert into test_nchar (my_col) values ('£ ¢ ¥ € ½ ½ ¼ ¾ ⅜ Å ( Å κ ε ω θ ρ Ω µ Ω Δ ∑ π α τ βγδΣΥηλξφρσψΔÅ');
select * from test_nchar gives:
£ ¢ ¥ € ½ ½ ¼ ¾ ¿ Å ( Å ¿ e ¿ ¿ ¿ O µ O ¿ ¿ p a t ß¿dS¿¿¿¿f¿s¿¿Å

Thinking it might be a client bug I created a database procedure to update the table

create or replace procedure test_update_utf8 as
begin
update test_nchar
set my_col = to_char(sysdate,'dd/mm/yyyy hhmiss')||to_nchar('£ ¢ ¥ € ½ ½ ¼ ¾ ⅜ Å ( Å κ ε ω θ ρ Ω µ Ω Δ ∑ π α τ βγδΣΥηλξφρσψΔÅ');
end;
/

select * from test_nchar gives:
16/05/2013 114803£ ¢ ¥ € ½ ½ ¼ ¾ ¿ Å ( Å ¿ e ¿ ¿ ¿ O µ O ¿ ¿ p a t ß¿dS¿¿¿¿f¿s¿¿Å

I created a database job that calls the procedure but still get the same result.

Why can I not insert utf8 characters into an utf8 defined column?

Thanks

Dave
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 13 2013
Added on May 16 2013
3 comments
4,230 views