I'm using C# with oracle ODP.NET to query a table, and it seems to have a odd behavior if the field value has the symbol "%". As the following example:
In Oracle (11g or 12c) I run the following command to create a test table inserting the values with the symbol %
CREATE TABLE "TESTCHAR" ("NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE, "RESULT" VARCHAR2(4000 BYTE));
COMMIT;
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 1 % Area','10');
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 2 % Area','10');
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 3 % Area','10');
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 4 % Area','10');
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 5 % Area','10');
INSERT INTO TESTCHAR (NAME, RESULT) VALUES ('Test 6 % Area','10');
COMMIT;
Now in a simple windows form application in Visual Studio (used 2012 and 2015) I just create the following app:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection("User Id=myUser;Password=myPass;Data Source=myData");
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from testchar";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
DataTable dt = new DataTable();
dt.Load(dr);
dataGridView1.DataSource = dt;
}
}
}
It will pops up only 5 rows in the datagrid. But if you goes directly to SQL Developer and uses the same query, it will return all rows. I've already tried the managed and unmanaged odp.net. It seems to be a bad interpretation in the oracle provider.
If you use System.Data.OleDb this problem does not occurs, but I cannot use it as we deal with Clob type.
Does anybody know a solution for this problem?
Thansk all!
