orafce — funkcje Oracle w PostgreSQL. Migracja bez bólu

Migracja z Oracle na PostgreSQL to jedno z popularniejszych wyzwań w nowoczesnych projektach bazodanowych. Firmy decydują się na taki krok z powodów licencyjnych, kosztowych lub architektonicznych. Problem? Oracle i PostgreSQL różnią się w wielu detalach — funkcje mają inne nazwy, inaczej obsługują NULL-e, inaczej formatują daty.

Tutaj wchodzi orafce — Oracle Functions and Packages for PostgreSQL. To darmowe, open-source rozszerzenie, które dostarcza setki funkcji i pakietów zgodnych z Oracle, bezpośrednio w PostgreSQL. Dzięki niemu wiele zapytań migruje się niemal bez zmian.

Czym jest orafce?

orafce (github.com/orafce/orafce) to rozszerzenie PostgreSQL aktywnie rozwijane od ponad 15 lat. Wersja 4.x obsługuje PostgreSQL 13–17. Dostarcza:

Instalacja orafce

Debian / Ubuntu

# Dla PostgreSQL 16
sudo apt install postgresql-16-orafce

# Dla PostgreSQL 17
sudo apt install postgresql-17-orafce

Red Hat / Rocky Linux / AlmaLinux

# Repozytorium PGDG musi być już skonfigurowane
sudo dnf install orafce_16

# Lub dla PG 17
sudo dnf install orafce_17

Aktywacja w bazie

-- W psql, jako superuser lub użytkownik z CREATE EXTENSION
CREATE EXTENSION orafce;

-- Weryfikacja
SELECT extname, extversion FROM pg_extension WHERE extname = 'orafce';
--  extname | extversion
-- ---------+------------
--  orafce  | 4.14.1

orafce tworzy swój schemat oracle (lub dbms_output). Funkcje są dostępne po kwalifikacji schematu (oracle.nvl()) lub po dodaniu schematu do search_path:

SET search_path = "$user", public, oracle;

Funkcje obsługi NULL — NVL i DECODE

W Oracle NVL(wyrażenie, wartość_zastępcza) jest używane wszędzie zamiast COALESCE. orafce dostarcza go jako pełny odpowiednik:

-- Oracle i orafce: zwróć 0 jeśli wartość NULL
SELECT NVL(prowizja, 0) AS prowizja_bezpieczna
FROM pracownicy;

-- NVL2: inna wartość gdy NULL, inna gdy NOT NULL
SELECT NVL2(premia, 'Ma premię', 'Brak premii') AS status
FROM pracownicy;

-- DECODE: wielowarunkowa funkcja (odpowiednik CASE WHEN)
SELECT DECODE(dzial_id,
  10, 'Księgowość',
  20, 'Sprzedaż',
  30, 'IT',
  'Inny dział'   -- wartość domyślna
) AS nazwa_dzialu
FROM pracownicy;

Ważna różnica: Oracle traktuje NULL = NULL jako TRUE w DECODE. orafce zachowuje to samo zachowanie — w przeciwieństwie do PostgreSQL CASE WHEN.

-- Oracle i orafce: DECODE obsługuje NULL = NULL jako TRUE
SELECT DECODE(NULL, NULL, 'jest null', 'nie null');
-- Wynik: 'jest null'  ✓

-- Pure PostgreSQL CASE: NULL != NULL (dlatego potrzeba IS NULL)
SELECT CASE NULL WHEN NULL THEN 'jest null' ELSE 'nie null' END;
-- Wynik: 'nie null'  ← inne zachowanie!

Funkcje datowe — ADD_MONTHS, LAST_DAY, NEXT_DAY

To jedne z najczęściej używanych funkcji Oracle w aplikacjach biznesowych. W czystym PostgreSQL wymagają nieporęcznych wyrażeń z INTERVAL i date_trunc. orafce przywraca znajomą składnię:

-- ADD_MONTHS: dodaj N miesięcy do daty
SELECT ADD_MONTHS('2026-01-31'::date, 1);
-- Wynik: 2026-02-28  (Oracle: zaokrągla do końca miesiąca jeśli dzień nie istnieje)

