Custom Oracle Sequence

MurhyJ · **Joined:** Sat Jan 20, 2007 3:16 pm **Posts:** 9

1.2.0

Hi,

I am trying to use a platform independent mapping for custom oracle sequences. I came across this post which seemed like what I need: http://www.hibernate.org/296.html

I am relatively new and am confused as to how I can use this code snippet at the end of that post (shown below). I can convert this to C# and I understand the Oracle DB side of things with table specific sequences, etc.

How do I indicate that I have this custom sequence generator instead of the default? I appreciate any help:

public class TableNameSequenceGenerator extends SequenceGenerator {

/**
* If the parameters do not contain a {@link SequenceGenerator#SEQUENCE} name, we
* assign one based on the table name.
*/
public void configure(Type type, Properties params, Dialect dialect) throws MappingException {
if(params.getProperty(SEQUENCE) == null || params.getProperty(SEQUENCE).length() == 0) {
String tableName = params.getProperty(PersistentIdentifierGenerator.TABLE);
if(tableName != null) {
String seqName = “seq_” + tableName;
params.setProperty(SEQUENCE, seqName);
}
}
super.configure(type, params, dialect);
}
}

thanks
Murthy

MurhyJ · **Joined:** Sat Jan 20, 2007 3:16 pm **Posts:** 9

Hi,

I would like to rephrase my question since I didn't get any replies:

Is it possible to use native id approach and a sequence per table when working with Oracle? I need to keep it platform independent and at the same time use different sequences for different tables.

If so, what do I need to do?

Please let me know if the question is not comprehensible. I appreciate any help.

thanks
Murthy

vdimitr · **Joined:** Thu Jul 19, 2007 4:48 am **Posts:** 2

Dear friend, I just want to share with you what I've done with Oracle
Custom Sequences. The problem was that I wanted to have a different
sequence for each table with no cache so as to overcome the problem
of "sequence gaps" (see http://www.techonthenet.com/oracle/sequences.php).
So I read the article that you mention and I did the following:

1) I've created a dialect which extends the Oracle9Dialect and
2) I've created a Generator which extends the SequenceGenerator.

Basically I did what the last part of the article said.

The code is :

Code:

public class MyDialect extends Oracle9Dialect {
   public Class getNativeIdentifierGeneratorClass() {
      return NonCachingSequenceGenerator.class;
   }
}
public class NonCachingSequenceGenerator extends SequenceGenerator {
   
   /**
    * The longest allowable sequence name length: 30 for oracle. 
    */
   protected int SEQUENCE_NAME_LENGTH = 30;
   
   /**
    * Additional parameters for the DDL of the sequence.
    */
   protected static final String DDL_PARAMETERS = "nocache";

   private static final Log log = LogFactory.getLog(NonCachingSequenceGenerator.class);
   
    /**
     * If the parameters do not contain a {@link SequenceGenerator#SEQUENCE} name, we
     * assign one based on the table name.
     */
    public void configure(Type type, Properties params, Dialect dialect) throws MappingException {
       params.setProperty(PARAMETERS, DDL_PARAMETERS);
        if(params.getProperty(SEQUENCE) == null || params.getProperty(SEQUENCE).length() == 0) {
            String tableName = params.getProperty(PersistentIdentifierGenerator.TABLE);
            if(tableName != null) {
               String tmpSeqName = "S_" + tableName;
                String seqName;
                if(tmpSeqName.length() <= SEQUENCE_NAME_LENGTH){
                   seqName = tmpSeqName;
                }
                else{
                   seqName = tmpSeqName.substring(0, SEQUENCE_NAME_LENGTH);
                   log.warn(tmpSeqName + " truncated to " + seqName 
                         + ". This may collide with another sequence already in the database.");
                }
                params.setProperty(SEQUENCE, seqName);
            }
        }
        super.configure(type, params, dialect);
    }

}

So what i get is a sequence for each table with maximum 30 char length
with no cache in order to avoid gaps. In adition all i had to do is in the
configuration file to declare which dialect to use and still preserve the "native" attribute in the hbm.

Any thoughts or suggestions are welcomed.

DavyBrion · **Posted:** Thu Jul 19, 2007 5:37 am

MurhyJ wrote:

Hi,

I would like to rephrase my question since I didn't get any replies:

Is it possible to use native id approach and a sequence per table when working with Oracle? I need to keep it platform independent and at the same time use different sequences for different tables.

If so, what do I need to do?

Please let me know if the question is not comprehensible. I appreciate any help.

thanks
Murthy

http://ralinx.wordpress.com/2007/07/17/ ... hibernate/

i think this is what you're looking for

MurhyJ · **Joined:** Sat Jan 20, 2007 3:16 pm **Posts:** 9

Vdimitr:

Thank you. I got stuck with this part:

public class MyDialect extends Oracle9Dialect {
public Class getNativeIdentifierGeneratorClass() {
return NonCachingSequenceGenerator.class;
}
}

NHibernate doesn't seem to have a way for me to extend and provide the generator class as you did here. There is no method to override (getNativeIdentifierGeneratorClass() is missing). Not sure why, but the port doesn't seem to include it in the Dialect.cs or the Oracle subclasses.

But, thanks anyway.

Davy Brion:

I was stuck on wanting the identical mapping files to stay fully db agnostic. But also I didn't read the fine print which I just did thanks to you, that specifying Sequence can still be safely ignored when working in SQL Server environment therefore I may be ok. Not sure if there are any gotchas in other non-Oracle Sequence environments. I just have this one-time extra work of specifying the sequence names across many dozens of classes we have already developed.

If I could do what was suggested for the Java environment, it would have been perfect. But this is definitely a work around I will consider using.

thanks for your help.

Murthy

vdimitr · **Joined:** Thu Jul 19, 2007 4:48 am **Posts:** 2

Dear MurhyJ the code was about Hibernate not NHibernate but your topic
was the most relevant I could find. Sry I don't have the exact solution to
your problem but I figured this was the closest place to post something.

Bye and good luck!