Is a cursor parameter really to be declared differently than a "regular" parameter?

Question

OK, nobody seems to know how to solve the problem I'm having looping through a cursor/result set for storage into a List, so I'm going to break it down into pieces and try to slog through it that way. So, first of all:

I add SQL Parameters to an OracleCommand object this way (works fine):

cmd.Parameters.Add("ABCID", _ABCID);
cmd.Parameters["ABCID"].Direction = ParameterDirection.Input;
cmd.Parameters["ABCID"].DbType = DbType.String;

IOW, when I add the param, I pass the name of the parameterized portion of the SQL ("ABCID" above) and a value to give it (_ABCID is a variable that has been assigned, let's say, "42").

However, when adding a Cursor (output) param, it seems to want, not a value (such as an initialized cursor object), but simply the data type:

cmd.Parameters.Add("cur", Devart.Data.Oracle.OracleDbType.Cursor);
cmd.Parameters["cur"].Direction = ParameterDirection.Output;

(I tried both ways, and neither one works, so...?)

Verily/thus, my question is: Is this really the correct way of declaring a Cursor parameter to be outputted back for traversal/access?

I'm using the brand new version of DevArt DotConnect components (6.80.332), VS 2010, .NET 4

Updated:

Here's the code in more context:

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("cur", Devart.Data.Oracle.OracleDbType.Cursor); 
  cmd.Parameters["cur"].Direction = ParameterDirection.Output; 

  cmd.Parameters.Add("ABCID", _ABCID); 
  cmd.Parameters["ABCID"].Direction = ParameterDirection.Input; 
  cmd.Parameters["ABCID"].DbType = DbType.String; 
  //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)); 
  } 
}

Why are you using a cursor? Cursors are (generally) the worst thing you can do in SQL? I suggest relooking at the SQL AND/OR using a WHILE loop. Remember, SQL is SET based logic, not procedural. If you are looping heavily, then you should be doing this in your code, not the SQL — Justin Pihony, Mar 29 '12 at 17:28
"neither one works" doesn't really explain what you've observed when you've tried it... — Jon Skeet, Mar 29 '12 at 17:29
@Jon: If I call ExecuteNonQuery() - which seems to be the consensus, although it makes no sense to me, as my SQL statements IS a query (Select) - I get, "illegal variable name/number". If I comment ExecuteNonQuery() out, I get, "Object reference not set to an instance of an object" when I call GetDataReader() — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 18:58
@Justin: I'm trying to use a while loop, and that's what I was doing with Oracle's ODP components. I'm not seeing how to do it with the DotConnect components. — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 18:59
@Justin: My SQL is a simple select with a parameter. I want to loop through the result set, assigning what the SQL returns to the members of a List of String. — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 19:00
@ClayShannon: You haven't shown the code which is calling `GetDataReader`, which makes it hard to say what's going on... — Jon Skeet, Mar 29 '12 at 19:02

radarbob · Answer 1 · 2012-03-30T20:03:00.713

the following is from the Oracle Data Provider for .NET Developer's Guide. yes, I know, "Devart". Nonetheless, It suggests the following:

Be careful with your parameter typing declaration.
add that cursor/output parameter to the Parameters collection before any others.

As a long shot... my guide shows a OracleDbType.RefCursor but not a OracleDbType.Cursor. If DevArt has RefCursor, try that. In visual studio, what type does .NET think that parameter is? This question is not as dumb as I used to think.

... On the other hand, if the parameter is set as an OracleDbType.Char type by setting the OracleDbType property, the output data is returned as an OracleString type. If both DbType and OracleDbType properties are set before the command execution, the last setting takes affect.

. . .

"An application should not bind a value for output parameters; it is the responsibility of ODP.NET to create the value object and populate the OracleParameter Value property with the object. When binding by position (default) to a function, ODP.NET expects the return value to be bound first, before any other parameters."

EDIT:

Based on @Clay's self-answer... so there is no parameter specified for the output, rather one simply does this: OracleDataReader odr = cmd.ExecuteReader();

"Be careful with your parameter typing declaration" - meaning...? — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 19:21
There is a "Ref" but no "RefCursor". Replacing .Cursor with .Ref made no difference (same err msg). — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 19:30
The DotConnect examples I've seen are all about using a DataAdapter, filling it, and then supplying a grid with that data. I'm beginning to think I'm going to have to do something like that, and then traverse the Dataset (which middleman I shouldn't need) to populate my List of String...arghhhh — B. Clay Shannon-B. Crow Raven, Mar 29 '12 at 22:03

score 0 · Accepted Answer · answered Mar 30 '12 at 16:07

Straight from the horse's mouth (the DevArt folks):

_UserName = AUserName;
// From the DevArtisans:
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;
Devart.Data.Oracle.OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read()) {
  ACurrentUserRoles.Add(odr.GetString(0));
}

To quote Casey and the Sonshine Banned, "That's the way, Uh huh Uh huh, I like it, Uh huh Uh huh"; actually, I can't stand that crap, but I do kind of relate to that sentiment right about now.

Is a cursor parameter really to be declared differently than a "regular" parameter?

2 Answers2