Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / VBScript

Access to Oracle without tnsnames.ora

Rate me:
Please Sign up or sign in to vote.
4.54/5 (13 votes)
25 Oct 2005CPOL2 min read 100K   649   25   8
Access to Oracle without tnsnames.ora
Sample image

Introduction

Have you ever tried to access an Oracle instance in VBScript? This is not too complicated, but normally you need an additional file called tnsnames.ora in the Oracle installation directory. This article describes an easy way to access an Oracle instance without this file. All you have to do is use a different connection string.

Using the Code

You can use this kind of connection string in VBScript, VBA and Visual Basic. I also tested it with Perl, it worked. Using a connection string without the need to have a correct tnsname.ora on your computer is especially useful for ad hoc scripting on many different database instances, or when you cannot be sure if the user has a correct tnsnames.ora on his computer.

First take a look at the standard connection string for Oracle, used in a VBScript file:

VBScript
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=MYDB; uid=read;pwd=read;"

You see in the first section the driver name (Microsoft ODBC for Oracle), followed by the alias for the instance and last the Oracle username and password. The alias name MYDB also needs a corresponding entry in the file tnsnames.ora. Mostly you find this file in the following directory: <Oracle-Home>\network\admin. In this file, you need a definition of the alias:

VBScript
MYDB.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mysrv)(PORT = 7001))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYDB)
    )
  )

Oracle uses this definition to determine which physical database instance is associated with the given alias MYDB. This is why we need the file tnsnames.ora when we access an Oracle instance. But is this necessary? No, not really! All you have to change: include the physical connection data (like host and port) in your connection string. Here is the modified connection string in VBScript:

VBScript
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=mysrv)(PORT=7001))" & _
         "(CONNECT_DATA=(SERVICE_NAME=MYDB))); uid=read;pwd=read;"

Now you will be able to access every Oracle instance without the need to have a correct tnsnames.ora on your client machine. Finally here is the complete source code that selects some rows and writes them to STDOUT:

VBScript
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=mysrv)(PORT=7001))" & _
         "(CONNECT_DATA=(SERVICE_NAME=MYDB))); uid=read;pwd=read;"

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

I hope that you found this article useful. For me this kind of connection string was already useful, when I could not be sure if the end-user had a correct tnsnames.ora on his computer.

History

  • 25th October, 2005: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
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

 
GeneralGreat Find Pin
/randz2-Jul-07 19:30
/randz2-Jul-07 19:30 
GeneralRe: Great Find Pin
fstrahberger4-Jul-07 3:48
fstrahberger4-Jul-07 3:48 
GeneralThank you thank you thank you! Pin
BPloe17-Nov-05 10:41
BPloe17-Nov-05 10:41 
AnswerRe: Thank you thank you thank you! Pin
joelperez12-Mar-08 4:57
joelperez12-Mar-08 4:57 
GeneralIt works with the Oracle Client Pin
MichaelElliott26-Oct-05 4:37
MichaelElliott26-Oct-05 4:37 
GeneralIt also works with Oracle OLEDB Provider Pin
Prasad Khandekar25-Oct-05 20:37
professionalPrasad Khandekar25-Oct-05 20:37 
GeneralRe: It also works with Oracle OLEDB Provider Pin
fstrahberger26-Oct-05 0:35
fstrahberger26-Oct-05 0:35 
QuestionRe: It also works with Oracle OLEDB Provider Pin
ahaskins18-Aug-06 3:09
ahaskins18-Aug-06 3:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.