Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Trouble with unicode and Oracle 8.0

Posted on 2004-09-17
Medium Priority
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 http://www.winnetmag.com/Article/ArticleID/14399/14399.html)  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 ...
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

610 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