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;"
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")
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