Prev: Materialized Views
Next: Toubles Storing ASCII character greater than 127
From: joel garry on 20 Apr 2010 14:15
On Apr 20, 10:32 am, "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

Characters > 127 are not part of the ASCII characterset. However,
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
 | 
Pages: 1
Prev: Materialized Views
Next: Toubles Storing ASCII character greater than 127