Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sábado, 2 de novembro de 2013

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

Por Eduardo Legatti

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.



17 comentários:

Anônimo disse...

Prezado Eduardo, tentei implementar esse script no ambiente. O cursor não está retornando nenhuma linha o mesmo retorna se comentar a linha do SQL "b.owner=v_owner" tem alguma ideia o que pode ser?

Eduardo Legatti disse...

Olá Anônimo,

A stored procedure foi criada no usuário SYSTEM? Você está executando ela estando conectado com ele? Você está passando como parâmetro o nome do schema correto? Quando você remove o join "b.owner=v_owner" na verdade você está deixando de filtrar o schema que você passou como parâmetro, ou seja, a pesquisa será feita em todos os schemas do banco de dados.

Se nenhuma linha está sendo retornada, é porque nenhuma linha de tabela contém a palavra pesquisada no schema que você passou. Quando você remove o join são retornados registros do schema que você tinha passado como parâmetro?

Abraços

Legatti

Anônimo disse...

Olá Eduardo,

Obrigado pela atenção.

O nome do esquema está correto e o usuário tem permissões para executar essa query mesmo não sendo o SYSTEM. Inclusive, copiando a query do cursor para um outro arquivo script no sqldeveloper com o mesmo valor do schema e o mesmo usuário retorna os valores normalmente.

Tens alguma idéia o que pode ser?
Tem alguma coisa haver eu executar em ambiente SQL Developer e a mesma foi executado nesse blog em SQLPLUS?

Mais uma vez muito obrigado pela atenção.

Eduardo Legatti disse...

Olá Anônimo,

O problema não está relacionado ao SQL Developer ou ao SQL*Plus. Perceba que eu utilizo as views ALL_* dentro da procedure. Certique-se de que o usuário que é o owner da procedure SP_PESQUISA_DADOS tenha o privilégio de sistema SELECT ANY TABLE.

Abraços

Legatti

Unknown disse...

Olá!
Primeiramente, quero agradecer pelas postagens, sempre muito úteis.
Seria gentileza me ajudar numa busca.Uso o banco Oracle 11g e preciso selecionar todos os campos de todas as tabelas, cujo tipo de dado armazenado é clob, independentemente de owner ou schema.
Na minha ignorância, seria algo do tipo:

Select * from database
Where collumn datatype is "clob"


Alguma resposta?

Abraços

Lorena

Eduardo Legatti disse...

Olá Lorena,

Basta acessar a view DBA_TAB_COLUMNS como no SELECT abaixo:

select owner,table_name,column_name,data_type
from DBA_TAB_COLUMNS
where data_type='CLOB';


Abraços e até mais ...

Legatti

Unknown disse...

Muito prestativo!Agradeço mais uma vez.

Até :*

Unknown disse...

Boa tarde.

Eu fiz exatamente como no exemplo. Algumas vezes funciona, outras aparece o seguinte erro:

ORA-00907: parênteses direito não encontrado

Fiz debugando e erro acontece no trecho de código abaixo:

EXECUTE IMMEDIATE

'select count(*) from '
|| p_owner
|| '.'
|| rec.table_name
|| ' where upper('
|| rec.column_name
|| ') like '''
|| v_information
|| ''''
INTO v_count;


Tem alguma ideia do que seja?

Eduardo Legatti disse...

Olá Glauco,

Parece que algum caractere deveria estar sendo escapado como alguma aspas ou algo do tipo e é o que deve estar atrapalhando a execução desse SELECT para alguma tabela específica. Sugiro você tentar debugar a procedure de forma que você consiga capturar exatamente as informações do que o SELECT está tentando executar.

Abraços,

Legatti

Souza disse...

Boa tarde.

Vim do futuro para dizer que em 22/12/2022 o script funciona perfeitamente.

Eduardo Legatti disse...

Jóia!!! ✌

Tia Monica Artesanatos disse...

Bom dia! A procedure não esta funcionando. Será que podem me ajudar??
Aparece a seguinte mensagem de erro:

-------------------------------------------------
Erro a partir da linha : 43 no comando -
BEGIN sp_pesquisa_dados('%te%lefonia','ALUNO'); END;
Relatório de erros -
ORA-00923: palavra-chave FROM não localizada onde esperada
ORA-06512: em "SYSTEM.SP_PESQUISA_DADOS", line 20
ORA-06512: em "SYSTEM.SP_PESQUISA_DADOS", line 20
ORA-06512: em line 1
00923. 00000 - "FROM keyword not found where expected"

*Cause:
*Action:
--------------------------------------------------------
o ERRO ESTA NESTE SELECT AI.....(É ESPERADO A CLAUSULA INTO NESTA INSTRUCAO SELECT)
-----------------------------------------------
FOR rec
IN (SELECT b.table_name, b.column_name
FROM all_tables a, all_tab_columns b
WHERE a.table_name = b.table_name
AND a.owner = b.owner
AND b.owner = v_owner
AND data_type IN ('VARCHAR2', 'VARCHAR', 'CHAR', 'CLOB', 'DATE')
ORDER BY b.table_name, b.column_name)

Eduardo Legatti disse...

Olá,

A tabela temp_pesquisa foi criada no usuário SYSTEM?

A consulta abaixo retorna algum dado?

SELECT b.table_name, b.column_name
FROM all_tables a, all_tab_columns b
WHERE a.table_name = b.table_name
AND a.owner = b.owner
AND b.owner = 'ALUNO'
AND data_type IN
('VARCHAR2', 'VARCHAR', 'CHAR', 'CLOB', 'DATE')
ORDER BY b.table_name, b.column_name

Tia Monica Artesanatos disse...

criei na tabela system. Não retorna nenhum resultado.

Eduardo Legatti disse...

Não existe tabelas no schema ALUNO?

A procedure você está executando estando conectado com o usuário SYSTEM?

Tia Monica Artesanatos disse...

A pesquisa retorna sim...todas as tabelas e colunas do owner "aluno"
A procedure foi criada no usuario SYSTEM

Eduardo Legatti disse...

Qual é a versão do seu banco de dados Oracle?

Postagens populares