Private Temporary Table
Решил не откладывать в долгий ящик информацию по приватным временным таблицам.

Приватные временные таблицы (PTT) - таблицы, создающиеся и хранящие данные на время сеанса или транзакции. Они создаются “на лету” по ходу выполнения кода.

Хранение структуры и данных происходит целиком в памяти. Redo и Undo не генерятся.

Два типа PTT:
1. Существуют до commit’a (on commit drop definition)
2. Существуют до окончания сеанса (on commit preserve definition).
Тип можно задать только при создании.

Название должно начинаться с префикса - ORA$PTT_

Синтаксис:

create private temporary table ora$ptt_***(
    column_definition,
    ...
) on commit
  [drop definition | preserve definition];

#временныетаблицы

Задача: Выполняется скрипт. В нем есть ошибка. Где она?

Скрипт:

create private temporary table ora$ptt_my_tab
(
  id number(30)
)
on commit drop definition;

insert into ora$ptt_my_tab values(1);
insert into ora$ptt_my_tab values(2);

commit;

select count(*) from ora$ptt_my_tab;

———-
Рекомендую не выполнять код, а решить эту задачу "в уме".

Смотрите объяснения в посте в четверг 🎓

#sql #задача

Задача: смотрите постановку в посте вторника.

Решение:
1. Таблица является приватной временной. Сама таблица и данные из неё удаляются по commit’у (опция создания - on commit drop definition).

2. Команда commit зафиксирует транзакцию, начавшуюся после создания таблицы, и удалит её вместе с данными.

3. Команда select не выполнится из-за ошибки "ORA-00942: table or view does not exist”. Таблицы то уже нет.

Итого: команда select не выполнится из-за отсутствия таблицы.

На этом простейшем примере показано свойство приватных временных таблиц.

Ставьте 👍 , если вам понравилась задачка.

#sql #решениезадачи #временныетаблицы

Private Temporary Table

Всем привет!

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

Приятного просмотра!

🎥 Смотреть видео - 8 мин

#видео #временныетаблицы

Задача:
Как получить версию СУБД, к которой вы подключены при помощи SQL, PL/SQL?
Существует несколько способов. Попробуйте найти хотя бы два.

Смотрите объяснения в посте в четверг 🎓

#задача

Профилирование PL/SQL-кода

Всем привет!

Месяца полтора назад вы проголосовали за видео с профилированием PL/SQL-кода.
Сделано 😉

В этом видео, я расскажу, что такое профилирование PL/SQL кода, как его производить, зачем оно нужно, как читать отчет профилировщика.
Будет полезно тем, кто не понимает, как найти узкое место в PL/SQL коде, где он тормозит, как его можно оптимизировать.

Приятного просмотра!

🎥 Смотреть видео - 10 мин

#видео #профилирование #оптимизация

Задача: Как получить версию СУБД, к которой вы подключены при помощи SQL, PL/SQL?

Решения:
1 способ

declare
  v_version varchar2(1000 char);
  v_compatibility varchar2(1000 char);
begin
  dbms_utility.db_version(version => v_version, compatibility => v_compatibility);
  dbms_output.put_line('Version: '||v_version);
  dbms_output.put_line('Compatibility: '||v_compatibility);  
end;

2 способ

select * from v$version;

3 способ
Для привелегированных пользователей

select version from v$instance;

4 способ

select * from product_component_version;

5 способ

begin
  dbms_output.put_line(dbms_db_version.version||'.'||dbms_db_version.release);
end;

Есть и другие способы.
——
Ставьте 👍 , если вам понравилась задачка.

#решениезадачи #версиясубд

Выбираем видео 🎥

Коллеги, всем привет!

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

Создал голосовалку с перечислением тем.
1. Как посмотреть план запроса.
2. Что такое “compound”-триггеры.
3. Что такое “instead of”-триггеры.
4. Секционированные таблицы.
5. Условная компиляция в PL/SQL.
6. Что-нибудь другое (можете смело писать в личку тему).

Запилю видосы по трем темам, набравшим большее количество голосов.

Выбирайте 👍

Итоги голосования

Всем привет!

Судя по опросу, на момент написания поста, рейтинг опроса по видео такой:
1️⃣ Как посмотреть план запроса.
2️⃣ Условная компиляция в PL/SQL.
3️⃣ Секционированные таблицы.

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

Спасибо за активность на канале, за ваши голоса 👍

