Using DbCommand to call a stored proc with cursor output

Using DbCommand to call a stored proc with cursor output

Postby wkchen » Tue 28 Dec 2010 02:13

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?
wkchen
 
Posts: 4
Joined: Tue 28 Dec 2010 01:46

Postby AndreyR » Tue 28 Dec 2010 12:24

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();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Using DbCommand to call a stored proc with cursor output

Postby wkchen » Tue 28 Dec 2010 20:45

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?
wkchen
 
Posts: 4
Joined: Tue 28 Dec 2010 01:46

Postby Shalex » Wed 29 Dec 2010 08:16

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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleCursor.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).
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Using DbCommand to call a stored proc with cursor output

Postby wkchen » Wed 29 Dec 2010 15:47

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..
wkchen
 
Posts: 4
Joined: Tue 28 Dec 2010 01:46


Return to dotConnect for Oracle