How do I get the NextVal from an oracle Sequence thru NHibernate

Question

I am working on c# .net 4.0 and using NHibernate to talk with an Oracle DB. You would think something as simple as this is already addressed somewhere but sadly its not. I need the NextVal from an Oracle sequence. I do not need to insert it a database as part of an Id or Primary key. I just need to use the next val on the c# side.

Can somebody help me out with xml mapping and C# file(or a link) to achieve this.

Thanks.

Something like

int NextValueOfSequence = GetNextValueofSequence();

public int GetNextValueOfSequence()
{

// Access NHibernate to return the next value of the sequence.

}

Cant you just run a max on the Id property and then add 1 to it? — Baz1nga, Jan 07 '11 at 19:03
No. Just assume that the DB has no tables, no SPs , nothing at all but an oracle sequence. I need to get the nextval and use it on the c# side. — trainer, Jan 07 '11 at 19:26

Petr Kozelek · Accepted Answer · 2011-01-07T22:07:54.487

7

Mapping:

  <sql-query name="GetSequence" read-only="true">
    <return-scalar type="Int64"/>
    <![CDATA[
    SELECT SeqName.NEXTVAL from DUAL;
    ]]>
  </sql-query>

Code:

Int64 nextValue = session.GetNamedQuery("GetSequence").UniqueResult<System.Int64>();

edited Jan 07 '11 at 22:07

answered Jan 07 '11 at 19:39

Petr Kozelek

1,126
8
14

Thanks, I did get some syntax errors in the mapping. Due to time constraints I could not get down to investigating/fixing them. I ended up using................CreateSQLQuery("select .NEXTVAL from dual").UniqueResult(); – trainer Jan 07 '11 at 21:54
But thanks for your input, it helped me change my course to SQL instead of searching all over for a standard nhibernate mapping/domain class solution. – trainer Jan 07 '11 at 21:58
Sorry, I edited the answer. There should be "return-scalar" in the mapping. – Petr Kozelek Jan 07 '11 at 22:08

score 5 · Answer 2 · answered Nov 24 '16 at 23:48

With NH4 I use this DB agnostic ISession extension method (obviously DB must support sequences)

public static T GetSequenceNextValue<T>(this ISession session, string sequenceName) where T : struct
{
    var dialect = session.GetSessionImplementation().Factory.Dialect;
    var sqlQuery = dialect.GetSequenceNextValString(sequenceName);
    return session.CreateSQLQuery(sqlQuery).UniqueResult<T>();
}

score 5 · Answer 3 · answered Jan 07 '11 at 22:53

5

This also does the trick.

 <your session variable>.CreateSQLQuery("select <your sequence>.NEXTVAL from dual").UniqueResult<Int64>();

answered Jan 07 '11 at 22:53

trainer

369
4
14

score 0 · Answer 4 · answered Jul 09 '12 at 17:33

There is a small correction in the mapping given by @Petr Kozelek

<sql-query name="GetSequence" read-only="true">
    <return-scalar column="NextNo" type="Int64"/>
    <![CDATA[
        SELECT SeqName.NEXTVAL as NextNo from DUAL
    ]]>
</sql-query>

How do I get the NextVal from an oracle Sequence thru NHibernate

4 Answers4