Why do I error out when trying to retrieve a result set with DotConnect components?

Question

With the code below I get, "ORA-01036: illegal variable name/number" on the call to ExecuteReader:

cmd.Parameters.Add("cur", Devart.Data.Oracle.OracleDbType.Cursor);
cmd.Parameters["cur"].Direction = ParameterDirection.Output;
Devart.Data.Oracle.OracleCursor oraCursor =
    (Devart.Data.Oracle.OracleCursor)cmd.Parameters["cur"].Value;
Devart.Data.Oracle.OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read()) {
    ACurrentUserRoles.Add(odr.GetString(0));
}

What I want to do is populate a List with the result of the query. I don't see any examples for that in DevArt's documentation (or googling). I had it working with Oracle's ODP components with:

OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read()) 
{
    ACurrentUserRoles.Add(odr.GetString(0));
}

...but can't find the parallel working with DotConnect components.

Updated:

Okay, here's the entire method (ACurrentUserRoles is a List of Strings):

public void PopulateCurrentUserRoles(String AUserName, List<String> ACurrentUserRoles) {
  _UserName = AUserName;

  String query = "select roleid from ABCrole where ABCid = :ABCID";
  Devart.Data.Oracle.OracleCommand cmd = new Devart.Data.Oracle.OracleCommand(query, con);
  cmd.CommandType = CommandType.Text;
  int _ABCID = GetABCIDForUserName();
  cmd.Parameters.Add("ABCID", _ABCID);
  cmd.Parameters["ABCID"].Direction = ParameterDirection.Input;
  cmd.Parameters["ABCID"].DbType = DbType.String;
  cmd.Parameters.Add("cur", Devart.Data.Oracle.OracleDbType.Cursor);
  cmd.Parameters["cur"].Direction = ParameterDirection.Output;
  //cmd.ExecuteNonQuery(); blows up: "illegal variable name/number"
  //cmd.ExecuteCursor();   " "
  //cmd.ExecuteReader();   " "
  Devart.Data.Oracle.OracleCursor oraCursor =
    (Devart.Data.Oracle.OracleCursor)cmd.Parameters["cur"].Value;
  Devart.Data.Oracle.OracleDataReader odr = oraCursor.GetDataReader(); // "Object reference not set to an instance of an object"
  while (odr.Read()) {
    ACurrentUserRoles.Add(odr.GetString(0));
  }
}

The err msgs I'm getting are appended as comments to the lines where they occur.

score 1 · Answer 1 · answered Mar 28 '12 at 22:13

1

First, why are you adding a cursor type parameter and then totally ignore it?.
Second, I have never seen this use of cursor with the ExecuteReader but with the ExecuteNonQuery.

For example:

string cmdText = "begin open :cur for select * from dept; end;";
OracleCommand oraCommand = new OracleCommand(cmdText, oraConnection);
oraCommand.Parameters.Add("cur", OracleDbType.Cursor);
oraCommand.Parameters["cur"].Direction = ParameterDirection.Output;
oraCommand.ExecuteNonQuery();
OracleCursor oraCursor = (OracleCursor)oraCommand.Parameters["cur"].Value;
oraDataAdapter.Fill(dataSet, "Table", oraCursor);

So probably your exception derives from the use of ExecuteReader

This is another example taken directly from the site of DevArt:

string cmdText = "begin open :cur1 for select * from dept;" + 
    "open :cur2 for select * from emp; end;";
OracleCommand oraCommand = new OracleCommand(cmdText, oraConnection);
oraCommand.Parameters.Add("cur1", OracleDbType.Cursor);
oraCommand.Parameters["cur1"].Direction = ParameterDirection.Output;
oraCommand.Parameters.Add("cur2", OracleDbType.Cursor);
oraCommand.Parameters["cur2"].Direction = ParameterDirection.Output;
oraDataAdapter.SelectCommand = oraCommand;
oraDataAdapter.Fill(dataSet);

answered Mar 28 '12 at 22:13

Steve

213,761
22
232
286

But it IS a query, so why would I use ExecuteNonQuery()? It sounds like it should be Insert, Update, Delete statements. This is a Select that returns 1..N records. – B. Clay Shannon-B. Crow Raven Mar 28 '12 at 22:20
Also, I want to add the results of the query to a List, that's why I want to loop through it using a reader. In the first example above, "dataSet" comes out of nowhere - are they filling a grid, or what? – B. Clay Shannon-B. Crow Raven Mar 28 '12 at 22:22
Then, please, show the text of your query. If cursor is not needed remove it from the parameters, or use the second example. – Steve Mar 28 '12 at 22:22
The examples contains only relevant code. From the name we could assume that is a DataSet declared elsewhere. – Steve Mar 28 '12 at 22:24
I'm sure you have already checked, but there is an extensive documentation on their site here [DevArt dotConnect for Oracle](http://www.devart.com/dotconnect/oracle/articles/toc.html) – Steve Mar 28 '12 at 22:28
Trying to adapt (no pun intended) the first example, I get the same err msg when I call ExecuteNonQuery() – B. Clay Shannon-B. Crow Raven Mar 28 '12 at 22:35
Sorry @ClayShannon I call a break now. It's past midnight here in Italy. See you tomorrow... *And post the text of your query*.... – Steve Mar 28 '12 at 22:38
I see your query now. And for me it's clear. There is no cursor declared inside the query. Remove the two lines `cmd.Parameters.Add("cur", Devart.Data.Oracle.OracleDbType.Cursor);` and `cmd.Parameters["cur"].Direction = ParameterDirection.Output;` and reenable the line with ExecuteReader(). – Steve Mar 29 '12 at 17:51
OK, I'll czech this out when I get to work; if it solves my dilemma, I'll buy you an Espresso or something when you come to America. – B. Clay Shannon-B. Crow Raven Mar 30 '12 at 13:14

Why do I error out when trying to retrieve a result set with DotConnect components?

1 Answers1