RSS

Query using Oracle Parameter returns no result

23 Jul

I had a parameterized query with some string variables binding to the OracleParameters. The query works with certain parameters but doesn’t with others. Take a simplified database table with only 2 columns for example:

SampleTable:

Name Type
Currency Char(3)
SerialNo Char(8)

My original query looks something like this. The first query works. The second runs normally without error message but returns no result when there are records matched in the database.

Dim currency As string = "USD"
Dim serialNo As string = "1311B"

'Quering with cmd1 works
Dim cmd1 As New OracleCommand("SELECT * FROM SampleTable WHERE Currency = :Currency")
cmd1.Parameters.Add(New OracleParameter("Currency", currency))

'Quering with cmd2 works but no record is returned when there are matching records in the database
Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = :SerialNo")
cmd2.Parameters.Add(New OracleParameter("SerialNo", serialNo))


Writing the values directly into the command text works for the second query. With the risk of SQL injection this is not usually what we want.

Dim serialNo As string = "1311B"
Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = '" & serialNo & "'")

It took me some time to figure out specifying the OracleType of the OracleParameter is a must in this case. The default OracleType of the OracleParameter generated for string variables is OracleType.VarChar.

Dim serialNo As string = "1311B"

Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = :SerialNo")
Dim param As New OracleParameter("SerialNo", serialNo)
param.OracleType = OracleClient.OracleType.Char
cmd2.Parameters.Add(param)

The difference here is the column Currency is of type Char(3) and the column SerialNo is of type Char(8). Without specifying the OracleType of the OracleParameter only the query for the former column could work as expected.

Advertisements
 
Leave a comment

Posted by on July 23, 2012 in .NET, ADO.NET, Database, Oracle, SQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: