Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

nls_lang When Using Multiple Oracle Homes

0.00/5 (No votes)
1 Nov 2005 1  
nls_lang when using multiple Oracle homes
Sample Image - nls_lang.jpg

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

  • 1st November, 2005: Initial post

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here