Re: Database link and JDBC

Paul wrote:

> Hello all,
>
> I 'm working with 2 databases Oracle v9.2 : DB1 and DB2.
> I setup a database link from DB1 to DB2.
> I've created a SP which joins between tableA from DB1 and tableB from
> DB2.
> This sp works fine from an sql+ session on the DB1 server.
> However, when I'm executing this sp via JDBC (oracle thin driver) from
> a java app, it triggers an SQLException : ORA-12154 TNS. Could not
> resolve service name error.
> The error line number points to the line in the procedure where tableA
> and tableB are joined ...
>
>
> Any ideas ?

Your exact configuration is not something I've worked with, but since you asked for ideas...

The JDBC thick client uses Oracle OCI, which in turn uses Net8 (SQL*Net) naming, such as TNSNAMES.ORA. If your SQL*Plus client (OCI) can "tnsping" DB2, then it should be able to execute the SP, because the DB link is able to resolve your connect string

The JDBC thin client does not use TNS naming (or at least, it's not supposed to...). Somehow, you need to completely specify the connect string, you can't use a lookup like "sid.world", for example. Somewhere (maybe the DB Link connect string?) you need to specify something like

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sid.yyy)))

to connect to the remote database via JDBC thin client.

HTH, Mark Bole Received on Thu Jan 22 2004 - 21:52:04 CST