воскресенье, 5 декабря 2010 г.

Customizing Locale Data NLS_SORT=UKRUMIX

    Недавно получилась забавная ситуация: во время сортировки буква Э оказалась между К и М вместо того чтобы быть на своем месте: перед Ю и Я. Оказалось ничего удивительного, на клиентских машинах локализация Windows установлена как UKRAINIAN и при инсталляции Oracle Client для программ которые работают с базой данных Oracle устанавливается украинская локализация(в реестре прописывается ключ NLS_LANG = UKRAINIAN_UKRAINE.CL8MSWIN1251), в которой буквы Э не существует, вот она и всплывает не на своем месте. Пока что данные в базе на русском и поэтому надо будет букву Э поставить на свое место в украинской локализации.

    Разработчики разными способами выходят из этой ситуации и самый первый способ к которому они прибегают - это решить проблему при помощи SQL. Но в Oracle существует более элегантный способ - настроить локализацию на сервере баз данных при помощи утилиты : Locale Builder.

    Назовем нашу локализацию UKRUMIX - добавим в украинский букву Э, а также мне не понравилась наша буква Ґ, американцы кажется не ту букву приняли за используемую у нас в Украине.

  1. Запуск утилиты Locale Builder:
    Start > Programs > Oracle-OraHome10 > Configuration and Migration Tools > Locale Builder
    или
    $oracle_home/nls/lbuilder/lbuilder.bat
  2. Переходим на Creating a New Linguistic Sort with the Oracle Locale Builder
    Нам новой сортировки не надо, мы скопируем существующую Linguistic Sort=UKRAINIAN,
    добавим в нужную позицию необходимые нам буквы, сохраним в новый файл в директорию D:\Temp, имя файла предложит утилита сама

    File->Open...->By Object Name...


    Выбираем Linguistic Sort(ID): UKRAINIAN(46)


    Откроется закладка General


    Заменим Collation Name: UKRAINIAN -> UKRUMIX
    Так как мы редактируем Monolingual Linguistic Sort, то возьмем ID из диапазона 1001
    (The valid range for Collation ID (sort ID) for a user-defined sort is 1000 to 2000 for monolingual collation and 10000 to 11000 for multilingual collation.)


    Переходим на закладку Major/Minor, сортируем по полю Major Sort,
    становимся на запись с Major Sort = 147, Unicode Value = 0x0403


    • Нажимаем New и указываем следующий значения:
      • Unicode Value = 0x0490
      • Major Sort = 147
      • Minor Sort = 3


    • Add
    • Нажимаем New и указываем следующий значения:
      • Unicode Value = 0x0491
      • Major Sort = 147
      • Minor Sort = 4


    • Add
    • Нажимаем New и указываем следующий значения:
      • Unicode Value = 0x042d
      • Major Sort = 227
      • Minor Sort = 1
    • Add
    • Нажимаем New и указываем следующий значения:
      • Unicode Value = 0x044d
      • Major Sort = 227
      • Minor Sort = 2
    • Add
    • Сохраним NLT файл в D:\temp Сохранять под именем, предложенным самой утилитой(под другим именем не даст сохранить) File -> Save As...
  3. Generating and Installing NLB Files
    1. As the user who owns the files (typically user oracle), back up the NLS installation boot file (lx0boot.nlb) and the NLS system boot file (lx1boot.nlb) in the ORA_NLS10 directory. On a UNIX platform, enter commands similar to the following example:
      % setenv ORA_NLS10 $ORACLE_HOME/nls/data
      % cd $ORA_NLS10
      % cp -p lx0boot.nlb lx0boot.nlb.orig
      % cp -p lx1boot.nlb lx1boot.nlb.orig
      
      
      Note that the -p option preserves the timestamp of the original file.
    2. In Oracle Locale Builder, choose Tools > Generate NLB or click the Generate NLB icon in the left side bar.
    3. Click Browse to find the directory where the NLT file is located
    4. Click OK to generate the NLB files.
    5. Copy the lx1boot.nlb file into the path:
      copy D:\temp\lx1boot.nlb $ORACLE_HOME/nls/data
      
    6. Copy the new NLB files into the $ORACLE_HOME/nls/data directory.
      copy D:\temp\lx303e9.nlb $ORACLE_HOME\nls\data
      
    7. Restart the database to use the newly created locale data.
      cmd> lsnrctl stop
      
      cmd> sqlplus / as sysdba
      sql> shutdown immediate
      sql> startup
      sql> exit
      
      
      
  4. Тестируем:
    select * from (
      select 'Э'/*unistr('\042d')*/ name from dual
      union all
      select 'э'/*unistr('\044d')*/ name from dual
      union all
      select 'ґ'/*unistr('\0491')*/ name from dual
      union all
      select 'Ґ'/*unistr('\0490')*/ name from dual
      union all
      select 'Ю'/*unistr('\xxxx')*/ name from dual
      union all
      select 'Я'/*unistr('\xxxx')*/ name from dual
      union all
      select 'В'/*unistr('\xxxx')*/ name from dual
      union all
      select 'Д'/*unistr('\xxxx')*/ name from dual
    )
    order by NLSSORT(name, 'NLS_SORT = UKRUMIX');
    /
    
    NA
    -- 
    В
    Ґ
    ґ
    Д
    Э
    э
    Ю
    Я
    
    8 rows selected. 
    

