🎵 «Тот мужик поклоны бьет, отвечает вежливо:
"Я не вор, я не шпион, я вообще-то - дух,-
За свободу за мою - захотите ежели вы -
Изобью за вас любого, можно даже двух!"
Тут я понял: это - джинн,- он ведь может многое -
Он же может мне сказать: "Враз озолочу!"...
"Ваше предложение,- говорю,- убогое.
Морды будем после бить - я вина хочу!» — Владимир Высоцкий

🔍 Проверка существования уникальной записи, или «Развенчание мифов»
В предыдущем опросе приняло участие 47 подписчиков — всем большое спасибо за неравнодушие 🙌

Честно сказать, я никогда глубоко не исследовал эту тему.
По наивности думал, что разницы нет, ведь самое тяжёлое в БД — это чтение с диска.
Всё остальное так или иначе оптимизировано и работает приемлемо.

Но коллеги настаивали на своём подходе — и я решил проверить и убедиться сам.

🧪 Начинаем эксперимент

Создадим тестовую таблицу и заполним её данными.
id заполнил значениями от 1 до 1 млн, но только нечётными.
Итого получилось 500 тыс. записей.

CREATE TABLE test_2025_11_23
(
  id       NUMBER PRIMARY KEY,
  email    VARCHAR2(200)
);

INSERT /*+ APPEND */
  INTO test_2025_11_23 (id, email)
  SELECT a.id, a.email
    FROM (
          SELECT LEVEL AS id,
                 'user_' || TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 9))) || '@example.com' AS email
            FROM DUAL
          CONNECT BY LEVEL <= POWER(10, 6)
         ) a
   WHERE MOD(a.id, 2) = 1;

COMMIT;

Далее исследуем «мини»-трассировкой три варианта проверки существования строки по уникальному индексу.
В цикле — 10 млн итераций, id вычисляется случайно от 1 до 1 млн.

⚙️ Вариант 1 — через EXCEPTION NO_DATA_FOUND

DECLARE
  test_id     test_2025_11_23.id%TYPE;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT t.id
    INTO test_id
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  ids_count := ids_count + 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    non_ids_count := non_ids_count + 1;
END;

⚙️ Вариант 2 — через EXISTS

От себя могу добавить: этот вариант я всегда считал самым логичным и читаемым.
Вообще люблю, когда код отражает логику:
проверяем существование → используем EXISTS. Логично! ✔️

DECLARE
  is_exists_id   VARCHAR2(1);
  random_id      test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT CASE
           WHEN EXISTS (SELECT 1
                          FROM test_2025_11_23 t
                         WHERE t.id = get_count_ids.random_id)
           THEN 'Y'
           ELSE 'N'
         END
    INTO is_exists_id
    FROM DUAL;

  IF is_exists_id = 'Y' THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

⚙️ Вариант 3 — через MAX

DECLARE
  id_count    NUMBER;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT MAX(t.id)
    INTO id_count
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  IF id_count IS NOT NULL THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

📊 Результаты

Эксперимент проводился на «разогретой» базе — все блоки таблицы были в кэше

variant1_with_exception_no_data_found
ids_count = 4998770, non_ids_count = 5001230
time: 00:02:23.434
consistent gets: 30000000
CPU: 14262

variant1_with_exists
ids_count = 4997912, non_ids_count = 5002088
time: 00:02:00.054
consistent gets: 30000000
CPU: 11942

variant1_with_max
ids_count = 4999842, non_ids_count = 5000158
time: 00:02:03.955
consistent gets: 30000000
CPU: 12377

🏁 Итог
Самый оптимальный вариант — через EXISTS.
Он быстрее и по времени, и по использованному CPU.
Исходники теста в приложении!

А выводы… делайте сами!

👍 Интересно или неожиданно!
👎 Скучно или ожидаемо!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Хотите сказать - скажите!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Глеб Жеглов и Володя Шарапов за столом засиделись не зря
Глеб Жеглов и Володя Шарапов ловят банду и главаря!
Расцвела буйным цветом малина, разухабилась разная тварь
Хлеба нет, а полно гуталина, да глумится горбатый главарь»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка?

На днях получил задачу найти баг в коде. Задача оказалась нетривиальной: код незнакомый, пакет — больше 10 тысяч строк, ошибка — где-то внутри метода примерно на 1500 строк.

В общем, «развлечение» на четыре часа.
Но ошибка — интересная!

📌 Суть
Допустим, есть строка, в которой лежит форматированный SQL-запрос.
Запрос парсится на наличие подзапросов, которые сохраняются в коллекцию, а в исходной строке — временно заменяются.
Потом должны вернуться обратно.

Но что-то пошло не так.
Задача: выяснить, где ошибка, и предложить простой вариант исправления.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  -- return subselects
  if v_child_selects.count > 0
  then
  for i in 1..v_child_selects.count
    loop
    if instr(str,'@' || i || '@') > 0
    then
     str := replace(str,
                      '@'||i||'@',
                      v_child_selects(i)
                      );
    end if;
    end loop;
  end if;
  
  dbms_output.put_line ('str=' || str);
END;

📤 Результат

