Задача:
Имеется текст, содержащий 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. Довольно интересный пакетик, о котором мало кто знает.

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

Как посмотреть план запроса

Всем привет!
Первое видео из тех, за которые вы проголосовали 👍

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

Чего не будет - как читать планы запросов, как оптимизировать запросы. Я потихоньку формирую курс по оптимизации, в котором будет рассмотрен этот вопрос.

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

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

#видео #оптимизация

Представления

Представления - объекты, базы данных, представляющие собой либо виртуальную таблицу, либо физическую (в зависимости от типа). Они создаются с помощью запроса соединяющего одну или несколько таблиц.

Виды представлений
1. Обычные (view) - содержат в себе только определение как получать данные.
2. Материализованные (materialized view) - помимо определения содержат в себе данные.
3. Системные (system dictionary) - обычные представления, но с отображением системной информацией, предопределены в СУБД.

В следующем посте рассмотрим, зачем они нужны.

#представления

Сегодня будет немного необычная задачка в формате голосования.

Можно ли менять данные в таблицах через обычное представление?
Представление в вашей схеме, построено на ваших таблицах, т.е. права есть.

Вопрос довольно частый на собеседованиях.

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

#задача

Зачем нужны представления

1. Уменьшение сложности - “за кадром” происходит выполнение сложного запроса по соединению таблиц, агрегации данных и т.д.

2. Повышение безопасности - сокрытие некоторых полей.

3. Повышение удобства - работаем только с необходимыми данными, обращаемся к одному объекту, не таскаем кучу разных таблиц.

4. Переименование столбцов таблицы - можно представить конечную выборку в более удобном виде.

5. Настройка данных для пользователей - агрегация данных и другие преобразования

6. Предварительная материализация данных - материализованные представления, содержат данные, таким образом, обращаясь к ним не тратится время на выполнения сборки данных с разных источников.

7. Сокрытие реального источника данных - иногда бывает полезно при переименовании исходной таблицы. Чтобы не ломать весь код, view создают с аналогичным названием.

Основные назначения я перечислил. Наверняка, этот список можно дополнить какими-то редкими кейсами использования.

#представления

Задача: Можно ли менять данные в таблицах через обычное представление?

Решение:
Правильный ответ: зависит от ситуации.

Данные в таблицах не получится менять через представления в следующих случаях:
1. Oracle не понимает какую строку менять в исходных таблицах. Например, есть агрегаты\группировки и т.д.
2. Если Oracle не понимает какую строку менять и нет триггера instead of.
3. Представление создано с опцией read only, которая запрещает изменение данных в исходной таблице(ах).

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

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

#решениезадачи #представления

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

Представление не существует физически как обычная таблица. Фактически представление создает виртуальную таблицу или подтаблицу только с теми строками и/или столбцами, которые нужно показать пользователю.

Всякий раз, когда нужен доступ к представлению, Oracle должен выполнить запрос,по которому определено представление, и вернуть результат. Этот процесс наполнения представления называется разрешением представления (view resolution) и он повторяется при каждом обращении пользователя к представлению.

Синтаксис создания

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

#представление

Материализованные представления

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

Чаще всего, материализованные представления разделяют на два типа:
1. Обновляемые по commit’у (on commit)
2. Обновляемые вручную (on demand) - по умолчанию.

Синтаксис создания по ссылке.

Для обновления вручную (on demand) используется процедура: dbms_mview.refresh(‘название м.п.’);

Есть еще механизм журнальных групп изменений. Об этом в другой раз.

#представления

Журнальные группы изменений (log group)

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

Да можно! Для этого сделали Log-группы (материализованные журналы изменений), в которые накапливается инкремент изменений. Соответственно, при обновлении мат представления изменения берутся из журнала изменений. За счет чего происходит, быстрое обновление данных.

Материализованное представление должно быть с опцией: REFRESH FAST

Синтаксис:

CREATE MATERIALIZED VIEW LOG ON таблица
TABLESPACE имя_табл_пространства
WITH PRIMARY KEY
INCLUDING NEW VALUES;

#представления