Ждите, скоро все будет 😉

Задача:
Имеется текст, содержащий JSON. Он может быть как сложным так и простым.
Необходимо проверить является ли текст валидным JSON. Решение может быть как на SQL, так и на PL/SQL.
Примечание: задача для версий выше Oracle 11g.

Пусть для примера будет такой JSON:

{
  "name": "value",
  "list": [
    "1",
    "2",
    "3"
  ]
}

Смотрите объяснения в посте в четверг 🎓

#задача

Вызов внешнего RestAPI

You asked…
Мне необходимо вызывать внешний Rest-сервис. Как это лучше всего организовать? Приведите, пожалуйста, пример.

...and we said
Если вам необходимо вызывать из базы данных внешние сервисы необходимо:
1. Настроить доступ из БД через ACL по определенному адресу/порту.
2. Затем используя пакет utl_http организовать логику работы с внешним сервисом.

Пример такой организации вы можете посмотреть по ссылкам в статье.

Подробности в статье Тома

---
От себя добавлю. Это решение подходит для систем построенных на серверной логике. Если же у вас трехзвенная архитектура со средним слоем на Java-приложении (например), то лучшее решение - организовать запросы во внешние сервисы именно там.

#asktom #restapi #acl #utl_http

Задача:
Имеется текст, содержащий JSON. Он может быть как сложным так и простым.
Необходимо проверить является ли текст валидным JSON. Решение может быть как на SQL, так и на PL/SQL.

Решение:
Для SQL:

select nvl(max(1),0) is_json
  from dual
 where '{ "name": "value", }' is json;

Для PL/SQL

begin
  if '{ "name": "value"} ' is json then
    dbms_output.put_line('It is json');
  end if;
end;
/

В примере, я использовал маленький JSON, чтоб влезло в пост.

В Oracle 12c появился набор функций\процедур для работы с JSON-форматом. Одна из полезных функций это IS JSON, которая возвращает true - если текст в формате JSON, false - если нет.

Ставьте 👍 , если вам понравилась задачка.

#решениезадачи #json

Правила именования объектов

Информация будет полезна тем, кто только недавно столкнулся с СУБД Oracle.

Существует ряд особенностей и ограничений в нейминге объектов и идентификаторов:
* регистронезависимые.
* не должны начинаться с цифры.
* иметь длину до 30 байт (до версии 12c) или не более 128 (12c+).
* не могут содержать пробелы, недопустимые символы “-”, “%”.
* могут включать символы «$», «_» и «#»;
* не должны быть ключевыми словами языка.

Системное представление v$reserved_words поможет определить, является ли слово ключевым в СУБД. Думаю, что подсветка слов в IDE также основана на этом представлении.

Как именовать объекты (переменные, таблицы, индексы и т.д.) договариваются внутри команды/компании. Далее следуют этим правилам для единообразия кодовой базы, более легкой поддержки и создания кода.

Однако, есть некоторые условности и договоренности в мире разработки Oracle. Об этом в следующий раз.

Ради интереса, запускаю голосовалку. Не поленитесь - жмакните кнопку 🙋🏻‍♂️

#именование

Задача:
1. Необходимо создать таблицу с названием "Моя Таблица" с тремя колонками любого типа на ваш выбор с названиями:
- "1-ый столбец"
- "2-ой столбец"
- "column name"
2. Выполнить insert в таблицу нескольких записей.
3. Выполнить select из таблицы только первых двух полей.

Смотрите объяснения в посте в четверг 🎓

#задача

Соглашение об именовании (naming convention)

You asked…
Привет, Том!
Хотел бы узнать вашу точку зрения на соглашение об именах?
Например, вы ограничиваете количество символов (скажем, 20) для имени таблицы?
Предоставляет ли Oracle шаблон для соглашения об именах в базе данных?

...and we said
Лично у меня нет соглашений об именах. Я не люблю набирать действительно длинные имена, но время от времени я достигаю ограничения в 30 символов.

Это, прежде всего, вопрос выбора команды.

Единственные соглашения об именах, которые у меня есть, относятся к PLSQL.

Локальные переменные должны начинаться с L_
Параметры должны начинаться с P_
Глобальные переменные пакета должны начинаться с G_

Например, параметр ENAME не будет путаться со столбцом с именем ENAME в запросе - у меня никогда не будет столбца p_ename - поэтому нет двусмысленности.

Подробности в статье Тома

#asktom #именование

