Search code examples
c#oracle-databasedevartdotconnect

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


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)); 
  } 
}

Solution

  • 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.