I am refactoring an existing application to change the Oracle Data Provider from OLE DB to the Managed Oracle Data Provider (ODP).
I need to call the following stored procedure, which has several input parameters, and several OUT parameters which are basically of type TABLE of VARCHAR2
PROCEDURE RetrieveInfo(
p_vParam1 IN Varchar2,
p_vParam2 IN Varchar2,
p_vParam3 IN Varchar2,
p_vParam4 IN Varchar2,
MARK_CODE OUT NOCOPY g_tMarkCd,
EQPUN_NBR OUT NOCOPY g_tEqpunNbr,
CSTMR _ID OUT NOCOPY g_tCstmrId,
CNSGN_CSTMR_ID OUT NOCOPY g_tCstmrId,
SHPR_CSTMR_ID OUT NOCOPY g_tCstmrId);
where TYPE g_tMarkCd IS TABLE OF my_table.my_varchar_column%TYPE
(and the other types are similar).
Unfortunately the procedure does not return a Ref Cursor.
OLE DB:
The existing code sets up a OleDbCommand with CommandType of Text, and CommandText is set to
"{call MY_SCHEMA.RetrieveInfo(?,?,?,?,{resultset 10000,MARK_CODE,EQPUN_NBR,SHPR_CSTMR_ID,CNSGN_CSTMR_ID,CSTMR_ID})}"
Then the proc is called by adding the four input OleDbParameter to the OleDbCommand object (but not adding any OUT parameters), and then calling the OleDbCommand ExecuteReader() method. Everything works with OLE DB. The resulting IDataReader has fields for each of the output parameters.
ODP:
ODP does not support the SQL syntax (so I set the OracleCommand CommandText to the name of the proc and set CommandType to StoredProcedure).
Is there a way to set the C# client code up in a way that I can call the OracleCommand ExecuteReader() method in the same way as with OLE DB?
(If I have to explicitly set the OUT variables as parameters in the command, then the system is going to need a major rewrite, which I would want to avoid if at all possible.)
EDIT: The (to be deprecated) OLEDB code looked like
objCommand = new OleDbCommand();
objCommand.Connection = oledbConnection;
objCommand.CommandText = "{call MY_SCHEMA.RetrieveInfo(?,?,?,?,{resultset 10000,MARK_CODE,EQPUN_NBR,SHPR_CSTMR_ID,CNSGN_CSTMR_ID,CSTMR_ID})}";
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Add(new OracleParameter("", OracleDbType.Varchar2, ParameterDirection.Input, 2000, value));
// repeat for the other input parameters, but not output parameters
IDataReader rdr = objCommand.ExecuteReader();
// the resulting data reader has field count = 5