суббота, 27 ноября 2010 г.

Get a difference between dates (получить разницу между датами)

Распространенный вопрос, при вычитании получается число, и что с ним делать дальше часто никто не знает, либо начинают сочинять невероятные решения.

После появления в Oracle интервальных типов:
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
жить стало проще и разницу дат можно представить в виде интервала INTERVAL DAY TO SECOND (в днях/часах/минутах/секундах/милисекундах), используя следующие функции:

Формат NUMTODSINTERVAL(n, 'interval_unit'):
  • NUMTODSINTERVAL(5, 'DAY')
  • NUMTODSINTERVAL(21, 'HOUR')
  • NUMTODSINTERVAL(10, 'MINUTE')
  • NUMTODSINTERVAL(15, 'SECOND')

Формат TO_DSINTERVAL('D HH:MI:SS'):
  • TO_DSINTERVAL('5 21:10:15')

Примеры:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

set linesize 256

COLUMN DSINTERVAL2 FORMAT A20
COLUMN DSINTERVAL3 FORMAT A20

with dates as (
  select to_date('20.11.2010 23:10:05', 'DD.MM.YYYY HH24:MI:SS') date_begin,
         to_date('26.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') date_end
    from dual
)
select d.*,
       --Разница дат в до появления интервальных типов
       date_end - date_begin date_diffirence,
       --Приведение к интервальному типу при помощи NUMTODSINTERVAL
       NUMTODSINTERVAL(date_end - date_begin, 'DAY') DSINTERVAL2,
       --Приведение к интервальному типу при помощи day(5) to second(0)
       (date_end - date_begin) day(5) to second(0) DSINTERVAL3,
       -- Интервал в строковом виде заданного формата
       to_char(extract(day from (date_end - date_begin) day(5) to second(0)), '99') || 'd' ||
       to_char(extract(hour from (date_end - date_begin) day(5) to second(0)), '99') || 'h' ||
       to_char(extract(minute from (date_end - date_begin) day(5) to second(0)), '99') || 'm' ||
       to_char(extract(second from (date_end - date_begin) day(5) to second(0)), '99') || 's' as DSNTERVAL_TO_STR4,
       --Вычитаем из даты интервал
       date_end - TO_DSINTERVAL('5 21:10:15') DATE_BEGIN2,
       --Прибавляем к дате интервал
       date_begin + TO_DSINTERVAL('5 21:10:15') DATE_END2
  from dates d
 where 1 = 1
   and ( 
         numtodsinterval(date_end - date_begin, 'DAY') between numtodsinterval(-10, 'DAY') and numtodsinterval(10, 'DAY')
         or
         (date_end - date_begin) day(5) to second(0) between numtodsinterval(-10, 'DAY') and numtodsinterval(10, 'DAY')
         or
         (date_end - date_begin) day(5) to second(0) between to_dsinterval('-10 00:00:00') and to_dsinterval('10 00:00:00')
       )
;