SELECT ADD_MONTHS('2026-03-15'::date, -3);
-- Wynik: 2025-12-15

-- LAST_DAY: ostatni dzień miesiąca
SELECT LAST_DAY('2026-02-10'::date);
-- Wynik: 2026-02-28

-- NEXT_DAY: następna podana nazwa dnia tygodnia
SELECT NEXT_DAY('2026-06-09'::date, 'MONDAY');
-- Wynik: 2026-06-15 (następny poniedziałek)

-- MONTHS_BETWEEN: ile miesięcy między datami (wynik ułamkowy)
SELECT MONTHS_BETWEEN('2026-06-15'::date, '2026-01-01'::date);
-- Wynik: 5.45... (5 miesięcy i kilka dni)

-- TRUNC(date): odcięcie do początku okresu (jak Oracle TRUNC z maską)
SELECT TRUNC('2026-06-15 14:35:22'::timestamp, 'MM');
-- Wynik: 2026-06-01 00:00:00  (początek miesiąca)

SELECT TRUNC('2026-06-15 14:35:22'::timestamp, 'YYYY');
-- Wynik: 2026-01-01 00:00:00  (początek roku)

Funkcje tekstowe — INSTR, SUBSTR, LPAD

Oracle i PostgreSQL mają funkcje tekstowe o podobnych nazwach, ale różnej semantyce. orafce ujednolica zachowanie:

-- INSTR: pozycja podciągu (1-based, jak Oracle; 0 = nie znaleziono)
SELECT INSTR('Hello World', 'World');      -- 7
SELECT INSTR('Hello World', 'xyz');        -- 0 (Oracle: 0, nie NULL)
SELECT INSTR('abcabc', 'bc', 1, 2);        -- 5 (drugie wystąpienie)

-- SUBSTR (Oracle-style): SUBSTR(str, start, len) — 0 traktowane jak 1
SELECT SUBSTR('PostgreSQL', 1, 4);        -- 'Post'
SELECT SUBSTR('PostgreSQL', 0, 4);        -- 'Pos' (Oracle: 0 = 1)

-- LPAD/RPAD z Oracle semantics (NULL = pusty string)
SELECT LPAD('42', 6, '0');               -- '000042'

-- LTRIM/RTRIM z listą znaków do odcięcia (jak Oracle)
SELECT LTRIM('###hello###', '#');         -- 'hello###'

-- CONCAT NULL-safe: Oracle traktuje NULL || 'x' = 'x'
SET search_path = oracle, public;
SELECT NULL || 'PostgreSQL';             -- 'PostgreSQL' (Oracle behavior)
-- Standard PostgreSQL: NULL || 'PostgreSQL' = NULL

LISTAGG — agregacja do stringa

Oracle LISTAGG to odpowiednik PostgreSQL STRING_AGG, ale ze składnią Oracle. orafce dostarcza go w pełnej formie:

-- Oracle LISTAGG w orafce
SELECT dzial_id,
       LISTAGG(nazwisko, ', ') WITHIN GROUP (ORDER BY nazwisko) AS pracownicy
FROM pracownicy
GROUP BY dzial_id;

-- Odpowiednik w czystym PostgreSQL
SELECT dzial_id,
       STRING_AGG(nazwisko, ', ' ORDER BY nazwisko) AS pracownicy
FROM pracownicy
GROUP BY dzial_id;

DBMS_OUTPUT — debug w PL/pgSQL

Programiści Oracle przyzwyczajeni są do DBMS_OUTPUT.PUT_LINE do debugowania procedur. orafce dostarcza pełny odpowiednik działający w PL/pgSQL:

-- Włącz bufor DBMS_OUTPUT (opcjonalne, domyślnie włączony)
SELECT dbms_output.enable(1000000);

-- Użycie w bloku PL/pgSQL
DO $$
BEGIN
    dbms_output.put_line('Przetwarzanie zamówień...');

    FOR rec IN SELECT id, kwota FROM zamowienia WHERE status = 'NEW'
    LOOP
        dbms_output.put_line('Zamówienie ' || rec.id || ': ' || rec.kwota || ' PLN');
    END LOOP;

    dbms_output.put_line('Gotowe.');
