Friday, 18 April 2008

JDBC Thin connection using tnsnames.ora entry

As of the 10.2.x JDBC driver we now allow you to connect to your database using a tnsnames.ora entry from JDBC THIN. Previously this was not possible without fully qualifying the description alias. To do this in 10.2.x JDBC driver you can do it one of 2 ways.

Option 1:

Pass a system property as follows at the command line

-Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Option 2:


System.setProperty("oracle.net.tns_admin",
"D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");



His a quick code example showing how it works:

tnsnames.ora entry

LNX102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = papicell-au2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lnx102)
)
)

Java Code


/*
* Option 1 : Systsem property as command line option
*
* -Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
*
* Option 2 : System propety within code
*
* System.setProperty("oracle.net.tns_admin",
* "D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
*/

package pas.test;

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class JdbcThinTnsNamesTest
{
public JdbcThinTnsNamesTest()
{
System.setProperty("oracle.net.tns_admin",
"D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
}

public static Connection getConnection() throws SQLException
{
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@lnx102";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}

public void run () throws SQLException
{
Connection conn = getConnection();
System.out.println("Auto Commit = " + conn.getAutoCommit());
conn.close();
}

public static void main(String[] args)
{
JdbcThinTnsNamesTest test = new JdbcThinTnsNamesTest();
try
{
test.run();
System.out.println("all done..");
}
catch (SQLException e)
{
e.printStackTrace();
}
}

}


Runtime Output

Auto Commit = false
all done..

8 comments:

Anonymous said...

"Previously this was not possible without fully qualifying the description alias."

I've spent too much time trying to figure out how to specify a full connection description (as in the tnsnames.ora description) in the bc4j.xcfg file.

We need to configure the app to work with the Oracle Connection Manager and we have a description like this:

OUR.SERVER.LOCAL =
(DESCRIPTION=
(SOURCE_ROUTE=on)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=first.server.local)(PORT=1630))
(ADDRESS=(PROTOCOL=TCP)(HOST=second.server.local)(PORT=1521))
)
(CONNECT_DATA = (SID=OURSID))
)

How can you specify this in the bc4j.xcfg file?

We're using OAS 9.0.4.

Thanks in advance.

Pas Apicella said...

I have never tried from a bc4j.xcfg file but there are 2 problems I see here.

1. OAS 9.0.4 is a very old version and would be using a 10.1 JDBC driver or maybe older driver which doesn't support jdbc thin using a tnsnames.ora entry.

2. The bc4j.xcfg file if using JDBC URL style connection will dynamically build the URL from the ConnectionDefinition tag which means there isn't a way I can see how to do it from there using that type of Connection.

One way would be to use a data source connection from bc4j.xcfg, then setup your data source to connect as shown by this post. However the JDBC driver version will still be an issue on OAS 9.0.4.

Anonymous said...

We're using JDeveloper 9.0.4.2 to build the project (it's an old one, and hard to port to a later version).

If we create a database connection with a different driver (We have: Oracle (JDBC), JDBC-ODBC Bridge, Oracle Lite, Third Party JDBC Driver) and currently we're using Oracle (JDBC), will it reflect the changes on the bc4j.xcfg file?

Would any of the above options make a difference in allowing us to specify a connection description similar to the one in tnsnames.ora?

Thanks in advance.

Pas Apicella said...

I have only ever used oracle's JDBC driver, so can't comment on the other drivers and what is generated in bc4j.xcfg for them.

openhand said...

Thanks, just what I needed today :)

Chui Tey - JDBC URLs said...

Thanks. If you don't mind, I've updated my JDBC url collection with the information you have provided.

Training Badminton Skills said...

Works like a charm with 11g, thanks for the sample.

Anonymous said...

Quick question: is there a default for oracle.net.tns_admin? In other words, if I do NOT set it, will the thin driver look in some sensible place like $TNS_ADMIN, or else $ORACLE_HOME/network/admin? Or will it just give an error?
Thanks for the useful post!