Re: Database link and JDBC

On 2004-01-23, makbo <makbo_at_pacbell.net> wrote:
> 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...

Based on my limited experience with the distributed option, I would expect TNS resoltion issues in this instance to be dependent only upon the SERVER configuration of the system you're connecting to.

The client configuration (tnsnames and such) should be completely irrelevant. @foo is strictly relative to the tnsnames config of the server you are executing that link against.

Eventhough you are using JDBC, all your requests are being directed through a server that uses "classic" TNS.

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

-- 
There's no reason not to contribute to the stone soup if those    |||
contributions are not critical to your competitive edge.         / | \