Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

Tag Archives: ODP.NET

Passing Optional & Unordered Parameters via ODP.NET – PLS-00306

Times change. Recently I was interacting with a team that was using ODP.NET. And they had this stored procedure written in SQL server which they were moving to Oracle DB. They had some challenges in porting their data access code to support Oracle. One of them was – ODP.NET requires all parameters (even if they have default values) to be passed to the Stored Procedure and also expects them in the same order. But with SqlClient the same is not required. Solution to this is use BindByName property of OracleCommand class. Let me elaborate through a small sample code for better understanding:

Dummy

Consider the following dummy procedure using the above Dummy Table
CREATE PROCEDURE [dbo].[DummyProc]
@Age As Int,
@Name As varchar(50) = ‘AA’ –default value
AS
BEGIN
SET NOCOUNT ON;
DECLARE @noOfRows as INT

SELECT * from Dummy d where d.Age = @Age AND d.Name = @Name

SELECT @noofrows = @@rowcount –tSql is not case sensitive
RETURN @noofrows
END
Now the C# code to pass parameters to this would be:

SqlConnection conn = new SqlConnection(@”ConnString”);
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
command.Parameters.Add(new SqlParameter(“@Age”, 2)); //Order doesn’t matter
//command.Parameters.Add(new SqlParameter(“@Name”, “AA”)); – This is not required
SqlParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “ReturnCode”;
paramReturnCode.DbType = DbType.Int32;
paramReturnCode.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@”ReturnCode”].Value);
conn.Close();

//Output
1

Pretty simple. Let’s move the above to oracle now & access it from ODP.NET. First let’s create a stored procedure in Oracle.

create or replace
PROCEDURE DUMMYPROC
( p_Age IN NUMBER
, p_Name IN VARCHAR2 DEFAULT ‘AA’
, p_NoOfRows OUT NUMBER
) AS
BEGIN
select count(*) into p_noofrows from dummy d where d.age = p_Age AND d.name = p_name;
–N.B. In above ‘into’ is required, for select returning a recordset you need a RefCursor
END DUMMYPROC;

Now let’s write some code using ODP.NET (Oracle.DataAccess.dll)

OracleConnection conn = new OracleConnection(@”ConnString”);
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
//command.BindByName = true;
command.Parameters.Add(new OracleParameter(“p_Age”, 2));
//command.Parameters.Add(new OracleParameter(“p_Name”, “AA”)); // This is not required
OracleParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “p_NoOfRows”;
paramReturnCode.OracleDbType = OracleDbType.Int32;
paramReturnCode.Direction = ParameterDirection.Output;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@”p_NoOfRows”].Value);
conn.Close();

The above code on execution throws an error – PLS-00306: wrong number or types of arguments in call to ‘DUMMYPROC’. The issue here is oracle expects all parameters to be passed in same order for faster execution. But as it turns out that it’s not always the case. Run the same Oracle code above but uncomment line no. 7 – command.BindByName = true

And here you are with the expected output.
//Output
1

Hope this helps :) .

Accessing Oracle from .NET

I am a MS guy & prefer to work & express myself using MS technologies. Lately things changed. I had to work on a product that supported Oracle DB. It has been a long time since I had written code to connect to Oracle DB, less even to install an Oracle Database. Luckily for me Oracle DB was already installed. So I set out to connect to Oracle. I found that there are quite a few  options to connect to Oracle. I am going to discuss them in this post.

1) You can do it via Microsoft .NET data provider for ODBC / OLEDB. This look like old way of communication to me especially when ADO.NET provides the powerful data provider model.

2) Microsoft’s data provider for Oracle – This is installed by default while installing .NET Framework / VS.NET. The provider resides in System.Data.OracleClient assembly. This requires the installation of Oracle client software.
(N.B. If you are using .NET 1.0 / VS.NET 2002 you can download the provider here. Oracle Client software seems to be specific to version of Oracle, for instance you can get access to 10g client from here & 9i Client from here. I haven’t tried the backward compatibility for the Oracle Client Software i.e. accessing 9i database from 10g client).

3) Oracle Data Provider for .NET (ODP.NET) is the ADO.NET provider coming straight from Oracle. It seems to be better in Performance & more close to Oracle in terms of available features. You can download the same here. Note this bundle also includes ASP.NET Provider, Oracle Services for Distributed Transactions, support for .NET 2.0 & 1.x. I tried installing Oracle 11g ODAC, it works perfectly fine with VS.NET 2008 & I was able to connect to my 9i Oracle instance from it. Oracle also provides Developer Tools for Visual Studio (ODT.NET), which for instance allows you to access Oracle from server explorer. A detail list of ODT features can be found here. ODT also comes bundled with ODP, & the same can be downloaded from here. The base assembly for ODP.NET is Oracle.DataAccess.
(N.B. After installation you of Oracle Client / ODP you need to create a file named TNSNAMES.ORA in the %Oracle Installation%/Network/Admin folder).

4) Of course I didn’t use either of above. We are using a O/R mapper & one which seems to be reasonable at present is NHibernate. You can download its latest version 2.0 from here. NHibernate 2.0 wiki is here. Now the question is how to you make NHibernate use ODP.NET? Simplest solution is given here.

Hope this would save your valuable time.

Follow

Get every new post delivered to your Inbox.

Join 180 other followers