How can I connect to an Oracle database from Ant using the tnsname?

Question

I am looking for something similar to the Ant sql task but that will accept a JDBC url of the format:

jdbc:oracle:thin:@TNS_NAME

One possible approach seems to be to write my own Ant task that uses an OracleDataSource to create the Connection, but is there a way to do this straight in Ant?

EDIT: Thanks for the responses so far guys. I hope it helps if I elaborate a bit more on the error I'm getting.

My Ant task looks as follows:

<target name="MyTarget" >
    <property name="oracle.net.tns_admin" value="/opt/oracle/product/10.2.0.1/NETWORK/ADMIN" />
    <property name="jdbc.driver" value="ojdbc5.jar" />
    <property name="jdbc.i18n.support" value="orai18n.jar" />
    <property name="jdbc.driver.class" value="oracle.jdbc.OracleDriver" />
    <path id="sql.class.path">
        <pathelement location="${jdbc.driver}" />
        <pathelement location="${jdbc.i18n.support}" />
    </path>

    <sql driver="${jdbc.driver.class}" url="jdbc:oracle:thin:@THE_TNS_NAME" userid="USER" password="PASSWORD" classpathref="sql.class.path" >
        <![CDATA[
        #SOME ARBITRARY SQL HERE
        ]]>
    </sql>
</target>

This fails with the error:

java.sql.SQLException: Io exception: Unknown host specified

Replacing the url with "jdbc:oracle:thin:@HOST:PORT:INSTANCE" works fine, and I can also tnsping the tns name used above, so I know it's valid.

Answer 1

Was just working with this today and stumbled upon the missing piece. The TNS location needs to be set as a system property as indicated here: Oracle thin JDBC to TNS name

To establish an Oracle thin JDBC connection to a TNS alias (tnsname), make sure you pass the oracle.net.tns_admin system property to the JVM. Its value should be the directory in which your tnsnames.ora file is located. After that, you can just pass the TNS alias in place of the host name in the JDBC URL.

E.g. if you simply try to connect to jdbc:oracle:thin:@MYDB, which is in your tnsnames.ora file, you’ll get an SQLException with a detail message of Io exception: Unknown host specified. If you fire up the JVM with a -Doracle.net.tns_admin=/oracle/10g/NETWORK/ADMIN, or use System.setProperty(String,String) after startup, the connection will be established successfully.

After doing this I was able to successfully connect using the TNS alias alone.