orafce. Совместимость логики Oracle для PL/Pgsql

Версия: 4.4.0.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Модуль orafce реализует некоторые функции из СУБД Oracle, которые отсутствуют или ведут себя по-другому в СУБД PostgreSQL.

Модуль является функциональным расширением совместимости для логики, написанной под Oracle.

Функционал

dbms_alert

Пакет добавляет модель межсессионного взаимодействия.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Процедура	`_signal`	`name::text`, `message::text`	–	Internal
Триггер	`deferred_signal`	–	`trigger`	Internal
Процедура	`register`	`name::text`	–	Регистрация `ipc` c именем `name`
Процедура	`remove`	`name::text`	–	Удаление `ipc` с именем `name`
Процедура	`removeall`	–	–	Удаление всех `ipc`
Процедура	`set_defaults`	`sensitivity::float8`	–	Определение `sensitivity`
Процедура	`signal`	`event::text`, `_message::text`	–	Регистрация сигнала для `ipc event` с сообщением `_message`
Функция	`waitany`	`timeout::float8`	`name::text`, `message::text`, `status::int`	Ожидание сигналов в течение `timeout` секунд
Функция	`waitone`	`name::text`, `timeout::float8`	`message::text`, `status::int`	Ожидание сигнала в `ipc name` в течение `timeout` секунд

dbms_assert

Пакет добавляет дополнительные проверки в целях защиты от SQL injection.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`enquote_literal`	`str::varchar`	`::varchar`	Квотирование строки; верификация двойного квотирования строки
Функция	`enquote_name`	`str::varchar`, `[lowercase::bool]`	`::varchar`	Квотирование имени объекта SQL. Опциональный параметр - приведение имени к нижнему регистру ВНИМАНИЕ: поведение отличается от Oracle, где имя приводится к верхнему регистру
Функция	`noop`	`str::varchar`	`::varchar`	Функция-заглушка. Изменений не производится.
Функция	`qualified_sql_name`	`str::varchar`	`::varchar`	Проверка того, что входной параметр является правильным именем объекта SQL
Функция	`schema_name`	`str::varchar`	`::varchar`	Проверка существования в БД определенной схемы
Функция	`simple_sql_name`	`str::varchar`	`::varchar`	Проверка применимости входного параметра для использования в качестве идентификатора SQL
Функция	`object_name`	`str::varchar`	`::varchar`	Проверка существования нефункционального объекта в БД с именем входного параметра

dbms_output

Пакет добавляет консольный вывод сообщений.

В Pangolin используется RAISE, однако поведение функций пакета отличается от принятого в Pangolin порядка выдачи сообщений. Функции пакета представляют собой очередь сообщений и могут быть прочитаны внутри сеанса.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Процедура	`disable`	–	–	Отключение вывода сообщений
Процедура	`enable`	`[buffer_size::int]`	–	Включение вывода сообщений. Опциональный параметр указывает размер буфера в байтах
Функция	`get_line`	–	`line::text`, `status::int`	Получение сообщений
Функция	`get_lines`	`numlines::int`	`lines::text[]`, `numlines::int`	Получение блока последних сообщений
Процедура	`new_line`	–	–	Добавление нового пустого сообщения
Функция	`put`	`a::text`	–	Добавление нового сообщения (блок)
Функция	`put_line`	`a::text`	–	Добавление нового сообщения (строка)
Процедура	`serveroutput`	`::bool`	–	Переключение вывода сообщений в консоль

dbms_pipe

Пакет добавляет эмуляцию каналов Oracle. Реализация основана на использовании shared memory.

Максимальное количество каналов - 50;
Длина канала определяется не в байтах, а в количестве элементов;
Возможна отправка сообщений без ожидания;
Возможна отправка пустых сообщений;
Тип timestamp для next_item_type = 13;
СУБД Pangolin не поддерживает тип RAW. Используйте тип bytea.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Представление	`db_pipes`	–	`name::varchar`; `items::int4`; `size::int4`; `limit::int4`; `private::bool`; `owner::varchar`	Список каналов
Процедура	`create_pipe`	`::text`; `[::int4,[::bool]]`	–	Передача параметров (имени, размера, признака) при создании канала
Функция	`next_item_type`		`::int4`	Определение формата сообщения в канале: 0 - канал пуст; 9 - `numeric`/`int4`/`int8`; 11 - `text`; 12 - `date`; 13 - `timestamptz`; 23 - `byte`; 24 - `record`
Процедура	`pack_message`	`::bytea`; `::int4`; `::int8`; `::numeric`; `::text`; `::date`; `::timestamptz`; `::bytea`; `::record`	–	Добавление сообщения в канал
Процедура	`purge`	`::text`	–	Очистка канала. Параметр - имя канала
Функция	`receive_message`	`::text [::int4]`	`::int4`	Прием сообщения. Копирование сообщения в локальный буфер. Параметры: - имя канала; - время ожидания в секундах. Результат: код возврата. 0 - успех; 1 - тайм-аут; 2 - Ошибка: размер сообщения превышает размер буфера; 3 - Прерывание; ? - Недостаточно привилегий
Процедура	`remove_pipe`	`::text`	–	Удаление канала. Параметр - имя канала
Процедура	`reset_buffer`	–	–	Очистка буфера
Функция	`send_message`	`::text[::int4 [::int4]]`	`::int4`	Передача сообщения. Параметры: - имя канала; - тайм-аут в секундах; - максимальный размер канала. Канал, созданный этой функцией, будет удален после передачи сообщения (в отличие от канала, созданного функцией `create_pipe`). Код возврата совпадает с кодами функции `receive_message`
Функция	`unique_session_name`	–	`::varchar`	Возвращает уникальное имя сессии, в которой создан канал
Функция	`unpack_message_bytea`	–	`::bytea`	Распаковка сообщения `bytea`
Функция	`unpack_message_date`	–	`::date`	Распаковка сообщения `date`
Функция	`unpack_message_number`	–	`::numeric`	Распаковка сообщения `number`
Функция	`unpack_message_record`	–	`::record`	Распаковка сообщения `record`
Функция	`unpack_message_timestamp`	–	`::timestamptz`	Распаковка сообщения `timestamp`
Функция	`unpack_message_text`	–	`::text`	Распаковка сообщения `text`

