Connection to Oracle via VBScript

Question

I have two databases on the same server with the same name and different port number. The tnxnames.ora entry looks like (actually one database is a clone of the other one.)

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

I am using MS-Access to develop the application and using ADODB connectivity to access the server. I am try to access the data from the server. My connection string is look like:

sConStr = "Driver={oracle in oraclient10g_home1};SERVER=SERVER_NAME;DBQ=AAAA;UID=username;Pwd=pswd"

Set oConn = New ADODB.Connection

oConn.connectionstring = sConStr
oConn.open

My problem is, my ADODB always connect to database port number 1530. I am not able to set it in the connetion string to access the database in the port 1521.

Is it possible to mention the port number in the connection string to access different database with the same name and in the same server?

I need to prepare the application to access the cloned database instead of original one. Nothing but the port number is different between the databases.

Answer 1

Try adding your port number to the end of the server name separated by a colon.

SERVER=SERVER_NAME:1521

I'm not an Oracle user, but that will work with MS SQL Server.

Answer 2

You may find this useful: http://www.connectionstrings.com/oracle

Answer 3

This may be a dumb question... but are both of those entries in your tnsnames.ora?

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Try changing it so each name is unique.

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AA21.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Then on the command line try tnsping AAAA.FSA.GOV.UK and tnsping AAAA21.FSA.GOV.UK