How to Identify the NLS column
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to Identify the NLS column

  1. #1
    Join Date
    Jan 2002
    Posts
    25
    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.
    GVK

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What do you mean by "NLS column"?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2002
    Posts
    25
    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.
    GVK

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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');
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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]
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Ah, yes, this makes sence....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by TimHall
    Getsu yobi daigaku ni basu de ikimasu!
    Ah yes, now it makes sence even more!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width