[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_26019 ] 

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

Actual after consulting with our DBA  I suggest this SQL to get the list of sequences!

SELECT sequence_name
  FROM user_sequences
UNION
SELECT synonym_name
  FROM user_synonyms us
 WHERE EXISTS (
             SELECT 1
               FROM all_sequences asq
              WHERE asq.sequence_name = us.table_name 
                AND asq.sequence_owner = us.table_owner )	

Statement check the object owner is the same as a table owner in the synonyms. (to eliminate the case when the sequence is decalred under the owner different from these in the synonym declaration).


    public String getQuerySequencesString() {
        return 
        "SELECT sequence_name "
        + " FROM user_sequences "
        + "UNION "
        + "SELECT synonym_name "
        + " FROM user_synonyms us "
        + "WHERE EXISTS ( "
        + "           SELECT 1 "
        + "             FROM all_sequences asq "
        + "            WHERE asq.sequence_name  = us.table_name" 
        + "              AND asq.sequence_owner = us.table_owner)";
    }


> 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