В Oracle 8 появился новый тип данных - вложенные таблицы.Их можно использовать в обычных SQL запросах, предварительно наполнив их в функции пакета. Это позволяет разгрузить приложение, перекинув часть логики на сервер.

(пример заимствованный) вариант с таблицей из одного поля.

-- определяем новый тип вложенных таблиц
create type Num_Coll is table of number(10);

-- создаём пакет с функцией, возвращающей вложенную таблицу
create or replace package test is
function getColl return Num_Coll;
end test;

create or replace package body test is
myColl Num_Coll;
function getColl return Num_Coll is
begin
  return myColl;          -- если таблицу заполнять тут, то это будет производиться
end;                      -- при каждом вызове функции !!!

begin                     -- инициализация пакета
  myColl := num_coll();
  myColl.extend(100);
  for i in 1..100 loop    -- если определять данные во вложенной таблице
    myColl(i) := i;       -- здесь, то они будут вычисляться только один раз **
  end loop;               -- при первом запуске фунции get_coll() !!!  
end test;

-- Типичное применение
SELECT a.COLUMN_VALUE, s.subacc
  FROM TABLE(cast(test.getColl() AS Num_Coll)) a, subacc s
 WHERE s.id_subacc=a.COLUMN_VALUE AND s.subacc='001180446'

**************************************************************************

А это полный вариант - таблица имеет нужное кол-во полей и есть функция, возвращающая таблицу и процедура, заполняющая таблицу.

CREATE OR REPLACE TYPE t_bal AS OBJECT 
( ID NUMBER(10), NUM_ACC NUMBER(5));

CREATE OR REPLACE TYPE o_bal AS TABLE OF t_bal;

CREATE OR REPLACE PACKAGE tmp AS
  FUNCTION get_acc (db DATE, de DATE) RETURN o_bal;
    PRAGMA RESTRICT_REFERENCES (get_acc, WNDS);  
END;

CREATE OR REPLACE PACKAGE BODY tmp AS
  result o_bal;
  d_bg DATE; --объявлена глобально, так как проверяет на повтор запроса
             -- те если запрос идентичный, то перезаполнять таблицу не надо
PROCEDURE fill_acc (db DATE)  -- процедура заполнения/перезаполнения таблицы
IS                            -- видна только изнутри пакета
CURSOR cur IS 
  SELECT id, num_acc FROM t1 WHERE day=db;
BEGIN
 result:=o_bal(); -- иницииализация таблицы, если уже существует - пересоздаётся
 FOR a IN cur LOOP 
   result.EXTEND;
   result(result.LAST) :=  t_bal(a.id,a.num_acc);
 END LOOP;
END fill_acc;
--функция возвращающая таблицу пользователю, при необходимости вызывает fill_acc
FUNCTION get_acc (db DATE) RETURN o_bal IS
a t_bal; --вспомогательная переменная типа RECORD 
BEGIN 
   BEGIN  -- если таблица пустая, заполним её
     a:=result(1);
   EXCEPTION WHEN OTHERS THEN  
     fill_tmp_acc (db);
     d_bg:=db;
   END;
   -- если даты изменились, перезаписать таблицу
   IF db<>d_bg THEN 
     fill_tmp_acc (db);
     d_bg:=db;
   END IF;
RETURN result; 
END get_acc;
END tmp;

Применение аналогично первому варианту:

SELECT vi.* FROM TABLE (CAST(tmp.get_acc(:db) AS o_bal)) vi;

** Имеет смысл в пакете сделать отдельно процедуру (как во втором примере), которая будет заполнять/перезаполнять вложенную таблицу. Эта процедура вызывается один раз в начале транзакции, а функция get_coll - нужное количество раз за транзакцию. И получать она будет те же самые данные, причём мгновенно.

Во многих случаях это может сильно ускорить выполнение запросов.

Например один раз выбрать нужные данные (напр. подборку счетов, которые выбираются из группы таблиц со сложными условиями) в такую таблицу, а потом применять её в целом ряде последовательных запросов.

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

НО! На такой таблице НЕТ индекса ! То есть в любом запросе она ВСЕГДА будет считываться вся полностью. Это нужно учитывать при работе с ней.

Идеальный вариант применения - возврат данных из процедуры в клиентское приложение.

На эту же тему и о применении pipelined варианта