[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1697) OracleDialect fails to recognize sequence accessible through syonyms when validating schema

[Vlad Skarzhevskyy (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1697?page=comments#action_26018 ] 

Vlad Skarzhevskyy commented on HHH-1697:
----------------------------------------

I had the same problem. 

Our production database connection looks to synonyms of tables and sequences.

My solution was to Extend the Oracle9Dialect class and set hibernate.dialect in config.

In hibernate.cfg.xml
  hibernate.dialect=myapp.persistence.Oracle9fixedDialect

And the java file

package myapp.persistence;

import org.hibernate.dialect.Oracle9Dialect;

/**
 * Fix the problem with hibernate.hbm2ddl.auto=validate 
 * When connecting to read only schema.
 */
public class Oracle9fixedDialect extends Oracle9Dialect {

    public Oracle9fixedDialect() {
        super();
    }

    public String getQuerySequencesString() {
        return "select sequence_name from user_sequences " 
            + "union " 
            + "select synonym_name from user_synonyms us "
            + "where exists (select 1 from all_objects ao where object_type='SEQUENCE' and "
            + "us.table_name = ao.object_name)";
    }

}



> OracleDialect fails to recognize sequence accessible through syonyms when validating schema
> -------------------------------------------------------------------------------------------
>
>          Key: HHH-1697
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1697
>      Project: Hibernate3
>         Type: Bug

>  Environment: Hibernate 3.1.2, Hibernate 3.1.3 Oracle 10g
>     Reporter: Bjørn Bjerkeli
>     Priority: Minor
>  Attachments: Oracle9Dialect.java
>
>
> The Dialect implementations in OracleDialect and Oracle9Dialect fails to recognize sequences upon validation when they are accesssed through synonyms 
> because the user_sequences table will not create when the sequence is acced through a synonym. This is needed when using hibernate.hbm2ddl.auto=validate 
> which is a very useful feature.
> Thus sequences returned by:
> public String getQuerySequencesString() {
> 		return "select sequence_name from user_sequences";
> 	}
> will mot identify sequences accessible using a synonym.
> By using this implementation:
> public String getQuerySequencesString() {
> 		return "select sequence_name from user_sequences " +
> 			   "union " +
>                            "select synonym_name from user_synonyms us " +
>                            "where exists (select 1 from all_objects ao where object_type='SEQUENCE' and " +
>                            "us.table_name = ao.object_name)";		
> }
> orale will also return sequences acccessible though synonyms.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira