Oracle schema table name conflicts

thinkbox · **Joined:** Tue Jul 28, 2009 11:44 pm **Posts:** 3

Hi,

Based on some research that I did, I found out that hibernate tries to search for a table in all schemas and then will use the first it finds with the same name. The first schema that will be searched for a specific table is not even the one that is used to logged in to the database. Now, I am having problems with my application since there exists another table with the same name on a different schema. Is there a way to force hibernate to search first for a specific table in my schema?

andydale · **Posted:** Wed Jul 29, 2009 4:15 am

You could try marking you entities with the following:

Code:

@Table(name = "<name>", schema = "<schema>")

And maybe you should also consider the rights of the user of the Oracle DB (don't allow SELECT ANY)

Cheers,

Andy

thinkbox · **Joined:** Tue Jul 28, 2009 11:44 pm **Posts:** 3

Hi Andy,

I am sorry if I this might be such a newbie question (for I am a Hibernate newbie myself), but where does the marking of entities go into my code?

CDillinger · **Joined:** Tue Oct 28, 2008 10:39 am **Posts:** 196

Seems that don't use Annotations... In hbm.xml-Files you can use table="Schemaname.Tablename" instead of tablename alone.

andydale · **Posted:** Wed Jul 29, 2009 5:32 am

Hi thinkbox,

Are you using POJO that are annotated, or hmb.xml mapping files ?

If using annotated POJOs then the annotation should go where you define the entity:

Code:

@Entity
@Table(name="<table>", schema="<schema>")
public class MyEntity {

I don't use hbm mapping files, but I imagine that the documentation contains all the info needed.

Cheers,

Andy

thinkbox · **Joined:** Tue Jul 28, 2009 11:44 pm **Posts:** 3

Hi Andy and CDillinger,

Thanks for your replies.

@Andy
The code that I am working on is using hbm.xml files. The thing is, right now, it is not possible to change anything to the codes, since I received them as already a build package. The application was originally using MySQL as its database and what I am trying to do right now is to let the application use Oracle as its database. It's true, I can modify how the application will connect to a database but other than that, I cannot change anything (e.g. java files, xml files, etc.). So the path I am taking right now is for me to find a way to work around the (said) issue of Hibernate with Oracle where tables with the same name that exist on different schema causes a conflict. The fix should happen outside of the application's code itself, if this is possible. :)
I have read somewhere that the issue where Hibernate searches other schema(s) for a particular table then use the first one it finds is fixed in Hibernate 3.3.x. However, I have tried the 3.3.x version and I still get the same error - Hibernate still seems to look through the other schema(s) first before the actual schema that I used to connect to the database.

andydale · **Posted:** Thu Jul 30, 2009 2:11 am

I would suggest you look into the rights of your database user, a simple test could be:

Code:

sqlplus <user>/<password>@<sid>
    SELECT count(*) FROM <otheruser>.<table>;

If you can do the above then maybe you should consider changing the rights of your oracle user so that it can only select from it's own schema.