dbms_random

Пакет добавляет псеводслучайные числа Oracle.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Процедура	`initialize`	`::int4`	–	Инициализация генератора псевдослучайных чисел c заданным зерном (`seed`)
Функция	`normal`	–	`::float`	Генерация числа в нормальном распределении
Функция	`random`	–	`::int4`	Генерация числа в полном диапазоне int4 (-2^31..2^31)
Процедура	`seed`	`::int4`	–	Передача зерна (`seed`) генератору
Процедура	`seed`	`::text`	–	Передача зерна (`seed`) генератору
Функция	`string`	`opt::text`; `len::int4`	`text`	Генерация случайной строки длиной `len`. Параметры: 'u','U' - UPPERCASE ALPHA; 'l','L' - lowercase alpha; 'a','A' - MiXeD AlPhA; 'x','X' – UPPERCASE ALPHANUMERIC; 'p','P' - Any printable characters
Процедура	`terminate`	–	–	Окончание работы пакета
Функция	`value`	`[ low::float, high::float]`	–	Генерация псевдослучайного номера из диапазона с нижней границей `low` включительно и верхней границей `high` не включительно)

dbms_utility

Пакет добавляет просмотр стека вызовов.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`format_call_stack`	`[ ::text ]`	`text`	Возвращает стек вызовов внутри блока pl/pgsql

utl_file

Пакет добавляет операции с файловой системой.

В каждой сессии допускается до 10 открытых файловых дескрипторов. Длина строки ограничена 32 Кб.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Таблица	`utl_file_dir`	`dir::text`; `dirname::text`	–	Таблица алиасов
Домен	`file_type`	`file_type::integer`	–	Домен для хранения файлового дескриптора
Функция	`fclose`	`file::utl_file.file_type`	`::utl_file.file_type`	Закрытие файлового дескриптора
Процедура	`fclose_all`	–	–	Закрытие всех открытых файловых дескрипторов
Процедура	`fcopy`	`src_location::text`; `src_filename::text`; `dest_location::text`; `dest_filename::text`; `[ start_line::int4 ]`; `[[ end_line::int4]]`	–	Копирование файла. Передаваемые параметры: - исходный каталог; - исходное имя файла; - каталог назначения; - имя файла назначения. Опционально: - начальная строка; - конечная строка
Процедура	`fflush`	`file::utl_file.file_type`	–	Сброс буфера на диск
Функция	`fgetattr`	`location::text`; `filename::text`	`fexist::boolean`; `file_length::bigint`; `block size::int4`	Получение атрибутов файла
Функция	`fopen`	`location::text`; `filename::text`; `open_mode::text`; `[ max_linesize::int4 ]`; `[[ encoding::name ]]`	`::utl_file.file_type`	Открытие файлового дескриптора. Параметр `open_mode` стандартный (`r`, `rw`, `a`, ...)
Процедура	`fremove`	`location::text`; `filename::text`	–	Удаление файла
Процедура	`frename`	`location::text`; `filename::text`; `[ dest_dir::text ]`; `[ dest_file::text ]`; `[[ overwrite::bool ]]`	–	Переименование/перемещение файла
Функция	`get_line`	`file::utl_file.file_type`; `len::int4`	`buffer::text`	Получение строки из открытого файла
Функция	`get_nextline`	`file::utl_file.file_type`	`buffer::text`	Получение строки из открытого файла
Функция	`is_open`	`file::utl_file.file_type`	`::boolean`	Проверка валидности файлового дескриптора
Функция	`new_line`	`file::utl_file.file_type`; `[ lines::int4]`	`::boolean`	Добавление новой строки в открытый файл
Функция	`put`	`file::utl_file.file_type`; `(buffer::text \| buffer::anyelement )`	`::boolean`	Добавление записи в файл
Функция	`put_line`	`file::utl_file.file_type`; `(buffer::text \| buffer::anyelement )`; `[ autoflush::boolean ]`	`::boolean`	Добавление новой строки в открытый файл
Функция	`putf`	`file::utl_file.file_type`; `format::text`; `[ arg1::text ]`; `[[ arg2::text ]]`; `[[[ arg3::text ]]]`; `[[[[ arg4::text ]]]]`; `[[[[[ arg5::text ]]]]]`	`::boolean`	Форматированный вывод в открытый файл
Функция	`tmpdir`	–	`::text`	Вывод значения системной переменной `$TEMP`

