Pesquisando uma palavra ou texto em um schema do banco de dados Oracle

Olá,

Você como DBA ou desenvolvedor PL/SQL já se deparou em uma situação onde alguém lhe perguntou se existiria a possibilidade de pesquisar uma palavra ou texto em todas as colunas de todas as tabelas de um schema de um banco de dados Oracle? Bom, o objetivo desse artigo será demonstrar a utilização de uma stored procedure criada especificamente para esse propósito. A stored procedure deverá receber dois parâmetros. O primeiro parâmetro será a palavra ou texto a ser pesquisado e o segundo parâmetro será o schema no banco de dados que será alvo da pesquisa. A stored procedure utilizará uma instrução SELECT para realizar a pesquisa nas colunas das tabelas utilizando o operador LIKE, ou seja, neste caso poderão ser utilizados wildcards como o sinal de porcentagem ('%') e o underscore ('_'). Vale a pena salientar que a pesquisa será realizada apenas em colunas do tipo VARCHAR2, VARCHAR, CHAR, CLOB e DATE. Dependendo do número de registros envolvidos nas tabelas, a pesquisa poderá levar horas e consumir um tempo de processamento considerável. Para minimizar a espera pela execução completa da pesquisa, irei criar uma tabela que chamarei de TEMP_PESQUISA que será utilizada para armazenar os resultados parciais durante a execução da stored procedure. A tabela TEMP_PESQUISA conterá 3 colunas que armazenarão o nome da tabela na qual foram encontradas ocorrências da palavra ou texto pesquisado, o nome da coluna em que foi encontrada a palavra ou texto pesquisado e o número de ocorrências encontradas, por coluna. Enfim, irei criar abaixo duas tabelas no schema de banco de dados SCOTT a fim de simular o uso da stored procedure SP_PESQUISA_DADOS que irei criar mais abaixo:

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Sáb Nov 2 11:34:37 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table T1
  2  (
  3     id        number,
  4     t1_col1   varchar2 (100),
  5     t1_col2   varchar (20),
  6     t1_col3   char (50),
  7     t1_col4   clob,
  8     t1_col5   date
  9  );

Tabela criada.

SQL> create table T2
  2  (
  3     id        number,
  4     t2_col1   varchar2 (100),
  5     t2_col2   varchar (20),
  6     t2_col3   char (50),
  7     t2_col4   clob,
  8     t2_col5   date
  9  );

Tabela criada.

Após a criação das tabelas acima, irei popular as mesmas com os registros abaixo:

SQL> insert into T1
  2  values (1,'teste1','Teste 03/11/2013','Oracle','Teste 123 oracle blog','03/11/2013');

1 linha criada.

SQL> insert into T1
  2  values (2,'teste2','Blog','123 teste','123 oracle blog 03/11/2012','02/11/2013');

1 linha criada.

SQL> insert into T2
  2  values (1,'Teste1','Teste 03/11/2013','Oracle','Teste345 oracle blog','03/11/2013');

1 linha criada.

SQL> insert into T2
  2  values (2,'TESTE2','Blog Oracle','123teste456 2013','123 Oracle blog 2011','12/10/2013');

1 linha criada.

SQL> commit;

Commit concluído.

Agora irei criar a tabela auxiliar TEMP_PESQUISA e a stored procedure SP_PESQUISA_DADOS no usuário SYSTEM.

SQL> connect system/manager
Conectado.

SQL> create table temp_pesquisa
  2  (  table_name varchar2(30),
  3     column_name varchar2(30),
  4     count number
  5  );

Tabela criada.


