How to Identify the NLS column

[venkateshk]

Hi folks,

Is there any way to identify the NLS column in the table. I am having a table which consists of 200 columns. Have to identify the NLS columns from table without viewing the data.

[jmodic]

What do you mean by "NLS column"?

[venkateshk]

Originally posted by jmodic
What do you mean by "NLS column"?

Natinal Language Set column . Other than english (default - single byte). we have some Japanese character set values in the some columns. so we used to call NLS columns.

[jmodic]

OK, so those column must be of type NCHAR, NVARCHAR2 or NCLOB. So simply describe your table and find those columns. Or you can:

SELECT column_name, data_type FROM user_tab_columns
WHERE table_name = 'MY_TABLE' AND data_type IN ('NCHAR', 'NVARCHAR2', NCLOB');

[TimHall]

Not necessarily. On one of our old 7.3.4 UNIX boxes we have Japanese characters stored in VARCHAR2 datatypes using the US7ASCII characterset.

Doing a desc of the table does not show which columns contain these characters. Also, there is a mix of Japanese and English in the same columns.

Fun fun fun!

[jmodic]

Originally posted by TimHall
Not necessarily. On one of our old 7.3.4 UNIX boxes we have Japanese characters stored in VARCHAR2 datatypes using the US7ASCII characterset.

I wonder how this can be!? AFAIK US7ASCII is 7-byte characterset, so it properly stores only ASCII codes from 0 to 127. You can't even store West European special characters in there, because MSB bit is trimmed off, so I wonder how were you able to stuff Japanese characters in there (or beter to say, how did you manage to retrieve them properly afterwards)?

[TimHall]

To be honest I'm not totally sure. I wasn't here when it got set up.

It works through a third party bit of software. When a Japanese character is typed the software notices, converts it to two individual character codes that represent the multibyte character. These are then stored in the database.

When a select is done you just get gibberish (non-printable characters) unless you are running this software. When the software is running it intercepts certain character code combinations and translates them to a multibyte character set and sends that data to windows. This means that SQL*Plus shows Japanese characters on screen.

It works pretty well. At the time the Oracle unicode implementation was so dodgy that Oracle recommended waiting until Oracle 8 was released, which was not an option at the time.

The software is called NJWIN. Not sure what version they use here as it's a client tool and I'm not involved in that project much.

[Edited by TimHall on 01-28-2002 at 06:48 AM]

[jmodic]

Ah, yes, this makes sence....

[TimHall]

Getsu yobi daigaku ni basu de ikimasu!

Please excuse spelling. It's several years since I last attended a Japanese class and this is all I can remember now

[jmodic]

Originally posted by TimHall
Getsu yobi daigaku ni basu de ikimasu!

Ah yes, now it makes sence even more!