Click here to Skip to main content
11,640,907 members (58,958 online)
Click here to Skip to main content

Tagged as

Gotcha #1161: Using Named Parameters with Oracle ODP.NET

, 8 Jun 2011 CPOL 42.7K 9
Rate this:
Please Sign up or sign in to vote.
Using Named Parameters with Oracle ODP.NET

Introduction

After being legged-up for a good couple of hours by this the other day, I'm writing this quick note to try and prevent other people from nearly throwing their PC out of the window in frustration. It concerns using named query parameters with the Oracle Data Provider for .NET (ODP.NET).

The Problem

Consider the following simple data-retrieval operation:

// C#
using (OracleConnection connection = new OracleConnection
	(ConfigurationManager.ConnectionStrings["OracleExpress"].ConnectionString))
{
    string query = "SELECT SOME_COLUMN, ANOTHER_COLUMN, THIRD_COLUMN FROM SOME_TABLE 
		WHERE ANOTHER_COLUMN = :SomeParam AND THIRD_COLUMN = :AnotherParam";
    OracleCommand command = new OracleCommand(query, connection) 
			{ CommandType = CommandType.Text };
    command.Parameters.Add(":AnotherParam", OracleDbType.Varchar2).Value = "Ping";
    command.Parameters.Add(":SomeParam", OracleDbType.Varchar2).Value = "Foo";
    connection.Open();
    IDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        int someColumn = reader.GetInt32(reader.GetOrdinal("SOME_COLUMN"));
        string anotherColumn = reader.GetString(reader.GetOrdinal("ANOTHER_COLUMN"));
        string thirdColumn = reader.GetString(reader.GetOrdinal("THIRD_COLUMN"));
        Console.WriteLine(String.Format("{0}: {1}, {2}", someColumn, 
		anotherColumn, thirdColumn));
    }
}
' Visual Basic
Using connection As OracleConnection = New OracleConnection_
	(ConfigurationManager.ConnectionStrings("OracleExpress").ConnectionString)
    Dim query As String = "SELECT SOME_COLUMN, ANOTHER_COLUMN, _
	THIRD_COLUMN FROM SOME_TABLE WHERE ANOTHER_COLUMN = _
	:SomeParam AND THIRD_COLUMN = :AnotherParam"
    Dim command As OracleCommand = New OracleCommand(query, connection) _
	With {.CommandType = CommandType.Text}
    command.Parameters.Add(":AnotherParam", OracleDbType.Varchar2).Value = "Ping"
    command.Parameters.Add(":SomeParam", OracleDbType.Varchar2).Value = "Foo"
    connection.Open()
    Dim reader As IDataReader = command.ExecuteReader()
    While reader.Read()
        Dim someColumn As Integer = reader.GetInt32(reader.GetOrdinal("SOME_COLUMN"))
        Dim anotherColumn As String = _
		reader.GetString(reader.GetOrdinal("ANOTHER_COLUMN"))
        Dim thirdColumn As String = reader.GetString(reader.GetOrdinal("THIRD_COLUMN"))
        Console.WriteLine(String.Format("{0}: {1}, {2}", someColumn, _
	anotherColumn, thirdColumn))
    End While
End Using

Now, assuming that the data in our table is as follows:

SOME_COLUMN ANOTHER_COLUMN THIRD_COLUMN
1 Foo Ping
2 Bar Pong
3 Baz Ping

We should get back a single row from the database, right? Wrong! Actually what we get back is nothing. Why is this? If we run this query in another tool of our choice (SQL+, TOAD, etc.), we find it works as expected.

The Solution

After some scouring of Google, I eventually tracked down what was causing the problem. It turns out that unlike, for example the SQL-Server data provider, the Oracle data provider always binds parameters by position unless told otherwise. So, even though we have named parameters in our query, and we have added named parameters to the OracleCommand object, the data provider still binds the parameters by position; and as we have added the parameters in the 'wrong' order (albeit deliberately in this example) the query doesn't throw an exception, it merely returns an empty result set as the query received by the database is the equivalent of:

SELECT SOME_COLUMN, ANOTHER_COLUMN, THIRD_COLUMN
FROM SOME_TABLE
WHERE ANOTHER_COLUMN = 'Ping'
AND THIRD_COLUMN = 'Foo'

The solution: This isn't made entirely clear on the Oracle documentation, but there is an additional property, BindByName, on the OracleCommand object, which must be set to true in order to bind parameters by name:

// C#
OracleCommand command = new OracleCommand(query, connection) 
	{ CommandType = CommandType.Text, BindByName = true };
' Visual Basic
Dim command As OracleCommand = New OracleCommand(query, connection) _
	With {.CommandType = CommandType.Text, .BindByName = True}

The above query should now work as expected.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

MBigglesworth79
Web Developer
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionThank you very much Pin
Amit Das Celtic19-May-14 0:54
memberAmit Das Celtic19-May-14 0:54 
AnswerRe: Thank you very much Pin
minek7328-May-14 0:40
memberminek7328-May-14 0:40 
QuestionMany thanks Pin
Shashidhar Rao Nellutla28-Nov-13 6:12
memberShashidhar Rao Nellutla28-Nov-13 6:12 
QuestionThis article saved my PC Pin
Member 98527419-Oct-13 5:52
memberMember 98527419-Oct-13 5:52 
QuestionThank you thank you thank you thank you! Pin
Marcos Foster18-Apr-13 4:29
memberMarcos Foster18-Apr-13 4:29 
QuestionThanks Pin
wickedtuner24-Mar-13 22:38
memberwickedtuner24-Mar-13 22:38 
GeneralMy vote of 5 Pin
Michael Latham5-Sep-12 7:29
memberMichael Latham5-Sep-12 7:29 
QuestionSaved me hours of fustration! Pin
RJustesen29-Nov-11 6:22
memberRJustesen29-Nov-11 6:22 
GeneralMy vote of 5 Pin
johnbrown1053-Nov-11 22:11
memberjohnbrown1053-Nov-11 22:11 
GeneralMy vote of 5 Pin
Dieter Buecherl13-Jun-11 21:01
memberDieter Buecherl13-Jun-11 21:01 
GeneralMy vote of 5 Pin
OrbitalInMotion9-Jun-11 10:50
memberOrbitalInMotion9-Jun-11 10:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 8 Jun 2011
Article Copyright 2011 by MBigglesworth79
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid