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

В данной статье приведен пример скрипта для поиска подстроки во всех столбцах таблиц указанной схемы в базе данных Oracle. Скрипт написан в TOAD.

1. Создание таблицы

CREATE TABLE MySCHEMA.TEST01
(
S_TABLE_NAME VARCHAR2(50 BYTE),
S_COLUMN_NAME VARCHAR2(50 BYTE),
S_SUBSTRING VARCHAR2(250 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

2. Скрипт для поиска

DECLARE
/*Неизменяемые переменные*/
v_table_name all_tab_columns.table_name%TYPE;
v_column_name all_tab_columns.column_name%TYPE;
v_table_save VARCHAR2(12 BYTE) :=’TEST01′; /*!!!Запрещено исправлять!!! — Имя временной таблицы*/
query_str VARCHAR2(600);

/*Вводимые параметры*/
v_substring VARCHAR2(25 BYTE) :=’Искомая подстрока’; /*ПАРАМЕТР искомой подстроки*/
v_owner VARCHAR2(12 BYTE) :=’SYSTEM’; /*ПАРАМЕТР в какой области находятся таблицы*/

CURSOR get_cursor IS
SELECT table_name, column_name
FROM all_tab_columns a
WHERE a.owner = v_owner
AND a.data_type IN (‘NUMBER’, ‘VARCHAR2’)
AND a.table_name NOT LIKE ‘%$%’;

BEGIN

OPEN get_cursor;

LOOP
EXIT WHEN (get_cursor%NOTFOUND);
IF get_cursor%ISOPEN THEN

FETCH get_cursor INTO v_table_name,v_column_name;
query_str := ‘INSERT INTO MySCHEMA.’||v_table_save||’ SELECT DISTINCT »’||v_owner||’.’||v_table_name ||»’ s_table_name, »’|| v_column_name ||»’ s_column_name, ‘||v_column_name||’ s_substring FROM ‘||v_owner||’.’||v_table_name||’ tbl1 WHERE UPPER(tbl1.’||v_column_name||’) LIKE UPPER(»%’||v_substring||’%»)’;
DBMS_OUTPUT.put_line(‘SQL query = ‘|| query_str);
DBMS_OUTPUT.put_line(‘Переменная v_table_name = ‘|| TO_CHAR(v_table_name)||’ и v_column_name =’|| TO_CHAR(v_column_name));
DBMS_OUTPUT.put_line(‘Переменная v_table_name = ‘|| TO_CHAR(get_cursor%ROWCOUNT));
DBMS_OUTPUT.put_line(‘******************************************’);
BEGIN
IF (v_table_name!=v_table_save) THEN
EXECUTE IMMEDIATE query_str;
COMMIT;
END IF;
EXCEPTION
when OTHERS then
dbms_output.put_line(SQLERRM||’ : ‘||SQLCODE);
dbms_output.put_line(query_str);
dbms_output.put_line(‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!’);
END;
ELSE
DBMS_OUTPUT.put_line(‘Курсор не открыт’);
DBMS_OUTPUT.put_line(‘******************************************’);
END IF;
/*EXIT name_loop WHEN (get_cursor%NOTFOUND);*/
END LOOP;
CLOSE get_cursor;

END;

0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x