Blog do Marcio Portes

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).

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.

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.

Agora podemos facilmente comparar o registro traduzido a um argumento, como o apresentado pelo autor da dúvida.

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.

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.

Labels: Duvidas

Nos últimos dias, recebi vários e-mails com dúvidas sobre o Treinamento que ministro. Gostaria de elucidar um pouco mais como trabalho nesse curso.

1. O Treinamento é exclusivo para empresas. Por que? Quando digo exclusivo, é porque escrevo o curso para a empresa, ou seja, não existe um curso igual a outro. Juntamente com a empresa, é discutido o "Estudo de Caso" e a partir do escopo fechado, levo uma semana escrevendo os slides, exemplos e conceitos que vou apresentar ao longo do treinamento. Todo esse material fica na empresa.
2. O "Estudo de Caso" do treinamento pode e deve ser real, portanto, a empresa terá uma discussão de um problema real. Todos os envolvidos no treinamento apreenderão com maior facilidade o que for apresentado, porque estarão atuando com a matéria diretamente no dia-a-dia. O Treinamento terá flexibilidade na agenda para desvios dentro do programa.
   
3. Para este modelo de treinamento, o número ideal para participantes é de 8 pessoas no mínimo e 20 no máximo. Existem outras modalidades, onde a discussão é mais ampla e genérica, comportando maior número de pessoas - modalidade Palestra.
4. Este curso vem com plantão de dúvida, um período onde os alunos podem acessar diretamente o instrutor com alguma dúvida extra, pertinente ao treinamento após o término do curso, que deverá ser respondida em até 48 horas do recebimento. A dúvida deve ser enviada ao instrutor somente através de e-mail e se houver entendimento por parte do instrutor, ele entra em contato on-line (Telefone ou chat). Nesses casos, no ato do contrato é definido o período do plantão de dúvidas.

Penso que esses pontos são os principais diferencias que apresento em meu Treinamento, para saber mais sobre o curso acesse aqui.

Sexta-feira, 13. Vou escrever amenidades, sem pensar muito, então revirei algumas coisas aqui e achei uma procedurizinha, que a época, serviu bem aos propósitos. Seguinte: os analistas que desenvolvem muitas vezes não tem acesso ao "show parameter <...>" e solicitam acesso a v$parameter, então, eu codifiquei um quebra-galho para mostrar os parâmetros mesmo não possuindo acesso a v$parameter.

O código é antigo, feio, todo mundo começa fazendo código feio mesmo, não vou mexer para postar aqui, então sintam-se a vontade para mudar na medida em que necessitem. Acho que esse código eu usei com o 8.1.6, algo assim - é antigão mesmo, mas para uma sexta-feira 13, está de bom tamanho.

O código

create or replace
procedure show_parameter ( p_name in varchar2,
   p_debug in boolean default false)
as
    l_intval number(7);
    l_strval varchar2(512) default 'Not found';
    l_return number(7);
    l_outval varchar2(512);
    l_outtyp varchar2(30);
    l_name   varchar2(32)  default '%' || p_name || '%';

begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line(rpad('Name', 36, ' ') ||' '||
      rpad('Type', 10, ' ') || ' ' || rpad('Value', 36, ' '));
    dbms_output.put_line(rpad('-', 36, '-') ||' '||
      rpad('-',10,'-') || ' '|| rpad('-', 30, '-'));

    for x in (select name, type from my$parameter where name like l_name order by 1 )
    loop
       if ( p_debug ) then
          dbms_output.put_line(chr(10)||'Before DBMS_UTILITY (' || x.name ||', '|| x.type ||')');
       end if;

       l_return := dbms_utility.get_parameter_value(trim(x.name), l_intval, l_strval);

       if ( p_debug ) then
          dbms_output.put('After DBMS_UTILITY: ');
          dbms_output.put_line(l_return || ', ' || l_intval ||', '|| l_strval);
       end if;

       select decode(x.type, 1, 'boolean'
                           , 2, 'string'
                           , 3, 'integer'
                           , 4, 'file', to_char(x.type)),
              decode(x.type, 1, decode(l_intval,1,'TRUE','FALSE')
                           , 2, l_strval
                           , 3, to_char(l_intval), l_strval)
         into l_outtyp, l_outval
         from dual;

       if ( p_debug ) then
          dbms_output.put_line('Did decode ');
       end if;

       dbms_output.put_line(rpad(x.name,36,' ') ||' '|| rpad(l_outtyp, 10, ' ') || ' ' ||
                l_outval );

       if ( p_debug ) then
          dbms_output.put_line(rpad('-', 78, '-'));
       end if;

    end loop;