plunit

Пакет добавляет функции проверок.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Процедура	`assert_equals`	`expected::anyelement`; `actual::anyelement`; `[ message::varchar]`	–	Проверка условия `expected = actual`
Процедура	`assert_equals`	`expected::float8`; `actual::float8`; `range::float8`; `[ message::varchar]`	–	Проверка условия `expected = actual` в пределах `range`
Процедура	`assert_false`	`condition::bool`; `[ message::varchar]`	–	Проверка логического условия `FALSE`
Процедура	`assert_true`	`condition::bool`; `[ message::varchar]`	–	Проверка логического условия `TRUE`
Процедура	`assert_not_equals`	`expected::anyelement`; `actual::anyelement`; `[ message::varchar]`.	–	Проверка условия `expected != actual`
Процедура	`assert_not_equals`	`expected::float8`; `actual::float8`; `range::float8`; `[ message::varchar]`	–	Проверка условия `expected != actual` в пределах `range`
Процедура	`assert_not_null`	`actual::anyelement`; `[ message::varchar]`	–	Проверка входного параметра на присутствие значения `NOT NULL`
Процедура	`assert_null`	`actual::anyelement`; `[ message::varchar]`	–	Проверка входного параметра на отсутствие значения `IS NULL`
Процедура	`fail`	`[ message::varchar]`	–	Безусловный возврат с ошибкой

plvchr

Пакет добавляет специфичные для Oracle функции при работе с текстом.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`_is_kind`	`str::text,kind::int4`; `c::int4, kind::int4`	`boolean`	Скрытый траппер для функций `is_%`
Функция	`char_name`	`c::text`	`varchar`	Возвращает код символа в кодировке `ASCII`
Функция	`first`	`str::text`	`varchar`	Возвращает первый символ в строке
Функция	`last`	`str::text`	`varchar`	Возвращает последний символ в строке
Функция	`nth`	`str::text`; `n::int4`	`text`	Возвращает `n`-ый символ в строке
Функция	`quoted1`	`str::text`	`varchar`	Возвращает текст, заключенный в апострофы
Функция	`quoted2`	`str::text`	`varchar`	Возвращает текст, заключенный в кавычки
Функция	`stripped`	`str::text`; `char_in::text`	`varchar`	Удаление символов подстроки `char_in` из `str` с учетом регистра символов
Функция	`is_blank`	`str::text`; `c::int4`	`boolean`	Проверка значения параметра на заполненность
Функция	`is_digit`	`str::text`; `c::int4`	`boolean`	Проверка значения параметра на цифровой формат
Функция	`is_letter`	`str::text`; `c::int4`	`boolean`	Проверка значения параметра на текстовый формат
Функция	`is_other`	`str::text`; `c::int4`	`boolean`	Проверка значения параметра на несоответствие ни цифровому, ни текстовому формату
Функция	`is_quote`	`str::text`; `c::int4`	`boolean`	Проверка значения текстового параметра на квотирование (кавычки или апострофы)

plvdate

Пакет добавляет специфичные для Oracle функции при работе с датами.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`add_bizdays`	`::date`; `::int4`	`date`	Получение рабочей даты, спустя `<n>` рабочих дней от заданной
Функция	`bizdays_between`	`::date`; `::date`	`int4`	Количество рабочих дней между двумя датами
Функция	`days_inmonth`	`::date`	`int4`	Количество дней в месяце
Процедура	`default_holidays`	`::text`	–	Загрузка рабочего календаря. Принимаемые конфигурации: – `Czech`; – `German`; – `Austria`; – `Poland`; – `Slovakia`; – `Russia`; – `GB`; – `USA`
Функция	`include_start`	`[ ::boolean ]`	`boolean`	Включение первой даты в расчет
Функция	`noinclude_start`	–	`boolean`	Исключение первой даты из расчета
Функция	`isbizday`	`::date`	`boolean`	Проверить, является ли дата рабочим днем
Функция	`isleapyear`	`::date`	`boolean`	Проверить, является ли год високосным
Функция	`nearest_bizday`	`::date`	`date`	Получить ближайшую дату рабочего дня
Функция	`next_bizday`	`::date`	`date`	Получить дату следующего рабочего дня от заданного
Функция	`prev_bizday`	`::date`	`date`	Получить дату предыдущего рабочего дня относительно заданного
Функция	`set_nonbizday`	`::date`	`boolean`	Задать дату как нерабочий день
Процедура	`set_nonbizday`	`::text` `::date`,`::boolean`	–	Задать день недели как нерабочий. Задать день как нерабочий. Второй параметр - рекурсия (каждый год)
Функция	`unset_nonbizday`	`::date`	`boolean`	Определение рабочего дня. Возвращаемый параметр - рекурсия (каждый год)
Процедура	`unset_nonbizday`	`::text` `::date`,`::boolean`	–	Задать день недели как рабочий. Задать день как рабочий. Второй параметр - рекурсия (каждый год)
Функция	`use_easter`	–	`boolean`	Задать Пасху как нерабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедура	`use_easter`	`::boolean`	–	Задать Пасху как нерабочий день
Функция	`unuse_easter`	–	`boolean`	Задать Пасху как рабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедура	`unuse_easter`	`::boolean`	–	Задать Пасху как рабочий день
Функция	`use_great_friday`	–	`boolean`	Задать Страстную пятницу как нерабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедура	`use_great_friday`	`::boolean`	–	Задать Страстную пятницу как нерабочий день
Функция	`using_easter`	–	`boolean`	Проверить, является ли Пасха рабочим днем
Функция	`using_great_friday`	–	`boolean`	Проверить, является ли Страстная пятница рабочим днем
Функция	`version`	–	`cstring`	Версия схем

