Error on Oracle while using DBLINK

Question

I am Using jboss5.1.x, EJB3.0 ,JPA3.

I am trying to do 'select' query from view which is connected via dblink to another database.

source database is Oracle 9, destination dabatase is Oracle 8.

I am getting this error:

15:27:06,625 WARN [JDBCExceptionReporter] SQL Error: 24777, SQLState: 99999

15:27:06,625 ERROR [JDBCExceptionReporter] ORA-24777: use of non-migratable database link not allowed

I found solution to this error after I understood that I cant use dblink while using XA. So I managed to solved by changing the dblink script to create shared database link as follow:

 CREATE SHARED DATABASE LINK CONNECT TO IDENTIFIED BY AUTHENTICATED BY IDENTIFIED BY USING 

everything worked fine in this test environment.

Now I've moved my application to production environment, where the source database is Oracle 11 while the destination is still Oracle 8.

The trick that I used didnt work this time and I couldnt find a solution. This is the new exception I am getting:

    Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at ....Caused by: java.sql.SQLException: ORA-01012: not logged on
    ORA-02063: preceding line from TO_VANTIVE

Thanks for your help,

ray,

Answer 1

ORA-01012: not logged on

seems to suggest that you haven't configured the new link correctly and since the database is now 11g which may have case sensitive passwords that would be the first thing to check.

Put quotes around the password in the CREATE LINK if the remote schema(s) have case sensitive passwords. Thus

CREATE SHARED DATABASE LINK
CONNECT TO bob IDENTIFIED  BY "MyNewPasswd1"
AUTHENTICATED BY jim IDENTIFIED BY "JimsPass23" USING 'DB01';