How Do You Get The Value of a DB Sequence.

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

I'm using an Oracle 10g DB and it has a sequence. I need to programatically get the value of the sequence. What is the best way to do it? HQL?

A code example would be great.

Thanks,

dharmendra.pandey · **Posted:** Mon May 07, 2007 1:14 am

Hi nealc,

Hi hibernate doesn’t say to use sequence manually. You could use generator class and let the hibernate manage sequence. If you manage sequence then lot of concurrency problem you would face ,that leads to complexities of unnecessary code. Hibernate has done that best for you

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

All I want to do is get the value of the sequence.

I am building an application on top of a legacy DB. One of the tables has a primary key field that is defined as VARCHAR2(30). In the new application the value of this key field does not have any inherent meaning. My plan is to fetch a sequence value and have my application convert it to a String and use that value as the key.

pramodkp · **Joined:** Mon Mar 26, 2007 12:38 am **Posts:** 119

Hi,
I suggest the use of <generator class="uuid"/>
For the other option, ( using a sequence ) you can visit my earlier post at
http://forum.hibernate.org/viewtopic.php?t=973633

-----------------------------------------------
Rate the reply if you find it helpful

dharmendra.pandey · **Posted:** Mon May 07, 2007 11:46 pm

Hi pramodkp,

I am bit confused that nealc ask for sequence for his legacy system.for that he would go for SQL to get next sequence and assign it to object.That exactly what he want to do,Am i right nealc.As your last posting suggested that you should go for

<id name="id" column="id">
<generator class="assigned"/>
</id>

and uuid is used when Hibernate takes care for sequence.For nealc case how this is going to help .Could you Plz.. give us some explanatory comment.

pramodkp · **Joined:** Mon Mar 26, 2007 12:38 am **Posts:** 119

Hi,
Kindly excuse if the reply was unclear. Let me explain.

Scenario:
i) String ( varchar ) key.
ii) No specific meaning assigned.
iii)It is nealc's plan to construct a key by using a sequence. ( not mandatory )

I had posted two options.

i) generator class="uuid"
Reason: String Key, no meaning attached, shall be unique within a network.
Why the term suggestion?
Because, it is a legacy DB. I don't know, if he can modify the column (to varchar2(128) or anything appropriate).
If, this was not the case, it is a solution ( not just a suggestion ).

ii)generator class="assigned"
Reason: If he cannot use the previous option, and is inclined towards using a sequence, this is one way.

tomas@procserve · **Posted:** Tue May 08, 2007 4:09 am

You want to the following generator tag:

Code:

<generator class="sequence">
    <param name="sequence">sequence name</param>
</generator>

I am using 10g as well and it's working just fine, Hibernate gets the next key from the sequense.

This is used on integer id columns though, not string key columns. For string key columns I think pramodkp has a point. Using Hibernate to generate a unique string ID makes more sence since it will save you a trip to the db and then having to cast it.

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

pramodkp answered my question. The URL he provided gave me all the information I need - Thanks!

dharmendra.pandey · **Posted:** Tue May 08, 2007 7:33 am

And you forgot to give credit to pramodkp.