Oracle's Session object stores the Country and the Language values for each SQL Plus session and user, but how can we get hold of the Character Set used by a given user or for that matter any other client side information such as Computer Name in a Windows environment ?
After a little investigation and agreeing that it was not possible simply using Oracle, we decided to use the Windows Scripting Host (WSH) to produce a program that would do the following :
- Connect to the local registry and recover the values.
- Create a .sql file to reference from the login.sql file
- Call the SQL Plus executable
Dim my_charset
Dim key
Dim Sh
Set Sh = CreateObject("WScript.Shell")
key = "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLSLANG"
my_charset=Sh.RegRead(key)
Dim fso
Dim tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.OpenTextFile("C:\myfile.sql", 2, True)
tf.write("UPDATE myTable SET client_char_set='" & my_charset & ")
tf.close
WshShell.Run sqlplus80w
This gave us our completed script in less than 20 lines of codes. The completed heavily commented version is available, the header contains several reference sites used in the creation of the script. My thanks go to Claire Bennett of Oracle Training (Reading UK) for her assistance in the development of this script.