вторник, 14 апреля 2020 г.

Oracle + PL/SQL - поиск строки в базе

Возникла тут одна небольшая задачка. Есть некая строка, например "ABC123", есть база данных на Oracle, и нужно опеределить, в каких таблицах и каких столбцах эта строка встречается. Известен владелец таблиц, назовём его HUHMUH. В общем, требуется некий reverse engineering.

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

Поскольку всё происходит под линуксом в командной строке, возникла мысль воспользоваться инструментом SQL*Plus, входящим в комплект Oracle Instant Client-а (у меня он версии 12.2). Запуск скрипта, хранящегося в файле myscript.txt, с помощью этой утилиты не представляет сложностей:
#!/bin/sh

export NLS_LANG=American_America.AL32UTF8

ora_path=/opt/oracle/instantclient_12_2

exit | $ora_path/sqlplus -S mylogin/mypassword@myserver:1521/mydatabase @myscript.txt > output.txt
Запускаем, получаем в файле output.txt всё, что этот самый скрипт посчитает нужным сообщить.

Сам скрипт тоже не содержит ничего необычного. Выглядит он так:
-- без этого не работает dbms_output
SET SERVEROUTPUT ON

-- без этого пишется лишняя строка "PL/SQL procedure successfully completed"
SET FEED OFF

DECLARE
    search_string  varchar(256);

    row_count integer;

    CURSOR cur
    IS
    SELECT
        owner,
        table_name,
        column_name
    FROM
        all_tab_columns
    WHERE
        owner = 'HUHMUH'
        and data_type like '%CHAR%'
    ORDER BY
        1, 2;

BEGIN

search_string := 'ABC123';

-- можно было сразу FOR r IN (SELECT ...) LOOP ... END LOOP;
FOR r IN cur
LOOP
    BEGIN

        EXECUTE IMMEDIATE 'SELECT count(*) FROM '
            || r.table_name
            || ' WHERE '
            || r.column_name || ' = ''' || search_string || ''' '
            INTO row_count;

        IF row_count > 0 THEN
            dbms_output.put_line(r.owner || '.' || r.table_name || ' -> ' || r.column_name);
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('error ' || r.owner || '.' ||r.table_name || ' -> ' || r.column_name);
            CONTINUE;
    END;

END LOOP;

END;

-- без этого не возвращается результат dbms_output
/

Вот, собственно, и всё. Работает долго, но нужные мне данные нашло.

Комментариев нет:

Отправить комментарий