END;
$$ LANGUAGE plpgsql;

-- Odczyt bufora
SELECT * FROM dbms_output.get_lines(100);

Praktyczny przykład migracji

Poniżej realne zapytanie Oracle i jego odpowiednik w PostgreSQL — z orafce i bez:

-- === Oryginalne zapytanie Oracle ===
SELECT
    e.employee_id,
    NVL(e.last_name, '(brak)') AS nazwisko,
    DECODE(e.job_id, 'CLERK', 'Asystent', 'MANAGER', 'Kierownik', 'Pracownik') AS stanowisko,
    ADD_MONTHS(e.hire_date, 12) AS koniec_okresu_probnego,
    MONTHS_BETWEEN(SYSDATE, e.hire_date) AS staz_miesiecy,
    LAST_DAY(e.hire_date) AS ostatni_dzien_miesiaca_zatrudnienia
FROM employees e
WHERE INSTR(e.email, '@jsystems') > 0;

-- === PostgreSQL + orafce (bez zmian!) ===
-- Wystarczy: CREATE EXTENSION orafce; SET search_path = oracle, public;
SELECT
    e.employee_id,
    NVL(e.last_name, '(brak)') AS nazwisko,
    DECODE(e.job_id, 'CLERK', 'Asystent', 'MANAGER', 'Kierownik', 'Pracownik') AS stanowisko,
    ADD_MONTHS(e.hire_date, 12) AS koniec_okresu_probnego,
    MONTHS_BETWEEN(NOW()::date, e.hire_date) AS staz_miesiecy,
    LAST_DAY(e.hire_date) AS ostatni_dzien_miesiaca_zatrudnienia
FROM employees e
WHERE INSTR(e.email, '@jsystems') > 0;

-- === PostgreSQL bez orafce (wymaga przepisania) ===
SELECT
    e.employee_id,
    COALESCE(e.last_name, '(brak)') AS nazwisko,
    CASE e.job_id
        WHEN 'CLERK'   THEN 'Asystent'
        WHEN 'MANAGER' THEN 'Kierownik'
        ELSE 'Pracownik'
    END AS stanowisko,
    (e.hire_date + INTERVAL '12 months')::date AS koniec_okresu_probnego,
    EXTRACT(YEAR FROM age(NOW(), e.hire_date)) * 12
      + EXTRACT(MONTH FROM age(NOW(), e.hire_date)) AS staz_miesiecy,
    DATE_TRUNC('month', e.hire_date) + INTERVAL '1 month' - INTERVAL '1 day' AS ostatni_dzien
FROM employees e
WHERE POSITION('@jsystems' IN e.email) > 0;

Widać wyraźnie — orafce eliminuje konieczność przepisywania setek linii zapytań.

Na co uważać — różnice od Oracle

orafce nie jest 100% zgodny z Oracle. Kilka ważnych różnic:

Kiedy używać orafce, kiedy przepisać?

orafce warto użyć gdy:

• Masz dużą bazę kodu SQL z funkcjami Oracle i chcesz zmigrować szybko
• Aplikacja używa głównie NVL, DECODE, ADD_MONTHS, LAST_DAY
• Kod PL/SQL korzysta z DBMS_OUTPUT do debugowania
• Chcesz uruchomić aplikację Oracle na PostgreSQL bez ryzyka i kosztów pełnego przepisania

Lepiej przepisać natywnie gdy:

• Aplikacja jest nowo tworzona od zera — pisz idiomatycznie PostgreSQL
• Używasz CONNECT BY, ROWNUM, MERGE (złożona logika hierarchiczna)
• Zależy Ci na maksymalnej wydajności — natywne funkcje PostgreSQL są zoptymalizowane pod jego planner
• Masz mały, ograniczony kod SQL — przepisanie jest tańsze niż utrzymanie warstwy orafce

W praktyce najczęstsza strategia to hybrydowe podejście: orafce na start, by aplikacja działała od razu, a potem stopniowe zastępowanie funkcji natywnymi odpowiednikami w kolejnych sprintach.