SQL> CREATE OR REPLACE PROCEDURE sp_pesquisa_dados (p_information   IN VARCHAR2,
  2                                                 p_owner         IN VARCHAR2)
  3  IS
  4  BEGIN
  5     DECLARE
  6        v_information   VARCHAR2 (100);
  7        v_owner         VARCHAR2 (30);
  8        v_count         NUMBER := 0;
  9     BEGIN
 10        v_information := UPPER (p_information);
 11        v_owner := UPPER (p_owner);
 12
 13        FOR rec
 14           IN (  SELECT b.table_name, b.column_name
 15                   FROM all_tables a, all_tab_columns b
 16                  WHERE     a.table_name = b.table_name
 17                        AND a.owner = b.owner
 18                        AND b.owner = v_owner
 19                        AND data_type IN
 20                               ('VARCHAR2', 'VARCHAR', 'CHAR', 'CLOB', 'DATE')
 21               ORDER BY b.table_name, b.column_name)
 22        LOOP
 23           EXECUTE IMMEDIATE
 24                 'select count(*) from '
 25              || p_owner
 26              || '.'
 27              || rec.table_name
 28              || ' where upper('
 29              || rec.column_name
 30              || ') like '''
 31              || v_information
 32              || ''''
 33              INTO v_count;
 34
 35           IF v_count > 0
 36           THEN
 37              INSERT INTO temp_pesquisa
 38                   VALUES (rec.table_name, rec.column_name, v_count);
 39
 40              COMMIT;
 41           END IF;
 42        END LOOP;
 43     END;
 44  END;
 45  /

Procedimento criado.

Com os objetos devidamente criados, irei simular uma pesquisa no schema SCOTT. No exemplo abaixo irei pesquisar quais tabelas e colunas que possuam em seu conteúdo a palavra 'teste', seja ela no início, meio ou fim de uma cadeia de caracteres. Vale a pena salientar que a pesquisa também será "case insensitive", ou seja, tanto faz informar a palavra 'teste', 'TESTE', 'Teste', etc.

SQL> exec sp_pesquisa_dados('%teste%','SCOTT');

Procedimento PL/SQL concluído com sucesso.

SQL> select * from temp_pesquisa;

TABLE_NAME                     COLUMN_NAME                         COUNT
------------------------------ ------------------------------ ----------
T1                             T1_COL1                                 2
T1                             T1_COL2                                 1
T1                             T1_COL3                                 1
T1                             T1_COL4                                 1
T2                             T2_COL1                                 2
T2                             T2_COL2                                 1
T2                             T2_COL3                                 1
T2                             T2_COL4                                 1

8 linhas selecionadas. 

Após a execução da stored procedure, podemos verificar acima que o resultado da pesquisa encontrou 10 ocorrências. Segue abaixo outra pesquisa na qual irei pesquisar a palavra 'oracle', seja ela no início, meio ou fim de uma cadeia de caracteres.

SQL> truncate table temp_pesquisa;

Tabela truncada.

SQL> exec sp_pesquisa_dados('%oracle%','SCOTT');

Procedimento PL/SQL concluído com sucesso.

SQL> select * from temp_pesquisa;

TABLE_NAME                     COLUMN_NAME                         COUNT
------------------------------ ------------------------------ ----------
T1                             T1_COL3                                 1
T1                             T1_COL4                                 2
T2                             T2_COL2                                 1
T2                             T2_COL3                                 1
T2                             T2_COL4                                 2

5 linhas selecionadas. 

Para finalizar, irei pesquisar o valor '2013' de forma a demonstrar que também será possível pesquisar caracteres em uma coluna do tipo DATE quando for necessário realizar algum tipo de pesquisa que envolvam datas.

SQL> truncate table temp_pesquisa;

Tabela truncada.

SQL> alter session set nls_date_format='dd/mm/yyyy';

Sessão alterada.

SQL> exec sp_pesquisa_dados('%2013%','SCOTT');

Procedimento PL/SQL concluído com sucesso.

SQL> select * from temp_pesquisa;

TABLE_NAME                     COLUMN_NAME                         COUNT
------------------------------ ------------------------------ ----------
T1                             T1_COL2                                 1
T1                             T1_COL5                                 2
T2                             T2_COL2                                 1
T2                             T2_COL3                                 1
T2                             T2_COL5                                 2

5 linhas selecionadas.