CODEWITHSUNDEEP
oracle-databaseunicode
Joe DeRose
Joe DeRose

Reputation: 3558

Unable to read Unicode characters from Oracle variable

I'm using an Oracle database that is not globally set up for Unicode -- but I have been able to work with Unicode characters as needed using the NVARCHAR2 data type in columns where it is needed.

This has worked fine for data stored in the database -- but my current use case is for a temporary variable. For the purposes of the question, the following illustrates my problem:

VAR TEST NVARCHAR2(50);
DEFINE TEST = 'This string contains “Unicode” characters';

SELECT '&&TEST' AS TEST
FROM DUAL;

I would expect this output to be:

This string contains “Unicode” characters

But instead it is:

This string contains ¿Unicode¿ characters

Is this failure because of the global database settings? If so, is there a workaround without changing those settings?

(I tested some of the solutions I found in other questions, such as experimenting with CAST and TO_NCHAR, and also saving the script on a page explicitly coded for UTF-8 -- but none of those strategies was successful. I think the fact that I am using a local variable distinguishes this from existing questions.)

Follow-up to Comments

laubster asked for the output from:

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'

That output is WE8ISO8859P15.

Alex Poole asked about the client and character settings.

The client is SQL Developer. OS is Windows 10. I'm not entirely sure where to look for client character settings -- but perhaps the answer is the same as the result from laubster's query: WE8ISO8859P15.

In any event, that may be a moot point. The N'text' strategy (which I'd never heard of before) worked.

I think I've got what I need. It doesn't look like Alex Poole is hurting for points -- but if he* wants to post that as an answer, I'll certainly upvote and mark as correct.

* I'm assuming "he" based on the avatar. Apologies if I'm mistaken.

Upvotes: 2

Views: 2980

Answers (1)

Joe DeRose
Joe DeRose

Reputation: 3558

Problem

Okay, based on the information provided by Alex Poole, the key to making this work is the N'text' notation for a text literal. Going back to the example in my question above:

VAR TEST VARCHAR2(50);
DEFINE TEST = 'This string contains “Unicode” characters';

SELECT '&&TEST' AS TEST
FROM DUAL;

Gives the result:

This string contains ¿Unicode¿ characters

Solution

But the N'text' strategy:

SELECT N'&&TEST' AS TEST
FROM DUAL;

Gives the expected result:

This string contains “Unicode” characters

In my real-world example, this functionality was wrapped in a CASE statement, which added a little more complexity. If I used the N'text' notation only in the line where Unicode characters were expected, it triggered error "ORA-12704: character set mismatch". to make it work, I had to add the N in front of the non-Unicode text in the ELSE line as well:

SELECT
  CASE
    WHEN 1 = 1 THEN N'&&TEST'
    ELSE N'(Some other output)'
  END AS TEST_OUTPUT
FROM DUAL;

Settings Information

In case it is helpful to others in a similar situation, here are the relevant settings that Alex Poole suggested I provide:

  • Oracle version: 11g (11.2.0.4.0) - 64 bit
  • NLS_CHARACTERSET: WE8ISO8859P15
  • NLS_NCHAR_CHARACTERSET: AL16UTF16
  • SQL Developer version: 4.1.5.21
  • SQL Developer Encoding: UTF-8

Upvotes: 1

Related Questions