plvlex

Пакет основан на оригинальном PL/Vision LEXical analysis и добавляет специфичные для Oracle функции при работе с лексемами.

примечание

Данный пакет основан на ключевых словах Postgresql и не является полностью совместимым с Oracle.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`tokens`	`str::text`; `skip_spaces::boolean`; `qualified_names::boolean`	`SETOF record`; `pos::integer`; `token::text`; `code::int4`; `class::text`; `separator::text`; `mod::text`	Лексический парсер. Возвращаемые параметры: – `pos`: позиция лексемы; – `token`: лексема; – `code`: порядковый номер в классе для лексем, являющихся ключевыми словами и идентификаторами в Pangolin; – `class`: класс лексемы; – `separator`: разделитель; – `mod`: модификатор

plvstr

Пакет добавляет специфичные для Oracle функции при работе со строками и текстовыми данными.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Функция	`betwn`	`str::text,start::int4,_end::int4[,inclusive:boolean]` `str::text,start::text,_end::text[,startnth::int4,endth::int4[,inclusive::boolean,gotoend::boolean]]`	`text`	Поиск подстроки в пределах от `start` до `_end` символа
Функция	`instr`	`str::text,patt::text[,start::int4[,nth::int4]]`	`int4`	Поиск позиции подстроки
Функция	`is_prefix`	`str::int8,prefix::int8` `str::int4,prefix::int4` `str::text,prefix::text[,cs::boolean]`	`boolean`	Проверка, начинается ли искомая строка с определенного префикса
Функция	`left`	`str::text,n::int4`	`varchar`	Возвращает `n` символов с начала строки
Функция	`right`	`str::text,n::int4`	`varchar`	Возвращает `n` символов с конца строки
Функция	`lpart`	`str::text,div::text[,start::int4[,nth::int4[,allifnotfound::boolean]]]`	`text`	Возвращает подстроку, находящуюся до строки поиска
Функция	`rpart`	`str::text,div::text[,start::int4[,nth::int4[,allifnotfound::boolean]]]`	`text`	Возвращает подстроку, находящуюся после первого символа строки поиска
Функция	`lstrip`	`str::text,substr::text[,num::int4]`	`text`	Усекает строку слева, если строка начинается с поисковой строки
Функция	`rstrip`	`str::text,substr::text[,num::int4]`	`text`	Усекает строку справа, если строка заканчивается на поисковую строку
Функция	`rvrs`	`str::text,start::int4[,_end::int4]`	`text`	Реверсирует порядок символов в строке
Функция	`substr`	`str::text,start::int4[,len::int4]`	`varchar`	Возвращает подстроку, начиная с позиции `start` и длиной `len`
Функция	`swap`	`str::text,replace::text[,start::int4,length::int4]`	`text`	Поиск и замена подстроки `replace` в строке, начиная с позиции `start`, длиной `length`

plvsubst

Пакет добавляет специфичные для Oracle функции форматирования текста.

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Процедура	`setsubst`	`[ str::text]`	–	Задать маску поиска. Маска по умолчанию - `%s`
Функция	`subst`	–	`text`	Получить маску поиска
Функция	`string`	`template_in::text[,vals_in::text[,delim_in::text[,substr-in::text]]]` `template_in::text[,values_in::text[][,subst::text]]`	`text`	Применение форматирования по шаблону

Изменения уровня базы данных

Новые обьекты:

Тип	Имя	Входные переменные функции	Выходные переменные функции	Описание
Представление	`dual`	–	`dummy::varchar`	Специфичное для Oracle представление, необходимое для поддержки стандарта SQL
Тип	`dummy`	–	–	Специфичный для Oracle тип фиктивных данных, служащий для формирования корректного по форме запроса со всеми необходимыми полями и значениями
Тип	`varchar2`	–	–	Специфичный для Oracle тип текстовых данных `single-byte`
Тип	`nvarchar2`	–	–	Специфичный для Oracle тип текстовых данных `multi-byte`

