 |
||||||||||
| ||||||||||
| ||||||||||
Languages »
VBScript »
General
Intermediate
access to oracle without tnsnames.oraBy fstrahbergeraccess to oracle without tnsnames.ora |
VBScript, Javascript, Perl, SQL, Windows, Visual Studio, ADO, ADO.NET, DBA, Dev Posted: 25 Oct 2005 Updated: 25 Oct 2005 Views: 43,190 Bookmarked: 13 times |
||
|
|
|||
|
Advanced Search Sitemap |
|
|
|
||||||||||||||||
Introduction
Have you ever tried to access an oracle instance in VBScript? This is not to 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 VisualBasic. 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 espacially usefull for ad hoc scripting on many different database instances. Or when you cannot be shure 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 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 neccesary? 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 found this article useful. For me this kind of connection
string was already usefull, when I could not be shure if the end-user had a
correct tnsnames.ora on his computer.
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
(PORT=<your port>
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 25 Oct 2005 Editor: |
Copyright 2005 by fstrahberger Everything else Copyright © CodeProject, 1999-2008 Web15 | Advertise on the Code Project |