The DataDirect provider for Oracle调用存储过程使用Cursor类型参数

When executing a stored procedure that returns a REF CURSOR parameter, the Microsoft Oracle provider requires that the REF CURSOR be bound as an OracleParameter object of type OracleDbType.Cursor.

The DataDirect provider for Oracle does not require these parameters to be bound at all. The DataDirect provider returns the results from REF CURSORs as a result set from the stored procedure, either through an OracleDataReader returned from OracleCommand.ExecuteReader or through an OracleDataAdapter.

This provides for easier coding as you do not need to do anything for REF CURSORs to be returned to the application. It also provides additional interoperability across providers as result sets from stored procedures are returned in the same manner with all of the DataDirect providers.

If multiple REF CURSORs are returned, then the DataDirect Oracle provider returns them as multiple result sets in the order of the REF CURSOR parameters in the Create Procedure statement. Therefore, you should simply remove the bindings for any parameters of this type.

The following code example creates a stored procedure and REF CURSOR on the Oracle server:

create or replace package EMP_PACKAGE AS
TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;
END EMP_PACKAGE;
create or replace procedure empcursor (emp_cv IN OUT
EMP_PACKAGE.EmpCurType,
salary IN NUMBER)
as
BEGIN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > salary;

END;

Thus a statement in the Microsoft Oracle provider would need to be changed from:

OracleCommand DBCmd = new OracleCommand("empcursor", DBConn);
DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.Parameters.Add("emp_cv", OracleType.Cursor).Direction

= ParameterDirection.Output;
DBCmd.Parameters.Add ("salary", OracleType.Number,15,"").Value = 30000;
OracleDataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();

to:

OracleCommand DBCmd = new OracleCommand("empcursor", DBConn);

DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.Parameters.Add ("anyname", OracleDbType.Number,15,"").Value = 30000;
OracleDataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();

Note that in the above example, only the second parameter in the stored procedure ("salary") is bound to the DataDirect Oracle provider.