DATE_BEGIN            DATE_END              DATE_DIFFIRENCE DSINTERVAL2                                        DSINTERVAL3                                        DSNTERVAL_TO_STR4 DATE_BEGIN2           DATE_END2            
--------------------- --------------------- --------------- -------------------------------------------------- -------------------------------------------------- ----------------- --------------------- ---------------------
11/20/2010 23:10:05   11/26/2010 20:20:20        5.88211806 +05 21:10:15.000000                                +05 21:10:15.000000                                  5d 21h 10m 15s  11/20/2010 23:10:05   11/26/2010 20:20:20  
1 row selected.

воскресенье, 21 ноября 2010 г.

Time zone, работа с временными зонами

Недавно понадобилось работать с часовыми поясами: Московское время перевести на Киевское.

Обычно делают так:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

COLUMN kiev_time FORMAT A40

select --Московское время
       to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') - 1 / 24 kiev_time
  from dual;

Session altered.

KIEV_TIME            
---------------------
22.11.2010 19:20:20  
1 row selected.


А вот как с помощью типа TIMESTAMP, полный перечень временных зон в документации:


--Список временных зон
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES;

COLUMN kiev_time FORMAT A40

-- С московского времени на киевское
select FROM_TZ(
         CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
         'Etc/GMT+3'
       ) AT TIME ZONE 'Etc/GMT+2' kiev_time
  from dual;

KIEV_TIME                          
-----------------------------------
22-NOV-10 07.20.20.000000 PM ETC/GM
T+2                                
                                                                                
1 row selected.

COLUMN kiev_time FORMAT A40

-- Полученный TIMESTAMP представить в виде даты(тип DATE)
select CAST(
         FROM_TZ(
           CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
           'Etc/GMT+3'
         ) AT TIME ZONE 'Etc/GMT+2'
         as DATE) kiev_time
  from dual;

KIEV_TIME                               
----------------------------------------
22.11.2010 19:20:20                     
1 row selected.

В предыдущих примерах использовался GMT, есть другой вариант Europe/Moscow -> Europe/Kiev:

select CAST(
         FROM_TZ(
           CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
           'Europe/Moscow'
         ) AT TIME ZONE 'Europe/Kiev'
         as DATE) kiev_time
  from dual;

KIEV_TIME            
---------------------
22.11.2010 19:20:20  
1 row selected.


-- Смещение (TZ_OFFSET) следующих временных зон:
select TZ_OFFSET('Europe/Kiev') from dual;

TZ_OFFSET('EUROPE/KIEV')
------------------------
+02:00                  
1 row selected.

select TZ_OFFSET('Europe/Moscow') from dual;

TZ_OFFSET('EUROPE/MOSCOW')
--------------------------
+03:00                    
1 row selected.

-- Текущая временная зона для сессии
SELECT SESSIONTIMEZONE FROM DUAL;

-- Сменить параметр TIME_ZONE для сеанса на Московское
alter session set TIME_ZONE = '+03:00';

select sysdate, current_date, current_timestamp, localtimestamp
 from dual;

суббота, 13 ноября 2010 г.

AGGREGATE_STRING с задаваемым разделителем

  Когда-то возникла необходимость выводить в одну строку текстовые значения принадлежащие одной группе, все варианты решения данной задачи можно посмотреть здесь String Aggregation Techniques, на то время, два года назад, самым простым и практичным и понятным мне показался этот вариант User-Defined Aggregate Function, который я взял на вооружение. Остальные решения через курсор, COLLECT, появившуюся в Oracle 10g не применимы на большим объемах записей или ограничения на пользовательскую сессию, как в случае с open_cursors. А функция SYS_CONNECT_BY_PATH очень сложная для применения в контексте этой задачи.

  Через неделю меня не устроило что разделитель жестко задан константой (, - запятая), а мне понадобилось заюзать перевод каретки и я переписал агрегатную функцию, чтобы в качестве разделителя можно было передать любую строку длиной до 30-ти символов, для разделителя этого хватит с головой.
 
  Оказывается в агрегатную функцию, в качестве входящего параметра можно, вместо строкового типа, указать объект:

--CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
--RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING t_string_agg;

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;

Чтобы функция AGGREGATE_STRING скомпилировалась сначала надо создать объект T_VALUE_DELIMITER:

CREATE OR REPLACE TYPE T_VALUE_DELIMITER as object (
  value varchar2(4000),
  delimiter varchar2(30),
  order member function match(other t_value_delimiter) return number
);
/

