RE: special characters

From: Fuad Arshad <fuadar_at_yahoo.com>
Date: Fri, 28 May 2004 08:40:18 -0700 (PDT)
Message-ID: <20040528154018.46677.qmail@web80501.mail.yahoo.com>

well it dependas alot on the nls_lang settings as well as the terminals ability to display and insert the right amount of characters here is a note from metalink for unix. its pretty aold but it helped us when we were having similar issues.

Doc ID: Note:72343.1

Subject: 8 bit characters in a 7 bit database...

Type: BULLETIN

Status: PUBLISHED

Content Type: TEXT/PLAIN

Creation Date: 16-JUN-1999

Last Revision Date: 26-MAR-2002

Q What is the difference between a 7-bit database and

an 8-bit database?

A Even if the database has been created specifying 7-bits for storage,

all the data are stored as 8-bit values

Q Under UNIX how is it that I can see 8-bit data in a

7-bit database.

A This happens because the following conditions have

been setup either by accident or intentionally;

The terminal settings allow 8-bit characters
Same characterset as the database is being used for NLS_LANG in

the client environment (must be the default value, US7ASCII).

3) The database was created with the default storage

character set, US7ASCII.

With this setup the client-server connection, provided

via sqlnet, does not believe that any character set

translation is required during normal processing, thus

none is done.

This in itself is not incorrect but may lead to confusion

when 8-bit characters are returned (correctly) during

use.

This will lead to problems if the database storage characterset

is changed on a rebuild or the value of NLS_LANG gets defined

such that it no longer matches the database storage characterset.

To demonstrate the problem the following may be performed;

On a unix system set the terminal characteristics to the following

% stty -istrip cs8

This will set the terminal up to use an 8-bit character set and

to disallow the stripping of the eighth bit on display, so after

setting this up we should be able to see the "£" sign - if the

terminal and keyboard support it

2) Execute the following command via sqlplus and create a test

table as follows (The database should have been created with

the US7ASCII storage characterset).

Verify this by doing the following;

Connected.

SVRMGR> select * from nls_database_parameters;

           PARAMETER                      VALUE 

           ------------------------------ ---------------- 

           NLS_LANGUAGE                           AMERICAN 

           NLS_TERRITORY                           AMERICA 

           NLS_CURRENCY                                  $ 

           NLS_ISO_CURRENCY                        AMERICA 

           NLS_DATE_FORMAT                       DD-MON-YY 

           NLS_DATE_LANGUAGE                      AMERICAN 

           NLS_CHARACTERSET                       US7ASCII 

           NLS_SORT                                 BINARY 

           NLS_CALENDAR                          GREGORIAN 

           NLS_RDBMS_VERSION                     7.3.4.0.0 

           11 rows selected. 

           SVRMGR> 

           SQL> create table t1 ( c1 varchar2(1)); 

       3)  We should now be able to enter 8-bit characters, character codes 

           which have biniary values of greater than 127. We can use the "£" 

           character for our test 

            SQL> insert into t1 values ('£'); 

      4) When selecting from the table we should see the character we inserted. 

         Using the provided command we can also see the binary value for the 

         character data stored within the column. This value will be outside

         the 0-127 range. 

           SQL> select c1,dump(c1) from t1; 

           C 

           - 

           DUMP(C1) 

           ------------------------------------ 

           £ 

           Typ=1 Len=1: 163 

      5)  Now exit from sqlplus and reset the value of NLS_LANG to something 

          other that the default of AMERICAN_AMERICA.US7ASCII and restart 

          sqlplus. 



          % setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1 




          % echo $NLS_LANG 


          AMERICAN_AMERICA.WE8ISO8859P1 

      6)  Now do the same insert into the table and we can see that there has been

          some characterset translation due to the setting of the NLS_LANG

          environment variable. 

          The important observations to make are that as we have set

          NLS_LANG then characterset translation happens, hence we get the

          value of 35. Also as we have this environment variable setup the

          client on displaying the data show the '#' character. 

           SQL> insert into t1 values ('£'); 

           1 row created. 

           SQL> select c1,dump(c1) from t1; 

           C 

           - 

           DUMP(C1) 

           ---------------------------------- 

           # 

           Typ=1 Len=1: 163 

           # 

           Typ=1 Len=1: 35 

           SQL>

What does this all mean?

We recommend that NLS_LANG should always be set; this means that

errors of this type should not be seen. We also know that the client

tools provided by Oracle do expect this to be set else they will use

the default value of AMERICAN_AMERICA.US7ASCII.

"Steiner, Randy" <RASTEIN_at_NYCT.com> wrote: select chr(191) from dual;

C
-
?

Randy
-----Original Message-----
From: Luis deUrioste [mailto:Luis.deUrioste_at_L-3com.com] Sent: Friday, May 28, 2004 11:04 AM
To: oracle-l_at_freelists.org
Subject: RE: special characters

what about giving this a try, since those characters are the extended ASCII characters normally found in foreign languages

SQL> select chr(191) from dual;

C
-
?

SQL> -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Steiner, Randy Sent: Friday, May 28, 2004 7:47 AM
To: oracle-l_at_freelists.org
Subject: special characters

I have a field with an upside down question mark chr(191). When we do a select on it, the character shows up as a normal question mark chr(63). I found this out by doing

SELECT fld1 ascii(fld1)

and it showed:

? 191

Since the upside down character is what is really in the database, how can I show it when I do a query.

Thanks
Randy

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri May 28 2004 - 10:37:39 CDT