Trouble with unicode and Oracle 8.0

Posted on 2004-09-17
Last Modified: 2009-12-16
I am trying to get unicode strings into an Oracle 8.0 database but am having difficulties. If I use SQLPlus and copy and paste the insert statement into the database, any special characters are inserted as ? or something like that. I then try to call a sql file that has been encoded to UTF-8 and the outcome is the same. I wrote a small ColdFusion page that parses through a sql statement and executes it against the database, but I haven't found an ODBC driver that I can connect to Oracle 8.0 that also supports unicode.

I have heard a little bit about having to "prefix" unicode strings with a literal N (see  but I have no idea what this means.

Does anyone know how to get unicode data into an Oracle 8.0 database? I'm going to be bald if I don't figure this one out soon!

Many thanks.
Question by:Kingjava
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12087766
What is the characterset of the database?

select * from nls_database_parameters;

And you are looking for the following entries:

To properly store this information in fields like varchar2, the charactersets have to match.  If these match, you then need to look at the NLS_LANG client variable.  This tells Oracle how to translate the data between the client and server.

In doing some reading on this in the past, it appears that if you use CLOB datatypes, a lot of this is taken care of for you.  The drawback:  ODBC doesn't support CLOBs.

I would look into using either the OO4O (Oracle objects for OLE) or the OLEDB drivers over ODBC.

Author Comment

ID: 12088503

Expert Comment

ID: 12099925
open your sql script in notepad.
change characters you want to store to that one, u want - in insert scripts, some unicodes
save the document, but in save dialog, choose field encoding to unicode (default is ansi, there are 3 unicodes, i don't remember which is correct)
run script in sqlplus ...
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12101943
What is the nls_lang on your client set to?

Author Comment

ID: 12103692
how do I check the nls_lang on the client? FYI. the client is run on a linux box.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12104038
If memory serves me right, it's an environment variable on UNIX (double check this with the docs).

As a side note:  Just because sqlplus displays the character as '?' doesn't necessarily mean it is stored incorrectly.  It may mean that sqlplus just doesn’t know how to display it.

To verify this, try:
select rawtohex(<column>) from <table>; -- or maybe use: rawtohex(substr(<column>,<start_pos>,<length>)) to shorten the output

Accepted Solution

swolicki earned 250 total points
ID: 12159712

To store Unicode UTF-8 codes in Oracle8's (VAR)CHAR2 variables you have two choices, provided NLS_CHARACTERSET in NLS_DATABASE_PARAMETERS is UTF8:

1. write the SQL script in UTF-8.   You can use Notepad on W2K and transfer the script to Linux or use an UTF-8-capable editor on Linux.  Note, in Notepad use "UTF-8" in Encoding field of the Save dialog box.  Do not use "Unicode" as it would create an UTF-16 file.

Run the script in SQL*Plus with NLS_LANG=.UTF8 set in the environment.

e.g. export NLS_LANG=.UTF8  (or setenv in csh)

You will not be able to see the non-ASCII letters correctly on your Linux terminal unless the terminal is configured for UTF-8.  You can use the SELECT DUMP(column) to verify codes in the database.

2. use the CHR function, that is:

   CHR(<code1>) || CHR(<code 2>) || ....

where <code n> is a decimal number calculated from the UTF-8 code bytes of a character by treating the bytes (1, 2 or 3 bytes depending on the character) as a big endian integer:

for 1-byte codes:  code n = byte value (in decimal)
for 2-byte codes:  code n = 256 * 1st byte value + 2nd byte value
for 3-byte codes:  code n = 65536 * 1st byte + 256 * 2nd byte + 3rd byte

NLS_LANG is irrelevant in such case.

You will not be able to see the non-ASCII letters correctly on your Linux terminal unless the terminal is configured for UTF-8.  You can use the SELECT DUMP(column) to verify codes in the database.

In this option you will not be able to see the non-ASCII letters correctly on your Linux terminal either, unless the terminal is configured for UTF-8.  You can use the SELECT DUMP(column) to verify codes in the database.

Oracle ODBC (Windows) supports Unicode starting from version  This is the support for Unicode (UTF-16) Windows API.

Best regards,


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question