Orafce

Это расширение QHB реализует функции и операторы, которые эмулируют подмножество функций и пакетов из Oracle RDBMS.

Обратите внимание, что эта версия orafce работает с QHB версии 1.5.1 и выше.

Установка

Функции совместимости и пакеты Oracle для QHB поставляется в виде пакета qhb-1.5.1-orafce.

Подключите репозиторий пакетов QHB и установите пакет расширения для выбранной платформы со страницы загрузки.

Функции и пакеты Oracle

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

Встроенные функции даты Oracle были протестированы на совместимость с Oracle 10. Диапазоны дат с 1960 по 2070 год работают корректно. Даты до 1100-03-01 не могут быть проверены из-за ошибки в Oracle.

Все функции полностью совместимы с Oracle и учитывают все известные строки формата.

Список строк формата для функций trunc, round

Y,YY,YYY,YYYY,SYYY,SYEAR      год
I,IY,IYY,IYYY			            недельный год по ISO 8601
Q				                      квартал
WW				                    неделя, начиная с первого дня года
IW				                    неделя, начиная с понедельника
W				                      неделя, начиная с первого дня месяца
DAY,DY,D			                первый день недели, воскресенье
MONTH,MON,MM,RM			          месяц
CC,SCC				                век
DDD,DD,J			                день
HH,HH12,HH24			            час
MI				                    минута

Функции округляются. То есть дата 1 июля будет округлена до следующего года. 16 июля округляется до августа.

Функции даты

orafce.nls_date_format value to DEFAULT
to_date('2014-05-19 17:23:53+5:30') -> 2014-05-19 17:23:53
orafce.nls_date_format='YYYY-MMDD HH24:MI:SS'
to_date('2014-0519 17:23:53+5:30')  -> 2014-05-19 17:23:53

Тип данных oracle.date

Этот модуль содержит реализацию совместимого с Oracle типа данных date, «oracle.date», и функций, использующих тип данных date, таких как oracle.add_months, oracle.last_day(), oracle.next_day(), oracle.months_between() и т. д.

Пример:

SET search_path TO oracle,"$user", public, pg_catalog;
CREATE TABLE oracle_date(col1 date);
INSERT INTO oracle_date values('2014-06-24 12:12:11'::date);
SELECT * FROM oracle_date;
        col1
---------------------
 2014-06-24 12:12:11
(1 row)

Функции oracle.date

orafce.nls_date_format='YY-MonDD HH24:MI:SS'
oracle.to_char(to_date('14-Jan08 11:44:49+05:30')) -> 14-Jan08 11:44:49
orafce.nls_date_format='YY-MonDD HH24:MI:SS'
oracle.to_char(oracle.to_date('21052014 12:13:44+05:30','DDMMYYYY HH24:MI:SS')) -> 14-May21 12:13:44

Операторы oracle.date

Вам нужно установить search_path TO oracle,"$user", public, pg_catalog, потому что такие функции, как oracle.add_months, oracle.last_day, oracle.next_day и oracle.months_between, устанавливаются бок о бок с функциями pg_catalog.add_months, pg_catalog.last_day, pg_catalog.next_day и pg_catalog.months_between.

«Двойная» таблица

QHB не нуждается в «двойной» таблице Oracle, но поскольку она интенсивно используется пользователями Oracle, она была добавлена ​​в orafce. Эта таблица находится в схеме oracle. Обычно имеет смысл разрешить к ней широкий доступ, поэтому следует добавить эту схему в конфигурацию search_path (как search_path = 'oracle, pg_catalog, "$user", public' в qhb.conf).

Пакет dbms_output

QHB отправляет информацию клиенту через RAISE NOTICE. Oracle использует dbms_output.put_line(). Это работает иначе, чем RAISE NOTICE. В Oracle имеется очередь сеанса, функция put_line() добавляет строку в очередь, а функция get_line() читает из очереди. Если установлен флаг serveroutput, то клиент по всем операторам sql читает очередь. Вы можете использовать:

SELECT dbms_output.enable();
SELECT dbms_output.put_line('first_line');
SELECT dbms_output.put_line('next_line');
SELECT * FROM dbms_output.get_lines(0);

