Переменные PL/SQL пакета: неочевидность

2 часа просидел над отладкой простейшего кода, не замечая подводного камня.
Допустим:

create or replace package VARTEST is
  BANNER varchar2(255);
  procedure test (p_cur out sys_refcursor);
end VARTEST;

create or replace package body VARTEST is
  procedure test (p_cur out sys_refcursor)
  is
  begin
    open p_cur for
       select count(*) from v$version v where v.BANNER = BANNER;
  end test;
end VARTEST;

Вызовем-ка функцию

begin
  VARTEST.BANNER := 'some awsome text';
  vartest.test(p_cur => :p_cur);
end;

--------------
count(*) 
5

Результат казался неожиданным, пока (случайно!) не выполнил запрос

select * from v$version v where v.BANNER = BANNER;

Запрос, на удивление, выполнился и честно вернул всё содержимое вьюхи. Т.е. BANNER - это в данном случае имя поля, а не переменная пакета.
Итого: имена переменных пакета лучше начинать с префикса, либо использовать явное указание принадлежности переменной пакету.

PL/SQL: MODEL (пример)

У движка отчетов JasperReports есть особенность: область детализации строго горизонтальна и занимает всю ширину страницы. Если нужно напечатать несколько маленьких бланков, которые умещаются по 2 штуки в ряд, то придется печатать всё-таки по одному.

Выход - подать на вход выборку, уже разбитую на 2 колонки.

Для простейших случаев наподобие представленного можно использовать MODEL - мощную инструкцию PL/SQL, позволяющую с выборкой производить операции с нетривиальной логикой. Применяется крайне редко в особо тяжелых случаях.

select *
  from (select id1,
               name1,
               lead(id2, 1) over(order by null) id2,
               lead(name2, 1) over(order by null) name2
          from (select id1, name1, id2, name2
                  from (select 1 r, 10 id, 'a' name
                          from dual
                        union
                        select 2 r, 15 id, 'b' name
                          from dual
                        union
                        select 3 r, 20 id, 'c' name
                          from dual
                        union
                        select 4 r, 25 id, 'd' name
                          from dual
                        union
                        select 5 r, 30 id, 'e' name
                          from dual) model 
                          dimension by(r, id, name) 
                          measures(0 id1, 
                          cast(null as varchar2(255)) name1, 0 id2, 
                          cast(null as varchar2(255)) name2) 
                          rules(id1 [ mod(r, 2) != 0, id, name ] = cv(id), 
                          id2 [ mod(r, 2) = 0, id, name ] = cv(id), 
                          name1 [ mod(r, 2) != 0, id, name ] = cv(name), 
                          name2 [ mod(r, 2) = 0, id, name ] = cv(name))))
 where id1 != 0

В приведенном примере сначала мы разбиваем выборку по колонкам в зависимости от четности/нечетности порядкового номера, затем на уровне выше с помощью функции LEAD склеиваем строки, чтобы убрать шахматный порядок в данных. Наконец, ограничением where id1 != 0 отсекаем пустые строки, образовавшиеся после склейки.

PL/SQL: Месяцы в родительном падеже

Не хотелось плодить кучу условий и использовать какие-либо кириллические константы, поэтому вот:

  function month_rodp(p_date in date) return varchar2
  is
           mon varchar2(20);
  begin
     mon := trim(lower(to_char(p_date, 'MONTH')));
     if (to_char(p_date, 'MM')) in (3,8) then
        mon := mon || chr(1072 using NCHAR_CS);
     else
        mon := substr(mon, 1,  length(mon)-1) || chr(1103 using NCHAR_CS);
     end if;
     return mon;
  end month_rodp;

Реализация очереди Oracle AQ

Oracle Advanced Queuing (AQ) - механизм организации очередей. В простейшем случае создается тип, на основе типа специальным API создается таблица очереди. Очередь действует по принципу FIFO. Приведенный ниже листинг создает демонстрирует вышесказанное.

Особенность функции PIVOT

Для облегчения запросов предварительно выполним

alter session set NLS_DATE_LANGUAGE = RUSSIAN

Положим, в текущем месяце мы сделали 5 и 8 скворечников, а в следующем сделаем -5 и -8.

select pk,
         initcap(to_char(pivot_key, 'MONTH')) pivot_key,
         sum(value) value
    from (select 1 pk, sysdate pivot_key, 5 value from dual
          union
          select 1, sysdate, 8 from dual
          union
          select 1, sysdate + 31, -5 from dual
          union
          select 1, sysdate + 31, -8 from dual)
   group by pk, initcap(to_char(pivot_key, 'MONTH'))

Развернем выборку, задав месяцы как колонки:

with a as
 (select pk,
         initcap(to_char(pivot_key, 'MONTH')) pivot_key,
         sum(value) value
    from (select 1 pk, sysdate pivot_key, 5 value from dual
          union
          select 1, sysdate, 8 from dual
          union
          select 1, sysdate + 31, -5 from dual
          union
          select 1, sysdate + 31, -8 from dual)
   group by pk, initcap(to_char(pivot_key, 'MONTH'))
 )
select pk, "'Январь'_MON" Jan, 
           "'Февраль'_MON" Feb
  from (select *
          from a pivot(sum(value) mon 
          FOR pivot_key IN('Январь',
                           'Февраль')
       )
);

PK        JAN        FEB
---------- ---------- ----------
         1   

Как видим, результат неверный. Обозначим месяц по-другому.

with a as
 (select pk,
         to_char(pivot_key, 'MM') pivot_key,
         sum(value) value
    from (select 1 pk, sysdate pivot_key, 5 value from dual
          union
          select 1, sysdate, 8 from dual
          union
          select 1, sysdate + 31, -5 from dual
          union
          select 1, sysdate + 31, -8 from dual)
   group by pk, to_char(pivot_key, 'MM')
 )
