Click here to Skip to main content
14,976,314 members
Articles / Programming Languages / SQL
Article
Posted 31 Oct 2005

Stats

51.4K views
255 downloads
15 bookmarked

nls_lang When Using Multiple Oracle Homes

Rate me:
Please Sign up or sign in to vote.
3.00/5 (2 votes)
31 Oct 2005CPOL1 min read
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:

VBScript
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:

VBScript
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

fstrahberger
Web Developer
Germany Germany
Florian works as consultant for change- and configuration management for about 7 years. In this environment he is often forced to work with unix, perl and shell scripts.

For more information about change- and configuration management (espacially Serena Dimensions) visit: www.venco.de

For video tutorials about asp.net, ajax, gridviews, ... (in german) visit: www.siore.com

Comments and Discussions

 
-- There are no messages in this forum --