If you subtract one timestamp from another in Oracle, the result is an interval type.  This is similar to how subtracting a DateTime from another in .NET yields a TimeSpan.  Unfortunately, unlike .NET, Oracle provides no simple equivalent to TimeSpan.TotalSeconds.

Here's the SQL to accomplish it.  Note that fractions of a second are included in the result.

You can't set the next value of a sequence directly in Oracle; you have to drop and recreate the sequence.  Here's a way to do it if you need to set the next value to the largest value in a particular column plus 1:

When I recently upgraded from Spring.NET 1.1.0 to 1.1.1, I started getting a bizarre message:

DbProvider product name = Oracle, Oracle provider V2.102.2.20, could not resolve commandBuilderDeriveParametersMethod DeriveParameters to MethodInfo

It's looking for a method named DeriveParameters on the Oracle.DataAccess.Client.OracleCommandBuilder class.  Poking around with the .NET reflector, the method, sure enough, is not there.

After looking at some forum posts and a logged defect against Spring.NET, there were workarounds.  But surely with a database as common as Oracle these steps shouldn't be necessary!  No one having the issue was using Oracle, and, as it turns out, it's because they're using a newer driver that defines this magical DeriveParameters method.

I was using version 10.1.0.200, which is much newer than the 2.102.2.20 that Spring.NET expects when you look at the dbproviders.xml file.  But, alas, 10.1.0.200 is apparently code for something pre-2.102.2.20.  Oracle has two versioning schemes.  I found that when I downloaded the latest Oracle data access components (ODAC), billed as 11.1.0, I ended up with an Oracle.DataAccess.dll with a version 2.116.6.20 timestamp.  Happily, I found this to work with Spring.NET 1.1 without issue.

As a side note, I upgraded to NHibernate 1.2.1 from 1.2.0 at the same time and found the Oracle dialect suddenly dropped support for the "guid" data type.  Moral of the story:  even point releases have gotchas!

1. Download the Oracle client (or "instant client"):
    
   http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/winsoft.html
    
   Without this client, you'll see the following error message when using Microsoft's Oracle driver:  Class Initialization method Imd.Tests.ServerApi.DeploymentTest.Initialize threw exception. NHibernate.HibernateException:  NHibernate.HibernateException: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. --->  System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
    
2. Set the ORACLE_HOME environment variable and restart your IDE.  An example value is C:\Oracle\product\10.1.0\Client_1. If you don't set this variable you get an error message stating:
    
   Unable to load DLL
   'OraOps10.dll': The specified module could not be found 
    
   If you specify the wrong directory, i.e. specify a directory one level too high or too low, you'll see bizarre exceptions, including NullReferenceExceptions circa Oracle.DataAccess.Client.OracleException.get_Source() or Oracle.DataAccess.Client.OracleException.get_Message().
     
3. Add a project assembly reference to Oracle.DataAccess.dll.  I found this in C:\Oracle\product\10.1.0\Client_1\BIN.
    
4. Update your App.config (or Web.config) properties to specify the Oracle connection string, driver, dialect, etc:
   
       <db:provider provider="OracleClient-2.0"
                    connectionString="Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {hostname})(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = {serviceName})));User Id={username};Password={pass};"/>
    
       <object id="ConfigDbSessionFactory"
               type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
           <property name="HibernateProperties">
               <dictionary>
                   <entry key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
                   <entry key="hibernate.dialect" value="NHibernate.Dialect.Oracle9Dialect"/>
                   <entry key="hibernate.connection.driver_class" value="NHibernate.Driver.OracleClientDriver"/>
               </dictionary>
           </property>
          . . .
       </object>

These instructions are for using Microsoft's .NET driver (System.Data.OracleClient / V2.0.0.0).  A few settings would have to change if you want to use Oracle's .NET driver driver instead (System.DataAccess.Client / V2.102.2.20):

1. Change OracleClient-2.0 to OracleODP-2.0.
2. Change NHibernate.Driver.OracleClientDriver to NHibernate.Driver.OracleDataClientDriver

According to the NHibernate documentation, "Microsoft's driver does not handle long character strings correctly. An error happens in some circumstances when using a string of length 2000-4000 as a parameter value.  Oracle cannot handle empty strings (""), you should use null instead. An IUserType implementation to perform the conversion is contained in Nullables.NHibernate library (part of NHibernateContrib package)."  I've also seen problems with the Microsoft driver not being able to convert a Guid into a byte[].  So pick your poison :-)

• You may recieve a perplexing "invalid username/password" error when your username and password is, in fact, correct.  ODP.NET might be adjusting the case of your password before hashing it and sending it to Oracle; you can get around this by turning off case sensitivity:
  ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
• You may see NullReferenceExceptions with Oracle.DataAccess.Client.OracleException.get_Source() in the stack trace.  This may be indicative of a mismatch between the Spring.NET provider name and the NHibernate driver, i.e. using an Oracle provider name and a Microsoft driver.  If you specify OracleClient-2.0, use the NHibernate.Driver.OracleDataClientDriver driver; if you specify OracleODP-2.0, use NHibernate.Driver.OracleClientDriver.