使用ODP.在不接触PL/SQL代码的情况下，从PL/SQL函数中获取RECORD

Question

标题不言自明:来自c#应用程序，使用ODP。. NET，我试图调用一个PL/SQL函数，返回的不是一个简单的值，而是一个记录。

不幸的是，我没有被授权添加或更改PL/SQL代码，所以尝试将函数包装在返回不同类型的另一个函数中对我来说不是一个选项。

下面是一个简单的例子…

PL/SQL:

CREATE OR REPLACE PACKAGE FOO_PACKAGE AS
    TYPE FOO_RECORD IS RECORD (
        BAR VARCHAR2(50),
        BAZ VARCHAR2(50)
    );
    FUNCTION FOO_FUNCTION RETURN FOO_RECORD;
END;
/
CREATE OR REPLACE PACKAGE BODY FOO_PACKAGE AS
    FUNCTION FOO_FUNCTION RETURN FOO_RECORD AS
        R FOO_RECORD;
    BEGIN
        R.BAR := 'Hello bar!';
        R.BAZ := 'Hello baz!';
        RETURN R;
    END;
END;
/

c#

:

我尝试的第一件事是最直接的方法，但我不知道如何绑定返回参数…

using (var conn = new OracleConnection(connection_string)) {
    conn.Open();
    using (var tran = conn.BeginTransaction()) {
        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = "FOO_PACKAGE.FOO_FUNCTION";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
                null,
                /* What to use here? */,
                ParameterDirection.ReturnValue
            );
            cmd.ExecuteNonQuery();
        }
    }
}

我也尝试过一些"迂回"的方法，但它们都抛出异常:

cmd.CommandText = "SELECT * FROM FOO_PACKAGE.FOO_FUNCTION";
cmd.ExecuteReader(); // ORA-00942: table or view does not exist
cmd.CommandText = "SELECT FOO_PACKAGE.FOO_FUNCTION FROM DUAL";
cmd.ExecuteReader(); // ORA-00902: invalid datatype
cmd.CommandText = "SELECT BAR, BAZ FROM (SELECT FOO_PACKAGE.FOO_FUNCTION FROM DUAL)";
cmd.ExecuteReader(); // ORA-00904: "BAZ": invalid identifier

Answer 1

您需要匿名PL/SQL块将函数结果转换为另一种表示:

declare
  vFooRes FOO_PACKAGE.FOO_RECORD;
  vRes sys_refcursor;
begin
  vFooRes := FOO_PACKAGE.FOO_FUNCTION;
  open vRes for select vFooRes.BAR, vFooRes.BAZ from dual;
  --:result := vRes;
end;

SQLfiddle

并执行它而不是调用存储过程:

cmd.CommandText = "declaren" +
                  "  vFooRes FOO_PACKAGE.FOO_RECORD;n" + 
                  "beginn" + 
                  "  vFooRes := FOO_PACKAGE.FOO_FUNCTION;n" + 
                  "  open :result for select vFooRes.BAR, vFooRes.BAZ from dual;n" + 
                  "end;";
OracleParameter p = cmd.Parameters.Add(
                      "result",
                       OracleDbType.RefCursor,
                       DBNull.Value,
                       ParameterDirection.Output
                    );
cmd.ExecuteNonQuery();

执行cmd后，您将获得result参数中的游标，该参数可用于填充数据集:

var adapter = new OracleDataAdapter(cmd);
var data = new DataSet("FooDataSet");
adapter.Fill(data, "result", (OracleRefCursor)(p.Value));