I guess, the character Y goes into db as Y or C as C.
But it may take more than 1 byte if a Japanese client or a Chinese client enter's the value instead of English Client.

I was able to see the details (in specific CHAR ) in SQL Plus when I did 

SQL>desc STATEMENT_STATE

But when I do select * from cols, it is not showing CHAR.


These are the additional cols in 9.0.1 vs 8.1.7.
CHAR_LENGTH            
CHAR_USED              
V80_FMT_IMAGE          
DATA_UPGRADED          

But none of these columns are storing the string CHAR.
Where do Oracle stores CHAR.

Thanks
Reddy 

Mr. Tom I downloaded you presentation slides of oracle 9i
</code> http://asktom.oracle.com/~tkyte/9iClass/index.html <code>

in that demo0013.sql gave me the following :

demo@ORA92> @E:\mak\oracle\ask-tom\Presentations\9iTrain\9iTrain\demo013
demo@ORA92>
demo@ORA92> create table t ( a char(1), b char( 1 char ), c char(2000 char) );

Table created.

demo@ORA92>
demo@ORA92> insert into t (a) values ( unistr( '\00d6') );

1 row created.

demo@ORA92>
demo@ORA92> insert into t (b) values ( unistr( '\00d6') );

1 row created.

demo@ORA92>
demo@ORA92> select length(b), lengthb(b), dump(b) dump from t;

LENGTH(B) LENGTHB(B)
---------- ----------
DUMP
--------------------------------------------------------------------------------------------

NULL

1 1
Typ=96 Len=1: 63


demo@ORA92>
demo@ORA92> insert into t (c) values ( rpad( unistr( '\00d6'), 2000, unistr('\00d6') ) );

1 row created.

demo@ORA92>
demo@ORA92> insert into t (c) values ( rpad( unistr( '\00d6'), 1001, unistr('\00d6') ) );

1 row created.

demo@ORA92>
demo@ORA92> insert into t (c) values ( rpad( unistr( '\00d6'), 1000, unistr('\00d6') ) );

1 row created.

demo@ORA92>
demo@ORA92> drop table t;

Table dropped.

But according to your slides when you enter a unicode character which is more than one byte into a char(1) column it should give an error which is'nt the case here.

My DB CharSet is :
AMERICAN_AMERICA.WE8MSWIN1252

Any comments,
Thanks.

Dear Sir,
I tried the following code snippet but got errors.Could you
please correct this? and is there any other way that this
code can be made to work?
SQL> begin
  2   for c in chr(ascii('a')) .. chr(ascii('z')) loop
  3    dbms_output.put(c); 
  4   end loop;               
  5   dbms_output.put_line(' ');
  6  end;
  7  /
begin
*
ERROR at line 1: 
ORA-06550: line 2, column 11: 
PLS-00364: loop index variable 'CHR' use is invalid 
ORA-06550: line 2, column 2: 
PL/SQL: Statement ignored 


I want the alphabets a..z to be printed.Could you please help?
Thanks in advance

 

that loop is not really any differen then:

ops$tkyte@ORA920PC> begin
  2     for c in 'a' .. 'z'
  3     loop
  4        dbms_output.put_line( c );
  5     end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
 

<b> you need to iterate over a NUMBER.  perhaps this is what you want:</b>
 

ops$tkyte@ORA920PC> begin
  2     for i in ascii('a') .. ascii('z')
  3     loop
  4        dbms_output.put_line( chr(i) );
  5     end loop;
  6  end;
  7  /
a
b
c
d
 

"It would be a good idea to use the char(N char) and varchar2(N char) everywhere in new development."

This statement seems to violate the concept of using data types to help enforce integrity. If I have a field that I know will never hold anything other than US ascii I would use varchar2(N) and if I have a field than will be used to store international characters I would use varchar2(N char).

Our application under development right now is a perfect example of this. We use many alphanumic codes and these are all defined as varchar2(N). International characters as codes wouldn't make sense to us. We have other fields that can store names from around the world and these are varchar2(N char). Isn't this an appropriate approach?

The database supports multi-byte data.

One field will only hold data like 'abc' so it is varchar2(3). It will never have international characters. Another field can hold international characters so it is varchar2(3 char).

Why is this not a better approach than your recommendation of using varchar2(N char) throughout the application?

Tom,

(1) Towards globalization of the existing application on oracle 9i, to set the character semantics, is the following correct?

1. Take export of the application schema.
2. Change the nls length semantics to CHAR using alter system command.
3. restart the instance, drop the schema.
4. create the schema again and do import.

anthing is missed out here?

(2) Which is the best way to achieve this towards globalization for the existing applications on 8i?

(3) Is changing the width of all char and varchar2 four times of the existing size would be the best option to achieve this in our application running both on 8i and 9i?

Regards,
Venkat