Oracle в терминале

Мне довелось поработать в одном из «подведов» Департамента экономической политики и развития, и одна из задач была на работу и анализ данных Росреестра, для чего необходимо было либо выпрашивать данные у программистов, либо получать их самому. Напишу по памяти, как было дело.

Данные Росреестра хранились в OracleDB 11-й версии, но использовать GUI для построения запросов мне не хотелось, а хотелось командной строки. Дело в том, что наши программисты баз данных считали удобным копировать-вставить запрос в поле графического интерфейса, и полученный ответ экспортировать (нажатием кнопки!) в Excel; мне, юристу, гораздо ближе заранее написанный параметризуемый запрос, который можно один раз написать и дополнять его параметром (кадастровым номером) или двумя. К примеру, запрос к базе на предмет, имелась ли в базе данных на определенную дату установленная связь между зданием и земельным участком, исполнялся так: @okz_zu_conn 20190429 77:05:0001001:1024. Это удобно, потому что в одном заранее написанном файле могут быть разные запросы к разным таблицам, и один и тот же параметр может передаваться в разные запросы несколько раз. Копировать-вставить и потом вставлять параметр по всему тексту запроса — непродуктивная трата времени.

К сожалению, у Oracle инструменты командной строки по мере возрастания версий становились всё медленнее, поэтому для работы с базой я взял утилиту SQLPlus той же версии что и база данных (v11.2), она оказалась быстрее всех. И вообще, я не сторонник устанавливать что-либо в операционную систему; предпочитаю переносимый софт, который хранит файл настроек где-то рядом или хотя бы в понятном месте, а не в дебрях системного реестра.

Чтобы утилита командной строки заработала, нужно скачать с сайта Oracle два пакета: instantclient-sqlplus-windows и instantclient-basiclite-windows. Вот список файлов, которые нужно положить в одну папку: oci.dll orannzsbb11.dll oraocci11.dll oraociei11.dll oraociicus11.dll orasql11.dll orasqlplusic11.dll sqlplus.exe oci.sym orannzsbb11.sym oraocci11.sym oraociei11.sym oraociicus11.sym orasql11.sym sqlplus.sym.

Подключение к серверу описывается файлом tnsnames.ora приблизительно следующего содержания:

server1 =
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = TCP)(HOST = ▒▒▒.▒▒.▒▒▒.▒▒)(PORT = 1521)
  )
  (CONNECT_DATA =
   (SID = ▒▒▒▒▒▒)
   (SERVER = DEDICATED)
  )
)

server2 =
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = TCP)(HOST = ▒▒▒.▒▒.▒▒▒.▒▒)(PORT = 1521)
  )
  (CONNECT_DATA =
   (SID = ▒▒▒▒▒▒)
   (SERVER = DEDICATED)
  )
)

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

Подключение простое: sqlplus LOGIN:PASS@server1, а также можно сразу указать файл исполняемого SQL-запроса.

Самое интересное, на мой взгляд, это настройки вывода.

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

-- Очищаем штатные настройки колонок и прочего
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS

-- Создаём своё приглашение на ввод; \ESC необходимо заменить на символ с кодом 0x1b
SET SQLPROMPT "\ESC[0;97m _CONNECT_IDENTIFIER\ESC[0m\ESC[0;92m§\ESC[0m "
-- Эта настройка выдаёт в терминал сразу весь результат запроса, а не постранично
SET PAGESIZE 0 EMBEDDED ON
SET UNDERLINE ON

SET TRIMSPOOL ON
SET TAB OFF
SET ECHO OFF
SET FEEDBACK OFF

-- Какой текст будет выведен при нулевом значении поля
SET NULL (none)
SET RECSEP EACH
SET RECSEPCHAR " "
-- Ширина терминала, в который выводится ответ сервера, в символах
SET LINESIZE 230
SET TIME ON
SET TRIMOUT ON
SET VERIFY OFF
SET WRAP OFF

SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED
-- Пишем всё в лог, очень полезно
SPOOL log.txt APPEND

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

Таблиц в базе данных много, и колонки названы «понятными» именами типа NAME (в десятке таблиц сразу), ASS_NAME, AREA, ADDR_ID и так далее. Сами таблицы называются, к примеру, ASUR_RR2_20190101.T$RR#REALTY (вспоминаем, что в OracleDB допустимы неалфавитные символы в именах таблиц).

Если не прописывать в теле запроса заголовок, то в выводе запросто появится несколько колонок NAME, да и дат может быть больше одной. Поэтому в каждом запросе необходимо сначала написать CLEAR COLUMNS (очищаем настройки вывода), а потом пишем

COLUMN ASUR_RR_REALTY_REALTY_ID FORMAT A20 HEADING "Номер ОКС"
COLUMN ASUR_RR_REALTYTP_NAME FORMAT A24 WORD_WRAPPED HEADING "Вид объекта"
COLUMN ASUR_RR_REALTY_NAME FORMAT A24 WORD_WRAPPED HEADING "Категория|объекта"
COLUMN ASUR_RR_REALTY_ASS_NAME FORMAT A24 WORD_WRAPPED HEADING "Назначение|объекта"
COLUMN ASUR_RR_REALTY_AREA FORMAT 999999.9 HEADING "Площадь"
COLUMN ASUR_RR_REALTY_ADDR_ID FORMAT A24 WORD_WRAPPED HEADING "Id адреса"
COLUMN ASUR_RR_REALTY_REG_NUMR_DTCR FORMAT DATE HEADING "Дата|постановки"
COLUMN ASUR_RR_REALTY_REG_NUMR_DTREM FORMAT DATE HEADING "Дата|снятия"
COLUMN ASUR_RR_REALTY_NOTE FORMAT A32 WORD_WRAPPED HEADING "Примечание"

За словом FORMAT следуют настройки: A20 означает, что эта колонка — текстовое поле шириной 20, число 999999.9 означает, что результат будет выведен с округлением до одного знака после запятой, WORD_WRAPPED будет переносить текст (а не обрезать его по размеру поля), а HEADING задаёт выводимый текст, причём | — это разрыв строки.

Перед самим запросом я бы для удобства рекомендовал создать служебный заголовок вида PROMPT \ESC[7m ЗАПИСИ ОБ ОКС \ESC[0m (где \ESC это символ 0x1b), то есть выделить заголовок цветом (если терминал умеет обрабатывать ESC-последовательности). К счастью, SQLPlus не обрабатывает выдаваемые строки, так что такие последовательности работают отлично.

В самом запросе необходим alias для запрашиваемой колонки данных вида

SELECT r.realty_id as ASUR_RR_REALTY_REALTY_ID
   , t.NAME as ASUR_RR_REALTYTP_NAME
   , r.NAME as ASUR_RR_REALTY_NAME
   , r.ASS_NAME as ASUR_RR_REALTY_ASS_NAME
   , r.AREA as ASUR_RR_REALTY_AREA
   , r.ADDR_ID as ASUR_RR_REALTY_ADDR_ID
   , r.REG_NUMR_DTCR as ASUR_RR_REALTY_REG_NUMR_DTCR
   , r.REG_NUMR_DTREM as ASUR_RR_REALTY_REG_NUMR_DTREM
   , r.NOTE as ASUR_RR_REALTY_NOTE

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