Oracle 12在SQL中的本地集合类型有问题吗?

长话短说，我建议讨论下面的代码。

运行时:

Oracle 11编译引发

"PLS-00306:在调用'PIPE_TABLE'时提示错误的参数数量或类型"

"PLS-00642: Local Collection Types Not Allowed in SQL Statement"
Oracle 12编译以下包时没有这样的警告，但是我们在运行时有一个惊喜

按原样执行匿名块-一切正常(我们可能会在pipe_table函数中管道一些行-这不会影响)

现在让我们用hello;取消注释行，或者在那里调用任何过程，并再次运行更改后的匿名块我们得到"ORA-22163:左侧和右侧集合不是同一类型"

问题是:Oracle 12允许SQL中的本地集合类型吗?如果是，那么PACKAGE buggy_report的代码有什么问题?

CREATE OR REPLACE PACKAGE buggy_report IS
  SUBTYPE t_id IS NUMBER(10);
  TYPE t_id_table IS TABLE OF t_id;
  TYPE t_info_rec IS RECORD ( first NUMBER );
  TYPE t_info_table IS TABLE OF t_info_rec;
  TYPE t_info_cur IS REF CURSOR RETURN t_info_rec;
  FUNCTION pipe_table(p t_id_table) RETURN t_info_table PIPELINED;
  FUNCTION get_cursor RETURN t_info_cur;
END buggy_report;
/
CREATE OR REPLACE PACKAGE BODY buggy_report IS
  FUNCTION pipe_table(p t_id_table) RETURN t_info_table PIPELINED IS
    l_table t_id_table;
    BEGIN
      l_table := p;
    END;
  FUNCTION get_cursor RETURN t_info_cur IS
    l_table  t_id_table;
    l_result t_info_cur;
    BEGIN
      OPEN l_result FOR SELECT * FROM TABLE (buggy_report.pipe_table(l_table));
      RETURN l_result;
    END;
END;
/
DECLARE
  l_cur buggy_report.t_info_cur;
  l_rec l_cur%ROWTYPE;
  PROCEDURE hello IS BEGIN NULL; END;
BEGIN
  l_cur := buggy_report.get_cursor();
  -- hello;
  LOOP
    FETCH l_cur INTO l_rec;
    EXIT WHEN l_cur%NOTFOUND;
  END LOOP;
  CLOSE l_cur;
  dbms_output.put_line('success');
END;
/

在进一步的实验中，我们发现问题比想象的还要严重。

例如，在包buggy_report中使用不同的元素，我们可以得到ORA-03113: end-of-file on communication channel运行脚本时(在问题中)。这可以通过将t_id_table的类型更改为VARRAY或TABLE .. INDEX BY ..来完成。有很多方法和变化导致我们产生不同的异常，这与本文的主题无关。

一个更有趣的事情是buggy_report包规范的编译时间可以长达25秒，正常情况下大约需要0.05秒。我可以肯定地说，这取决于pipe_table函数声明中TYPE t_id_table参数的存在，并且"长时间编译"发生在40%的安装案例中。因此，local collection types in SQL的问题似乎在编译过程中潜伏出现。

所以我们看到Oracle 12.1.0.2在SQL中使用本地集合类型的实现上明显有一个bug。

获取ORA-22163和ORA-03113的最小示例如下。在这里，我们假设与问题中相同的buggy_report包。

-- produces 'ORA-03113: end-of-file on communication channel'
DECLARE   
  l_cur buggy_report.t_info_cur;
  FUNCTION get_it RETURN buggy_report.t_info_cur IS BEGIN RETURN buggy_report.get_cursor(); END;    
BEGIN
   l_cur := get_it();
   dbms_output.put_line('');
END;
/
-- produces 'ORA-22163: left hand and right hand side collections are not of same type'
DECLARE  
  l_cur buggy_report.t_info_cur;
  PROCEDURE hello IS BEGIN NULL; END;
BEGIN
  l_cur := buggy_report.get_cursor;
  -- comment `hello` and exception disappears
  hello;
  CLOSE l_cur;
END;
/

允许在Oracle 12c中使用本地集合类型

文档数据库新特性指南说:

PL/SQL-to-SQL接口允许的PL/sql特定数据类型

表操作符现在可以在PL/SQL程序中对数据类型声明为PL/SQL的集合使用。这也允许数据类型为PL/SQL关联数组。(在以前的版本中，集合的数据类型必须在模式级别声明。)

然而，我不知道为什么你的代码不工作，也许这个新功能仍然有一个bug。

我对您的示例进行了修改。Oracle 12c在SQL语句中使用PL/SQL集合的诀窍是，Oracle创建具有兼容SQL类型属性的代理模式对象类型，并在查询中使用这些代理类型。你的案子看起来像个漏洞。我跟踪了执行过程，如果不存在代理类型，则只创建一次。因此，在执行管道函数期间，有效类型不会改变也不会重新编译(不知道是否使用ALTER语句进行了隐式重新编译)。只有在pipe_table函数中使用p参数时，才会出现这个问题。如果不调用l_table := p;，即使启用了方法调用，代码也会成功执行。

相关内容

最新更新

热门标签：