Номера строк, запросы в новой вкладке и другие хинты SQLDeveloper

В этом материале мы будем рассматривать стандартные настройки и проблемы при работе с SQL Developer. Cрывов покровов как в газете спид-инфо не будет (олды тут?). Поэтому, если вы опытный пользователь данного клиента, вряд ли вы узнаете что-то новое.

Как выводить запросы на новой вкладке?

По умолчанию каждый запрос в клиенте выводится в одном окне. Чтобы включить вывод на каждом новом окне нужно зайти в Tools -> Preferences

Далее находим Database -> Worksheet. В этом окне ставим галку на пункт Show query results in new tabs.

Как включить номера строк в редакторе?

Чтобы вывести номера строк в редакторе достаточно в левой части редактора кликнуть правой кнопкой мыши и нажать Toggle Line Numbers

Как решить ошибку c адаптером при подключении (ORA-17002: io exception: the network adaptor could not establish the connection Vendor code 17002)

Ошибка с адаптером — одна из самых встречающихся проблем при первых запусках SQL Developer. Решения проблемы есть две.

Отредактировать файл listener.ora и tnsnames.ora

Необходимо проверить содержание файлов listener.ora и tnsnames.ora. Они находится в этом каталоге:

C:\app\[ваше_имя_пользователя]\product\21c\homes\OraDB21Home1\network\admin.

Путь может отличатся в зависимости от того, куда был установлен Oracle Database Express Edition

Что нужно проверить в файле listener.ora : в первую очередь смотрите хост. Возможно, там будет вписан какой-то левый IP. Измените его на localhost.

Что нужно проверить в файле tnsnames.ora: Проверьте чтобы service name совпадал с тем, что у вас прописано в SQLDeveloper при подключении. У нас прописана стандартная база:

Запустить службу OracleOraDB21Home1TNSListener

Нажимаем  Windows+R, чтобы открылась командная строка (либо запустите её другим способом) и впишите туда services.msc

Откроется приложение Службы. В ней необходимо найти службу OracleOraDB21Home1TNSListener и включить её. Она должна быть в статусе «Выполняется».

Возможно, она не запустится с первого раза. В таком случае перезапустите службу OracleServiceXE.

Запуск зависает на Restoring Windows

Бывает, что запуск зависает на пункте Restoring Windows и дальше не идёт (в некоторых случаях в итоге загружается спустя долгое время). Есть два варианта решения проблемы. Первый — завершить процесс через диспетчер задач и заново его запустить

Второй вариант — очистить папку системного кэша клиента. Он находится здесь:

C:\Users\[ваше_имя_пользователя]\AppData\Roaming\SQLDeveloper\system19.2.1.247.2212\system_cache.

Номер после system может быть другой — зависит от версии клиента (Appdata — скрытая папка, нужно включить отображение в проводнике).

Удаляйте все файлы в этой папке и запускайте клиент (все настройки, в т.ч. подключения сохранятся)

Работа с БД

Небольшая шпаргалка для тех, кто только начинает осваивать Oracle

Создание временной таблицы из запроса

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

CREATE GLOBAL TEMPORARY TABLE temp_table_name

ON COMMIT PRESERVE ROWS as

SELECT ID as f, NAME as d, CITY as t

FROM your_table_name;

Запись ON COMMIT PRESERVE ROWS означает, что данные будут удалены в конце сеанса. На то она и временная таблица. Так же обратите внимание, что на каждый столбец нужно повесить идентификатор.

Сопоставление данных из двух таблиц

Обычный сплит данных из двух таблиц (без JOIN и UNION)

SELECT a.super_id, b.puper_id
FROM table1 e, table2 b WHERE a.super_id = b.puper_id;

Запрос из трёх таблиц

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

select table1.id, table2.superid, table3.puperid
from table1, table2, table3
where table1.id = table2.superid
and table2.superid = table3.puperid

Ищем часть значения в столбце через Like

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

select * from Your_table
where Super_id like '%123%'

Этот запрос вернет все super_id в которых встречается 123 (в любом месте).

Как создать схему данных (диаграмму ERP)

Как проверить связность таблиц в базе? Для этого в SQLDeveloper есть инструмент Data Modeler. Открываем следующее: File -> Data Modeler -> Import -> Data Dictionary

После чего откроется пошаговый мастер. На первом необходимо выбрать базу, из таблиц которых будет строится диаграмма. Выбираете и нажимаете Next (попросит ввести пароль, даже если вы уже подключены к базе)

Дальше нужно выбрать владельца БД. Можете выбрать все, можете выбрать конкретных.

Затем выбираем объекты (таблицы) и также нажимаем Next

Дальше подтверждаем генерацию дизайна и ждём. Найти  диаграмму (предварительно её сохранить) можно во вкладке Browser (View ->Data Modeler)

Он будет открыт снизу от вкладки Connections.

Как теперь найти связь между таблицами? Можем выбрать таблицу и посмотреть на неё в ER-диаграмме. Щелкаем правой кнопкой и в контекстном меню выбираем Go to Diagram

После этого мы увидим её на общей диаграмме

И сможем посмотреть связь с другой таблицей по первичному ключу, если кликнуть по связи:

Группировка значений по признаку

Существует задача, где в результате запроса остались только те ID, у которых все значения соседнего столбца оказались одинаковыми (значение или null). Сделать это можно следующим образом:

SELECT *
FROM table1 t1
WHERE NOT EXISTS (
  SELECT NULL
  FROM table1 t2
  WHERE t1.id = t2.id
    AND t2.range2 <> '0 - 0'
)

Готовый пример в SQLFiddle

Все таблицы и столбцы в БД

В незнакомой БД может понадобится посмотреть имя всех таблиц и столбцов, которые в них содержаться.

SELECT * FROM all_tab_columns

Посмотреть столбцы и тип данных в конкретной таблице

Можно проверить имя и тип данных столбцов в отдельной странице командой DESCRIBE TABLE_NAME или использовать следующий запрос:

SELECT column_name FROM all_tab_columns WHERE table_name='TABLE_NAME';

Обойти ограничение Where в 1000 значений

В запросе Where есть ограничение в 1000 значений. Что делать, если туда необходимо затолкать несколько больше?

SELECT * FROM table_name
WHERE superid in (value1....value999)

or superid in (value999...value1999)