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.

Solution 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.

Solution 2

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

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

Comments

[zakvdm]

This link has been very useful for me in the past, but in this particular case I am trying to connect from within Ant. Do I still need to specify the oracle.net.tns_admin system property? If so, how would I do that? I have updated my question with the way I am currently using it. Thanks for the help.

[zakvdm]

You're quite right, it is actually "network/admin". However, I have tried both and it made no difference. I'm wondering if there isn't a better way to pass the property to the sql task (when I call the java target, I use a 'sysproperty', but the sql task doesn't seem to support this...).

[Jens Schauder]

I would think so. You do it like this: <sysproperty key="test.property" value="blue" /> which I copied from here: ideoplex.com/id/372/setting-java-system-properties-with-ant

[zakvdm]

Thanks for your answer! It's been so long since I had this problem that I can barely remember the details, but your answer seems very promising, :)