Using DbCommand to call a stored proc with cursor output

[wkchen]

Is there a way I can call stored proc directly from the EntityContainer's Function Imports directly using the DbCommand(using the context's connection object) that outputs a cursor parameter? We were able to do it with other types(int, double, string, etc), but no option for cursor type when comes to create a DbParameter object. I've tried to cast the Connection.CreateCommand to OracleCommand but returned NULL.

Anybody knows another way to call an oracle stored proc to putput a cursor?

[AndreyR]

At first, take a look at this article.
If you are sure that you need to use OracleCommand, use the code like the following:

Code: Select all

((context.Connection as EntityConnection).StoreConnection as OracleConnection).CreateCommand();

[wkchen]

Thx for the reply.

Your code to create the Command object works. But now I have another problem getting the cursor value by calling the ExcuteReader(). See my codes below and let me know what I am doing wrong

using (OracleCommand command = ((OracleConnection) ((EntityConnection) Connection).StoreConnection).CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Stored_Proc_name";

//Parameters
OracleParameter countParam = command.CreateParameter();
countParam.ParameterName = "i_count";
countParam.Direction = ParameterDirection.Input;
countParam.OracleDbType = OracleDbType.Number;
countParam.Value = count;
command.Parameters.Add(countParam);

OracleParameter cursorParam = command.CreateParameter();
cursorParam.ParameterName = "o_resultCursor";
cursorParam.Direction = ParameterDirection.Output;
cursorParam.OracleDbType = OracleDbType.Cursor;
command.Parameters.Add(cursorParam);

if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}

OracleDataReader dr = command.ExecuteReader();

int sapId;
while (dr.Read())
{
sapId = System.Convert.ToInt32(dr[0]);
}
return dr;

}

The above code hangs on dr.Read() and eventually gets this error "ORA-08103: object no longer exists inner"

Any idea?

[Shalex]

Please try using

Code: Select all

command.ExecuteNonQuery();
OracleDataReader dr = ((OracleCursor)command.Parameters["o_resultCursor"].Value).GetDataReader();

instead of

Code: Select all

OracleDataReader dr = command.ExecuteReader();

A complete sample is available in our documentation here:
http://www.devart.com/dotconnect/oracle ... ursor.html.

If it doesn't help, please send us the following:
1) a small test project with the corresponding DDL/DML script to reproduce the issue in our environment;
2) the versions of your dotConnect for Oracle (x.xx.xxx), Oracle Server, and Oracle Client (for the OCI mode).

[wkchen]

Actually, the problem was in the stored proc itself. I was using the global temp table. I found another way to query my data and it's working with the existing code now. Thx for the cursor resource link anyway..