или

SELECT dbms_output.enable();
SELECT dbms_output.serveroutput('t');
SELECT dbms_output.put_line('first_line');

Этот пакет содержит следующие функции: enable(), disable(), serveroutput(), put(), put_line(), new_line(), get_line(), get_lines(). Очередь пакетов реализована в локальной памяти сеанса.

Пакет utl_file

Этот пакет позволяет программам PL/pgSQL читать и записывать любые файлы, доступные с сервера. Каждый сеанс может открыть максимум десять файлов, а максимальный размер строки составляет 32 КБ. Этот пакет содержит следующие функции:

Поскольку QHB не поддерживает вызов по ссылке, некоторые функции немного отличаются: fclose и get_line.

do $$
declare f utl_file.file_type;
begin
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  <<readl>>
  loop
    begin
      raise notice '%', utl_file.get_line(f);
    exception
      when no_data_found then
        exit readl;
    end;
  end loop;
  f := utl_file.fclose(f);
end;
$$;

или второй (со специальной функцией QHB get_nextline)

do $$
declare
  f utl_file.file_type;
  line text;
begin
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  loop
    line := utl_file.get_nextline(f);
    exit when line is NULL;
    raise notice '%', line;
  end loop
exception
  when others then
  perform utl_file.fclose_all();
end;
$$;