Доработка

Доработка:

В скриптах установки схема public заменена на ext.

Ограничены привилегии.

Версия: 4.4.0.

В настоящее время используется нативный PL/PGSql. При миграции с Oracle процедуры переписываются вручную разработчиками. Расширение orafce представляет собой портирование части функциональных пакетов Oracle в базу данных PostgreSQL, а так же типов данных Oracle и функций для работы с этими типами данных.

Использование этого расширения позволяет сократить время для миграции баз данных с Oracle на Pangolin.

orafce принципиально не меняет модель работы с данными в Postgresql и не добавляет новых уровней поведения в модель Postgresql, в частности:

добавление пакетов Oracle не позволяет использовать переменные пакета; функциональный класс переменных отсутствует и не может быть портирован;
портирована модель Oracle 10g1.

В Oracle существует возможность объединить группу функций в один мета-объект – пакет. В Pangolin такая возможность отсутствует, а для объединения функций используются отдельные схемы базы данных.

В целях сокращения затрат при портировании БД Oracle, разработчикам предоставляются схемы данных:

Схема данных	Описание
`dbms_alert`	Межсессионное взаимодействие, message queue
`dbms_assert`	Проверки, дополнительные тесты
`dbms_output`	Логирование
`dbms_pipe`	Межсессионное взаимодействие, каналы
`dbms_random`	Генератор псевдослучайных значений
`dbms_utility`	Просмотр стека вызовов
`utl_file`	Работа с файловой системой
`plunit`	Assert-функции
`plvchr` `plvstr` `plvsubst`	Функции по работе с текстом
`plvdate`	Функции по работе с датами
`plvlex`	Семантический анализ запроса

Влияние на обеспечение безопасности хранимых данных:

Решение добавляет функции для работы с файловой системой (пакет utl_file). При эксплуатации решения следует явно разграничить права пользователей, имеющих право на работу с файловой системой. Добавляемые функции аналогичны существующим встроенным функциям СУБД Pangolin:
- pg_ls_dir();
- pg_stat_file();
- pg_read_file();
- pg_read_binary_file().
Решение добавляет функции межсессионного взаимодействия (пакет dbms_pipe, dbms_alert). При эксплуатации решения следует явно разграничить права пользователей, имеющих право на работу с механизмом межсессионного взаимодействия.
В целях обеспечения безопасности отозваны права PUBLIC на пакеты, нарушающие периметр БД:
- dbms_alert, dbms_pipe (межсессионное взаимодействие);
- dbms_output (вывод информации в консоль);
- dbms_utility (трассировка запросов);
- utl_file (работа с файловой системой).
Для использования этой функциональности следует явно указать права на использование схем для необходимых ролей.

Ограничения

Для эксплуатации решения необходимо придерживаться принципа установки одинаковых версий расширения на всех узлах кластера высокой доступности, используемых в потоковой и логической репликации.
Схему ext следует добавить в параметр search_path последней в порядке поиска.
Все дополнительные пакеты и функции являются дополнительной функциональностью, не меняют поведения продукта в целом и требуют дополнительного тестирования в рамках миграции БД.

Установка

Модуль считается «доверенным», поэтому его могут устанавливать пользователи, имеющие право CREATE в текущей базе данных:

CREATE EXTENSION IF NOT EXISTS orafce SCHEMA ext;

Настройка

Настройка не требуется.

Использование модуля

dbms_alert – модель межсессионного взаимодействия

Сессия 1	Сессия 2	Сессия 3	Комментарий
`SELECT dbms_alert.register('alert1');`	`SELECT dbms_alert.register('alert1');`	–	Регистрация очереди событий `alert1`
`SELECT * from dbms_alert.waitany(10);`	`SELECT * from dbms_alert.waitany(10);`	–	Ожидание событий в течение 10 секунд
–	–	`SELECT dbms_alert.signal('alert1','Alert 1');`	Добавление события `Alert 1` в очереди `alert1`

dbms_assert – дополнительные проверки в целях защиты от SQL injection

Сессия 1	Результат
`SELECT dbms_assert.enquote_literal(E'O\'Reilly');`	`'O''Reilly'`
`SELECT dbms_assert.enquote_name(E'O\'Reilly');`	`"o'reilly"`
`SELECT dbms_assert.enquote_name(E'O\'Reilly',false);`	`"O'Reilly"`
`SELECT dbms_assert.noop(E'O\'Reilly');`	`O'Reilly`
`SELECT dbms_assert.qualified_sql_name(E'O\'Reilly');`	`ERROR: string is not qualified SQL name`
`SELECT dbms_assert.qualified_sql_name(E'noop');`	`noop`
`SELECT dbms_assert.qualified_sql_name(E'noop1');`	`noop1`
`SELECT dbms_assert.qualified_sql_name(E'1noop');`	`1noop`
`SELECT dbms_assert.qualified_sql_name(E'noOP');`	`noOP`
`SELECT dbms_assert.schema_name(E'public');`	`public`
`SELECT dbms_assert.schema_name(E'noop');`	`ERROR: invalid schema name`
`SELECT dbms_assert.simple_sql_name(E'public');`	`public`
`SELECT dbms_assert.simple_sql_name(E'O\'Reilly');`	`ERROR: string is not simple SQL name`
`SELECT dbms_assert.object_name(E'object_name');`	`ERROR: invalid object name`
`SELECT dbms_assert.object_name(E'information_schema');`	`ERROR: invalid object name`
`SELECT dbms_assert.object_name(E'pg_class');`	`pg_class`

