Thursday, May 21, 2009
Leitura de Arquivos pelo PL/SQL e Java
Dúvida:
Marcio,
Bom dia!
Visitei seu BLOG e encontrei um exemplo pra inserir arquivo num campo BLOB numa tabela de banco de dados do ORACLE.
Gerar Arquivo de Campo BLOB
Funcionou, beleza, porem a minha necessidade seria ,inserir todos os arquivos de um determindo diretorio em uma tabela.
Voce teria alguma dica, ou algum exemplo que poderia me auxiliar ?
Desde já agradeco pela ajuda.
Em minha resposta imediata, informei ao colega que era necessário usar uma procedure java para ler os arquivos do diretório no nível do sistema operacional e, desse modo, usar a coleção da forma que lhe convier. Não sou desenvolvedor java, então fui obrigado a pesquisar, acabei encontrando um ótimo código que irei "emprestar" para meu exemplo. O site onde o encontrei é a asktom, o fonte não pertence ao Tom Kyte e sim ao Anthony Wilson da Austrália. Na mesma thread é possível encontrar outros exemplos assim como excelentes respostas do Tom sobre o assunto.
Eu achei interessante a solução do Anthony na medida em que a informação devolvida é completa. Ele usou um type com informação do tipo: se arquivo; se legível; se gravável; se escondido; tamanho do arquivo em bytes; e última modificação.
ops$marcio:DB11G> set lines 80
ops$marcio:DB11G> desc DIR_ENTRY
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_TYPE VARCHAR2(1)
READABLE VARCHAR2(1)
WRITEABLE VARCHAR2(1)
HIDDEN VARCHAR2(1)
FILE_SIZE NUMBER
MODIFIED DATE
NAME VARCHAR2(4000)
Meu exemplo dar-se-á em um Oracle 11g com o java (JVM) rodando sobre Linux EL5. Caso haja interesse em reproduzir o exemplo, por favor, cheque suas configurações e verifique se estão parecidas.
[marcio@pc scripts]$ uname -a
Linux pc.mportes.local 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux
[marcio@pc scripts]$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5 (Carthage)
[marcio@pc scripts]$ sqlplus /
SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 21 00:30:24 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$marcio:DB11G> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
5 rows selected.
ops$marcio:DB11G> select COMP_NAME, VERSION, STATUS from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle Application Express 3.2.0.00.27 VALID
OWB 11.1.0.6.0 VALID
Oracle Enterprise Manager 11.1.0.7.0 VALID
Oracle Ultra Search 11.1.0.7.0 VALID
OLAP Catalog 11.1.0.7.0 VALID
Spatial 11.1.0.7.0 VALID
Oracle Multimedia 11.1.0.7.0 VALID
Oracle XML Database 11.1.0.7.0 VALID
Oracle Text 11.1.0.7.0 VALID
Oracle Expression Filter 11.1.0.7.0 VALID
Oracle Rules Manager 11.1.0.7.0 VALID
Oracle Workspace Manager 11.1.0.7.0 VALID
Oracle Database Catalog Views 11.1.0.7.0 VALID
Oracle Database Packages and Types 11.1.0.7.0 VALID
JServer JAVA Virtual Machine 11.1.0.7.0 VALID
Oracle XDK 11.1.0.7.0 VALID
Oracle Database Java Packages 11.1.0.7.0 VALID
OLAP Analytic Workspace 11.1.0.7.0 VALID
Oracle OLAP API 11.1.0.7.0 VALID
Em resumo, após criar a procedure java para fazer a leitura dos arquivos no diretório, vou salvá-los em uma tabela. Note que é possível usar a cláusula WHERE para selecionar os arquivos de acordo com os campos do type DIR_ENTRY, exemplo: todos os arquivos a partir de uma data; trazer somente os arquivos visíveis, etc. Neste caso, vou subir todos meus archives '/oracle/arch'.
ops$marcio:DB11G> !ls -l /oracle/arch
total 2377612
-rw-r----- 1 oracle dba 100942848 May 20 02:23 1_100_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 02:23 1_101_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 15:33 1_102_683430221.dbf
-rw-r----- 1 oracle dba 18439168 May 20 21:20 1_103_683430221.dbf
-rw-r----- 1 oracle dba 57431552 Apr 27 22:15 1_17_683430221.dbf
-rw-r----- 1 oracle dba 90300416 May 17 19:56 1_90_683430221.dbf
-rw-r----- 1 oracle dba 73134080 May 17 21:35 1_91_683430221.dbf
-rw-r----- 1 oracle dba 9502208 May 19 00:03 1_92_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 19 00:39 1_93_683430221.dbf
-rw-r----- 1 oracle dba 42433536 May 19 14:20 1_94_683430221.dbf
-rw-r----- 1 oracle dba 19104768 May 19 20:17 1_95_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 00:48 1_96_683430221.dbf
-rw-r----- 1 oracle dba 100937216 May 20 02:22 1_97_683430221.dbf
-rw-r----- 1 oracle dba 100942336 May 20 02:22 1_98_683430221.dbf
-rw-r----- 1 oracle dba 100940288 May 20 02:22 1_99_683430221.dbf
ops$marcio:DB11G> create or replace type
2 dir_entry as object (
3 file_type varchar2(1),
4 readable varchar2(1),
5 writeable varchar2(1),
6 hidden varchar2(1),
7 file_size number,
8 modified date,
9 name varchar2(4000)
10 );
11 /
Type created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace type dir_array as table of dir_entry;
2 /
Type created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace and compile java source named "Util" as
2
3 import java.io.File;
4 import java.io.FilenameFilter;
5 import java.io.IOException;
6
7 import java.sql.Connection;
8 import java.sql.SQLException;
9 import java.sql.ResultSet;
10 import java.sql.PreparedStatement;
11 import java.sql.Timestamp;
12
13 import oracle.sql.ARRAY;
14 import oracle.sql.STRUCT;
15 import oracle.sql.ArrayDescriptor;
16 import oracle.sql.StructDescriptor;
17
18 import oracle.jdbc.driver.OracleDriver;
19
20 public class Util {
21
22 private static Connection conn;
23
24 static {
25 try {
26 conn = (new OracleDriver()).defaultConnection();
27 } catch (SQLException e) {
28 System.out.println(e);
29 }
30 }
31
32
33 /**
34 * List the files in the directory represented by the given Oracle DIRECTORY
35 * object.
36 *
37 * @param dirname The name of the DIRECTORY object for which we want to list
38 * the files (case sensitive).
39 * @throws IOException
40 * @throws SQLException
41 */
42 public static ARRAY listFiles(String dirname)
43 throws IOException, SQLException {
44
45 String dirpath = getDirectoryPath(dirname);
46 File directory = getDirectory(dirpath);
47
48 STRUCT[] ret = fileList(directory);
49
50 // Create an array descriptor and return it.
51 ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
52 "DIR_ARRAY", conn);
53
54 return new ARRAY(desc, conn, ret);
55 }
56
57 /**
58 * Create a File object with the abstract pathname given by the parameter.
59 *
60 * @param dirpath The filesystem path of the directory
61 * @throws IOException If the directory represented by this pathname does
62 * not exist, or if it is a file.
63 */
64 private static File getDirectory(String dirpath) throws IOException {
65
66 File directory = new File(dirpath);
67
68 if(!directory.exists()) {
69 throw new IOException("Directory: "+dirpath+" does not exist.");
70 }
71 if(!directory.isDirectory()) {
72 throw new IOException("Path: "+dirpath+" is not a directory.");
73 }
74
75 return directory;
76 }
77
78 /**
79 * Get the filesystem path for the Oracle DIRECTORY object given by the
80 * input parameter.
81 *
82 * @param dir The name of the DIRECTORY object for which we want the path.
83 * @throws IOException If there is no DIRECTORY object with the given name.
84 */
85 private static String getDirectoryPath(String dir)
86 throws SQLException, IOException {
87 String sql = "select directory_path from all_directories where " +
88 "directory_name = ?";
89
90 PreparedStatement s = conn.prepareStatement(sql);
91 s.setString(1, dir);
92 ResultSet rs = s.executeQuery();
93
94 // There should be one row and one only.
95 if(rs.next()) {
96 return rs.getString(1);
97 } else {
98 throw new IOException("Directory object "+dir+" does not exist.");
99 }
100
101 }
102
103 /**
104 * Create an array of STRUCT objects representing the files in the given
105 * directory.
106 *
107 * @param directory The File object representing the directory.
108 * @throws SQLException
109 */
110 private static STRUCT[] fileList(File directory) throws SQLException {
111
112 // Create the array of files to add.
113 File[] files = directory.listFiles (
114 new FilenameFilter() {
115 // Accept all files
116 public boolean accept(File dirpath, String name) {
117 return true;
118 }
119 }
120 );
121
122 // No files in directory
123 if(files == null) {
124 return null;
125 }
126
127 STRUCT[] ret = new STRUCT[files.length];
128
129 // Create the struct entry for each file.
130 for(int i=0; i<files.length; i++) {
131 File f = files[i];
132 StructDescriptor sd = StructDescriptor.createDescriptor (
133 "DIR_ENTRY", conn);
134 Object[] attributes = {
135 f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
136 f.canRead() ? "Y" : "N",
137 f.canWrite() ? "Y" : "N",
138 f.isHidden() ? "Y" : "N",
139 new Long(f.length()),
140 new Timestamp(f.lastModified()),
141 f.getName()
142 };
143 STRUCT s = new STRUCT(sd, conn, attributes);
144 ret[i] = s;
145 }
146
147 return ret;
148 }
149
150 }
151 /
Java created.
ops$marcio:DB11G> create or replace package util
2 as
3 function ls(p_dirname in varchar2) return dir_array;
4 end;
5 /
Package created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace package body util
2 as
3 function ls(p_dirname in varchar2) return dir_array
4 is
5 language java name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
6 end;
7 /
Package body created.
ops$marcio:DB11G>
ops$marcio:DB11G> drop table t purge;
Table dropped.
ops$marcio:DB11G>
ops$marcio:DB11G> create table t ( n varchar2(40), x blob );
Table created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace directory test_dir as '/oracle/arch';
Directory created.
ops$marcio:DB11G>
ops$marcio:DB11G> declare
2 l_blob blob;
3 l_bfile bfile;
4 l_dir varchar2(30) default 'TEST_DIR';
5 begin
6 for x in ( select name from table(util.ls(l_dir)))
7 loop
8 insert into t( n, x ) values ( x.name, empty_blob() ) returning x into l_blob;
9 l_bfile := bfilename( l_dir, x.name );
10 dbms_lob.fileopen( l_bfile );
11 dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
12 dbms_lob.fileclose( l_bfile );
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
ops$marcio:DB11G>
ops$marcio:DB11G> select n, dbms_lob.getlength(x) bytes from t;
N BYTES
---------------------------------------- -------------
1_17_683430221.dbf 57431552
1_97_683430221.dbf 100937216
1_101_683430221.dbf 100943872
1_92_683430221.dbf 9502208
1_98_683430221.dbf 100942336
1_103_683430221.dbf 18439168
1_94_683430221.dbf 42433536
1_102_683430221.dbf 100943872
1_100_683430221.dbf 100942848
1_96_683430221.dbf 100943872
1_95_683430221.dbf 19104768
1_90_683430221.dbf 90300416
1_99_683430221.dbf 100940288
1_91_683430221.dbf 73134080
1_93_683430221.dbf 100943872
15 rows selected.
É bom lembrar que se for a primeira execução, o exemplo possivelmente vai falhar. Propositalmente, deixei esta explicação para o final a fim de advertir que é necessário dar privilégio de gravação no diretório onde a procedure java fará a leitura. Esse tipo de situação deve ser observada já que toda a permissão, especialmente nesse nível, precisa ser inventariada e controlada. Portanto, cuidado quando sugerir esse tipo de implementação em produção. Seguindo o exemplo, vou modificar o caminho do DIRECTORY para provocar o erro e demonstrar como dar o acesso.
ops$marcio:DB11G> create or replace directory test_dir as '/oracle';
Directory created.
ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));
select name from table(util.ls('TEST_DIR'))
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.io.FilePermission /oracle/admin write) has not been granted to OPS$MARCIO. The PL/SQL to grant this is
dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/admin', 'write' )
ops$marcio:DB11G> exec dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/*', 'write')
PL/SQL procedure successfully completed.
ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));
NAME
-----------------------------------------------
admin
lost+found
apex
arch
cfgtoollogs
oradata
oraInventory
11.1.0
diag
ewallet.p12
sqladm
oraInventory_10g
12 rows selected.
ops$marcio:DB11G> col name format a30
ops$marcio:DB11G> select * from table(util.ls('TEST_DIR'));
F R W H FILE_SIZE MODIFIED NAME
- - - - ------------- ------------------- ------------------------------
D Y N N 4096 25/07/2008 01:04:16 admin
D Y Y N 16384 07/05/2007 09:22:07 lost+found
D Y N N 4096 06/04/2009 01:04:34 apex
D Y N N 4096 21/05/2009 00:04:04 arch
D Y N N 4096 07/11/2008 19:53:34 cfgtoollogs
D Y N N 4096 05/11/2008 00:52:45 oradata
D Y Y N 4096 07/11/2008 19:48:13 oraInventory
D Y Y N 4096 19/06/2008 22:33:54 11.1.0
D Y Y N 4096 26/06/2008 01:45:37 diag
F N N N 9973 21/08/2008 00:51:39 ewallet.p12
D Y Y N 4096 27/09/2008 15:34:40 sqladm
D Y N N 4096 30/06/2008 22:16:28 oraInventory_10g
12 rows selected.
Wednesday, January 07, 2009
INSERT ALL
Dúvida:
Dessa forma, foi possível atender os requisitos e completar a tarefa com uma única instrução SQL.
Marcio,
Segue abaixo um exemplo.
Eu leio os dados da tabela t1 e vou inserir em t2 e em t3.
Na tabela t2 vai inserir todos os dados, na tabela t3 só pode
inserir se a coluna c2 da tabela T1 estiver preenchida.
Grato pela atenção.
create table t1 (CL1 NUMBER(3), CL2 NUMBER(3), DATA_CARGA DATE );
create table t2 (CL1 NUMBER(3) PRIMARY KEY, CL2 NUMBER(3), DATA_INSERT DATE, USUARIO VARCHAR2(30) );
create table t3 (CL1 NUMBER(3) PRIMARY KEY, CL2 NUMBER(3) NOT NULL , DATA_INSERT DATE, USUARIO VARCHAR2(30) );
INSERT INTO T1(CL1, CL2) VALUES (1,10);
INSERT INTO T1(CL1, CL2) VALUES (2,20);
INSERT INTO T1(CL1) VALUES (3);
INSERT INTO T1(CL1) VALUES (4);
INSERT INTO T1(CL1) VALUES (5);
Quando o colega me enviou essa dúvida pela primeira vez, ele gostaria que eu o ajudasse a depurar um problema em um código procedural onde ele abria um cursor para ler a tabela T1 e inserir nas tabelas T2 e T3. Muitas vezes esquecemos dos recursos que o Oracle oferece e o código procedural é o primeiro que aparece para solucionarmos o problema, mas vale a pena dar uma paradinha para verificar se com SQL puro não se consegue o mesmo resultado só que com a performance bem melhor. No problema acima, o colega poderia esquecer o código procedural (cursor) e usar o INSERT ALL.
ops$marcio:DB11G> select * from t1;
CL1 CL2 DATA_CARGA
------------- ------------- -------------------
1 10
2 20
3
4
5
5 rows selected.
ops$marcio:DB11G> select * from t2;
no rows selected
ops$marcio:DB11G> select * from t3;
no rows selected
ops$marcio:DB11G>
ops$marcio:DB11G> insert all
2 when ( cl2 is not null ) then
3 into t3( cl1, cl2, data_insert, usuario )
4 values ( cl1, cl2, data_carga, user )
5 when ( 1=1 ) then
6 into t2( cl1, cl2, data_insert, usuario )
7 values ( cl1, cl2, data_carga, user )
8 select *
9 from t1
10 /
7 rows created.
ops$marcio:DB11G>
ops$marcio:DB11G> select * from t1;
CL1 CL2 DATA_CARGA
------------- ------------- -------------------
1 10
2 20
3
4
5
5 rows selected.
ops$marcio:DB11G> select * from t2;
CL1 CL2 DATA_INSERT USUARIO
------------- ------------- ------------------- ------------------------------
1 10 OPS$MARCIO
2 20 OPS$MARCIO
3 OPS$MARCIO
4 OPS$MARCIO
5 OPS$MARCIO
5 rows selected.
ops$marcio:DB11G> select * from t3;
CL1 CL2 DATA_INSERT USUARIO
------------- ------------- ------------------- ------------------------------
1 10 OPS$MARCIO
2 20 OPS$MARCIO
2 rows selected.
Dessa forma, foi possível atender os requisitos e completar a tarefa com uma única instrução SQL.
Labels: Duvidas
Saturday, July 07, 2007
Consulta Ignorando Acentos
Algum tempo atrás, me fizeram uma pergunta sobre consulta sem considerar acentos. Quando respondi, falhei em pesquisar um pouco mais a fundo e então sugeri uma opção trabalhosa para resolver o problema.
Semana passada, fiz uma página de "eWho" - procura de ramais - que necessitava exatamente do mesmo mecanismo de busca, ou seja, que ignorasse acentos, maísculas e minúsculas. Como sempre recorro ao meu blog, vi no artigo que eu tinha que fazer uma tabela de conversão, pensei "não é possível!" e como sou muito preguiçoso, me restou ler o manual de globalização e encontrar exatamente o que precisava no capítulo 5.
Bastou dois alter session e a query vai ignorar os acentos.
Semana passada, fiz uma página de "eWho" - procura de ramais - que necessitava exatamente do mesmo mecanismo de busca, ou seja, que ignorasse acentos, maísculas e minúsculas. Como sempre recorro ao meu blog, vi no artigo que eu tinha que fazer uma tabela de conversão, pensei "não é possível!" e como sou muito preguiçoso, me restou ler o manual de globalização e encontrar exatamente o que precisava no capítulo 5.
Bastou dois alter session e a query vai ignorar os acentos.
ops$marcio:LX10G> select * from t where x = 'marcio';
no rows selected
ops$marcio:LX10G> alter session set nls_comp=linguistic;
Session altered.
ops$marcio:LX10G> alter session set nls_sort=binary_ai;
Session altered.
ops$marcio:LX10G> select * from t where x = 'marcio';
X
--------------------
Márcio
MÁRCIO
MARCIO
3 rows selected.
Tuesday, May 15, 2007
Criacao de Usuario Customizado (Duvida)
pessoal eu estou no oracle 10g e queria saber se existe a possibilidade de fazer o seguinte. Tenho uma tabela de usuário onde tem login e senha.. e gostaria de como ficaria a trigger após o insert para fazer um create user xxx identify by xxxx .... e dar o grant de create session to xxxx para o usuário do oracle...
E o exemplo completo.
ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G>
sys:LX10G> grant create user to ops$marcio;
Grant succeeded.
sys:LX10G> grant create session to ops$marcio with admin option;
Grant succeeded.
sys:LX10G>
sys:LX10G>
sys:LX10G> connect /
Connected.
ops$marcio:LX10G>
ops$marcio:LX10G> create table usuarios ( username varchar2(30), senha varchar2(30));
Table created.
ops$marcio:LX10G>
ops$marcio:LX10G> create or replace
2 procedure create_user (
3 p_username in usuarios.username%type,
4 p_senha in usuarios.senha%type
5 ) is
6 l_create long := 'create user '|| p_username ||' identified by '|| p_senha;
7 l_grant long := 'grant create session to '|| p_username;
8 begin
9 execute immediate( l_create );
10 execute immediate( l_grant );
11 end;
12 /
Procedure created.
ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure run_create (
2 p_username in usuarios.username%type,
3 p_senha in usuarios.senha%type
4 ) is
5 pragma autonomous_transaction;
6 l_job number;
7 begin
8 dbms_job.submit( l_job, 'create_user( '''|| p_username ||''','''|| p_senha ||''');');
9 commit;
10 end;
11 /
Procedure created.
ops$marcio:LX10G>
ops$marcio:LX10G> create or replace trigger tgr_aifer_usuarios
2 after insert on usuarios
3 for each row
4 begin
5 run_create( :new.username, :new.senha );
6 end;
7 /
Trigger created.
ops$marcio:LX10G>
ops$marcio:LX10G> show user
USER is "OPS$MARCIO"
ops$marcio:LX10G>
ops$marcio:LX10G> insert into usuarios values ('novo_usuario', 'senha01');
1 row created.
ops$marcio:LX10G>
ops$marcio:LX10G> exec dbms_lock.sleep(5)
PL/SQL procedure successfully completed.
ops$marcio:LX10G>
ops$marcio:LX10G> select * from usuarios;
USERNAME SENHA
-------------------- ------------------------------
novo_usuario senha01
1 row selected.
ops$marcio:LX10G>
ops$marcio:LX10G> select username, password
2 from dba_users
3 where username = 'NOVO_USUARIO'
4 /
USERNAME PASSWORD
-------------------- ------------------------------
NOVO_USUARIO 25249E3F24F1A585
1 row selected.
ops$marcio:LX10G>
ops$marcio:LX10G> connect novo_usuario/senha01
Connected.
novo_usuario:LX10G>
Labels: Duvidas
Friday, April 27, 2007
Query Ignorando Acentos
Olá Marcio, tudo bem contigo?
Eu estava lendo o seu blog e li o assunto sobre acentuação, fiz os testes igual ao seu e funcionou beleza.
O que esta acontecendo comigo, instalei o Oracle 10g XE e importei uma tabela de endereços que já vieram com acentos.
Bom quando faço uma pesquisa EX:
SELECT * FROM CEP_ENDERECO
WHERE LOWER(DSC_LOGRADOURO) LIKE LOWER('%ALÍPIO%') -> Retorna True
Mas não consigo retornar um registo que esta como ALIPIO, ou seja tenho que fazer uma pesquisa com acento e outra sem.
Para responder essa dúvida, é necessário explicar um conceito antes: 'Á' é diferente de 'A', ou seja, a letra "A" com acento agudo é diferente da letra "A" sem acento, então, quando em uma query há uma condição (WHERE) buscando por "A", o Oracle vai comparar números por trás da cena, ele compara os códigos ASCII.
Para conseguir alcançar o objetivo de trazer os nomes ignorando acentos, é preciso uma tradução customizada das letras que possuem acento para suas respectivas, ou seja, transformar a "letra com acento" em "letra sem acento". Em minha proposta para a solução, vou trabalhar com o código ASCII das letras ao invés da figura, isso serve para fixar o conceito da diferença entre as letras acentuadas e "normais".
A visualização do código ASCII é facilmente obtida através da função DUMP. Com ela, poderemos ver a diferença dos códigos entre as letras acentuadas e não-acentuadas. Dessa forma, o Oracle utiliza a comparação dos códigos e chega a conclusão que "Atílio" <> "Atilio".
Para exemplificar, criei uma tabela "t" com um campo "x" varchar2(20) e inseri 4 linhas. Veja a comparação da coluna com o dump ao lado.
ops$marcio:LX10G> col y format a40
ops$marcio:LX10G> select x, dump(x) y from t;
X Y
-------------------- ----------------------------------------
ALIPIO Typ=1 Len=6: 65,76,73,80,73,79
ALÍPIO Typ=1 Len=6: 65,76,205,80,73,79
MÁRCIO Typ=1 Len=6: 77,193,82,67,73,79
MARCIO Typ=1 Len=6: 77,65,82,67,73,79
4 rows selected.
Como podemos observar a partir do resultado acima,
A=65 e Á=193
I=73 e Í=205
Resta agora, "igualar" as letras através de tradução (translate).
Note que mantive o translate apenas das letras acentuadas no exemplo acima para simplificar, porém, para uma solução ampla e definitiva, é preciso de-para de todas as letras desejadas.
ops$marcio:LX10G> select x, translate(x,chr(193)||chr(205), 'AI') y from t;
X Y
-------------------- ----------------------------------------
ALIPIO ALIPIO
ALÍPIO ALIPIO
MÁRCIO MARCIO
MARCIO MARCIO
4 rows selected.
Agora podemos facilmente comparar o registro traduzido a um argumento, como o apresentado pelo autor da dúvida.
Analisando um pouco mais a solução, "de cara" salta aos olhos um problema sério de performance. Eu uso uma função em um campo e, portanto, inviabiliza o uso de índice, mas isso já é parte de outro artigo. Não deixe de pesquisar índice baseado em função para dimunir o tempo de busca deste tipo de query. Outro ponto também é o "cacheamento" de função que começou na versão 9i e está muito melhor na versão 10g, o Oracle "cachea" a função e depois resolve a query, isso evita muitas vezes um exagerado número de switch de contexto.
ops$marcio:LX10G> select x from t where translate(x,chr(205)||chr(193),'IA') like '%ALIPIO%';
X
--------------------
ALIPIO
ALÍPIO
2 rows selected.
ops$marcio:LX10G> select x from t where translate(x,chr(205)||chr(193),'IA') like '%MARCIO%';
X
--------------------
MÁRCIO
MARCIO
2 rows selected.
Labels: Duvidas
