使用ODP.NET从PL/SQL函数获取记录，而无需触摸PL/SQL代码 [英] Using ODP.NET to get a RECORD from PL/SQL function, without touching PL/SQL code

问题描述

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

The title is pretty-self-explanatory: from a C# application, using ODP.NET, I'm trying to call a PL/SQL function that returns not a simple value, but a record.

不幸的是，我无权添加或更改PL/SQL代码，因此尝试将函数包装在返回不同类型的另一个函数中对我来说不是一种选择.

Unfortunately, I'm not authorized to add or change the PL/SQL code, so attempting to wrap the function in another function that returns a different type is not an option for me.

这是一个简化的示例...

Here is a simplified example...

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#:

我尝试的第一件事是最直接的方法，但是我束手无策如何绑定返回参数...

The first thing I tried was the most direct approach, but I'm at a loss how to bind the return parameter...

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();
        }
    }
}

我也尝试了一些回旋"方法，但是它们都抛出异常:

I have also tried some "roundabout" approaches, but they all throw exceptions:

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

推荐答案

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

You need anonymous PL/SQL block to convert function result to another representation:

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

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

And execute it instead of calling stored procedure:

cmd.CommandText = "declare\n" +
                  "  vFooRes FOO_PACKAGE.FOO_RECORD;\n" + 
                  "begin\n" + 
                  "  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参数中获得光标，该参数可用于填充数据集:

After executing cmd you get cursor in result parameter which can be used to fill dataset:

adapter = New OracleDataAdapter(cmd)
data = New DataSet("FooDataSet")
adapter.Fill(data, "result", (OracleRefCursor)(cmd.Parameters["result"].Value));

这篇关于使用ODP.NET从PL/SQL函数获取记录，而无需触摸PL/SQL代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！