str=/*4_child_start*/(/**/select1 /*3_child_start*/@3@/*3_child_end*/ /*2_child_start*/@2@/*2_child_end*/ )/*4_child_end*/ 

❓ Интересен ли вам такой формат или эта ошибка понравилась только мне?

👍 Формат интересен!
👎 Своих проблем хватает!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Есть что написать — пишите! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «До утра не погаснет окошко —
Глеб Жеглов и Володя не спят.
Пресловутая «Чёрная кошка» забоится наших ребят!
Глеб Жеглов и Володя Шарапов заслужили в боях ордена —
После мирного дня трудового будь спокойна, родная страна!»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка. Разбор

Вот такой legacy-код необходимо поддерживать. Руки чешутся всё переделать, но нельзя: при любом рефакторинге велик риск изменить логику — и тогда все труды обесценятся, а то и вовсе навредят.
А главное правило при разработке, как у врача, — «Не навреди!» ⚕️
Такой код у новичков вызывает шок, но, на самом деле, это весьма лайтовый вариант - бывает куда хуже!

Читатель Алексей Онин обладает поразительной наблюдательностью — и уже не раз это доказал.
Он правильно отметил, что ошибка в том, что в цикле коллекция наполняется подзапросами в прямом порядке:
от самого верхнего уровня до самого глубокого.
Чтобы подмену провести корректно, замену нужно делать в обратном порядке 🔄.

