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
LVL 76

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 ...
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 76

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 76

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

860 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