dbms_output – консольный вывод сообщений

SELECT dbms_output.enable()

 enable
--------

(1 row)

SELECT dbms_output.put(E'One\nTwo');

 put
-----

(1 row)

SELECT dbms_output.get_lines(2);

 get_lines
------------
 ("{""One  +
 Two""}",1)
(1 row)

SELECT dbms_output.get_line();

 get_line
----------
 (,1)
(1 row)

SELECT dbms_output.put_line(E'One\nTwo');

 put_line
----------

(1 row)

SELECT dbms_output.get_line();

 get_line
----------
 ("One   +
 Two",0)
(1 row)

SELECT dbms_output.serveroutput(true);

 serveroutput
--------------

(1 row)

SELECT dbms_output.put(E'One\nTwo');

 put
-----

(1 row)

SELECT dbms_output.get_line();

 get_line
----------
 ("One   +
 Two",0)
(1 row)

SELECT dbms_output.disable();

 disable
---------

(1 row)

dbms_pipe – эмуляция каналов Oracle

Сессия 1	Сессия 2	Комментарий
`SELECT dbms_pipe.create_pipe('pipe1',10,true);`		`Создание частного канала с именем pipe1`
`SELECT * from dbms_pipe.db_pipes;`		Список: `name \| items \| size \| limit \| private \| owner` `-------+-------+------+-------+---------+--------` `pipe1\| 0 \| 0 \| 10 \| t \| pguser` `(1 row)`
`SELECT * from dbms_pipe.pack_message(timestamp 'epoch'+interval '2 days');`
`SELECT * from dbms_pipe.pack_message((date 'epoch'+interval '2 days')::date);`
`SELECT * from dbms_pipe.pack_message(2::int4);`
`SELECT * from dbms_pipe.pack_message(2::int8);`
`SELECT * from dbms_pipe.pack_message(2::numeric);`
`SELECT * from dbms_pipe.pack_message(2::text);`
`SELECT * from dbms_pipe.send_message('pipe1',20,0);`		Вывод кода возврата: 0
	`SELECT dbms_pipe.receive_message('pipe1',1);`	Вывод кода возврата: 0
	`SELECT dbms_pipe.next_item_type();`	Вывод 13 (`timestamp`)
	`SELECT dbms_pipe.unpack_message_timestamp();`	Вывод `1970-01-03 00:00:00+03` (для таймзоны MSK)
	`SELECT dbms_pipe.next_item_type();`	Вывод 12 (`date`)
	`SELECT dbms_pipe.unpack_message_timestamp();`	ERROR: datatype mismatch
	`SELECT dbms_pipe.unpack_message_date();`	Вывод: `1970-01-03`
	`SELECT dbms_pipe.next_item_type();`	Вывод: 9 (`number`)
	`SELECT dbms_pipe.unpack_message_number();`	Вывод: 2
	`SELECT dbms_pipe.next_item_type();`	Вывод 9 (`number`)
	`SELECT dbms_pipe.unpack_message_number();`	Вывод: 2
	`SELECT dbms_pipe.next_item_type();`	Вывод 9 (`number`)
	`SELECT dbms_pipe.unpack_message_number();`	Вывод: 2
	`SELECT dbms_pipe.next_item_type();`	Вывод 11 (`text`)
	`SELECT dbms_pipe.unpack_message_text();`	Вывод: 2
	`SELECT dbms_pipe.next_item_type();`	Вывод: 0 (конец канала)
`SELECT * from dbms_pipe.remove_pipe('pipe1');`

dbms_random – псевдослучайные числа Oracle

Сессия 1	Результат
`SELECT dbms_random.initialize(ceil(random()*1000)::int4);`
`SELECT dbms_random.normal();`	`-0.0777241069451229`
`SELECT dbms_random.normal();`	`0.498490513945213`
`SELECT dbms_random.string('u',10);`	`UREURVOTTQ`
`SELECT dbms_random.string('l',10);`	`jjrfoxiqrz`
`SELECT dbms_random.string('a',15);`	`hjNFMsoOWNyKvGz`
`SELECT dbms_random.string('x',10);`	`2G1T7O6KCD`
`SELECT dbms_random.string('p',15);`	`0]I{VU"0m."HAW}`
`SELECT dbms_random.value(-10,10);`	`0.17908088862896`
`SELECT dbms_random.terminate();`

dbms_utility – просмотр стека вызовов

do
$$
    declare
        res text;
    begin
        SELECT dbms_utility.format_call_stack() into res;
        raise notice 'Call stack: %',res;
    end
$$;

Пример результата запроса:

NOTICE:  Call stack: ----- PL/pgSQL Call Stack -----
  object     line  object
  handle   number  name
       0           function anonymous object
       0        5  function anonymous object
DO

utl_file – операции с файловой системой

INSERT INTO utl_file.utl_file_dir(dir,dirname) VALUES ('temp','/tmp');
COPY (SELECT * FROM pg_settings) TO '/tmp/pg_settings.csv';

Содержимое файла /tmp/pg_settings.csv:

do
$$
declare
    f utl_file.file_type;
begin
    if (SELECT fexists from utl_file.fgetattr('temp','pg_settings.csv')) then
        f := utl_file.fopen('temp', 'pg_settings.csv', '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 if;
end;
$$;

SELECT utl_file.fremove('temp','pg_settings.csv');

SELECT utl_file.fclose_all();

plunit – функции проверок

SELECT plunit.assert_equals(clock_timestamp(),current_timestamp,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_equals).

SELECT plunit.assert_equals(clock_timestamp()::date,current_timestamp::date,'Failed');

 assert_equals
---------------

(1 row)

SELECT plunit.assert_not_equals(clock_timestamp(),current_timestamp,'Failed');

 assert_not_equals
-------------------

(1 row)

SELECT plunit.assert_not_equals(clock_timestamp()::date,current_timestamp::date,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_not_equals).

SELECT plunit.assert_false(clock_timestamp()::date=current_timestamp::date,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_false).

SELECT plunit.assert_true(clock_timestamp()=current_timestamp,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_true).

SELECT plunit.assert_not_null(clock_timestamp(),'Failed');

 assert_not_null
-----------------

(1 row)

SELECT plunit.assert_null(clock_timestamp(),'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_null).

SELECT plunit.fail('Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation (assert_fail).

plvchr – специфичные для Oracle функции при работе с текстом

Сессия 1	Результат
`SELECT plvchr.char_name('Pangolin');`	`P`
`SELECT plvchr.first('Pangolin');`	`P`
`SELECT plvchr.last('Pangolin');`	`n`
`SELECT plvchr.nth('Pangolin',2);`	`a`
`SELECT plvchr.nth('Pangolin',-2);`	`i`
`SELECT plvchr.quoted1('Pangolin');`	`'Pangolin'`
`SELECT plvchr.quoted2('Pangolin');`	`"Pangolin"`
`SELECT plvchr.stripped('Pangolin','Pango');`	`li`
`SELECT plvchr.stripped('Pangolin','pango');`	`Pli`
`SELECT plvchr.is_blank('Pangolin');`	`f`, `false`
`SELECT plvchr.is_digit('Pangolin');`	`f`, `false`
`SELECT plvchr.is_letter('Pangolin');`	`t`, `true`
`SELECT plvchr.is_other('Pangolin');`	`f`, `false`
`SELECT plvchr.is_quote('Pangolin');`	`f`, `false`
`SELECT plvchr.is_quote('''Pangolin"');`	`t`, `true`

plvdate – специфичные для Oracle функции при работе с датами

Сессия 1	Результат
`SELECT plvdate.default_holidays('Russia');`
`SELECT plvdate.isleapyear('2020-01-01');`	`true`
`SELECT plvdate.isleapyear('2021-01-01');`	`false`
`SELECT plvdate.isbizday('2023-03-08');`	`false`
`SELECT plvdate.isbizday('2021-02-22');`	`true`
`SELECT plvdate.add_bizdays('2021-02-22',15);`	`2021-03-15`
`SELECT plvdate.bizdays_between('2021-04-30','2021-05-10');`	`7`
`SELECT plvdate.days_inmonth('2023-02-01');`	`28`
`SELECT plvdate.nearest_bizday('2023-02-22');`	`2023-02-21`
`SELECT plvdate.next_bizday('2023-03-03');`	`2023-03-06`
`SELECT plvdate.prev_bizday('2023-03-05');`	`2023-03-03`
`SELECT plvdate.set_nonbizday('2021-02-22',false);`
`SELECT plvdate.unset_nonbizday('2021-02-20',false);`	`ERROR: nonbizday unregisteration error` `DETAIL: Nonbizday not found.`
`SELECT plvdate.using_easter();`	`false`
`SELECT plvdate.using_greater_friday();`	`false`
`SELECT plvdate.version();`	`version` `-----------------------------------------------` `PostgreSQL PLVdate, version 3.7, October 2018` `(1 row)`

plvlex – специфичные для Oracle функции при работе с лексемами

SELECT * from plvlex.tokens ('
    SELECT  n.nspname                   AS schemaname,
            c.relname                   AS viewname,
            pg_get_userbyid(c.relowner) AS viewowner,
            pg_get_viewdef(c.oid)       AS definition
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = ''v''::"char";'
    ,true,true);

Пример результата выполнения запроса:

 pos |      token      | code |  class  | separator |   mod
-----+-----------------+------+---------+-----------+----------
| SELECT          |  604 | KEYWORD |           |
| n.nspname       |      | IDENT   |           |
| as              |  290 | KEYWORD |           |
| schemaname      |      | IDENT   |           |
| ,               |   44 | OTHERS  |           | self
| c.relname       |      | IDENT   |           |
| as              |  290 | KEYWORD |           |
| viewname        |      | IDENT   |           |
| ,               |   44 | OTHERS  |           | self
| pg_get_userbyid |      | IDENT   |           |
| (               |   40 | OTHERS  |           | self
| c.relowner      |      | IDENT   |           |
| )               |   41 | OTHERS  |           | self
| as              |  290 | KEYWORD |           |
| viewowner       |      | IDENT   |           |
| ,               |   44 | OTHERS  |           | self
| pg_get_viewdef  |      | IDENT   |           |
| (               |   40 | OTHERS  |           | self
| c.oid           |      | IDENT   |           |
| )               |   41 | OTHERS  |           | self
| as              |  290 | KEYWORD |           |
| definition      |      | IDENT   |           |
| from            |  418 | KEYWORD |           |
| pg_class        |      | IDENT   |           |
| c               |      | IDENT   |           |
| left            |  477 | KEYWORD |           |
| join            |  467 | KEYWORD |           |
| pg_namespace    |      | IDENT   |           |
| n               |      | IDENT   |           |
| on              |  524 | KEYWORD |           |
| n.oid           |      | IDENT   |           |
| =               |   61 | OTHERS  |           | self
| c.relnamespace  |      | IDENT   |           |
| where           |  689 | KEYWORD |           |
| c.relkind       |      | IDENT   |           |
| =               |   61 | OTHERS  |           | self
| v               |      | SCONST  |           | qs
| v               |  267 | OTHERS  |           | typecast
| char            |      | IDENT   |           | dq
| ;               |   59 | OTHERS  |           | self
(40 rows)

plvstr – специфичные для Oracle функции при работе со строками и текстовыми данными

Сессия 1	Результат
`SELECT plvstr.betwn('Pangolin','go','i');`	`goli`
`SELECT plvstr.instr('Pangolin','go');`	`4`
`SELECT plvstr.is_prefix('Pangolin','Pan');`	`true`
`SELECT plvstr.is_prefix('Pangolin','pan');`	`false`
`SELECT plvstr.left('Pangolin',5);`	`Pango`
`SELECT plvstr.right('Pangolin',5);`	`golin`
`SELECT plvstr.lpart('Pangolin','go');`	`Pan`
`SELECT plvstr.rpart('Pangolin','go');`	`olin`
`SELECT plvstr.lstrip('Pangolin','go');`	`Pangolin`
`SELECT plvstr.lstrip('Pangolin','Pan');`	`golin`
`SELECT plvstr.rstrip('Pangolin','lin');`	`Pango`
`SELECT plvstr.rvrs('Pangolin');`	`nilognaP`
`SELECT plvstr.substr('Pangolin',5);`	`olin`
`SELECT plvstr.swap('Pangolin','go')`	`gongolin`

plvsubst – специфичные для Oracle функции форматирования текста

Применить форматирования по шаблону:

SELECT plvsubst.string('%s codename %s','Postgresql,Pangolin');

или

SELECT plvsubst.string('%s codename %s',ARRAY['Postgresql','Pangolin']);

Результат выполнения запросов:

            string
------------------------------
 Postgresql codename Pangolin
(1 row)

Получить маску поиска:
```
SELECT plvsubst.subst();
```
Результат выполнения запросов:
```
 subst
-------
 %s
(1 row)
```

Ссылки на документацию разработчика

Дополнительно поставляемый модуль orafce: https://github.com/orafce/orafce.

Функционал​

dbms_alert​

dbms_assert​

dbms_output​

dbms_pipe​

dbms_random​

dbms_utility​

utl_file​

plunit​

plvchr​

plvdate​

plvlex​

plvstr​

plvsubst​

Изменения уровня базы данных​

Доработка​

Ограничения​

Установка​

Настройка​

Использование модуля​

dbms_alert – модель межсессионного взаимодействия​

dbms_assert – дополнительные проверки в целях защиты от SQL injection​

dbms_output – консольный вывод сообщений​

dbms_pipe – эмуляция каналов Oracle​

dbms_random – псевдослучайные числа Oracle​

dbms_utility – просмотр стека вызовов​

utl_file – операции с файловой системой​

plunit – функции проверок​

plvchr – специфичные для Oracle функции при работе с текстом​

plvdate – специфичные для Oracle функции при работе с датами​

plvlex – специфичные для Oracle функции при работе с лексемами​

plvstr – специфичные для Oracle функции при работе со строками и текстовыми данными​

plvsubst – специфичные для Oracle функции форматирования текста​

Ссылки на документацию разработчика​