end;
/

E os procedimentos para instalação
Instruções
- O usuário dono da procedure deve ter direito de acesso (direto) a v$parameter.
- Criar uma view da v$parameter.
- Criar a procedure e dar privilégio de execução para PUBLIC.
- Criar um sinônimo público da procedure.
- Testar.

ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G> grant select on v_$parameter to ops$marcio;

Grant succeeded.

sys:LX10G> connect /
Connected.
ops$marcio:LX10G> create view my$parameter as select * from sys.v_$parameter;

View created.

ops$marcio:LX10G> alter procedure show_parameter compile;

Procedure altered.

ops$marcio:LX10G> create or replace public synonym show_parameter for show_parameter;

Synonym created.

ops$marcio:LX10G> connect scott/tiger
Connected.
scott:LX10G> show parameter dump
ORA-00942: table or view does not exist


scott:LX10G> exec show_parameter( 'dump' )


Name                                 Type       Value
------------------------------------ ---------- ------------------------------
background_core_dump                 string     partial
background_dump_dest                 string     /oracle/admin/lx10g/bdump
core_dump_dest                       string     /oracle/admin/lx10g/cdump
max_dump_file_size                   string     UNLIMITED
shadow_core_dump                     string     partial
user_dump_dest                       string     /oracle/admin/lx10g/udump

PL/SQL procedure successfully completed.

Tem até debug.

scott:LX10G> exec show_parameter( 'dump', true )


Name                                 Type       Value
------------------------------------ ---------- ------------------------------

Before DBMS_UTILITY (background_core_dump, 2)
After DBMS_UTILITY: 1, 7, partial
Did decode
background_core_dump                 string     partial
------------------------------------------------------------------------------

