SQL+ Session

While attending an Oracle course recently, the tutor asked us to solve the following problem :

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 :

A quick look on the Microsoft site provided the code to access the local registry using WSH which provided the base of the first part, a little help from Oracle and we discovered the correct registy keys that were needed and we were able to get hold of the data :

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)

Next we created the .SQL file to be referenced in our login.sql file again using an amended form of a MSDN sample - for those of you not to familiar with this, on every connect to an Oracle Database via SQL Plus, a global login file (glogin.sql) is run to set up the environment, followed by the user specific login file (login.sql) - in these files you can reference other .SQL files to run when you login, we used this feature to load a SQL file containing a single query that placed our registry information into a table. N.B. to avoid load errors you should also create an empty .SQL file with the same name to ensure that the file is always in case you login to Oracle in another way :

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

Finally, we simply need to call the SQL Plus executable, which will start up, run the global and the local login files - and by extentsion our .SQL file - and we were finished. Again MSDN provided a sample or running an executable via a WSH Shell :

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.