Calling an Oracle function using NHibernate
- Tested with NHibernate 2.1.2 and Oracle 11g.
- Needs Oracle.DataAccess (not System.Data.OracleClient). Tested with Oracle.DataAccess 2.111.6.0 AMD64.
- If your Oracle function returns a scalar value of datatype number, the .NET object will be a Decimal.
NHibernate config:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.driver_class">
NHibernate.Driver.OracleDataClientDriver
</property>
</session-factory>
</hibernate-configuration>
C# code:
var result = nhibernateSession
.CreateSQLQuery("select GetSomeValue(:p_parameter1) from dual")
.SetParameter("p_parameter1", parameter1)
.UniqueResult();
// GetSomeValue returns a scalar of datatype number; result will be object of type Decimal
var someValue = Convert.ToInt32(result);
Calling an Oracle stored procedure using NHibernate
- The stored procedure must have an out sys_refcursor parameter as the first argument.
- Tested with NHibernate 2.1.2 and Oracle 11g.
- Needs Oracle.DataAccess (not System.Data.OracleClient). Tested with Oracle.DataAccess 2.111.6.0 AMD64.
NHibernate config:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.driver_class">
NHibernate.Driver.OracleDataClientDriver
</property>
</session-factory>
</hibernate-configuration>
NHibernate mapping:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="SampleProject" namespace="SampleProject">
<sql-query name="GetEmployeesByDepartmentId">
<return class="Employee" />
{ call GetEmployeesByDepartmentId(:p_departmentid) }
</sql-query>
</hibernate-mapping>
C# code:
var employees = nhibernateSession
.GetNamedQuery("GetEmployeesByDepartmentId")
.SetParameter("p_departmentid", departmentId)
.List<Employee>();
To execute a stored procedure that has no out parameters:
nhibernateSession
.CreateSQLQuery("call DoSomething(:p_parameter1)")
.SetParameter("p_parameter1", parameter1)
.ExecuteUpdate();
LINQPad – a worthy successor to Snippet Compiler
In 2009 I blogged about how Snippet Compiler allows us to test out C# code without having to create a project in Visual Studio just for that. Now there’s something better: LINQPad. Don’t be misled by its name; it’s not just for LINQ, it can run any C#, VB, or even SQL code. I particularly like its results view. A screenshot is worth a thousand words:
Problem with Transactions on Windows 64-bit Oracle Client
After upgrading to a 64-bit machine at the office, my Visual Studio 2010 unit tests that were making heavy use of transactions (i.e., hundreds of uncommitted database table insertions within a using (new TransactionScope()) statement) failed with the following error message:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [ktcirs:hds], [0×00F7D8078], [0×006F10BF0], [0×01B8C8078], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ktcirs:hds], [0×00F7D8078], [0×006F10BF0], [0×01B8C8078], [], [], [], [], [], [], [], []
Process ID: 4084
Session ID: 125 Serial number: 369
The solution
- Install 64-bit Oracle Client 11.1.0.6.0 (
win64_11gR1_client.zip) but do not install Oracle Services for Microsoft Transaction Server. - Install only OraMTS using 64-bit Oracle Data Access Component 11.2.0.2.1 (
ODAC112021Xcopy_x64.zip).
What’s the problem, BTW?
“Windows Vista and Windows Server 2008 introduce new MSDTC changes that do not interoperate with older versions of Oracle Services for MTS. Oracle Services for MTS 10.2.0.4 and higher, with the exception of 11.1.0.6, support these new changes on Windows Vista Service Pack 1 and Windows Server 2008 or higher.” Source: http://www.oracle.com/technetwork/database/windows/index-089915.html
Robocopy in lieu of rsync
Perhaps you’re a web developer familiar with Linux and SVN. Then you’ll definitely know how to copy files from your SVN working directory to a test server whilst excluding all .svn directories:
rsync -IrW --stats --exclude=.svn /path/to/project/dir/ //testsrv/whatever
If for some reason your development environment is Windows you might be tempted to use rsync on Cygwin. That’s works fine (albeit with a very noticeable performance lag), but there is actually a native Windows alternative to rsync:
robocopy \path\to\project\dir \\testsrv\whatever /MIR /XD .svn
Robocopy has long been available on NT 4.0 via the Windows Resource Kit, and is included in Vista, Windows 7, and Windows Server 2008.