Мне довелось поработать в одном из «подведов» Департамента экономической политики и развития, и одна из задач была на работу и анализ данных Росреестра, для чего необходимо было либо выпрашивать данные у программистов, либо получать их самому. Напишу по памяти, как было дело.
Данные Росреестра хранились в 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
Прописав всё в одном файле, можно одной строкой исполнять разные запросы к базе и получать удобные читаемые отчёты.