Solved

Trouble with unicode and Oracle 8.0

Posted on 2004-09-17
8
3,047 Views
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.
SK
0
Comment
Question by:Kingjava
8 Comments
 
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:
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16

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.
0
 

Author Comment

by:Kingjava
ID: 12088503
Both NLS_CHARACTERSET and NLS_NCHAR_CHARACTER are UTF8.
0
 
LVL 9

Expert Comment

by:konektor
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 ...
0
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?
0
 

Author Comment

by:Kingjava
ID: 12103692
how do I check the nls_lang on the client? FYI. the client is run on a linux box.
0
 
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
0
 
LVL 1

Accepted Solution

by:
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 8.1.5.5.  This is the support for Unicode (UTF-16) Windows API.


Best regards,

Sergiusz
0

Featured Post

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.

Join & Write a Comment

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…
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…
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 how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now