Oracle Data Provider - call ExecuteReader to get OUT parameters

377 Views Asked by At

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
0

There are 0 best solutions below