Задача: Необходимо создать таблицу с нестандартным названием, колонками и т.д. Полная постановка в посте вторника.

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

Для нашего случая:

drop table "Моя Таблица";

create table "Моя Таблица"(
"1-ый столбец" number(10),
"2-ой столбец" date,
"column name" number(3)
);
insert into "Моя Таблица"
  ("1-ый столбец", "2-ой столбец", "column name")
select level, sysdate, level from dual connect by level <=10;

select "1-ый столбец", "2-ой столбец" from "Моя Таблица";

select * from user_tables t where t.table_name = 'Моя Таблица';

Считается плохой практикой использование двойных кавычек при именовании. Максимум дозволенного - это алиасы в результатах запроса, и то, если они не передаются выше в приложение.

Когда это может пригодиться?
1. Если кто-то использовал не стандартное имя, и вам необходимо работать с этим объектом.
2. При крайней необходимости обхода стандарта именования в Oracle.

#решениезадачи #именование

На этой недели, мы кратко прошлись по теме нейминга при разработке.

Наличие зафиксированных договоренностей, best-practices по разработке, именованию объектов и идентификаторов, правила для форматирования кода (желательно автоматического), говорит об уровне инженерной культуры 👷‍♀️

Исходя из результатов голосования, 70% команд имеют некие договоренности. Довольно не плохой результат 👍

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

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

Примеры, нейминга смысла приводить не вижу. В основном все сводится к добавлению префиксов/постфиксов к именам. Типа: v_ - локальная переменная, c_ - константа, pi - входной параметр, ***_tbl - таблица и т.д.

Тут уж решает команда. Главное чтобы решение было 😉

Всем хороших выходных! 👍

#именование

Продолжаем посты про оптимизацию.

Виды планов запроса
Существует два вида планов запросов:

1. Гипотетический или потенциальный (explain) - это то как Оракл предположительно будет выполнять запрос. Его можно посмотреть без выполнения самого запроса. При этом не нужен sql_id, plan_hash_value. Иногда такой тип планов называют design time plan.

2. Фактически или реальный план (execution) - это то как реально выполнялся запрос. Соответственно, чтобы получить такой план, запрос необходимо выполнить хотя бы один раз. Иногда такой тип планов называют run time plan.

Эти планы могут отличаться, при том что вы не меняли текст запроса.

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

Для каждого типа плана существует несколько способов их просмотра.

Я уже почти смонтировал видео, о том как посмотреть планы. Думаю, в среду оно появится на канале. Следите за постами.

#оптимизация

Задача:
необходимо проверить есть ли схема "HR2" в базе данных.

Смотрите объяснения в посте в четверг 🎓

#задача

В чем разница между потенциальным планом и планом выполнения

You asked…
Привет, Том!
Привет, у меня вопрос по поводу потенциального и реального плана. Я читал, что план выполнения - это план, который Oracle намеревается использовать для запроса, и фактический план выполнения может отличаться; если это так, то как мне получить реальный план выполнения. Заранее спасибо.

...and we said
Представьте, что вы планируете автопутешествие. Вы открываете навигатор и строите маршрут. Это ваш гипотетический план.

Пришел день выезда. Вы садитесь за руль, включаете радио и слышите, что по вашему маршруту произошла крупная авария. Вы не хотите сидеть в многочасовой пробке. Вы открываете навигатор и строите маршрут заново. Затем вы едите по новому маршруту. Это и есть ваш реальный план маршрута.

Гипотетический план - это предсказание.
Фактический план - это то как реально все прошло.

Существует множество способов получить план выполнения. Ссылки в статье.

Подробности в статье Тома.

#asktom #оптимизация

Задача:
Необходимо проверить есть ли схема HR2 в базе данных.

Решение:
-- 1 способ (подойдет для любых пользователей)

declare
  v_schema user_objects.object_name%type := 'HR2';
begin
  dbms_output.put_line('Схема '||dbms_assert.schema_name(v_schema)||' существует.');
exception
  when dbms_assert.invalid_schema_name then
      dbms_output.put_line('Схемы '||v_schema||' не существует');
end;
/

-- 2 способ (подойдет для привилегированных пользователей)

select count(*) from dba_users t where t.username = 'HR2';

Наверняка, есть и другие способы.

Рекомендую посмотреть модули в пакете dbms_assert. Довольно интересный пакетик, о котором мало кто знает.

#решениезадачи