 | |
Toubles Storing ASCII character greater than 127
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Toubles Storing ASCII character greater than 127 -
04-20-2010
, 12:32 PM
#2
| |||
| |||
|
|
Hello all, Let me start by saying that I am new to Oracle so please excuse my ignorance. I have this problem whereby when I insert any characters from the the ASCII character set with code >= 127 Oracle seems to change its representation to a '?'--so when I select the data from either sqlplus or my program (perl using DBI) I do not get the same data I stored. My test table definition is as follows and I have tried this with both nvarchar2 and varchar2 with the same result. create table tester ( tester_id NUMBER(19,0) not null, tester_data varchar2(2000)); Here's my NLS_DATABASE_PARAMETERS table for your benefit: *PARAMETER * * * * * * * VALUE *----------------------- ---------------------------- *NLS_LANGUAGE * * * * * *AMERICAN *NLS_TERRITORY * * * * * AMERICA *NLS_CURRENCY * * * * * *$ *NLS_ISO_CURRENCY * * * *AMERICA *NLS_NUMERIC_CHARACTERS *., *NLS_CHARACTERSET * * * *AL32UTF8 *NLS_CALENDAR * * * * * *GREGORIAN *NLS_DATE_FORMAT * * * * DD-MON-RR *NLS_DATE_LANGUAGE * * * AMERICAN *NLS_SORT * * * * * * * *BINARY *NLS_TIME_FORMAT * * * * HH.MI.SSXFF AM *NLS_TIMESTAMP_FORMAT * *DD-MON-RR HH.MI.SSXFF AM *NLS_NCHAR_CHARACTERSET *AL16UTF16 *NLS_TIME_TZ_FORMAT * * *HH.MI.SSXFF AM TZR *NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR *NLS_DUAL_CURRENCY * * * $ *NLS_COMP * * * * * * * *BINARY *NLS_LENGTH_SEMANTICS * *BYTE *NLS_NCHAR_CONV_EXCP * * FALSE Any ideas will be greatly appreciated. George |
this does not prevent you from storing them, and AL32UTF8 is a
superset of ASCII anyways.
Oracle is quite helpful when translating characters, sometimes too
much so. It is important that everything from the client to the
server has the proper NLS settings, and you need to understand that
there are session settings too. There is a lot of documentation
available on tahiti.oracle.com specific to "globalization," look at
the book list for your version. By the way, what is your version?
10gR2 is not a version, 10.2.0.4 is a version.
My Oracle Support (MOS) also has many documents explaining NLS.
What may be happening (speculation here) is your perl environment is
setting NLS environment variables to the old long-ago default of
american ascii us7ascii 7-bit.
Please let us know exactly which versions of Oracle you are using on
both the server and the client, and platform information too. Let us
know the environment variables with NLS in them, and your session
settings. If Windows is involved, there may be additional things to
know.
You may even want to see if sqlplus can display the characters with
different NLS settings, and you can also do block dumps to see what
values are getting in there. If it is a display problem, that's not
so hard. If the wrong values are getting stored, it still may be a
simple matter of setting NLS properly.
Also, search the cdo.* archives, this has been discussed many times.
Also, beware of anything you read on the net, there is a lot of
misinformation floating about.
jg
--
@home.com is bogus.
lo world: http://www.lk.cs.ucla.edu/LK/Inet/1stmesg.html
#3
| |||
| |||
|
|
Hello all, Let me start by saying that I am new to Oracle so please excuse my ignorance. I have this problem whereby when I insert any characters from the the ASCII character set with code >= 127 Oracle seems to change its representation to a '?'--so when I select the data from either sqlplus or my program (perl using DBI) I do not get the same data I stored. |
the database server do you see the same behaviour? What are the NLS
settings on the client? This is where your problem orignates, I
expect.
Quote:
|
My test table definition is as follows and I have tried this with both nvarchar2 and varchar2 with the same result. create table tester ( tester_id NUMBER(19,0) not null, tester_data varchar2(2000)); Here's my NLS_DATABASE_PARAMETERS table for your benefit: *PARAMETER * * * * * * * VALUE *----------------------- ---------------------------- *NLS_LANGUAGE * * * * * *AMERICAN *NLS_TERRITORY * * * * * AMERICA *NLS_CURRENCY * * * * * *$ *NLS_ISO_CURRENCY * * * *AMERICA *NLS_NUMERIC_CHARACTERS *., *NLS_CHARACTERSET * * * *AL32UTF8 *NLS_CALENDAR * * * * * *GREGORIAN *NLS_DATE_FORMAT * * * * DD-MON-RR *NLS_DATE_LANGUAGE * * * AMERICAN *NLS_SORT * * * * * * * *BINARY *NLS_TIME_FORMAT * * * * HH.MI.SSXFF AM *NLS_TIMESTAMP_FORMAT * *DD-MON-RR HH.MI.SSXFF AM *NLS_NCHAR_CHARACTERSET *AL16UTF16 *NLS_TIME_TZ_FORMAT * * *HH.MI.SSXFF AM TZR *NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR *NLS_DUAL_CURRENCY * * * $ *NLS_COMP * * * * * * * *BINARY *NLS_LENGTH_SEMANTICS * *BYTE *NLS_NCHAR_CONV_EXCP * * FALSE Any ideas will be greatly appreciated. George |
your Oracle client is configured as I expect it's not the same as the
database server and therein lie the problems.
David Fitzjarrell
#4
| |||
| |||
|
|
Hello all, Let me start by saying that I am new to Oracle so please excuse my ignorance. I have this problem whereby when I insert any characters from the the ASCII character set with code >= 127 Oracle seems to change its representation to a '?'--so when I select the data from either sqlplus or my program (perl using DBI) I do not get the same data I stored. My test table definition is as follows and I have tried this with both nvarchar2 and varchar2 with the same result. create table tester ( tester_id NUMBER(19,0) not null, tester_data varchar2(2000)); Here's my NLS_DATABASE_PARAMETERS table for your benefit: *PARAMETER * * * * * * * VALUE *----------------------- ---------------------------- *NLS_LANGUAGE * * * * * *AMERICAN *NLS_TERRITORY * * * * * AMERICA *NLS_CURRENCY * * * * * *$ *NLS_ISO_CURRENCY * * * *AMERICA *NLS_NUMERIC_CHARACTERS *., *NLS_CHARACTERSET * * * *AL32UTF8 *NLS_CALENDAR * * * * * *GREGORIAN *NLS_DATE_FORMAT * * * * DD-MON-RR *NLS_DATE_LANGUAGE * * * AMERICAN *NLS_SORT * * * * * * * *BINARY *NLS_TIME_FORMAT * * * * HH.MI.SSXFF AM *NLS_TIMESTAMP_FORMAT * *DD-MON-RR HH.MI.SSXFF AM *NLS_NCHAR_CHARACTERSET *AL16UTF16 *NLS_TIME_TZ_FORMAT * * *HH.MI.SSXFF AM TZR *NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR *NLS_DUAL_CURRENCY * * * $ *NLS_COMP * * * * * * * *BINARY *NLS_LENGTH_SEMANTICS * *BYTE *NLS_NCHAR_CONV_EXCP * * FALSE Any ideas will be greatly appreciated. George |
the data in the daabase just fine but be unable to display it because
your client character set does not support it.
What is the full database version?
What is the client version?
How did you insert the data?
With what tool are you retrieving it? On what platform?
HTH -- Mark D Powell --
#5
| |||
| |||
|
|
| Carp::croak(DBI->errstr); |
Thank you again for your help, much appreciated.
George
#6
| |||
| |||
|
|
Thank you guys, I meant to write a response yesterday once I found the solution but I was too tired  .I found reading material about oracle's charsets and I run into all the different NLS options. Armed with that I was able to determine the connection options I have to setup in Perl in order to match the client with the server. Here are a few more details on the problem: 1) I am reading from an Informix table containing zlib compressed data stored in a lvarchar. 2) I need to store them in a mirror image table without losing integrity of the data because I can no longer un-compress them. The solution was finding an appropriate charset that will not messup the compressed data |
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
#7
| |||
| |||
|
|
El 21/04/2010 15:31, George K. escribió/wrote: Thank you guys, I meant to write a response yesterday once I found the solution but I was too tired .I found reading material about oracle's charsets and I run into all the different NLS options. *Armed with that I was able to determine the connection options I have to setup in Perl in order to match the client with the server. Here are a few more details on the problem: 1) I am reading from an Informix table containing zlib compressed data stored in a lvarchar. 2) I need to store them in a mirror image table without losing integrity of the data because I can no longer un-compress them. The solution was finding an appropriate charset that will not messup the compressed data Er... The solution for storing binary data is BLOB, isn't it? -- --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web:http://borrame.com -- Mi web de humor satinado:http://www.demogracia.com -- |
all those \0 \\ characters that may messup the data when stored in a
varchar2. I also believe blobs add overheard both in space and in
performance to the DB. Moreover, it is also a matter of, if its done
in informix why can't be done in Oracle.
Thanks again for your responses.
George
#8
| |||
| |||
|
|
Thank you guys, I meant to write a response yesterday once I found the solution but I was too tired .I found reading material about oracle's charsets and I run into all the different NLS options. *Armed with that I was able to determine the connection options I have to setup in Perl in order to match the client with the server. Here are a few more details on the problem: 1) I am reading from an Informix table containing zlib compressed data stored in a lvarchar. 2) I need to store them in a mirror image table without losing integrity of the data because I can no longer un-compress them. The solution was finding an appropriate charset that will not messup the compressed data, I was only able to achive this with the WE8ISO8859P9 charset and here's how I used it: * my $dbhOracle = DBI->connect('dbi:Oracle:'.$dbname,$user,$password, * * * * * * * * * * * { * * * * * * * * * * * * 'RaiseError' => 0, * * * * * * * * * * * * 'AutoCommit' => 0, * * * * * * * * * * * * 'PrintError' => 0, * * * * * * * * * * * * 'ora_charset'=> 'WE8ISO8859P9', * * * * * * * * * * * }) * * || Carp::croak(DBI->errstr); Thank you again for your help, much appreciated. George |
any time Oracle has to make a character conversion it may do so. I
don't recall if there is a P9 to AL32UTF8 issue, but something is
tickling the back of my brain that there are some obscure issues -
maybe having to do with the Euro sign? I know I've seen docs about
it, just can't remember the details. If you are relying on no
translation or strict supersets to do what you want (ie, nothing) with
binary data, you might have obscure intermittent problems. See the
docs or google about the csscan utility, you can use it to see if your
existing data is susceptible. But perhaps it isn't existing data you
have to worry about. Maybe you can make a quick test of storing all
characters and using the utility to see. If you are converting, you
have to be careful that all tools do the same conversion - this is a
classic issue with imp/exp.
As far as why Informix can do something Oracle can't... my search for
lvarchar at ansi.org yielded no results :-)
jg
--
@home.com is bogus.
http://www.twominutenews.com/2010/te...ased-8549.html
#9
| |||
| |||
|
|
On Apr 21, 6:31*am, "George K." <kara... (AT) gmail (DOT) com> wrote: Thank you guys, I meant to write a response yesterday once I found the solution but I was too tired .I found reading material about oracle's charsets and I run into all the different NLS options. *Armed with that I was able to determine the connection options I have to setup in Perl in order to match the client with the server. Here are a few more details on the problem: 1) I am reading from an Informix table containing zlib compressed data stored in a lvarchar. 2) I need to store them in a mirror image table without losing integrity of the data because I can no longer un-compress them. The solution was finding an appropriate charset that will not messup the compressed data, I was only able to achive this with the WE8ISO8859P9 charset and here's how I used it: * my $dbhOracle = DBI->connect('dbi:Oracle:'.$dbname,$user,$password, * * * * * * * * * * * { * * * * * * * * * * * * 'RaiseError' => 0, * * * * * * * * * * * * 'AutoCommit' => 0, * * * * * * * * * * * * 'PrintError' => 0, * * * * * * * * * * * * 'ora_charset'=> 'WE8ISO8859P9', * * * * * * * * * * * }) * * || Carp::croak(DBI->errstr); Thank you again for your help, much appreciated. George I'm not sure if this is an issue for you, but there's a possibility any time Oracle has to make a character conversion it may do so. *I don't recall if there is a P9 to AL32UTF8 issue, but something is tickling the back of my brain that there are some obscure issues - maybe having to do with the Euro sign? *I know I've seen docs about it, just can't remember the details. *If you are relying on no translation or strict supersets to do what you want (ie, nothing) with binary data, you might have obscure intermittent problems. *See the docs or google about the csscan utility, you can use it to see if your existing data is susceptible. *But perhaps it isn't existing data you have to worry about. *Maybe you can make a quick test of storing all characters and using the utility to see. *If you are converting, you have to be careful that all tools do the same conversion - this is a classic issue with imp/exp. As far as why Informix can do something Oracle can't... my search for lvarchar at ansi.org yielded no results :-) jg -- @home.com is bogus.http://www.twominutenews.com/2010/te...ings-have-incr... |
.I have actually created such a program, write all characters 0-256 and
retrieve them with no loss of information.
Many thanks again for the responses
.George
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.