Перед использованием пакета вы должны установить таблицу utl_file.utl_file_dir. Она содержит все разрешенные каталоги без завершающего символа ('/' или '\'). На платформе WinNT пути должны каждый раз заканчиваться символом '\'.

Записи каталога могут быть названы (второй столбец в таблице utl_file.utl_file_dir). Параметр location может быть либо именем каталога, либо путем к словарю. Расположение сначала интерпретируется и проверяется как имя каталога. Если не найден (во 2-м столбце), то расположение интерпретируется и проверяется как путь.

Функции из пакета utl_file (схема в QHB) требуют доступа к таблице utl_file.utl_file_dir. Этот факт можно использовать для контроля того, могут ли пользователи использовать эти функции или нет. Значение по умолчанию — READ для public. INSERT и UPDATE может делать только привилегированный пользователь (суперпользователь). Таким образом, непривилегированный пользователь может использовать функции из этого пакета, но не может изменять список безопасных каталогов (содержимое таблицы utl_file.utl_file_dir). Содержимое этой таблицы видно для public (или должно быть видно для пользователей, использующих функции из этого пакета).

Пакет dbms_sql

Данный пакет — реализация Oracle API пакета DBMS_SQL.

Пакет не обеспечивает полной совместимости, но должен уменьшить работу, необходимую для успешной миграции.

Внимание: архитектура QHB отличается от архитектуры Oracle. PL/pgSQL выполняется в том же контексте, что и механизм SQL. Тогда нет никакой причины использовать шаблоны Oracle, такие как массовый сбор и итерация по сбору в QHB, для получения хорошей производительности. Этот код предназначен для сокращения работы, связанной с переносом некоторых приложений с Oracle на QHB, и может работать хорошо. Но не будет никакого преимущества в производительности по сравнению со встроенными операторами PL/pgSQL. Эмуляция API Oracle требует дополнительных ресурсов памяти и ЦП, которые могут быть значительными при больших объемах данных.

Функциональность

Это расширение реализует подмножество интерфейса Oracle dbms_sql. Целью этого расширения является не совместимость с Oracle, оно предназначено для сокращения объема работы, связанной с миграцией приложений Oracle на QHB. Поддерживаются некоторые базовые функции массового DML:

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
  a := ARRAY[1, 2, 3, 4, 5];
  b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
  ca := ARRAY[3.14, 2.22, 3.8, 4];

  call dbms_sql.bind_array(c, 'a', a, 2, 3);
  call dbms_sql.bind_array(c, 'b', b, 3, 4);
  call dbms_sql.bind_array(c, 'c', ca);
  raise notice 'inserted rows %d', dbms_sql.execute(c);
end;
$$;

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
  call dbms_sql.define_array(c, 1, a, 10, 1);
  call dbms_sql.define_array(c, 2, b, 10, 1);
  call dbms_sql.define_array(c, 3, ca, 10, 1);

  perform dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0
  loop
    call dbms_sql.column_value(c, 1, a);
    call dbms_sql.column_value(c, 2, b);
    call dbms_sql.column_value(c, 3, ca);
    raise notice 'a = %', a;
    raise notice 'b = %', b;
    raise notice 'c = %', ca;
  end loop;
  call dbms_sql.close_cursor(c);
end;
$$;

Имеется функция dbms_sql.describe_columns_f, то есть процедура dbms_sql.describe_columns. Обратите внимание, что идентификаторы типов относятся к системе типов QHB. Значения не конвертируются в числа Oracle.

do $$
declare
  c int;
  r record;
  d dbms_sql.desc_rec;
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  r := dbms_sql.describe_columns(c);
  raise notice '%', r.col_cnt;

  foreach d in array r.desc_t
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

do $$
declare
  c int;
  n int;
  d dbms_sql.desc_rec;
  da dbms_sql.desc_rec[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  call dbms_sql.describe_columns(c, n, da);
  raise notice '%', n;

  foreach d in array da
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

Пакет dbms_pipe

Этот пакет является эмуляцией пакета Oracle dbms_pipe. Обеспечивает межсеансовую связь. Вы можете отправлять и читать любое сообщение с ожиданием или без него, просмотреть список активных каналов, установить канал как частный или общедоступный и использовать явные или неявные каналы.

Максимальное количество каналов: 50.

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

Ниже приведен пример:

-- Сеанс A
select dbms_pipe.create_pipe('my_pipe',10,true); -- явное создание канала
select dbms_pipe.pack_message('neco je jinak');
select dbms_pipe.pack_message('anything is else');
select dbms_pipe.send_message('my_pipe',20,0); -- изменение предела и передача без ожидания
select * from dbms_pipe.db_pipes; -- список текущих каналов

-- Сеанс B
select dbms_pipe.receive_message('my_pipe',1); -- ожидание для сообщения максимум 1 секунда
select dbms_pipe.next_item_type(); -- -> 11, текст
select dbms_pipe.unpack_message_text();
select dbms_pipe.next_item_type(); -- -> 11, текст
select dbms_pipe.unpack_message_text();
select dbms_pipe.next_item_type(); -- -> 0, больше элементов нет
select dbms_pipe.remove_pipe('my_pipe');

Однако есть некоторые отличия от Oracle:

• предел для каналов не в байтах, а в элементах в канале
• вы можете отправить сообщение без ожидания
• вы можете отправлять пустые сообщения
• next_item_type знает о timestamp (тип 13)
• QHB не знает о типе raw, вместо этого используйте bytea

Пакет dbms_alert

Еще одно средство межпроцессного взаимодействия.

-- Сеанс A
select dbms_alert.register('boo');
select * from dbms_alert.waitany(10);

-- Сеанс B
select dbms_alert.register('boo');
select * from dbms_alert.waitany(10);

-- Сеанс C
select dbms_alert.signal('boo','Nice day');

Пакет PLVdate

Этот модуль содержит некоторые функции для работы с рабочими днями из пакета PLVdate. Подробную документацию можно найти в библиотеке PLVision. Этот пакет является мультикультурным, но конфигурации по умолчанию предназначены только для европейских стран.

Вы должны определить свои собственные нерабочие дни (максимум 50 дней) и собственные праздники (максимум 30 дней). Выходной день — это любой нерабочий день, который одинаков каждый год. Например, Рождество в западных странах.

Функции

Пример:

qhb=# select plvdate.default_holidays('czech');
 default_holidays
 -----------------

(1 row)
qhb=# select to_char(current_date, 'day'),
           plvdate.next_bizday(current_date),
	   to_char(plvdate.next_bizday(current_date),'day');
  to_char  | next_bizday |  to_char
 ----------+-------------+-----------
 saturday  | 2006-03-13  | monday
(1 row)

Изменение для неевропейской среды:

select plvdate.unset_nonbizday('saturday');
select plvdate.unset_nonbizday('sunday');
select plvdate.set_nonbizday('friday');
select plvdate.set_nonbizday('2006-05-19', true);
select plvdate.unuse_easter();

Пакет PLVstr и PLVchr

Этот пакет содержит несколько полезных строковых и символьных функций. Каждая функция поддерживает положительные и отрицательные смещения, т. е. смещение как от начала, так и от конца строки. Например:

plvstr.left('abcdef',2) -> ab
plvstr.left('abcdef',-2) -> abcd
plvstr.substr('abcdef',1,1) -> a
plvstr.substr('abcdef',-1,1) -> f
plvstr.substr('abcde',-2,1) -> d

Список функций:

Пакет PLVsubst

Пакет PLVsubst выполняет замену строк на основе замещающего ключевого слова.

Примеры:

select plvsubst.string('My name is %s %s.', ARRAY['Pavel','Stěhule']);
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

select plvsubst.string('My name is %s %s.', 'Pavel,Stěhule');
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

select plvsubst.string('My name is $$ $$.', 'Pavel|Stěhule','|','$$');
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

Пакет DBMS_utility

dms_utility.format_call_stack()

Возвращает отформатированную строку с содержимым стека вызовов.

qhb=# select foo2();
               foo2
 ---------------------------------
 -----  Call Stack  -----
   line             object
 number  statement  name
      1  return     function foo
      1  return     function foo1
      1  return     function foo2
(1 row)

Пакет PLVlex

Этот пакет несовместим с оригинальным PLVlex.

plvlex.tokens(str text, skip_spaces bool, qualified_names bool)

Возвращает таблицу лексических элементов в строке.

qhb=# select * from
	plvlex.tokens('select * from a.b.c join d ON x=y', true, true);

 pos | token  | code |  class  | separator | mod
 ----+--------+------+---------+-----------+------
   0 | select |  527 | KEYWORD |           |
   7 | *      |   42 | OTHERS  |           | self
   9 | from   |  377 | KEYWORD |           |
  25 | a.b.c  |      | IDENT   |           |
  20 | join   |  418 | KEYWORD |           |
  25 | d      |      | IDENT   |           |
  27 | on     |  473 | KEYWORD |           |
  30 | x      |      | IDENT   |           |
  31 | =      |   61 | OTHERS  |           | self
  32 | y      |      | IDENT   |           |
(10 rows)

ВНИМАНИЕ!
Коды ключевых слов могут меняться от версии к версии QHB!

Пакет DBMS_ASSERT

Этот пакет защищает пользовательский ввод от SQL-инъекций.

Пакет PLUnit

Этот пакет содержит некоторые функции утверждения.

Пакет DBMS_random

Другие функции

Этот пакет содержит реализацию функций: concat, nvl, nvl2, lnnvl, decode, greatest, least, bitand, nanvl, sinh, cosh, tanh, oracle.substr и oracle.mod.

Возможно, вам потребуется установить для параметра search_path значение oracle, pg_catalog, "$user", public, поскольку функции oracle.substr, oracle.lpad, oracle.rpad, oracle.ltrim, oracle.rtrim, oracle.btrim и oracle.length устанавливаются бок о бок с функциями pg_catalog.substr, pg_catalog.lpad, pg_catalog.rpad, pg_catalog.ltrim, pg_catalog.rtrim, pg_catalog.btrim и pg_catalog.length соответственно.

Функции oracle.decode, oracle.greatest и oracle.least всегда должны начинаться с префикса имени схемы, даже если oracle стоит перед pg_catalog в search_path, потому что эти функции реализованы внутри парсера и анализатора QHB. Без имени схемы всегда будут использоваться внутренние функции.

Обратите внимание, что в случае lpad** и rpad параметры string и fill могут иметь типы char, varchar, text, varchar2 или nvarchar2 (обратите внимание, что последние два типа предоставляются orafce). Символ заполнения по умолчанию представляет собой пробел половинной ширины. То же самое верно для ltrim, rtrim и btrim.

Обратите внимание, что у функции oracle.length есть ограничение, заключающееся в том, что она работает только с единицами символов, поскольку в QHB тип char поддерживает только семантику символов.

Функция oracle.sys_guid()

Эта функция возвращает глобальный уникальный идентификатор. Она вызывает указанные функции из расширения uuid-ossp, а затем эта функция должна быть установлена ​​до использования функции sys_guid. По умолчанию эта функция использует функцию uuid_generate_v1, но также можно использовать функции uuid_generate_v1mc и uuid_generate_v4 (путем установки функции orafce.sys_guid_source). Кроме того, oracle.sys_guid может использовать встроенную функцию gen_random_uuid. В этом случае расширение uuid-ossp не требуется.

Поддержка varchar2 и nvarchar2

Тип varchar2 от orafce реализует части спецификации базы данных Oracle о varchar2:

• Единица модификатора типа — 'байты' (семантику символов см. в описании nvarchar2).

• В отличие от varchar QHB, неявное приведение к varchar2 не усекает пробелы сверх заявленной максимальной длины.

• При включении orafce.varchar2_null_safe_concat TO true для этих типов можно использовать защищенный от NULL оператор ||. Это поведение очень похоже на Oracle.

ВНИМАНИЕ!
Если результатом является пустая строка, то результат равен NULL. По умолчанию это поведение отключено.

-- null safe concat (по умолчанию отключено)
SELECT NULL || 'hello'::varchar2 || NULL;

SET orafce.varchar2_null_safe_concat TO true;
SELECT NULL || 'hello'::varchar2 || NULL;

Обратите внимание, что QHB не позволяет динамически указывать, как мы интерпретируем строки varchar. Она всегда интерпретирует их как «символьные» строки в соответствии с кодировкой базы данных. Таким образом, мы не можем одновременно поддерживать семантику байтов и символов для данного типа varchar в одной и той же базе данных. Мы решили реализовать семантику байтов, так как она используется в Oracle по умолчанию. Семантику символов см. в описании nvarchar2, который по умолчанию всегда реализует семантику символов.

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

nvarchar2 реализует следующее:

• Единица модификатора типа = 'символы' (с использованием набора символов/ кодировки базы данных)

Используйте этот тип, если предпочтительна семантика символов.

Обратите внимание, что, в отличие от Oracle, в orafce типы varchar2* и nvarchar2 не налагают ограничение в 4000 байт на «объявленный» размер. На самом деле это то же самое, что и у varchar в QHB, размер которого составляет около 10 МБ (хотя теоретически varchar может хранить значения размером до 1 ГБ).

Некоторые строковые функции на основе байтов для использования со строками varchar2:

substrb(varchar2, int [, int])

Извлекает подстроку заданной длины (в байтах), начиная с заданной позиции байта (считая с единицы); если третий аргумент не указан, то считается длина до конца строки.

strposb(varchar2, varchar2)

Возвращает расположение указанной подстроки в заданной строке (считая с единицы).

lengthb(varchar2)

Возвращает длину (в байтах) заданной строки.

Триггеры

Oracle не делает различий между NULL и пустой строкой (когда значение используется как текст). Для QHB NULL и пустая строка являются разными значениями. Для простоты рекомендуется обеспечить (в базе данных QHB) использование только NULL (и не использовать пустые строки) или использование только пустых строк (и не использовать NULL) для столбцов текстового типа. Оба варианта имеют некоторые преимущества и недостатки.

Это можно обеспечить с помощью триггерных функций:

oracle.replace_empty_strings([raise_warnings boolean])
oracle.replace_null_strings([raise_warnings boolean])

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

CREATE TABLE test(id serial, name varchar, surname varchar);
CREATE TRIGGER test_trg
  BEFORE INSERT OR UPDATE
  ON test
  FOR EACH ROW
  EXECUTE PROCEDURE oracle.replace_empty_strings();

INSERT INTO test(name, surname) VALUES('', 'Stehule');

-- имя будет заменено на NULL

Эмулированные представления

• oracle.user_tab_columns
• oracle.user_tables
• oracle.user_cons_columns
• oracle.user_constraints
• oracle.product_component_version
• oracle.user_objects
• oracle.dba_segments

См. также

Документация по orafce на Github