Schema validation with Oracle 10g

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Hi,

We've recently started doing some training projects on EJB 3 using oracle. We all have our own accounts and have created our tables using these accounts so they are all in their own schema and don't collide with each other.

The problem we are having is that when we deploy our entities to JBoss and hibernate validates them, it always checks against the same schema which appears to be the first user by alphabet.

Ex: We have 3 users Andrew, Ari, and Travis but when hibernate validates it always does it against ANDREW.[table name]. If I drop the user Andrew, then it starts validating against ARI.[table name].

None of use have set the default schema in either persistence.properties or persistence.xml and we don't have any other hibernate configuration files. Can anyone explain to me why it does this and how to fix it?

Thanks,

Drew
------

Setup:

Oracle 10g on linux
JBoss 4.0.4 rc2

Oracle-ds.xml

Code:

<datasources>
  <local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
    <connection-url>jdbc:oracle:thin:@celos.qcomm.com:1521:celos</connection-url>
      <driver-class>oracle.jdbc.pool.OracleDataSource</driver-class>
      <user-name>andrew</user-name> 
      <password>drew</password>
      <metadata>
        <type-mapping>oracle</type-mapping>
      </metadata>
  </local-tx-datasource>
</datasources>

Persistence.xml

Code:

<persistence>
   <persistence-unit name="custdb">
      <jta-data-source>java:/OracleDS</jta-data-source>
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
</persistence>

persistence.properties

Code:

hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup
hibernate.connection.release_mode=after_statement
#hibernate.transaction.flush_before_completion=true
hibernate.transaction.auto_close_session=true
hibernate.query.factory_class=org.hibernate.hql.ast.ASTQueryTranslatorFactory
#hibernate.hbm2ddl.auto=create-drop
hibernate.hbm2ddl.auto=validate
hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider
# Clustered cache with TreeCache
#hibernate.cache.provider_class=org.jboss.ejb3.entity.TreeCacheProviderHook
#hibernate.treecache.mbean.object_name=jboss.cache:service=EJB3EntityTreeCache
hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
hibernate.jndi.java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory
hibernate.jndi.java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces
hibernate.cglib.use_reflection_optimizer=false
hibernate.show_sql=true

sberna · **Joined:** Wed Feb 16, 2005 5:56 am **Posts:** 6

This seems like a classloader problem. EJBs inside jboss share the same classloader and so your mapping files might be colliding.

I would enable the JBoss property for classloader isolation inside the jboss application descriptor for ejb and give it a try.

Hope this helps.

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Thanks for the reply. I forgot to mention that we all have our own jboss app server running on our local machines but we are all connecting to the same Oracle server and SID. Since our mapping files don't ever specify a default schema and they are on different machines I don't think this could be the problem.

This is a very weird since we are all using different users to create the connection but it's always validating against the same user. Could this be some connection pooling problem with the Oracle server?

Any other ideas?

sberna · **Joined:** Wed Feb 16, 2005 5:56 am **Posts:** 6

COuld you try specifying the schema in the bean definion?

instead of table-name="table"

table-name="schema.table"

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Thanks again for the response.

I could (or specify it on the entities themselves) but I'm trying to figure out what the root of the problem is. It's my own curiosity as to what's going on in the back end that makes it always pick the same user's schema :)

Drew

sberna · **Joined:** Wed Feb 16, 2005 5:56 am **Posts:** 6

Seems like an oracle schema and user definition problem. We have had similar problems sometimes and it usually is solved by correct sinonym definition inside the user propietary schema.

You should try browsing schemas with oracle admin and ensuring that the users are correctly bound to their schema.

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

We're still having the same problem and I'm wondering if anyone else has come across it. We've also come across the strange case where hibernate will use the connection's user name to validate sequences but not for anything else (it just uses the first table it finds which belongs to the user with the lowest alphabetically ordered name ex: AAA)

I'm hoping someone can please help with this. I would think hibernate would honor the default_schema value in the persistence.properties but it does not.

Thanks,

Drew

willem · **Joined:** Wed Aug 23, 2006 7:59 am **Posts:** 7

I believe it is just because you connect using the Andrew user. When you connect to a Oracle DB, you are automatically in the schema you used for logging in. So when you don't further specify a schema, the schema Andrew will be used.

rezaghp · **Joined:** Fri Nov 12, 2004 1:40 am **Posts:** 7

UTDrew,
I am wondering if you have found a solution for this issue or not?

Thanks,
Reza

jrobette · **Joined:** Thu Apr 22, 2010 11:28 am **Posts:** 1

Sorry for posting on such an old subject, but I'm facing exactly the same problem and as rezaghp seems to have also faced this issue two months ago, maybe he/she found a solution.

Thanks for answering, any help would be greatly appreciated.

Regards,
Johann