Получить 'PL\SQL Table', возвращенную из функции с OracleCommand в C#

Question

Я новичок в использовании OracleCommand в C#, чтобы вернуть результаты процедур / функций Oracle, я смог заставить работать большинство моих выполненных хранимых процедур, но мне нужно посоветовать следующее.

Ниже приведена функция, которая возвращает таблицу, которую я создал на основе типа записи.

create or replace
function return_table return t_nested_table AS

v_ret   t_nested_table;

begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;

Тип был создан следующим образом

create or replace
type t_col as object (
  i number,
  n varchar2(30)
);

Таблица из записи t_col

create or replace
type t_nested_table as table of t_col;

Теперь, когда я хотел выполнить функцию в C#, я попробовал следующее, но понял, что OracleDbType не имеет перечисления для PL\SQL Table.

using (OracleConnection conn = new OracleConnection(connection))
            using (OracleCommand cmd = new OracleCommand())
            {
    cmd.Connection = conn;
    cmd.BindByName = true;
    cmd.CommandText = "return_table";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("\"v_ret\"", OracleDbType.Object, DBNull.Value, ParameterDirection.ReturnValue);
    conn.Open();
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Но это просто выдает ошибку:

Invalid parameter binding
Parameter name: "v_ret"

Я пробовал другие способы, но пока ничего не получалось.

Я надеюсь, что есть способ исправить это в моем коде C# только потому, что есть множество существующих функций, которые я не смогу редактировать.

Я также посмотрел на подобные вопросы, но не смог получить от них точный ответ.

Answer 1

Этот код C# может быть полезен для вас, он работал для меня с вашим собственным определенным типом Oracle:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.Connection = conn;
    cmd.CommandText = "select * from table(return_table())";
    cmd.CommandType = CommandType.Text;
    conn.Open();
    OracleDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetOracleDecimal(0));
        Console.WriteLine(rdr.GetOracleString(1));
    }
    conn.Close();
}

Answer 2

Я не думаю, что вы можете вернуть пользовательский объект в Oracle Data Provider. Но вы можете вернуть RefCursor:

create or replace
function return_table return SYS_REFCURSOR AS

v_ret   t_nested_table;
res SYS_REFCURSOR;

begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  OPEN res for SELECT * from TABLE(v_ret);
  RETURN res;
end return_table;

Тогда в C# это будет так:

cmd.Parameters.Add("v_ret", OracleDbType.RefCursor, ParameterDirection.ReturnValue);