access to oracle without tnsnames.ora

• Download source code - 0.6 Kb

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.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 8 of 8 (Total in Forum: 8) (Refresh) FirstPrevNext

Great Find /randz 20:30 2 Jul '07   this is great. thanks for sharing. /sheijin ~living on the edge.~ Sign In·View Thread·PermaLink Re: Great Find fstrahberger 4:48 4 Jul '07   Thanks ... Sign In·View Thread·PermaLink Thank you thank you thank you! BPloe 11:41 17 Nov '05   I had to do this for a connection I was using in an excel spreadsheet and didn't want to have to go to every user's desktop to set up a tnsnames.ora file and point a driver to it. This makes my life so much easier. Thank you! Brian Sign In·View Thread·PermaLink Re: Thank you thank you thank you! joelperez 5:57 12 Mar '08   Hey, I had the same problem, and this article is helping me to solve it!! Thank you very much Sign In·View Thread·PermaLink It works with the Oracle Client Mike Elliott 5:37 26 Oct '05   This works with the Oracle client as well. Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XXX)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)));User ID=XXXX;Password=XXXXX;Max Pool Size=100; Replace the X's with your information. Remember a Data Source value can not exceed 128 characters through the Oracle client. With this said, if you work with the Oracle admins, discuss this when setting up the host information. Sign In·View Thread·PermaLink 3.00/5 (3 votes) It also works with Oracle OLEDB Provider Prasad Khandekar 21:37 25 Oct '05   Hello,       I have tried this with Oracle OleDB provider and it works. For those interseted here is the sample. <code> strCnn = "Provider=OraOLEDB.Oracle;User Id=cwMay203;Password=cwMay203;" &_           "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=198.17.21.120)" &_           "(PORT=1521))(CONNECT_DATA=(SID=ORCL)));PLASQLRSet=1;" & _           "ChunkSize=4096;DistribTx=0;" </code> Regards, Prasad P. Khandekar Knowledge exists, man only discovers it. -- modified at 2:44 Wednesday 26th October, 2005 Sign In·View Thread·PermaLink 2.33/5 (3 votes) Re: It also works with Oracle OLEDB Provider fstrahberger 1:35 26 Oct '05   Hello, this sort of connection also works for commandline tools like tnsping and sqlplus: Here the examples: sqlplus <user>/<password>;@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<your host>(PORT=<your port>)(CONNECT_DATA=(SERVICE_NAME=<any alias>)) tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<your host>(PORT=<your port>)(CONNECT_DATA=(SERVICE_NAME=<any alias>)) Maybe this is usefull for someone. Best regards Sign In·View Thread·PermaLink 2.00/5 (2 votes) Re: It also works with Oracle OLEDB Provider ahaskins 4:09 18 Aug '06   I have this connection working with ADODB. I would much rather use OleDB but, when I tried this, I recieved an error on the connecion open "ORA-01033: Oracle initialization or shutdown in progress" but the database appears to be up and running. Did you encounter anyrthing similar? Thanks, Alan Sign In·View Thread·PermaLink 1.50/5 (2 votes)

Last Visit: 9:45 8 Nov '09     Last Update: 9:45 8 Nov '09 1