CREATE OR REPLACE TYPE BODY T_VALUE_DELIMITER is
  order member function match(other t_value_delimiter)
    return number is
  begin
    if value < other.value then
      return -1;
    elsif value > other.value then
      return 1;
    else
      return 0;
    end if;
  end;
END;
/

GRANT EXECUTE ON T_VALUE_DELIMITER TO PUBLIC;

CREATE PUBLIC SYNONYM T_VALUE_DELIMITER FOR T_VALUE_DELIMITER;

  Свойство value задано 4000 (максимальная длина типа VARCHAR2, которая может возвращаться из PL/SQL процедур и функций). Будьте внимательны, если у вас база создана в юникоде, то агрегатная функция сможет вернуть не более двух тысяч символов.
CREATE OR REPLACE TYPE T_AGGREGATE_STRING as object (

    str_agg varchar2(4000),
    delimiter varchar2(30),

    static function ODCIAggregateInitialize(sctx  in out t_aggregate_string)
                    return number,

    member function ODCIAggregateIterate   (self  in out t_aggregate_string,
                                            value in t_value_delimiter)
                    return number,

    member function ODCIAggregateTerminate (self         in     t_aggregate_string,
                                            return_value    out varchar2,
                                            flags        in number      )
                    return number,

    member function ODCIAggregateMerge(self in out t_aggregate_string,
                                       ctx2 in t_aggregate_string    )
                    return number
);
/

CREATE OR REPLACE TYPE BODY         T_AGGREGATE_STRING is

    static function ODCIAggregateInitialize(sctx in out t_aggregate_string)
        return number is
    begin
        sctx := t_aggregate_string(null, null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out t_aggregate_string, value in t_value_delimiter)
        return number is
    begin
        delimiter := value.delimiter;
        str_agg := str_agg || value.delimiter || value.value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in t_aggregate_string,
        return_value out varchar2, flags in number) return number is
    begin
        return_value := LTRIM(str_agg, delimiter);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out t_aggregate_string,
        ctx2 in t_aggregate_string) return number is
    begin
        str_agg := str_agg || delimiter || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/

GRANT EXECUTE ON T_AGGREGATE_STRING TO PUBLIC;

-- Синоним не создаю так как пользователь к этому объекту явно
-- никогда не обратится
--CREATE PUBLIC SYNONYM T_AGGREGATE_STRING FOR T_AGGREGATE_STRING;


Наша агрегатная функция:

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;
/
GRANT EXECUTE ON AGGREGATE_STRING TO PUBLIC;

CREATE PUBLIC SYNONYM AGGREGATE_STRING FOR AGGREGATE_STRING;

Пример:

COLUMN value FORMAT A40

select gumanoid, aggregate_string(t_value_delimiter(feature, ', ')) gumanoid_group
from (
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
     )
group by gumanoid;


GUMANOID VALUE                                   
-------- ----------------------------------------
звери    собаки, тюлени, медведи                 
птицы    синицы, пеликаны, воробьи               

2 rows selected.

Скрипт для удаления объектов

DROP PUBLIC SYNONYM T_VALUE_DELIMITER;
DROP PUBLIC SYNONYM AGGREGATE_STRING;
 
DROP FUNCTION AGGREGATE_STRING;
DROP TYPE BODY T_AGGREGATE_STRING;
DROP TYPE T_AGGREGATE_STRING;
DROP TYPE BODY T_VALUE_DELIMITER;
DROP TYPE T_VALUE_DELIMITER;


В Oracle 11g Release 2 появилась встроенная функция LISTAGG, которая делает агрегирование строк очень простым, плюс в ней, помимо разделителя, можно еще задавать сортировку в пределах группы:

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

А в Oracle 10g также подойдет вариант с XMLAGG:

WITH Q AS
(
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
)
SELECT   gumanoid,
         RTRIM(
            XMLAGG (XMLELEMENT(e, feature||'; ') ORDER BY feature).EXTRACT('//text()'),
            '; '
         ) AS concatval
    FROM q
GROUP BY gumanoid;

GUMANOID CONCATVAL 
-------- ------------------------
звери    медведи; собаки; тюлени
птицы    воробьи; пеликаны; синицы
                                                                                
