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

If you mean that you want a "thick" connection that uses tnsnames.ora and not the thin driver, then you can wrap a call to sqlplus in the xml file:

<target name="myTarget">
  <!-- login.sql should have sqlcode exit so failonerror will fail build -->
  <exec executable="sqlplus" failonerror="true">
        <arg value="${userid}/${password}@${tnsalias}"/>
        <arg value="@myScript"/>
  </exec>
</target>

... is the basic idea.

[where userid, password, and tnsalias are defined in your properties file]

This obviously means you'll have to have at least the Instant Client stack installed.

Answer 2

Since we don't now yet, what the exact problem is, I can only assume that this might help:

http://theblasfrompas.blogspot.com/2008/04/jdbc-thin-connection-using-tnsnamesora.html

Answer 3

Are you sure it's NETWORK/ADMIN and not network/admin?

Unix filesystems are usually case sensitive - (assuming it's on Unix).