ORA-20000

Очень редко пользуюсь этими функциями, так как область применения их достаточно узкая. Но появился случай, где эти функции можно применить. Особенность этих функций является то, что они как бы создают свое "подокно", внутри "окна" данных, которое определено для этих функций. Давай-те рассмотри на примере. В примере смотрим на табличку EMP в схему SCOTT. Задача такая: в каждом отделе нужно найти сотрудника с максимальным окладом (поле SAL имеет максимальное значение), который дольше всех работает на предприятии (поле HIREDATE имеет минимальное значение).

select emp.deptno, emp.ename, emp.sal, emp.hiredate,
      first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
      min(emp.hiredate)
        keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd1,
      count(1)
        keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd2
from   emp;

C помощью partition by emp.deptno создаем окно по подразделениям (окно, когда когда подразделения равен 20, выделено серым цветом). Внутри этого окна функция dense_rank last order by emp.sal формирует ранк по величине оклада и создает свое “подокно” (оно выделено жирным шрифтом). Уже внутри этого окна применяется функция min(emp.hiredate). Тот же результат можно получить с помощью функции first_value.

Почитать про эти функции можно здесь FIRST и LAST.

P.S. Редкое использование этих функций привело к тому, что Oracle не исправил такой интересный баг. Вместо слова order можно написать любую ерунду.

select emp.deptno, emp.ename, emp.sal, emp.hiredate,
      first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
      min(emp.hiredate)
        keep (dense_rank last TRAMPARARAM by emp.sal) over (partition by emp.deptno) as hd1,
      count(1)
        keep (dense_rank last QQ by emp.sal) over (partition by emp.deptno) as hd2
from   emp;

Для отладки и логирования я достаточно часто применяю пакет dbms_pipe, этот способ имеет свои преимущества и недостатки. Обычно для отладки применяется пошаговое выполнение, это удобно, но отладка должна быть разрешена, пакет должен быть скомпилирован с флажком debug, и код можно выполнить в своей сессии.

Для отладки можно использовать dbms_output, но результат виден только после выполнения всего кода, и также должна быть возможность выполнить код из своей сессии.

Также можно использовать любую таблички и в автономной транзакции писать туда служебную отладочную информацию – чаще всего так и поступаем. Единственное, чем мне не нравится этот способ – надо постоянно селектить табличку на предмет появления там новых строк.

Поэтому я достаточно часто использую передачу данных через pipe. Данные передаются не транзакционно, мы их получаем в реальном времени. Но есть и недостатки: dbms_pipe нельзя использовать в RAC, и возможны случаи, когда буфер может быть переполнен.

Сначала надо дать гранты на выполнения пакета dbms_pipe.

grant execute on dbms_pipe to myuser;

Создадим 2 метода для отсылки и приема сообщений.

create or replace package custom_pipe is

 -- Author  : USER
 -- Purpose : работаем с pipe
 -- отправить сообщение через pipe
 procedure SendMessage( psPipe    in varchar2
                       ,psMessage in varchar2
                      );

 -- получить сообщение через pipe
 function ReceiveMessage( psPipe    in  varchar2
                         ,psKey     in  varchar2
                         ,psMessage out varchar2
                        ) return integer;

end custom_pipe;

/



create or replace package body custom_pipe is

 procedure SendMessage( psPipe    in varchar2
                       ,psMessage in varchar2
                      )
 is
   i integer;
 begin
   dbms_pipe.pack_message(psMessage);
   i := dbms_pipe.send_message(psPipe, 30);
   if i != 0 then
     raise_application_error(-20101, 'Ошибка при посылке сообщения клиенту!');
   end if;
 end SendMessage;

 function ReceiveMessage( psPipe    in  varchar2
                         ,psKey     in  varchar2
                         ,psMessage out varchar2
                        ) return integer
 is
   call_stat   integer := 0;
   next_item   integer := 0;
   temp_varchar2  VARCHAR2(2000);
   temp_date      DATE;
   temp_number    NUMBER;
   temp_rowid     ROWID;
   temp_raw       RAW(2000);
   vnResult integer := 0;
 begin
   psMessage := '';
   call_stat := DBMS_PIPE.RECEIVE_MESSAGE(psPipe, 0);
   if (call_stat = 0) then
     next_item := DBMS_PIPE.NEXT_ITEM_TYPE;
     while next_item > 0 loop
       if next_item = 9 then
         DBMS_PIPE.UNPACK_MESSAGE(temp_varchar2);
       elsif next_item = 6 then
         DBMS_PIPE.UNPACK_MESSAGE(temp_number);
         temp_varchar2 := TO_CHAR(temp_number);
       elsif next_item = 11 then
         DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);
         temp_varchar2 := ROWIDTOCHAR(temp_rowid);
       elsif next_item = 12 then
         DBMS_PIPE.UNPACK_MESSAGE(temp_date);
         temp_varchar2 := TO_CHAR(temp_date,'DD.MM.YYYY HH24:MI:SS');
       elsif next_item = 23 then
         DBMS_PIPE.UNPACK_MESSAGE_RAW(temp_raw);
         temp_varchar2 := RAWTOHEX(temp_raw);
       else
         temp_varchar2 := '';
--          temp_varchar2 := 'Не верный тип элемента: '||TO_CHAR(next_item); 
       end if;
       psMessage := substr(psMessage || temp_varchar2, 1, 4000);
       next_item := DBMS_PIPE.NEXT_ITEM_TYPE;
       if (upper(temp_varchar2) = upper(psKey) ) then
         vnResult := 1;
         exit;
       end if;
     end loop;
   elsif (call_stat = 1) then
     null;
   elsif (call_stat in (2,3) ) then
     raise_application_error(-20100, 'Ошибка при получении сообщения!');
   end if;
   return(vnResult);
 end ReceiveMessage;

end custom_pipe;

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

SQL> declare
 vspipe varchar2(30) := 'TEST_PIPE';
begin
 for i in 1 .. 20
 loop
   custom_pipe.sendmessage(pspipe => vspipe,
                           psmessage => 'Текущее время: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));
   dbms_output.put_line('Текущее время: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));
   -- задержка 4 секунды                       
   dbms_lock.sleep(3);
 end loop;   
end;
/

Для получения сообщении можно воспользоваться функцией ReceiveMessage, я так и делаю, когда надо передать сообщение в другую сессию. Но для отладки я пользуюсь возможностью, которая предоставляется средой разработки “PL/SQL Developer” от Allround Automations. Выбираем в меню “Tools->Event Monitor…” и настраиваем на прослушивание своего pipe.