2 rows selected.
Но LISTAGG и XMLAGG не позволяет использовать distinct, поэтому ниже привожу пример использования XMLQUERY для удаления дублей из результата функций LISTAGG и XMLAGG:
SELECT LISTAGG (id, ',') WITHIN GROUP (ORDER BY id) listagg_no_distinct,
       TO_CHAR (
          RTRIM (
             XMLAGG (XMLELEMENT (e, id, ',').EXTRACT ('//text()') ORDER BY id).GetClobVal (),
             ','))
          xmlagg_no_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (LISTAGG (id, ',') WITHIN GROUP (ORDER BY id),
                          ',',
                          ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          listagg_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (
                    TO_CHAR (
                       RTRIM (
                          XMLAGG (XMLELEMENT (e, id, ',').EXTRACT (
                                     '//text()') ORDER BY id).GetClobVal (),
                          ',')),
                    ',',
                    ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          xmlagg_distinct
  FROM (SELECT ROWNUM rn, CASE WHEN LEVEL IN (3, 4, 7) THEN LEVEL END AS id
              FROM DUAL
        CONNECT BY LEVEL < 10
        UNION ALL
        SELECT 10, 7 FROM DUAL
       );

суббота, 23 октября 2010 г.

AGGREGATE_BITOR

  Агрегатные функции определенные пользователем, так называемые user-defined aggregate functions создают используя следующий интерфейс

  Это моя вторая функция, о AGGREGATE_STRING напишу чуть позднее. Реализовав AGGREGATE_BITOR, я конечно же зашел в поисковик и сразу же нашел аналог на Radino’s blog (Bitwise OR Aggregate Function).

  Разница лишь в том, что я заменил "операцию логического побитового ИЛИ" одноименной функцией BITOR. Об этой, а также о других  битовых функциях можно почитать на www.orafaq.com.

CREATE FUNCTION bitor(x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
BEGIN
    RETURN x + y - bitand(x,y);
END;
/

GRANT EXECUTE on BITOR to PUBLIC;

--User must have privilege create public synonym;
CREATE PUBLIC SYNONYM BITOR FOR BITOR;


  Таким образом, код AGGREGATE_BITOR получился более понятен:

CREATE OR REPLACE TYPE T_AGGREGATE_BITOR as object (

    value number,

    static function ODCIAggregateInitialize(sctx  in out t_aggregate_bitor)
                    return number,

    member function ODCIAggregateIterate   (self  in out t_aggregate_bitor,
                                            p_value in number)
                    return number,

    member function ODCIAggregateTerminate (self         in     t_aggregate_bitor,
                                            return_value    out number,
                                            flags        in number      )
                    return number,

    member function ODCIAggregateMerge(self in out t_aggregate_bitor,
                                       ctx2 in t_aggregate_bitor    )
                    return number
);
/


 
CREATE OR REPLACE TYPE BODY T_AGGREGATE_BITOR is

    static function ODCIAggregateInitialize(sctx in out t_aggregate_bitor)
        return number is
    begin
        sctx := t_aggregate_bitor(0);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out t_aggregate_bitor, p_value in number)
        return number is
    begin
        value := BITOR(value, p_value);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in t_aggregate_bitor,
        return_value out number, flags in number) return number is
    begin
        return_value := value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out t_aggregate_bitor,
        ctx2 in t_aggregate_bitor) return number is
    begin
        value := BITOR(value, ctx2.value);
        return ODCIConst.Success;
    end;
end;
/
 

Сама агрегатная функция:


CREATE OR REPLACE FUNCTION AGGREGATE_BITOR (input NUMBER) return NUMBER
    parallel_enable aggregate using T_AGGREGATE_BITOR;
/

CREATE PUBLIC SYNONYM AGGREGATE_BITOR FOR AGGREGATE_BITOR;

Проверим в действии, значение флагов для тестирования возьмем тут Bitwise OR Aggregate Function:


with bitor_test as (
    select 'table1' name, 5 flags  -- 0101
      from dual
     union all
    select 'table2', 1  -- 0001
      from dual
     union all
    select 'table3', 9  -- 1001
      from dual
     union all
    select 'table4', 12 -- 1100
      from dual
)
SELECT AGGREGATE_BITOR(flags) FROM bitor_test;

AGGREGATE_BITOR(FLAGS)
---------------------
13