I am using ODP.NET (migration from a Microsoft provider) and I am stuck in a stored procedure that returns refcursor. I have the following PL / SQL procedure (I modified it a bit to make it more general):
PROCEDURE MyProc(parameter_no1 IN NUMBER, parameter_no2 IN NUMBER, RETCURSOR OUT ret_type) AS BEGIN OPEN RETCURSOR FOR SELECT ad.logo logo FROM tab_a a, tab_h h WHERE a.id IS NOT NULL AND a.h_id = h.id AND a.no1 = parameter_no1 AND a.no2= parameter_no2; END HanteraLogotype;
And then I have the following C # code to call it:
internal void RefCursorDataReader() { OracleCommand cmd = new OracleCommand("ABC$MYPACKAGE.MyProc", new OracleConnection(_constr)); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); cmd.BindByName = true; OracleParameter p = cmd.Parameters.Add("parameter_no1", OracleDbType.Decimal); p.Value = 12345678; p.Direction = ParameterDirection.Input; p = cmd.Parameters.Add("parameter_no2", OracleDbType.Decimal); p.Value = 123456; p.Direction = ParameterDirection.Input; p = cmd.Parameters.Add("RETCURSOR", OracleDbType.RefCursor); p.Direction = ParameterDirection.Output; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { System.Diagnostics.Debug.WriteLine(reader[0].GetType().ToString()); } cmd.Connection.Close(); }
And when I run this, I get this exception:
ORA-03106: fatal communication protocol error with two tasks
I tried many different options for the parameters, their type, order, etc., but nothing helps. This is reader.Read() , which throws an exception. I am very grateful for the help in this!
Added: ret_type parameter is defined as:
TYPE ret_type IS REF CURSOR;
source share