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.