🎯 Задача: исправленный вариант.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      dbms_output.put_line ('rec.childs_select=' || rec.childs_select || ',' || TO_CHAR (rec.is_child));
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  IF v_child_selects.COUNT > 0
  THEN
   <<replace_subselects_back>>
    DECLARE
      index#   PLS_INTEGER;
    BEGIN
      index# := v_child_selects.LAST;                           -- REVERSE is needed

      WHILE index# IS NOT NULL
      LOOP
        IF INSTR (str, '@' || TO_CHAR (index#) || '@') > 0
        THEN
          str :=
            REPLACE (str, '@' || TO_CHAR (index#) || '@', v_child_selects (index#));
        END IF;

        index# := v_child_selects.PRIOR (index#);
      END LOOP;
    END;
  END IF;

  DBMS_OUTPUT.put_line ('str=' || str);
END;

📤 Результат — ровно то, что и ожидалось:

str=/*4_child_start*/(/**/select1 /*3_child_start*/(/**/select2 /*1_child_start*/(/**/select4)/*1_child_end*/ )/*3_child_end*/ /*2_child_start*/(/**/select3)/*2_child_end*/ )/*4_child_end*/

Дополнительно я изменил проход по ассоциативному массиву. Мы уже подробно разбирали эту тему.
Объяснять, почему так, — не буду 😉.

👍 Хочу ещё разбор интересных ошибок!
👎 Давайте что-то другое!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Пишите комменты: не для меня или себя, а просто так! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Сколько лет прошло, всё о том же гудят провода
Всё того же ждут самолёты
Девочка с глазами из самого синего льда
Тает под огнём пулемёта
Должен же растаять хоть кто-то» — Александр Васильев

🔒 Deadlock в одной сессии? Да, это возможно!

Что «нефиг делать» для одних — то «не знаю» для других.
Такой вопрос мне частенько задавали устно на собеседованиях, и, наверное, поэтому я так хорошо его запомнил.

Обычно deadlock представляют как ситуацию, когда две разные сессии ждут друг друга.
Но Oracle умеет удивлять — словить deadlock можно даже внутри одной сессии.

Вот минимальный пример:

DECLARE
  v_id   t.id%TYPE := 1;

  PROCEDURE update_t (id IN t.id%TYPE)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE t
       SET t.something = 111
     WHERE t.id = update_t.id;

    COMMIT;
  END update_t;
BEGIN
  UPDATE t
     SET t.something = 222
   WHERE t.id = v_id;

  update_t (id => v_id);

  COMMIT;
END;

🧩 Что здесь происходит

Главный блок начинает транзакцию и ставит блокировку на строку t.id = 1.
Затем вызывается процедура update_t, помеченная как PRAGMA AUTONOMOUS_TRANSACTION — это новая независимая транзакция, работающая в рамках той же сессии.
Внутри автономной транзакции мы снова пытаемся обновить ту же строку t.id = 1.
Но строка уже заблокирована внешним UPDATE.
Oracle пытается получить блокировку… у самой себя.
Ждать сам себя он, конечно, не может — и возникает классический deadlock:
ORA-00060: deadlock detected while waiting for resource

🔍 Почему Oracle не ждёт сам себя?

Потому что автономная транзакция — это полноценная независимая транзакция со своим собственным контекстом блокировок (lock manager context).
Oracle видит две конкурирующие транзакции внутри одной сессии и прекращает ожидание, считая его заведомо бесперспективным.

🧠 Важный вывод

AUTONOMOUS_TRANSACTION — мощный инструмент,
но на практике оправдан, по моему опыту, только в двух случаях:
Логирование (особенно ошибок).
Выполнение DDL-операций как в «оболочке», чтобы скрыть неявный COMMIT для основной транзакции.

👍 Тема автономок интересна - хочу ещё!
👎 Тема автономок не интересна!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Если есть время - уделите 30 секунд комменту!👇
#Cases #SQL #Oracle #PLSQL

🎵 «У леса на опушке
Жила Зима в избушке.
Она снежки солила
В берёзовой кадушке.
Она сучила пряжу,
Она ткала холсты,
Ковала ледяные
Над реками мосты» — Сергей Островой

🧵 Автономка и DDL операции

По реакции видно, что тема автономных транзакций зашла 🔥
Не отпускаем её и посмотрим на другой важный аспект — неявный коммит в DDL.

Для этого возьмём такую задачу 👇

--DROP TABLE test_2025_12_01 PURGE;

CREATE TABLE test_2025_12_01
(
  id           NUMBER PRIMARY KEY,
  something    NUMBER
);

DECLARE
  PROCEDURE update_execute_rollback (execute_ddl IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE test_2025_12_01 t
       SET t.something = NVL (t.something, 0) + 1
     WHERE t.id = 1;

    EXECUTE IMMEDIATE execute_ddl;

    ROLLBACK;
  END update_execute_rollback;
BEGIN
  INSERT INTO test_2025_12_01 (id, something)
       VALUES (1, 1);

  COMMIT;

  update_execute_rollback (
    execute_ddl   => q'{COMMENT ON TABLE test_2025_12_01 IS 'test_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{COMMENT ON COLUMN test_2025_12_01.id IS 'test_column_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{ALTER TABLE test_2025_12_01 ADD CONSTRAINT test_2025_12_01_chk01 CHECK (something > 0)}');

  update_execute_rollback (
    execute_ddl   =>
      q'{CREATE INDEX test_2025_12_01_i01 ON test_2025_12_01 (something)}');

 <<print_something>>
  DECLARE
    something   test_2025_12_01.something%TYPE;
  BEGIN
    SELECT t.something
      INTO something
      FROM test_2025_12_01 t
     WHERE t.id = 1;

    DBMS_OUTPUT.put_line ('something =' || TO_CHAR (something));
  END print_something;
END;

❓ Вопрос к вам

Как по-вашему, какое значение будет выведено в консоль?
И главное — почему именно такое?

👍 Славно, славно!
👎 Кошмар и ужас!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Каждый имеет право высказаться в рамках приличия!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Вот и ко мне пришла беда —
стартер заел, —
Теперь уж это не езда,
а ерзанье.
И надо б выйти, подтолкнуть —
но прыти нет, —
Авось подъедет кто-нибудь
и вытянет». — Владимир Высоцкий

🎯 Практическая задача получения списка уникальных символов.

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

Формулируется она очень просто: требуется из строкового поля, по всем строкам большой таблицы, проанализировать и получить список всех уникальных символов, которые могут встречаться в этом поле.

Умение решать такие задачи — ценный навык: детали могут быть разные, но по сути техник решения подобных задача несколько, и трудно придумать новый велосипед 🚲🙂.

Итак, формальная постановка:
есть большая таблица (от 1 млн до 1 млрд записей).

CREATE TABLE test_texts (
  id   NUMBER PRIMARY KEY,
  text VARCHAR2(4000)
);

INSERT INTO test_texts (id, text)
VALUES (1, '11qqq%%11qq^^');

INSERT INTO test_texts (id, text)
VALUES (2, '7766&&^^qqEE11');

COMMIT;

Необходимо получить список уникальных символов (порядок не важен):

'1', 'q', '%', '^', '7', '6', '&', 'E'

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

👍 Задача моей мечты!
👎 Хорошо, что мне такая задача не попалась!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 С нетерпением жду ваши варианты решения!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Сам виноват — и слёзы лью, и охаю:
Попал в чужую колею глубокую.
Я цели намечал свои на выбор сам —
А вот теперь из колеи не выбраться.
Крутые скользкие края имеет эта колея.
Я кляну проложивших её — скоро лопнет терпенье моё —
И склоняю, как школьник плохой:
Колею, в колее, с колеей…» — Владимир Высоцкий

🔬 Эксперимент: как быстрее всего получить набор уникальных символов из больших текстов?

Как я измучился с этой задачей! Кто бы знал! 😅
— «Зачем тебе это надо?» — спрашивает меня моя любимая женушка.
— «Мне это надо! Мне это нравится!» — отвечаю я ей.

Спасибо всем, кто активно и пассивно участвовал 🙏
Всем, кто присылал свои варианты, — искреннее уважение и благодарность.
И тебе, Ирек, — за три дня беспокойной жизни — тоже скромное, но честное спасибо 😄

Итак… всем розовых слонов раздали 🐘 — начинаем!

🛠 Подготовим тестовую среду

CREATE TABLE test_texts
(
  id      NUMBER PRIMARY KEY,
  text    VARCHAR2 (4000 CHAR)
);

CREATE TABLE test_chars
(
  "CHAR" VARCHAR2 (1 CHAR)
);

DECLARE
  current_max_id INTEGER;
BEGIN
  FOR index# IN 1..100
    LOOP
      IF index# = 1 THEN
        current_max_id := 0;
      ELSE
        current_max_id := current_max_id + POWER (10, 3);
      END IF;

      INSERT INTO test_texts (id, text)
        WITH
          generated_rows
          AS
            (    SELECT current_max_id + LEVEL AS id, TRUNC (DBMS_RANDOM.VALUE (1, 1000)) AS text_length
                   FROM DUAL
             CONNECT BY LEVEL <= POWER (10, 3)),
          generated_random_text
          AS
           (
            SELECT a.id, CHR(
                            CASE
                              WHEN a.random_char_number < 55296 THEN a.random_char_number
                              ELSE a.random_char_number + 2048   -- без суррогатов D800–DFFF
                            END
                            USING NCHAR_CS
                          ) AS random_symbol
            FROM(  
            SELECT gr.id, TRUNC(DBMS_RANDOM.VALUE(1, 60000)) AS random_char_number
                   FROM generated_rows gr
             CONNECT BY     PRIOR gr.id = gr.id
                        AND PRIOR DBMS_RANDOM.VALUE () IS NOT NULL
                        AND LEVEL <= gr.text_length
              ) a
            )
          SELECT grt.id,
                 LISTAGG (grt.random_symbol, '') WITHIN GROUP (ORDER BY NULL)  AS text
            FROM generated_random_text grt
        GROUP BY grt.id;
    END LOOP;
END;

Мы наполняем test_texts случайными данными:

1️⃣ Длина строки — от 1 до 1000.
(Почему не 4000? LISTAGG снова шалит. В каждом релизе Oracle её чинят, но баги — как тараканы — всегда возвращаются.)

2️⃣ Символы — случайные CHAR 1–60000 с пропуском суррогатов.

Получаются строки вроде такой:

賔ᩆऩ뷖⧕쟀ᕺྗ畄됖ֱ䌆䳻漽㕲萓麱䷃䅠쿬...

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

🧪 Тестируемые варианты

1️⃣ CONNECT BY LEVEL + SYS_GUID()
2️⃣ CONNECT BY LEVEL + DBMS_RANDOM.VALUE
3️⃣ CROSS APPLY + CONNECT BY
4️⃣ PL/SQL + ассоциативный массив
5️⃣ То же, но с оптимизацией через REPLACE
6️⃣ Рекурсивный WITH (от «великого и ужасного» Славы Рожнего —
как говорится, «старый богатырь ещё может завинтить свой измаильский штык!» ⚔️)

📦 Вариант 1 — SYS_GUID()

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR SYS_GUID () IS NOT NULL;

SYS_GUID() здесь выглядит тяжеловесно.
Честно говоря, напоминает «стрелять по воробьям из гаубицы».
И шанс переполнить TEMP очень велик.

📦 Вариант 2 — DBMS_RANDOM

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR DBMS_RANDOM.value () IS NOT NULL;

То же, но чуть легче. TEMP всё равно в печали.

Продолжение 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🔬 Эксперимент: как быстрее всего получить набор уникальных символов из больших текстов?. Продолжение

📦 Вариант 3 — CROSS APPLY

SELECT DISTINCT c."CHAR"
  FROM test_texts  tt
       CROSS APPLY (SELECT SUBSTR (tt.text, LEVEL, 1) AS "CHAR"
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH (tt.text)) c;

Красиво, логично, TEMP не бомбится.
Но не чемпион.

📦 Вариант 4 — Ассоциативный массив

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars        chars_tab;
  char_index   VARCHAR2 (1);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Просто, надёжно, как автомат Калашникова.

📦 Вариант 5 — Ассоциативный массив + REPLACE

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars           chars_tab;
  replaced_text   VARCHAR2 (4000 CHAR);

  char_index      VARCHAR2 (1 CHAR);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    char_index := chars.FIRST;
    replaced_text := chars.NEXT (char_index);

    WHILE char_index IS NOT NULL AND replaced_text IS NOT NULL
    LOOP
      replaced_text := REPLACE (replaced_text, char_index, NULL);
      char_index := chars.NEXT (char_index);
    END LOOP;

    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Хорош, когда набор символов ограничен.
В случае Unicode-зоопарка — разницы почти нет.

📦 Вариант 6 — Рекурсивный WITH

WITH
  text_split ("CHAR", remaining_text)
  AS
    (SELECT SUBSTR (tt.text, 1, 1) AS "CHAR", SUBSTR (tt.text, 2) AS remaining_text
       FROM test_texts tt
     UNION ALL
     SELECT SUBSTR (ts.remaining_text, 1, 1)   AS "CHAR",
            SUBSTR (ts.remaining_text, 2)      AS remaining_text
       FROM text_split ts
      WHERE ts.remaining_text IS NOT NULL)
SELECT DISTINCT "CHAR"
  FROM text_split;

С самого начала подозрительный вариант.
И действительно — TEMP взрывается как дрожжевое тесто.

📊 Результаты
variant1 (SYS_GUID) 00:41:32 ❌
variant2 (RANDOM) 00:05:23 ❌
variant3 (CROSS APPLY) 00:04:22 ⚠️
variant4 (indexed table) 00:01:39 ⭐
variant5 (indexed + replace) 00:01:38 ⭐
variant6 (recursive with) ORA-01652 ❌ TEMP умер героем

🧠 Итог
❌ Варианты 1, 2, 6 — слишком прожорливы по TEMP.
⚠️ Вариант 3 — рабочий, но не лучший.
🏆 Варианты 4 и 5 — оптимальны.

Разницы почти нет, но если набор символов ограничен реальным алфавитом — REPLACE даёт небольшой бонус.

Все скрипты теста в приложенном файле.

🏁 Занавес
Я пошёл зализывать раны.
Надеюсь, «хитрый татарин» получил то, на что рассчитывал.

👍 Страшно интересно!
👎 Страшно скучно!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Вот хотя бы что-то напишите - я же старался!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «По первому взгляду и виду
Нетрудная, кажется, вещь:
Роскошную эту равнину
Верхом, не спеша, пересечь

Но нам, к сожаленью, известно,
Как ястребы рвутся с цепи,
Как до смерти может быть тесно
И в самой бескрайней степи» — Юлий Ким.

📦 Архив без партиций или жизнь полна приключений!

Есть задача архивирования данных на Oracle Standard Edition.
Тем, кто всю жизнь работал на Enterprise, трудно представить, но в этой версии нет возможности использовать партиции.
И задача по переводу таблиц в архив заиграла другими красками 🎨

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

Какие именно данные — в принципе, неинтересно. Главное, что они занимают место, а вот читают их редко, а возможно, и вообще не читают.
Есть дисковое пространство медленное, но дешёвое 💾
И вот логично не удалять их насовсем, а перенести данные из одного оперативного диска на архивный.

Эта задача относительно легко решается через партиции, но их нет ❌
А как можно решить без них?

Вот "законный" список требований:
1️⃣ Данные из оперативного диска перенести в архивный диск.
2️⃣ В таблицах убрать лишнее, т.е. понизить HWM.
3️⃣ Предоставить чтение архивных данных.
4⃣ Без downtime, т.е. поток записи в таблицы останавливать нельзя.

Как бы Вы решали такую задачу концептуально?

👍 Задача стоит внимания!
👎 Это что, задача что ли?

💎 Поддержка канала⁉️.

💬 Ваши мысли туда!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Механик, крути киноленту,
Дежурные лампы туши!
Вноси свою светлую лепту
В суровые будни души!
Свети нам лучом из окошка
И сам вместе с нами поверь,
Что эту равнину возможно
Проехать почти без потерь!..» — Юлий Ким.

Я предлагаю свой концепт процесса архивирование без использования партиций
Вероятно, всё можно совсем по-другому. Но уж как есть...

🧠 Концепт архитектуры архивирование таблиц (SE Edition) или мир полон приключений.

1️⃣ Две рабочие таблицы вместо одной
Вместо одной журнальной таблицы используются две идентичные.
Текущая активная выбирается через синоним.
т.е. синоним это переключатель, который указывает, какая из таблиц активная для записи.

Зачем?
поток инсёртов всегда идёт в одну из них;
можно безопасно переключать поток;
неактивная таблица становится источником архивации;
после экспорта таблица очищается через TRUNCATE → низкий HWM.
Этот механизм позволяет организовать архивацию без блокировок и без downtime.

2️⃣ Периодический процесс архивации
Раз в период (День, Неделя, Месяц, Год):
Проверяем, не прервался ли предыдущий запуск архивации. Если прервался — продолжаем старый процесс.
Переключает синоним на другую рабочую таблицу.
Экспортируем данные.
Переносим остатки данных, которые могли попасть в таблицу во время переключения и за предыдуший период.
Полностью очищаем использованную таблицу через TRUNCATE.
Все действия фиксируются в логах и статусах.

3️⃣ Организация хранение архива
Видится 2 варианта:
Через отдельное архивное табличное пространство.
Через дамп файлы.

4️⃣ Самовосстановление после ошибок
Если произошёл сбой (например, возникла ошибка или пропало питание):
смена синонима не происходит.
при следующем запуске процесс сам определяет, на каком шаге остановился,
повторяет только незавершённый шаг,
продолжает дальше.

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

5️⃣ Что это даёт?
✔ Архивация без партиций
✔ без downtime
✔ Минимальный рост рабочих таблиц
✔ Простое хранение архивов
✔ Прозрачное восстановление после ошибок
✔ Лёгкое удаление устаревших данных
✔ Доступ к архиву разный в зависимости от вариантов архивации

🎨 Итог:
Даже на Standard Edition можно построить надёжный процесс архивации,
который работает как "ручная партиционизация".
Хотя повторюсь - мир полон приключений, с партициями, это было бы проще.

👍 Толково!
👎 Мусор!

💎 Поддержка канала⁉️.

💬 Мне важно Ваше мнение. Может быть, я не вижу очевидных возможностей! 👇
#Cases #CodeArchitecture

Уважаемые читатели!

Хочу узнать ваше мнение.

В моём канале нет «пустых» постов ни о чём.
Я не публикую свои фото среди пальм и моря (хотя живу в курортном городе 🌴).
Я не хейчу «товарищей по опасному бизнесу» ради поднятия самооценки (с ней всё в порядке, спасибо).

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

Лично я считаю, что подобные посты — это вода.
И публиковать такое в профессиональном канале — проявление неуважения к профи.
Но… возможно, я ошибаюсь.

Может быть, вам действительно интересно увидеть «внутреннюю кухню» канала, ретроспективу, цифры, историю, немного личного?

Поэтому прошу высказаться в комментариях:
нужны ли такие посты или нет?

Эмодзи в этот раз не считаются — только текстовые комменты.

Если будет хотя бы 20 комментариев, что это действительно интересно,
то под Новый год ждите серию «водяных» постов.

С уважением,
Поликарпов Кирилл.

✨ «Все это, видите ль, слова, слова, слова.
Иные, лучшие, мне дороги права;
Иная, лучшая, потребна мне свобода:
Зависеть от царя, зависеть от народа —
Не всё ли нам равно? Бог с ними.
Никому
Отчёта не давать, себе лишь самому
Служить и угождать; для власти, для ливреи
Не гнуть ни совести, ни помыслов, ни шеи.»
— Александр Сергеевич Пушкин

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻

Читатель Максим Феликсович меня справедливо осудил за то, что в последнее время задачи, которые я предлагаю, не для новичков.
Я подумал и нашёл тему, которая будет полезна для всех и при этом несложная для понимания

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

Решать такую задачу можно по-разному:

🔹 а) Самый интуитивно простой способ
Сделать несколько шаблонов и каждый использовать в отдельном случае.

Метод простой, но у него есть несколько недостатков:
1️⃣ Очень трудно поддерживать такой код.
Если шаблонов несколько десятков и в каждый нужно внести одну и ту же правку —
очень легко что-то пропустить.
И тогда ошибка становится плавающей:
в одном кейсе есть, в другом — нет.
На практике такие ошибки очень трудно отлавливать.
2️⃣ Такой код трудно читать.
Если шаблоны сложные и их много — рябит в глазах

🔹 б) Другой простой метод — метод «матрёшки»
или метод подшаблонов,
когда один общий шаблон разбивается на множество подшаблонов,
каждый заполняется отдельно,
а в конце всё собирается в готовый текст с помощью REPLACE.

Давайте рассмотрим на примере 👇

📌 Первый подход

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                     'Ivan Ivanоv',
                     'Y',
                     TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                     3.9,
                     'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
  BEGIN
    IF pupil.average_score >= 4
    THEN
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We are pleased to report that pupil '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) is doing very well academically. 
'
        || CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END
         ' shows great interest in '  pupil.favorite_subject || '.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
The school is proud of this pupil.';
    ELSE
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We inform you that the pupil''s studies are '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) it causes concern. 
We noticed that the pupil shows interest in ' 
 TO_CHAR(pupil.favorite_subject)  ', but the general approach to learning is unsatisfactory.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
We ask you to pay attention.';
    END IF;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

Продолжение 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

📌 Второй подход — метод подшаблонов

Разбиваем текст на подшаблоны:

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                               'Ivan Ivanоv',
                               'Y',
                               TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                               4,
                               'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
    common_template            VARCHAR2 (4000)
      := '[HEADER_TEMPLATE][ESTIMATION_TEMPLATE][AVERAGE_SCORE_TEMPLATE][CONCLUSION_TEMPLATE]';
    header_template            VARCHAR2 (4000)
      := 'As of [SYSDATE], we can report the following:' || CHR (10);
    estimate_template          VARCHAR2 (4000)
      := '[GOOD_ESTIMATE_TEMPLATE][BAD_ESTIMATE_TEMPLATE]' || CHR (10);

    good_estimate_template     VARCHAR2 (4000)
      := 'We are pleased to report that pupil [PUPIL_NAME] ([PUPIL_AGE] years old) is doing very well academically.
[PUPIL_HE] shows great interest in [FAVOURITE_SUBJECT].';

    bad_estimate_template      VARCHAR2 (4000)
      := 'We inform you that the pupil''s studies are [PUPIL_NAME] ([PUPIL_AGE] years old) it causes concern.
We noticed that the pupil shows interest in [FAVOURITE_SUBJECT], but the general approach to learning is unsatisfactory.';

    average_score_template     VARCHAR2 (4000)
      :=    '[HIS] average score in all subjects is [AVARAGE_SCORE].'
         || CHR (10);
    conclusion_template        VARCHAR2 (4000)
      := '[GOOD_CONCLUSION_TEMPLATE][BAD_CONCLUSION_TEMPLATE]';

    good_conclusion_template   VARCHAR2 (4000)
                                 := 'The school is proud of this pupil.';
    bad_conclusion_template    VARCHAR2 (4000)
                                 := 'We ask you to pay attention.';
  BEGIN
    header_template :=
      REPLACE (header_template, '[SYSDATE]', TO_CHAR (SYSDATE, 'dd.mm.yyyy'));

    IF pupil.average_score >= 4
    THEN
      good_estimate_template :=
        REPLACE (good_estimate_template, '[PUPIL_NAME]', pupil.name);
      good_estimate_template :=
        REPLACE (
          good_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[PUPIL_HE]',
                 CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END);
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template,
                 '[GOOD_ESTIMATE_TEMPLATE]',
                 good_estimate_template);
      estimate_template :=
        REPLACE (estimate_template, '[BAD_ESTIMATE_TEMPLATE]', NULL);

      conclusion_template :=
        REPLACE (conclusion_template,
                 '[GOOD_CONCLUSION_TEMPLATE]',
                 good_conclusion_template);
      conclusion_template :=
        REPLACE (conclusion_template, '[BAD_CONCLUSION_TEMPLATE]', NULL);

Продолжение 👇

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

    ELSE
      bad_estimate_template :=
        REPLACE (bad_estimate_template, '[PUPIL_NAME]', pupil.name);
      bad_estimate_template :=
        REPLACE (
          bad_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      bad_estimate_template :=
        REPLACE (bad_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template, '[GOOD_ESTIMATE_TEMPLATE]', NULL);
      estimate_template :=
        REPLACE (estimate_template,
                 '[BAD_ESTIMATE_TEMPLATE]',
                 bad_estimate_template);

      conclusion_template :=
        REPLACE (conclusion_template, '[GOOD_CONCLUSION_TEMPLATE]', NULL);
      conclusion_template :=
        REPLACE (conclusion_template,
                 '[BAD_CONCLUSION_TEMPLATE]',
                 bad_conclusion_template);
    END IF;

    average_score_template :=
      REPLACE (average_score_template,
               '[HIS]',
               CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END);
    average_score_template :=
      REPLACE (average_score_template,
               '[AVARAGE_SCORE]',
               TO_CHAR (pupil.average_score));

    common_template :=
      REPLACE (common_template, '[HEADER_TEMPLATE]', header_template);
    common_template :=
      REPLACE (common_template, '[ESTIMATION_TEMPLATE]', estimate_template);
    common_template :=
      REPLACE (common_template,
               '[AVERAGE_SCORE_TEMPLATE]',
               average_score_template);
    common_template :=
      REPLACE (common_template, '[CONCLUSION_TEMPLATE]', conclusion_template);

    pupil_info := common_template;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

Метод подшаблонов простой, но неочевидный 🤔
Я, например, его не придумал —
а встретил у коллег лет 5 назад, и он мне понравился.

👍 Интересный подход!
👎 Это каждый школьник знает!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Эмоции, мысли, страхи, анекдоты туда!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

✨ «Так всех нас в трусов превращает мысль,
И вянет, как цветок, решимость наша
В бесплодье умственного тупика,
Так погибают замыслы с размахом,
В начале обещавшие успех,
От долгих отлагательств.» — Вильям Шекспир (перевод Бориса Пастернака)

Задача на пять минут или есть ещё порох в пороховницах?
Сколько существует способов строки превратить в столбцы?
И какой способ наиболее оптимальный?

Вот представим: есть таблица

CREATE TABLE test
(
  name   VARCHAR2 (100),
  amount NUMBER
);

INSERT INTO test (name, amount) VALUES ('name1', 1);
INSERT INTO test (name, amount) VALUES ('name2', 2);
INSERT INTO test (name, amount) VALUES ('name3', 3);
COMMIT;

Как нам строки 'name1', 'name2', 'name3' превратить в столбцы? 🔄

name1_amount  name2_amount  name3_amount
     1              2              3

Есть же конструкция PIVOT.
Да, есть. Но если этих строк много?
Оптимальна ли она? ⚖️

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Каждый коммент идет в общую копилку!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

✨ «Но довольно!
Офелия! О радость! Помяни
Мои грехи в своих молитвах, нимфа.»
— Уильям Шекспир (перевод Бориса Пастернака)

Задача на 5 минут или способы строки превратить в столбцы 🔄

Задача классическая, но может удивить и на этот раз.
Добавим побольше записей в таблицу test для наглядности.

INSERT INTO test (name, amount)
     SELECT 'name' || TRUNC (DBMS_RANDOM.VALUE (1, 4)) AS name,
            LEVEL                                  AS amount
       FROM dual
 CONNECT BY LEVEL < 1000000;

COMMIT;

Миллион строк — достаточно, чтобы проверить на работоспособность варианты решения.

Итак, метод PIVOT задекларирован как решение для таких задач.

SELECT *
  FROM test t
       PIVOT (
         SUM (t.amount)
         FOR name
         IN ('name1' AS name1_amount,
             'name2' AS name2_amount,
             'name3' AS name3_amount)
       );

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

Классический и прозрачный метод.

Я бы для такой задачи выбрал именно его из-за простоты и надёжности

SELECT SUM (CASE WHEN t.name = 'name1' THEN t.amount END) AS name1_amount,
       SUM (CASE WHEN t.name = 'name2' THEN t.amount END) AS name2_amount,
       SUM (CASE WHEN t.name = 'name3' THEN t.amount END) AS name3_amount
  FROM test t;

Если же значений name много, и мы не знаем их всех,
то можно использовать динамику.

DECLARE
  v_case_list   VARCHAR2 (4000);
  v_sql         VARCHAR2 (4000);
BEGIN
  SELECT LISTAGG (
           '  SUM (CASE WHEN t.name = '''
        || t.name
        || ''' THEN t.amount END) AS '
        || t.name
        || '_amount',
           ',' || CHR (10)
         )
         WITHIN GROUP (ORDER BY t.name)
    INTO v_case_list
    FROM (SELECT DISTINCT t.name
            FROM test t) t;

  v_sql :=
       'SELECT '
    || CHR (10)
    || v_case_list
    || CHR (10)
    || 'FROM test t';

  DBMS_OUTPUT.put_line (v_sql);

  EXECUTE IMMEDIATE v_sql;
END;

Есть и способ через XMLTYPE получить динамический набор

SELECT XMLAGG (
         XMLELEMENT (EVALNAME (t.name), t.sum_amount)
         ORDER BY t.name
       ) AS xml_data
  FROM (SELECT t.name, SUM (t.amount) AS sum_amount
          FROM test t
      GROUP BY t.name) t;

👍 Хорошо!
👎 Могло быть и лучше!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Категорически интересно Ваше мнение👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «Счастье вдруг в тишине постучалось в двери,
Неужель ты ко мне — верю и не верю.
Падал снег, плыл рассвет, осень моросила…
Столько лет, столько лет — где тебя носило?»
— Леонид Дербенёв.

Давно такого не было — и вот опять.
Работа с CLOB — наверное, самая опасная тема:
столько раз о неё обжигался 🔥

И вот опять совершил обидную ошибку.
Уважаемым читателям предлагаю на рассмотрение мою собственную ошибку.
Вопрос: где она происходит и, главное, почему?

DECLARE
   v_dest CLOB;
   v_src  VARCHAR2 (256);
BEGIN
   DBMS_LOB.createtemporary (v_dest, TRUE);

   DBMS_LOB.append (
      dest_lob => v_dest,
      src_lob  => '123'
   );

   DBMS_LOB.append (
      dest_lob => v_dest,
      src_lob  => v_src
   );

   IF DBMS_LOB.istemporary (v_dest) = 1
   THEN
      DBMS_LOB.freetemporary (v_dest);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      IF DBMS_LOB.istemporary (v_dest) = 1
      THEN
         DBMS_LOB.freetemporary (v_dest);
      END IF;

      RAISE;
END;

Ошибка вроде бы незаметная…
Но именно такие мелочи в работе с CLOB чаще всего и происходят.

👍 Интересная ошибка!
👎 Черт знает что такое!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Кто напишет комментов пачку, тот получит водокачку!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Вдруг, как в сказке, скрипнула дверь
Все мне стало ясно теперь
Столько лет я спорил с судьбой
Ради этой встречи с тобой
Мерз я где-то, плыл за моря
Знаю - это было не зря
Все на свете было не зря
Не напрасно было.» — Леонид Дербенёв

Давно такого не было — и вот опять.
Разбираем мою ошибку с CLOB.
Многие читатели заметили: проблема в том, что мы шлем NULL в метод append.

Всё так и есть! Ошибка возникает здесь:

DBMS_LOB.append (
   dest_lob => v_dest,
   src_lob  => v_src
)

Причём падает всё вне зависимости от типа: будь то CLOB или VARCHAR2. Вот подробное описание «виновника» торжества:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 
ORA-06512: at line 29 
ORA-06512: at "SYS.DBMS_LOB", line 656 
ORA-06512: at line 12

Как это валидно разрешить?
TO_CLOB(v_src) — не поможет.

NVL(v_src, ' ') — можно, но как-то «не солидно».

Даже NVL(v_src, EMPTY_CLOB()) вызовет ошибку, если v_src имеет тип VARCHAR2.

Единственный по-моему солидный вариант:

NVL(TO_CLOB(v_src), EMPTY_CLOB())

Да! Да! Да!
EMPTY_CLOB IS NOT NULL <=> TRUE

Риторический вопрос 🤔
У меня возникает законный вопрос: почему любой входящий CLOB не может быть NULL?
Почему внутри DBMS_LOB нельзя автоматически выполнять подмену через тот же NVL?

Я, честно, такой логики не понимаю. Но имеем то, что имеем!

👍 Полезно!
👎 Черт знает что!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 За комменты деньги не берут! Пишите бесплатно!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Если душевно ранен
Если с тобой беда,
Ты ведь пойдёшь не в баню,
Ты ведь пойдёшь сюда.
Здесь ты вздохнёшь счастливо,
Крякнешь и скажешь: да,
Губит людей не пиво,
Губит людей вода.» — Леонид Дербенёв

🛠 Джобы или распределение запуска джобов в рамках разных схем

Вот задача, которую я решаю на данный момент.

Есть БД, и в ней несколько схем, каждая из которых — отдельная самостоятельная сущность.
В каждой такой схеме работает отдельная организация.
Т.е., условно, 10 схем — это 10 отдельных «подбаз».

Таких серверов много, т.е. больше чем два.
Я не буду критиковать такой подход — просто примем это как данность: так исторически сложилось.

📦 Процесс архивации запускается для каждой схемы отдельным джобом.

В чём проблема: плохо будет, если 10 джобов запустятся одновременно ⚠️

С Вашего позволения, я воспользую экспертным сообществом канала для решения моих собственных производственных проблем.

❓ Вопрос дня:
Какие способы есть распределить нагрузку на общую БД и запускать джобы распределённо?
При этом какие-то структуры над этими «подбазами» не предполагаются.

👍 Задача стоит внимания!
👎 Чушь какая-то!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Все Ваши комменты будут внимательно прочитаны!👇
#Cases #SQL #Oracle #PLSQL