jskubick
asked on
how to determine the oracle version and default character encoding?
Is there an easy way for somebody connected to an Oracle instance running on a host somewhere to determine 1) the version of Oracle that it's running under and 2) the default character encoding (UTF8, ISO-8859-1, ASCII7, etc)? I assume the values are stored and selectable in Oracle's housekeeping pseudo-tables, but don't have any idea which one(s).
Specifically... suppose I'm connected to the Oracle instance 'foo.database' running on the host 'foohost.mydomian.net' as the user 'bar' using SQL*Plus.
Suppose further that I need to create a table that contains an XMLType field, and I've learned the hard way that using XMLType fields in databases whose encoding ISN'T UTF-8 is just begging for grief and trouble, and that 9.2 fixes lots of bugs with XMLType that Oracle never fixed in 9.0.
Finally, assume it's 3am on Monday morning with a demo looming at noon, the DBA is sound asleep, and I have access to nearly a dozen running instances of Oracle on nearly a half-dozen hosts, running everything from 8i to 9.2i... most using Windows native character encoding (WEI-*?), a couple using ISO-8859-1, and a few using UTF-8. Before I waste an hour setting up the new tables, stored procedures, sequences, and triggers, I want to make sure I'm actually connected to a UTF8-encoded instance running 9.2. How?
Specifically... suppose I'm connected to the Oracle instance 'foo.database' running on the host 'foohost.mydomian.net' as the user 'bar' using SQL*Plus.
Suppose further that I need to create a table that contains an XMLType field, and I've learned the hard way that using XMLType fields in databases whose encoding ISN'T UTF-8 is just begging for grief and trouble, and that 9.2 fixes lots of bugs with XMLType that Oracle never fixed in 9.0.
Finally, assume it's 3am on Monday morning with a demo looming at noon, the DBA is sound asleep, and I have access to nearly a dozen running instances of Oracle on nearly a half-dozen hosts, running everything from 8i to 9.2i... most using Windows native character encoding (WEI-*?), a couple using ISO-8859-1, and a few using UTF-8. Before I waste an hour setting up the new tables, stored procedures, sequences, and triggers, I want to make sure I'm actually connected to a UTF8-encoded instance running 9.2. How?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, "select * from v$version;" worked :-)
Unfortunately, "show parameter NLS" didn't quite do the trick... it just indicated that various parameters are of type "string"... a few of which had values of "AMERICA", "BYTE", or "FALSE" (most had no value).
What I need to know is the database's default encoding... ie, UTF8, WEISOWIN12 (I'm sure I mangled that one... it's the default windows character encoding), ASCII7, etc.
Unfortunately, "show parameter NLS" didn't quite do the trick... it just indicated that various parameters are of type "string"... a few of which had values of "AMERICA", "BYTE", or "FALSE" (most had no value).
What I need to know is the database's default encoding... ie, UTF8, WEISOWIN12 (I'm sure I mangled that one... it's the default windows character encoding), ASCII7, etc.
You are to fast and do not read what I mentioned:
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSE
VALUE
UTF8
SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARA
VALUE
UTF8
SQL>
This definitelly showa the character set of the DATABASE.
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSE
VALUE
UTF8
SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARA
VALUE
UTF8
SQL>
This definitelly showa the character set of the DATABASE.
select * from v$version;
2 Character set
SQL> connect system/manager@lin1
Connected.
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSE
VALUE
UTF8
SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARA
VALUE
UTF8
SQL>