asked on

java.sql.SQLException: ORA-02019: connection description for remote database not found

Following is the code snippet. It is throwing the ORA-02019 exception . Querying all_db_links i can see the link there with the 'PUBLIC' user. What could be wrong

PreparedStatement ps = null;
       ResultSet rs = null;
             try {
            ps = conn.prepareStatement("select emplid,action_dttm from ps_jcp_idm_tbl@HRDB");
            rs = ps.executeQuery();
            while (rs.next()){
                  System.out.println(rs.getInt(1));
            }
       } catch(Exception sqle) {
            sqle.printStackTrace();
            logger.error("Could not fetch archive aging date for WORK_DETAILS. Will force quiery to union the archive shema.");
       } finally {
            SQLHelper.cleanUp(ps, rs);
       }

for_yan

ORA-02019: connection description for remote database not found

try to run your query from SQL command line before diing it in the code

archiecool

ASKER

It works fine from the Sql tool

for_yan

Are you sure you connect in your tool and in the code
to the same instance and under the same schema?

archiecool

ASKER

Yes

for_yan

Don't know how this can happen - I'm using a lot of links in my jdbc
they work fine.

Please, check this maybe it could help:

https://www.experts-exchange.com/questions/20174416/Creating-tables-by-selecting-from-a-remote-database.html

archiecool

ASKER

Is there anything else i can provide for you to make it easy to look for the cause? Can you provide me code samples of how you have configured and used a db link in the java code. That would really help.
Thanks for looking into it.

for_yan

In the java code I used db link in the same way as you showed in your question,just with @ sign and the link. What can in fact differ is the way this link was created - because there is definitely more than one way of creating links and these may be different. As I understand in some cases links may not specify password at creation time, also for some cases tnsname s.ora files may be important. This is in part I gess what is mentioned in that trail I sent you. Do you know how your link was created?

archiecool

ASKER

tnsnames.ora...what should be in that for a dblink? I have tried the suggestions in that email trial but no luck. And i can connect through toad (SQL tool) alright, but not through java.

Thanks

archiecool

ASKER

by the way that is a really good article. Thanks for sharing

for_yan

Yes, these links are not always that straightwforweard because there are many ways
for creation of them.

Some of my links I created like that:

create database link my_link connect to schema_name
identified by "password" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host = server.company.com)(Port = 1521))(CONNECT_DATA =(SID = SID_NAME)))'

from the TOAD command prompt
I think password was in quotes because it contained some
weird symbols, probably you don't need quote here

after that using the same connection, same schema, etc,
which I used in Toad, when creating this link,
in jdbc I just say

select * from table_name@my_link

and it works

But, one thing, I probably never updated through
these links (only sleceted) - should not matter, but still

and, another thing, also I never used PreparedStatements togetehr with
these links, I used simple statement - again should not make difference, but still

And I am working with Oracle 9

archiecool

ASKER

so was your database running on the same server as where you were executing your java program. Does the jndi for java have to look any different?

Thanks,

for_yan

I was connecting with Toad to the same database to which I was connecting
in another case by JDBC. Not only it was the same databse,
but I was also connecting to the same schema, where I was sitting in Toad
and where I connected to JDBC - those envionements were in fact the same -
this is of course very important.

Through the lnk, however, I was of course connecting to different database on
different computer (and it was in fact many miles away from my
original database)

archiecool

ASKER

the oracle version i have is 10g. and java is 1.4.2.13. Is that any indication of an existing issue.

for_yan

No, I don't think so.

So, do you know how was your db_link created?

archiecool

ASKER

Found the issue...my bad. I was testing this through junit test case and realized the junit had its db config file, which was not pointing to the right database.

Thanks for looking into it though.

ASKER CERTIFIED SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

archiecool

ASKER

I figured out the answer on my own. It was not the expert's inability to answer, it was just that the expert did not have all the details of my setup to answer.