select pk, "'01'_MON" Jan, 
           "'02'_MON" Feb
  from (select *
          from a pivot(sum(value) mon 
          FOR pivot_key IN('01',
                           '02')
       )
);

PK        JAN        FEB
---------- ---------- ----------
         1         13        -13 

Непонятно, в чем причина ошибки, но точно не в кириллических названиях.
Вывод: для опорного поля в функции PIVOT использовать максимально простые, желательно циферные, псевдонимы.

Пример типа с коллекциями

Например, нужно реализовать настройку в виде нескольких списков (списки товаров и торговых точек, где применяется акция). ID товаров и точек будем хранить в таблице со структурой (Тип сущности {Товар, Точка}, ID). С помощью приведенной ниже методики мы можем хранить оба списка в одном объекте и легко к ним обращаться как к коллекциям. Алгоритм сбора данных инкапсулируется в самом объекте, а выполняется в конструкторе, что позволяет выполнить сбор данных одной строкой кода.
Итак:
Тип строки

create or replace type MY_COLLECTION_ROW is
       object(
          value varchar2(150)
       );

Табличный тип

create or replace type MY_COLLECTION is
       table of MY_COLLECTION_ROW 

Новый тип, содержащий объекты типа MY_COLLECTION

create or replace type MY_COLLECTION_TYPE as object (

       GOODS_LIST                MY_COLLECTION,
       SHOPS_LIST                MY_COLLECTION,

       constructor function MY_COLLECTION_TYPE return self as result,

       member      procedure init_SHOPS_LIST_collection(p_list in out MY_COLLECTION),
       member      procedure init_GOODS_LIST_collection(p_list in out MY_COLLECTION)
)

create or replace type body MY_COLLECTION_TYPE is

       constructor function MY_COLLECTION_TYPE return self as result
       is
       begin
          GOODS_LIST                := new MY_COLLECTION(null); 
          SHOPS_LIST                := new MY_COLLECTION(null);

          init_GOODS_LIST_collection(p_list => GOODS_LIST);
          init_SHOPS_LIST_collection(p_list => SHOPS_LIST);
         return;
       end MY_COLLECTION_TYPE;
       --
       member      procedure init_GOODS_LIST_collection(p_list in out MY_COLLECTION)
       is
         l_line varchar2(150);
         cursor cur is
                    select rownum
                           from dual
                           connect by rownum < 10;
       begin
         open cur;
         loop
           fetch cur into l_line;
           exit when cur%NOTFOUND;
                 p_list.extend;
                 p_list(p_list.last) := MY_COLLECTION_ROW(l_line);
           end loop;
           close cur;
       end init_GOODS_LIST_collection;
       --
       member      procedure init_SHOPS_LIST_collection(p_list in out MY_COLLECTION)
       is
         l_line varchar2(150);
         cursor cur is
                    select rownum
                           from dual
                           connect by rownum < 10;
       begin
         open cur;
         loop
           fetch cur into l_line;
           exit when cur%NOTFOUND;
                 p_list.extend;
                 p_list(p_list.last) := MY_COLLECTION_ROW(l_line);
           end loop;
           close cur;
       end init_SHOPS_LIST_collection;
end;

Обращаемся к данным

declare 
  l_options         MY_COLLECTION_TYPE;
  cursor cur(obj MY_COLLECTION) is select * from table(obj);
  l_tmp_coll        varchar2(150);           
begin
  l_options := new MY_COLLECTION_TYPE();
  open cur(obj => l_options.GOODS_LIST);
  loop
        fetch cur into l_tmp_coll;
        exit when cur%NOTFOUND;
           dbms_output.put_line(l_tmp_coll);
  end loop;
  close cur;
end;

Ошибка ORA-08103 и партиционирование

Причины появления ошибки ORA-08103: object no longer exists при работе с партиционированными таблицами.

ООП на PL/SQL: введение

В который раз не знаю, что писать в заголовок поста. Ну, давай начнем так: "реализация ООП впервые появилась в РСУБД Oracle 9g".

Мелкий траблшутинг: задвоение в SYS_REFCURSOR

Бывает, звезды сложатся так, что последняя запись задваивается.

PL/SQL Entity Object

Создание Enitiy Object c переопределенной логикой операций DML. Подход обычно применяется в случае наличия API для таблицы.

WF: Бизнес-событие и подписка с пользовательской логикой на PL/SQL

Выполнение PL/SQL кода, инициируемого бизнес-событием.

WF: Бизнес-событие и подписка на основе потока

Выполнения workflow-потока, инициируемого бизнес-событием

Специфика индексов по функции

Индексы по функции имеют некоторые зависимости

Материализованное представление

Использование материализованных представлений (materialized view)в Oracle10g позволяет одновременно управлять сводной информацией в хранилище данных и ускорять выполнение запросов.

Размер начального экстента

Методика расчета

Передача XML>32k через ADO в Oracle

При работе с XML+ADO+Oracle всё прекрасно, пока размер потока с XML не превышает 32k

Полный список значений NLS_CHARSET_NAME

Текущий набор можно узнать, выполнив select VALUE$ from
sys.props$where NAME='NLS_CHARACTERSET'

Аналог continue в циклах

Оператор continue в Oracle 11g отсутствует. Приведены 4 приема с аналогичным функционалом.

Формирование XML c помощью DBMS_XMLDOM

Создание объекта XML с помощью пакета DBMS_XMLDOM с передачей в виде CLOB

Выборка привилегий

Используются системные представления sys.dba_role_privs, sys.dba_tab_privs, sys.dba_sys_privs