Before DBMS_UTILITY (background_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/bdump
Did decode
background_dump_dest                 string     /oracle/admin/lx10g/bdump
------------------------------------------------------------------------------

Before DBMS_UTILITY (core_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/cdump
Did decode
core_dump_dest                       string     /oracle/admin/lx10g/cdump
------------------------------------------------------------------------------

Before DBMS_UTILITY (max_dump_file_size, 2)
After DBMS_UTILITY: 1, 9, UNLIMITED
Did decode
max_dump_file_size                   string     UNLIMITED
------------------------------------------------------------------------------

Before DBMS_UTILITY (shadow_core_dump, 2)
After DBMS_UTILITY: 1, 7, partial
Did decode
shadow_core_dump                     string     partial
------------------------------------------------------------------------------

Before DBMS_UTILITY (user_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/udump
Did decode
user_dump_dest                       string     /oracle/admin/lx10g/udump
------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Quem nunca ouviu dizer, que devemos evitar o uso de funções em comparações na clausula WHERE, porque podemos invalidar o uso de índice caso esse exista. É uma boa prática de programação e o que vou demonstrar abaixo não a invalida, porém, mostra a capacidade do otimizador em identificar as informações que damos a ele e como é importante alimentarmos o Oracle com as constraints (primary key, not null, foreign key, checks, etc).

Iniciamos o teste com um tabela com aproximadamente 52 mil registros, nela, haverá duas constraints - uma primary key (o_id) e um check (oname), também haverá um índice normal na coluna (oname). Depois de coletar as estatísticas, farei uma consulta com o predicado UPPER(oname) = 'valor' cujo o uso invalidaria o indice, porém em nosso exemplo, o otimizador sabe que a coluna não permite nulo e só permite valor em UPPER case, então ele faz a dedução e decide usar o índice.

Essa capacidade de entender e decidir usar o índice já está valendo desde a versão 9i. Juntamente com essa demonstração, gostaria de mostrar algumas melhorias que vieram com o 10g. Na demo, há uma query principal que faz a projeção das colunas que estou selecionando e uma subquery, onde busco o predicado UPPER e o max( da primary key ). Agora é possível nomear as subqueries e as queries de uma sentença com o hint /*+ qb_name() */ (Query Block Name) e a partir dessa mesma versão, o dbms_xplan dá mais informações, inclusive dos nomes destas queries.

Preparando o exemplo e coletando estatísticas

ops$marcio:LX10G> create table t (
  2          o_id            number constraint t_pk primary key,
  3          oname           varchar2(30) not null,
  4          padding         varchar2(200),
  5          constraint t_chk_oname check ( oname = upper(oname) )
  6  );

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create index t_i1 on t ( oname );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> insert /*+ append */ into t
  2  select object_id,
  3         substr(upper(object_name),1,30),
  4         lpad('x',200,'x')
  5    from dba_objects
  6  /

51925 rows created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
  2          dbms_stats.gather_table_stats(
  3                  user,
  4                  'T',
  5                  cascade => true
  6          );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Limpar a plan_table

ops$marcio:LX10G>
ops$marcio:LX10G> delete from plan_table;

6 rows deleted.

ops$marcio:LX10G> commit;

Commit complete.

ops$marcio:LX10G>
ops$marcio:LX10G> explain plan for
  2  select /*+ qb_name( principal ) */ *
  3    from t t1
  4   where o_id = ( select /*+ qb_name ( maxid ) */ max(o_id)
  5                    from t t2
  6                   where upper(oname) = 'ALL_OBJECTS' )
  7
  8  /

Explained.

Acima está o exemplo. Note o upper(oname) na linha 6, isso normalmente invalidaria o índice, mas como pode ser visto abaixo, ele usou o índice T_I1, que está relacionado a esta coluna (oname), sem que o índice fosse do tipo FBI (Function Based Index), ou seja, eu não preparei um índice especial para que ele assumisse, não, o índice é um b-tree normal. Note também, os argumentos null, null, 'ALL' que passo para o dbms_xplan.display, isso é novo no 10g e abaixo, repare nas 3 seções que o resultado traz, deixa bem mais fácil a vida da pessoa que está trabalhando com otimização de queries inclusive com os nomes das queries que usei apenas para ilustrar o exemplo.

ops$marcio:LX10G>
ops$marcio:LX10G> select * from table( dbms_xplan.display( null, null, 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2855282288

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |   229 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |     1 |   229 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | T_PK |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |      |     1 |    30 |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    |     1 |    30 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - PRINCIPAL / T1@PRINCIPAL
   2 - PRINCIPAL / T1@PRINCIPAL
   3 - MAXID
   4 - MAXID     / T2@MAXID
   5 - MAXID     / T2@MAXID

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O_ID"= (SELECT /*+ QB_NAME ("MAXID") */ MAX("O_ID") FROM "T"
              "T2" WHERE "ONAME"='ALL_OBJECTS' AND UPPER("ONAME")='ALL_OBJECTS'))
   5 - access("ONAME"='ALL_OBJECTS')
       filter(UPPER("ONAME")='ALL_OBJECTS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O_ID"[NUMBER,22], "T1"."ONAME"[VARCHAR2,30],
       "T1"."PADDING"[VARCHAR2,200]
   2 - "T1".ROWID[ROWID,10], "O_ID"[NUMBER,22]
   3 - (#keys=0) MAX("O_ID")[22]
   4 - "O_ID"[NUMBER,22]
   5 - "T2".ROWID[ROWID,10]

39 rows selected.

Labels: New Feature, Performance

Na lista de discussão oracle_br, tivemos um dúvida relacionada ao uso do LOB. Era preciso descarregar o conteúdo de um campo CLOB em um arquivo, porém a longitude do registro era maior que 32767 (limite documentado do utl_file). Depois de algum bate-bola, chegamos a conclusão que era possível usar o dbms_lob.put_raw para escrever tal linha. Faltou o exemplo e aqui está ele.

• Criei uma tabela com um campo CLOB
• Preenchi esse campo com um registro bem longo (~64k)
• Em um bloco anônimo, criei uma instância de um BLOB, fiz a conversão do CLOB para o BLOB, escrevi em um arquivo usando o utl_file.put_raw.
• Gostaria de chamar a atenção no momento da abertura do arquivo, já que usei como argumento o 'wb', novo na versão 10g. Significa: abrindo arquivo para recber binário.
• Para testar se o arquivo foi criado corretamente, usei o comando wc -L do unix para trazer o tamanho da maior linha do arquivo.
• Teste executado na versão 10gR2 (10.2.0.2) e Enterprise Linux.

ops$marcio:LX10G> create table t ( x clob );

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace directory my_files as '/tmp';

Directory created.

ops$marcio:LX10G>
ops$marcio:LX10G> declare
  2       l_clob     clob;
  3  begin
  4       loop
  5          exit when dbms_lob.getlength( l_clob ) > 50000;
  6          l_clob := l_clob || rpad('z', 32760, 'x') ;
  7       end loop;
  8
  9       insert into t values ( l_clob ) ;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select dbms_lob.getlength( x ) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
                65520

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> declare
  2          l_output        utl_file.file_type;
  3          l_blob          blob;
  4          l_clob          clob;
  5          l_amt           number default 32000;
  6          l_off           number default 1;
  7          l_raw           raw(32000);
  8          l_length        number;
  9
 10          l_warning       number;
 11          l_doff7         number default 1;
 12          l_soff7         number default 1;
 13          l_lang_ctx      number default dbms_lob.default_lang_ctx;
 14
 15  begin
 16          l_output := utl_file.fopen( 'MY_FILES', 'bigclob.txt', 'wb', 32767 );
 17          select x into l_clob from t;
 18
 19          dbms_lob.createtemporary( l_blob, true, dbms_lob.session );
 20
 21          dbms_lob.converttoblob(
 22                  l_blob,
 23                  l_clob,
 24                  dbms_lob.LOBMAXSIZE,
 25                  l_doff7,
 26                  l_soff7,
 27                  dbms_lob.DEFAULT_CSID,
 28                  l_lang_ctx,
 29                  l_warning
 30          );
 31
 32          l_length := dbms_lob.getlength(l_blob);
 33          loop
 34                  exit when l_off > l_length;
 35                  dbms_lob.read( l_blob, l_amt, l_off, l_raw );
 36                  l_off := l_off + l_amt;
 37                  utl_file.put_raw( l_output, l_raw, true );
 38          end loop;
 39
 40          utl_file.fclose( l_output );
 41          dbms_lob.freetemporary( l_blob );
 42  end;
 43  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> !ls -l /tmp/bigclob.txt
-rw-r--r--  1 oracle dba 65520 Apr 10 10:28 /tmp/bigclob.txt

ops$marcio:LX10G> !wc -L /tmp/bigclob.txt
65520 /tmp/bigclob.txt

Labels: how to

Enfim aconteceu! A primeira dúvida na "Ask ao Tio" (nome provisório e uma brincadeira) que já foi respondida. Mais do que um espaço para esclarecimentos, penso que posso convidar outras pessoas que gostem de compartilhar conhecimento para me ajudar a montar uma verdadeira página que seja referência em português em dúvidas sobre Oracle, fora os comentários em forma de thread que eventualmente acontecem em páginas desse estilo.

Para ler a primeira dúvida: Click Aqui.

Peço a gentileza de um pedacinho do seu tempo. Deixem comentários sobre o tema. Sugestões são extremamente bem-vindas. Em minha opinião, ainda falta um espaço para esclarecer dúvidas em português.

É provável que todos, entre os falíveis mortais, já passaram por algum tipo de enfermidade e se deram conta que, por mais que seja uma unha, a saúde é essencial. Estou passando por uma situação difícil e por isso não tenho tido oportunidade de atualizar o blog com tanta freqüência. Justo meus olhos estão com uma pequena infecção que demora a cicatrizar e isso me deixa impossibilitado de lidar com luz. Hoje, volto a trabalhar, depois de duas semanas "de molho", ainda não estou 100%, porém é preciso voltar. Espero poder voltar a escrever e produzir o mais rápido possível. Até breve!

Márcio Portes.