APPS-ORACLE.RU

with t as
(
  select 'a' a, 2 n from dual union all
  select 'b' a, 3 n from dual union all
  select 'c' a, 5 n from dual union all
  select 'd' a, 1 n from dual union all
  select 'e' a, 0 n from dual
)
select *
 from t,
      table(select collect(level) from dual connect by level<=t.n);

A          N COLUMN_VALUE
- ---------- ------------
a          2            1
a          2            2
b          3            1
b          3            2
b          3            3
c          5            1
c          5            2
c          5            3
c          5            4
c          5            5
d          1            1
e          0            1

12 rows selected

alter session enable  commit in procedure;
alter session disable commit in procedure;

-- создаем глобальный тип данных, локальный нельзя использовать
create or replace type xx_test_type is object ( id number, descr varchar2(20));
create or replace type xx_test_tab is table of xx_test_type;

declare
 vcollect xx_test_tab := xx_test_tab(); -- инициализация
begin
 -- заполняем данными
 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(1,'test1');

 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(2,'test2');
 -- запрос из коллекции
 for i in (select * from Table(vcollect) order by id )
 loop
   dbms_output.put_line(i.descr);
 end loop;
end;

with t as (
select 1 as id, 'a' gr from dual
union all
select 2 as id, 'a' gr from dual
union all
select 4 as id, 'b' gr from dual
)
select
 rtrim(to_char(sys_xmlagg(xmlelement(id,id ||',')).extract('/ROWSET/ID/text()').getclobval()),',') TEXT
from t
group by gr;

TEXT
-------
1,2
4

select *
from gme_batch_header          gbh,
     gme_material_details      gmd,
     mtl_material_transactions mmt
where 1=1
      and gbh.batch_type = 0
      -- gmd
      and gmd.batch_id = gbh.batch_id
      -- mmt
      and mmt.transaction_source_id = gmd.batch_id
      and mmt.trx_source_line_id    = gmd.material_detail_id
      -- for test
      --and trunc(gbh.plan_start_date) >
      --and gbh.batch_no =
      --and mmt.attribute_category =
      and rownum<100;

select 'drop '||object_type||' '||object_name
       ||decode(object_type,'TABLE',' cascade constraints;','TYPE',' force;',';')
from user_objects
where object_type in ('TABLE','VIEW','SEQUENCE','PACKAGE','PROCEDURE','FUNCTION','TYPE')
order by object_type;

  -- удалить все объекты из корзины текущего пользователя
  purge recyclebin;

  -- Отключить корзину для сессии
  ALTER SESSION SET RECYCLEBIN=OFF;
  -- Отключить корзину
  ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;
  -- Включить корзину для сессии
  ALTER SESSION SET RECYCLEBIN=ON;
  -- Включить корзину
  ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;