JPA Native Query to tables from different schema in Oracle

Question

I have table MV_ULICE in schema ADRESY. However in JPA I connect to database (Oracle) using different user then ADRESY. This user has privilege to access tables from schema ADRESY, so in JPA there was no problem with defining entities, since you can easily provide different schema in entity definition:

@Entity
@Table(name = "MV_ULICE", schema = "ADRESY")
public class PoiStreet {
...

The problem started when I wanted to create Native Query using JPA. Query looks like this:

final String queryString = "SELECT * "
                + "FROM MV_ULICE streets "
                + "WHERE CONNECT_BY_ISLEAF = 1 AND streets.status != 'H' "
                + "CONNECT BY NOCYCLE PRIOR streets.sym_ul = streets.symulold "
                + "START WITH streets.sym_ul = 'ulica'";

Query query = getEntityManager().createNativeQuery(
                queryString, poi.domain.entities.streets.PoiStreet.class);

And this does not work. I simply get exception form Oracle "Table or view does not exist".

I tried chanign MV_ULICE to ADRESY.MV_ULICE

final String queryString = "SELECT * "
                + "FROM ADRESY.MV_ULICE streets " + ...

but that did not help.

So does anyone has experience with native queries on oracle with different schemas then user that is accessing the database? Please help :)

Answer 1

I don't know if this is the best solution but I think that a Database link would work.

First, connect to your "first" database and execute the following at the SQL prompt:

CREATE DATABASE LINK mylink CONNECT TO scott IDENTIFIED BY tiger USING '(DESCRIPTION = (ADDRESS = 
(PROTOCOL = TCP) (HOST = <hostname>)(PORT = <port>)) (CONNECT_DATA = (SID = <SID>)))' 

where

mylink      Name of the link.
<hostname>  host name where the database is installed
<port>      TNS listener port of the database
<SID>       database name

This statement creates a database link with the name 'mylink'. The link connects to the SCOTT user of the database installed in the host (<hostname>)

Then, use the link name to reference the object on the remote database:

SELECT * FROM MV_ULICE@mylink

Answer 2

"This user has privilege to access tables from schema ADRESY"

Privileges are granted on individual schema objects, not an entire schema. I suspect that the user you are connecting as does not have privileges for this particular table/view.

Answer 3

Ok, so the real solution to the problem is to actually use a solution that I misleadingly said that is not working.

I took my quite time to find my mistake, but to tell the long story short using the standard notation SCHEMA.TABLENAME will work. So in my case the query should start like this:

final String queryString = "SELECT * "
                + "FROM ADRESY.MV_ULICE streets "