Oracle 12在SQL中的本地集合类型上是否有问题? [英] Does Oracle 12 have problems with local collection types in SQL?

问题描述

长话短说，我建议讨论您在下面看到的代码.

To make a long story short I propose to discuss the code you see below.

运行时:

• Oracle 11编译器引发

• Oracle 11 compiler raises

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

"PLS-00306: wrong number or types of arguments tips in call to 'PIPE_TABLE'"

"PLS-00642:SQL语句中不允许使用本地集合类型"

"PLS-00642: Local Collection Types Not Allowed in SQL Statement"

Oracle 12编译以下软件包时没有出现这样的警告，但我们在运行时感到惊讶

Oracle 12 compiles the following package with no such warnings, but we have a surprise in runtime

按原样执行匿名块时-一切都很好 (我们可以在pipe_table函数中传递一些行-不会影响)

when executing the anonymous block as is - everything is fine (we may pipe some rows in the pipe_table function - it doesn't affect)

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

now let's uncomment the line with hello; or put there a call to any procedure, and run the changed anonumous block again we get "ORA-22163: left hand and right hand side collections are not of same type"

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

And the question is: Does Oracle 12 allow local collection types in SQL? If yes then what's wrong with the code of 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;
/

推荐答案

在进一步的实验中，我们发现问题甚至比设想的要深.

In further experiments we found out that problems are even deeper than it's been assumed.

例如，在包buggy_report中使用了各种元素，我们可以得到ORA-03113: end-of-file on communication channel 在运行脚本时(在问题中).可以通过将t_id_table的类型更改为VARRAY或TABLE .. INDEX BY ..来完成.有很多方法和变体将我们引向不同的例外，而本文不涉及这些例外.

For example, varying elements used in the package buggy_report we can get an ORA-03113: end-of-file on communication channel when running the script (in the question). It can be done with changing the type of t_id_table to VARRAY or TABLE .. INDEX BY ... There are a lot of ways and variations leading us to different exceptions, which are off topic to this post.

另一件事是buggy_report软件包规范的编译时间最多可能需要25秒， 通常情况下，大约需要0.05秒.我可以肯定地说，这取决于pipe_table函数声明中TYPE t_id_table参数的存在，在40％的安装案例中都会发生长时间编译".因此，看来local collection types in SQL的问题潜在地出现在编译过程中.

The one more interesting thing is that compilation time of buggy_report package specification can take up to 25 seconds, when normally it takes about 0.05 seconds. I can definitely say that it depends on presence of TYPE t_id_table parameter in the pipe_table function declaration, and "long time compilation" happen in 40% of installation cases. So it seems that the problem with local collection types in SQL latently appear during the compilation.

因此，我们看到Oracle 12.1.0.2在实现在SQL中使用本地集合类型方面显然存在一个错误.

So we see that Oracle 12.1.0.2 obviously have a bug in realization of using local collection types in SQL.

以下是获取ORA-22163和ORA-03113的最小示例.在那里，我们假设与问题中的buggy_report软件包相同.

The minimal examples to get ORA-22163 and ORA-03113 are following. There we assume the same buggy_report package as in the question.

-- 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 12在SQL中的本地集合类型上是否有问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！