Oracle 如何在PL/SQL中使用流水线表函数 (实现ODCI表接口)？

我已经创建了一个流水线表函数，类似于众所周知的Anton Scheffer的PIVOT函数，它按预期工作 (在SQL * Plus中)
当我尝试在PL/SQL中使用它来打开游标或批量获取时，它会失败，并出现以下异常:

PL/SQL: ORA-22905: 无法从非嵌套表项访问行

我尝试了Anton的功能和其他一些相同的结果 (即完全相同的错误)。

如果您创建此处描述的NColPipe函数，则可以重现它:https://community.oracle.com/message/1299717#1297717

SQL> select * 从表 (NColPipe.show( 'test'，3) );

测试1测试2测试3
----------
行: 1 1
行: 2 2
行: 3 3

然而，

DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR
SELECT * FROM TABLE( NColPipe.show( 'test', 3 ) );
END;

-- 抛出异常:

PL/SQL: ORA-22905: 无法从非嵌套表项访问行

如果PL/SQL无法处理此类表函数，那么此ODCI表接口的实际用途是什么？

您可以在PL/SQL中使用这些。用动态SQL打开游标:

set serveroutput on
DECLARE 
  c sys_refcursor;
  type rec is record (
    test1 varchar2(100),
    test2 varchar2(100),
    test3 varchar2(100)
  );
  v rec;
BEGIN  
  open c for 
    'SELECT * FROM TABLE( NColPipe.show( ''test'', 3 ) ) ';
  loop
    fetch c into v;
    exit when c%notfound;
    dbms_output.put_line(v.test1 || ' ' || v.test2 || ' ' || v.test3);
  end loop;
  close c;
END; 
/

row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3

但是使用可以返回不同数量的列的表函数在大多数情况下都是相当值得怀疑的。然后，您必须编写动态SQL来处理不同数量的列数，或者构建if/else分支来获取列。

它会破坏您使用绑定变量来确定列数的参数的能力:

create or replace procedure p ( cols varchar2 ) as
  c sys_refcursor;
  type rec3 is record (
    test1 varchar2(100),
    test2 varchar2(100),
    test3 varchar2(100)
  );
  type rec2 is record (
    one varchar2(100),
    two varchar2(100)
  );
  v3 rec3;
  v2 rec2;
begin  
  open c for 
    'select * from table( ncolpipe.show( :cols, 3 ) ) ' 
    using cols;
  loop
    if cols = 'two' then
      fetch c into v2;
      exit when c%notfound;
      dbms_output.put_line(v2.one || ' ' || v2.two);
    elsif cols = 'test' then
      fetch c into v3;
      exit when c%notfound;
      dbms_output.put_line(v3.test1 || ' ' || v3.test2 || ' ' || v3.test3);
    else
      exit;
    end if;
  end loop;
  close c;
end p; 
/

exec p('test');

row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3

exec p('two');

ORA-00932: inconsistent datatypes: expected - got -

因此，您必须手动构建字符串并清理输入以避免SQL注入!

create or replace procedure p ( cols varchar2 ) as
  c sys_refcursor;
  type rec3 is record (
    test1 varchar2(100),
    test2 varchar2(100),
    test3 varchar2(100)
  );
  type rec2 is record (
    one varchar2(100),
    two varchar2(100)
  );
  v3 rec3;
  v2 rec2;
begin  
  open c for 
    'select * from table( ncolpipe.show( ' || dbms_assert.enquote_literal (cols ) || ', 3 ) ) ' ;
  loop
    if cols = 'two' then
      fetch c into v2;
      exit when c%notfound;
      dbms_output.put_line(v2.one || ' ' || v2.two);
    elsif cols = 'test' then
      fetch c into v3;
      exit when c%notfound;
      dbms_output.put_line(v3.test1 || ' ' || v3.test2 || ' ' || v3.test3);
    else
      exit;
    end if;
  end loop;
  close c;
end p; 
/

exec p('test');

row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3

exec p('two');

1 row: 1
2 row: 2
3 row: 3