nls_lang When Using Multiple Oracle Homes

• Download source - 547 B

Introduction

Did you ever use Oracle with more than one Oracle home, maybe in different language settings like AMERICAN and GERMAN. Then you probably had the following problem in native Oracle (e.g. sqlplus) when selecting a date field: "ORA-01843: not a valid month". This problem is even worse when using ODBC (maybe from VBScript). Then you simply get no rows if you select a date field. You can see this in the above screen shot. This article shows you a way to ensure the usage of the correct NLS_LANG parameter in your database scripts.

Using the Code

Use this code to avoid errors in database queries from VBScript using ODBC connection to Oracle. It is also useful when you have no control about the environment setting of NLS_LANG on your customers workstation.

Before we start to query the database, we adjust the process environment for the script:

Dim oShell: Set oShell = WScript.CreateObject("WScript.Shell")
Dim oEnv: Set oEnv = oShell.Environment("PROCESS")
oEnv.Item("NLS_LANG") = "AMERICAN_AMERICA.WE8ISO8859P1"
WScript.Echo oEnv.Item("NLS_LANG")

First we access the current shell with CreateObject("WScript.Shell"). Using the shell, we have access to the environment of the current process. Now we can set the NLS_LANG parameter to a correct value for our database. With this code, you can be sure that your scripts run with the correct setting for the parameter NLS_LANG. You no longer have to worry about registry settings on your customers workstation.

Finally here is the complete script that sets NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1, queries the database and writes to STDOUT:

Dim strCon: strCon = "Driver={Microsoft ODBC for Oracle}; " & _
               "CONNECTSTRING=<xxx>; uid=read;pwd=read;"         

' set NLS_LANG to AMERICAN_AMERICA
Dim oShell: Set oShell = WScript.CreateObject("WScript.Shell")
Dim oEnv: Set oEnv = oShell.Environment("PROCESS")
oEnv.Item("NLS_LANG") = "AMERICAN_AMERICA.WE8ISO8859P1"
WScript.Echo oEnv.Item("NLS_LANG")

' select statement
Dim strSql
strSql = "SELECT myfield from mytable"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
Set oRs = oCon.Execute(strSql)
While Not oRs.EOF
    WSCript.Echo oRs.Fields(0).Value & " " & oRs.Fields(1).Value
    oRs.MoveNext
Wend
oCon.Close
Set oRs = Nothing
Set oCon = Nothing
Set oCon = Nothing

I hope you found this article useful. The adjustment of the parameter NLS_LANG was already useful for me, when I could not be sure if the end-user had the correct settings on his computer.

History

• 1^st November, 2005: Initial post