Working with .NET Boolean and Oracle along with ASP.NET MVC

Somethings that are straight forward in MS-SQL and .NET are bit convoluted when using Oracle and .NET. A good example for this case is when trying to map a database Boolean field to a .NET POCO. With MS-SQL we can straight away use bit which has a possible values of 1 or `, but with Oracle since it does not have a built in Boolean type we would normally go with a single character field with a value of‘Y’or‘N’`.

[Read More]

Displaying SYS_REFCURSOR / CURSOR results from Oracle in Toad

I keep forgetting this method when I try to debug a procedure after some interval, so I am blogging it for my own reference.

Procedure:

PROCEDURE GET_ROLES_FOR_USER_LOGIN (
      i_user_domain   IN     IEMS_USERS.DOMAIN%TYPE,
      i_user_login    IN     IEMS_USERS.LOGIN%TYPE,
      o_roles            OUT SYS_REFCURSOR);

Generally, we can right click on the procedure name on the schema browser and select Execute package, which gives the skeleton for executing your procedure. Once we have the code, we just add a refcursor variable at the top, assign its value from the output of the procedure and then print it at the end.

[Read More]

Oracle Managed ODP.NET

Note:This documentation/guide is based on Managed ODP.NET v11.2.0.3.50 Beta

Download Managed ODP.NET from Oracle Technology Network.

Finally Oracle has released a pure .NET based client for connecting and working with Oracle databases from your CLR world without the dependencies lying around in the GC ignorant world. With a reference added to the Oracle.ManagedDataAccess.dll which weighs a mere 6+ MB’s (against 150+ MB’s previously) you are ready to get started with you day-to-day interaction to the 10g R2 or higher Oracle Databases.

[Read More]

Dapper – Micro ORM for Oracle and Microsoft .NET

I have been on the hunt for an easier ORM tool to help with my day-to-day work. Till now the ones I have tested have fallen short of fulling one of criteria: Should work with Oracle Should support multiple REFCURSOR output parameters from Oracle Stored Procedures and OracleDbType’s We use stored procedures exclusively for our CURD operations, so this is the determining requirement and I do not like compromising having an abstract layer which does not support OracleDbType’s just because it has to work with other DB’s [Read More]