Why am I wrong when trying to get a result set with DotConnect components?
With the code below, I get "ORA-01036: Illegal variable name / number" when I call 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 the list with the query result. I don't see examples for this in the DevArt documentation (or on Google). I worked with Oracle ODP components with:
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
ACurrentUserRoles.Add(odr.GetString(0));
}
... but can't find a parallel working with DotConnect components.
Updated:
Ok, here's the whole 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));
}
}
I get error messages that are added to comments on the lines where they occur.
source to share
First, why are you adding a cursor type parameter and then ignoring it completely ?.
Second, I have never seen this use of a cursor with ExecuteReader, but with 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 your exception is probably due to the use of ExecuteReader
This is another example taken directly from the DevArt site:
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);
source to share