How to use tnsnames.ora in Grails DataSource?

Question

I'm trying to connect to a load-balanced virtual host specified in tnsnames.ora in my Grails application in DataSource.groovy, with no luck - it (obviously) throws an "Unknown Host Specified" exception.

The tnsnames entry looks like this

someServiceName =
 (DESCRIPTION_LIST =
   (FAILOVER=ON)
   (LOAD_BALANCE=ON)
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = someServerName1)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = someServiceName1)
     )
   )
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = someServerName2)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = someServiceName2)
     )
   )
 )

I can connect to one of the services (someServiceName1 & someServiceName2) by simply setting the values in DataSource.groovy in the usual way, but I don't know how to connect to the load-balanced virtual host. I've searched around for using tnsnames.ora in Grails but I can't seem to find a particularly "groovy" way of making this work. Any pointers will be appreciated.

P.S. I would prefer a solution where I would still be able to inject the dataSource into my controllers etc.

Answer 1

I think you need to configure a single service with multiple addresses. Then you have to set your datasource URL string to something like this (without the newlines of course):

jdbc:oracle:thin@(DESCRIPTION=
   (LOAD_BALANCE=on)
   (ADDRESS=(PROTOCOL=TCP)(HOST=someServerName1) (PORT=1521))
   (ADDRESS=(PROTOCOL=TCP)(HOST=someServerName2) (PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=theOnlyServiceName)))

Look here and here for references.