Имеет ли Oracle 12 проблемы с локальными типами коллекций в SQL?

Короче говоря, я предлагаю обсудить код, который вы видите ниже.

При запуске:

  • Компилятор Oracle 11 повышает

    "PLS-00306: неправильное количество или типы аргументов при вызове в 'PIPE_TABLE'"

    "PLS-00642: Типы локальных коллекций, не разрешенные в SQL-заявлении"

  • 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;
/

Ответы

Ответ 1

В дальнейших экспериментах мы выяснили, что проблемы еще глубже, чем предполагалось.

Например, различные элементы, используемые в пакете buggy_report, мы можем получить ORA-03113: end-of-file on communication channel при запуске script (в вопросе). Это можно сделать с изменением типа t_id_table на VARRAY или TABLE .. INDEX BY ... Есть много способов и вариантов, приводящих нас к различным исключениям, которые не относятся к этой теме.

Еще одна интересная вещь заключается в том, что время компоновки спецификации пакета buggy_report может занимать до 25 секунд, когда обычно это занимает около 0,05 секунды. Я могу определенно сказать, что это зависит от наличия параметра TYPE t_id_table в объявлении функции pipe_table, а "длинная компиляция" происходит в 40% случаев установки. Таким образом, кажется, что проблема с local collection types in SQL скрытно появляется во время компиляции.

Итак, мы видим, что Oracle 12.1.0.2, очевидно, имеет ошибку в реализации использования локальных типов коллекций в SQL.

Ниже приведены минимальные примеры для получения 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;
/

Ответ 2

Да, в Oracle 12c вам разрешено использовать локальные типы коллекций в SQL.

Документация Руководство по новым функциям базы данных говорит:

Типы данных, специфичные для PL/SQL, разрешенные на уровне интерфейса PL/SQL-SQL

Теперь оператор таблицы можно использовать в программе PL/SQL в коллекции, тип данных которой объявлен в PL/SQL. Это также позволяет типу данных быть ассоциативным массивом PL/SQL. (В предыдущих выпусках тип данных коллекции должен был быть объявлен на уровне схемы.)

Однако я не знаю, почему ваш код не работает, возможно, эта новая функция по-прежнему является ошибкой.

Ответ 3

Я попробовал ваш пример. Фокус на том, как Oracle 12c может использовать коллекции PL/SQL в операторах SQL, заключается в том, что Oracle создает суррогатные типы объектов схемы с совместимыми атрибутами типа SQL и использует эти суррогатные типы в запросе. Ваше дело выглядит как ошибка. Я проследил выполнение, и суррогатные типы создаются только один раз, если не существует. Таким образом, эффективный тип не изменяется и не перекомпилируется (не знаю, выполняется ли неявная перекомпиляция с использованием инструкции ALTER) во время выполнения конвейерной функции. И проблема возникает, если вы используете параметр p в функции pipe_table. Если вы не вызываете l_table := p;, код выполняется успешно даже при включенном вызове метода.