Click here to Skip to main content
11,412,516 members (73,227 online)
Click here to Skip to main content

Access Oracle without tnsnames.ora

, 4 Nov 2005
Rate this:
Please Sign up or sign in to vote.
How to access Oracle without tnsnames.ora.

DOS box

Introduction

Have you ever tried to access an Oracle instance from VBScript? It 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 strings from 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 are not 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:

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 will find this file in the following directory: <Oracle-Home>\network\admin. In this file you need a definition of the alias:

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:

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:

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 find this article useful. For me this kind of a connection string is useful, when I cannot be sure if the end-user had a correct tnsnames.ora on his computer.

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

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

 
GeneralRetrieve / Insert XML (or txt) file in Oracle Pin
2Shaar at 26-Feb-09 7:25
member2Shaar26-Feb-09 7:25 
GeneralCheck whether Oracle Client is installed Pin
swanand_rk at 21-Nov-07 4:06
memberswanand_rk21-Nov-07 4:06 
QuestionOracle Connection Manager Pin
trberube at 12-Apr-07 7:01
membertrberube12-Apr-07 7:01 
GeneralOracle Connectivity from VBscript Pin
binimol at 3-Apr-07 20:46
memberbinimol3-Apr-07 20:46 
GeneralRe: Oracle Connectivity from VBscript Pin
fstrahberger at 4-Apr-07 0:49
memberfstrahberger4-Apr-07 0:49 
GeneralRe: Oracle Connectivity from VBscript Pin
bsatishus at 4-Apr-07 4:04
memberbsatishus4-Apr-07 4:04 
GeneralRe: Oracle Connectivity from VBscript Pin
fstrahberger at 4-Apr-07 5:16
memberfstrahberger4-Apr-07 5:16 
Hi,

you can download the oracle software from directly from Oracle. There are also links to older versions like Oracle 9i.

The download contains the client software. You have to choose Client when running the oracle installer.

>and pleas also let me know where to run this command ??(you mentioned in your >answer):
>sqlplus user/password;@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))>(CONNECT_DATA=(SERVICE_NAME=)))

Open a windows command prompt and run this command. This will open a sqlplus session. When this was OK, you alread know that your client can talk to the server. Make sure you also have correct values for HOST and PORT.

The very first step in testing the connection is tnsping (also from a windows prompt): >tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(CONNECT_DATA=(SERVICE_NAME=)))

The answer should be something like:
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myport)
) OK (50 msec)
GeneralRe: Oracle Connectivity from VBscript Pin
bsatishus at 4-Apr-07 11:27
memberbsatishus4-Apr-07 11:27 
GeneralRe: Oracle Connectivity from VBscript Pin
Lonestar.org at 1-Oct-10 22:01
memberLonestar.org1-Oct-10 22:01 
GeneralORA-12154 Pin
PasNad at 17-Oct-06 2:32
memberPasNad17-Oct-06 2:32 
GeneralRe: ORA-12154 Pin
fstrahberger at 17-Oct-06 2:54
memberfstrahberger17-Oct-06 2:54 
GeneralRe: ORA-12154 Pin
PasNad at 17-Oct-06 17:14
memberPasNad17-Oct-06 17:14 
GeneralRe: ORA-12154 Pin
fstrahberger at 17-Oct-06 20:30
memberfstrahberger17-Oct-06 20:30 
QuestionOther providers Pin
YoniP at 21-Nov-05 1:24
memberYoniP21-Nov-05 1:24 
QuestionWhy? Pin
Patrice Borne at 4-Nov-05 13:20
memberPatrice Borne4-Nov-05 13:20 
AnswerRe: Why? Pin
fstrahberger at 5-Nov-05 13:39
memberfstrahberger5-Nov-05 13:39 
GeneralRe: Why? Pin
CastorTiu at 12-Nov-05 23:08
memberCastorTiu12-Nov-05 23:08 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150427.1 | Last Updated 4 Nov 2005
Article Copyright 2005 by fstrahberger
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid