Prev: Toubles Storing ASCII character greater than 127
Next: How many hours take an Oracle RAC implementation?
From: George K. on 20 Apr 2010 13:32
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
From: ddf on 20 Apr 2010 16:11
Comments embedded.

On Apr 20, 1:32 pm, "George K." <kara...(a)gmail.com> wrote:
> 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.
>

These inserts are from a client machine I presume. If you do this on
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.

> 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

Those are server settings, not the client-side values. Check how
your Oracle client is configured as I expect it's not the same as the
database server and therein lie the problems.


David Fitzjarrell
From: Mark D Powell on 20 Apr 2010 16:31
On Apr 20, 1:32 pm, "George K." <kara...(a)gmail.com> wrote:
> 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 database has a characterset as does the client. You might have
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 --
From: George K. on 23 Apr 2010 11:04
On Apr 21, 9:40 am, joel garry <joel-ga...(a)home.com> wrote:
> On Apr 21, 6:31 am, "George K." <kara...(a)gmail.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/technology/apple-earnings-have-incr...

Thanks for the insights and the warning :).
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
From: George K. on 21 Apr 2010 09:31
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
 |  Next  |  Last
Pages: 1 2
Prev: Toubles Storing ASCII character greater than 127
Next: How many hours take an Oracle RAC implementation?