Oracle собирать функции и типы

Question

У меня проблема с Oracle 10g и использованием COLLECT функция. Я только узнал о его существовании сегодня утром, но у меня есть проблема, которую можно решить, используя ее в сочетании с member of состояние.

Первоначально я написал код, показанный ниже, который вернулся с ошибкой "ORA_00932: несовместимые типы данных: ожидаемый UDT получил -".

with my_tab as (
  select 1 as cola, 1 as colb from dual union all
  select 1 as cola, 2 as colb from dual union all
  select 2 as cola, 3 as colb from dual union all
  select 2 as cola, 4 as colb from dual union all
  select 3 as cola, 3 as colb from dual union all
  select 3 as cola, 4 as colb from dual union all
  select 4 as cola, 1 as colb from dual union all
  select 4 as cola, 2 as colb from dual 
)
select 
  cola, 
  colb_vals
from (
  select 
    cola, 
    collect(colb) as colb_vals
  from my_tab
  group by cola
)
where 2 member of colb_vals

Я нашел это немного странным, поскольку в Oracle 10.2.4.0 кажется, что база данных создаст временный системный сгенерированный пользовательский тип и будет использовать его. Если я сниму условие, (where 2 member of colb_vals) затем код запустится и покажет полученные данные, включая временный UDT (с именем SYSTPblahblahblah==).

После нескольких дополнительных поисков я понял, что могу решить эту проблему, используя CREATE TYPE а затем с помощью CAST функция для изменения типа вложенной таблицы. Который работал.

Это было с помощью CREATE TYPE number_ntt as TABLE OF NUMBER; и замена collect(colb) с cast(collect(colb) as number_ntt)

Затем я попытался использовать вложенный тип таблицы, созданный в пакете, поскольку мне нужно, чтобы этот тип был доступен только для одного конкретного запроса в одной процедуре в одном пакете. И я не мог заставить его работать.

create or replace package mike_temp_pkg as
  type number_ntt is table of number;
end mike_temp_pkg;

И на этот раз замена collect(colb) с cast(collect(colb) as mike_temp_pkg.number_ntt)

Это привело к ORA-00932: неверный тип данных.

Итак, вопрос, который у меня есть, состоит из двух частей:

1. Почему сгенерированный системой пользовательский тип работает для selectно не для member of?

2. Почему тип должен быть типом SQL, а не типом PL/SQL в пакете? Я действительно не определяю типы, которые часто бывают, поэтому на этот вопрос может быть простой ответ.

Answer 1

(1)

Документация к функции COLLECT гласит: "Чтобы получить результаты этой функции, вы должны использовать ее в функции CAST". Я подозреваю, что он просто не предназначен для поддержки каких-либо целей, кроме простого дампа его содержимого, если только вы не приведете его к определенному типу.

(2)

Анализатор SQL не знает и не имеет доступа к типам, определенным в блоках PL/SQL. Даже когда вы выполняете оператор SQL внутри кода PL/SQL, этот оператор по существу передается независимому анализатору (с заменой имен переменных PL/SQL на заполнители переменных связывания).