FUNÇÕES ANALÍTICAS DO ORACLE – Look Back e Look Ahead (LAG/LEAD)

Duas outras funções analíticas são o LAG (Look back - olhe para trás) e o LEAD (Look Ahead - olhar adiante), que foram introduzidos no Oracle 8i. Ele fornece acesso a mais do que uma linha de uma tabela, ao mesmo tempo sem um auto-junção. Dada uma série de linhas retornadas de uma consulta e uma posição do cursor, LAG / LEAD fornece acesso a uma linha em um dado físico antes do deslocamento do posicionamento.

Sintaxe:

LAG(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
LEAD(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)

Continue lendo em oraclehome.com.br


Exemplos de como obter datas e cálculos para datas

Há muito tempo tenho esse script que sempre me ajudou a calcular horas, minutos e dias.
Utilizando algumas funções do Oracle para ajudar a montar datas decorrente do dia de hoje.

 

Continue lendo em oraclehome.com.br


Flashback query

Função que veio no Oracle 9i, fornece a habilidade de visualizar os dados como eles estavam em um determinado tempo no passado. Por padrão, operações no banco de dados usam os dados disponíveis mais recentemente “comitados”. Se você quiser pesquisar determinados dados em algum ponto no passado, você precisará utilizar o recurso de Flashback Query na qual será necessário especificar um “horário” ou um SCN (System change Number) para efetuar a pesquisa.

Este recurso é muito útil, quando você precisa restaurar dados que foram deletados ou alterados erroneamente. É melhor efetuar este procedimento do que voltar um export da tabela de um ponto no passado ou mesmo um backup full até um momento no tempo, que pode ser lento deixando a sua base totalmente inoperante até a sua conclusão.

Antes de mais nada, para você poder usar o recurso de Flashback Query, é necessário configurar o seu banco de dados para usar o gerenciamento automático de UNDO (Automatic Undo Management).

– retificado

E um detalhe importante que é liberado para todas as edições do Oracle, podendo ser vistos nessatabela de comparações das features contra suas edições.

CLAUSULA AS OF

Nas releases anteriores, o recurso FlashBack permitia ao usuario ter uma visao de um imagem mais antiga do banco de dados, com base em um horario ou em um SCN escolhido.

Para utilizar os dados dessa imagem mais antiga, por exemplo, para substituir linhas que haviam sido deletadas acidentalmente de uma tabela, a operação do Flashback precisava ser incorporada a um bloco PL/SQL.

Agora, pode-se obter imagens distintas, de tabelas individuais, em uma unica sessão.

Continue lendo em oraclehome.com.br


Heterogeneous Services Oracle Linux, Select on Mysql

Bom para entender melhor o que estarei explicando vou mostrar uma imagem que irá nos ajudar a entender o processo de acessar o mysql apartir do Oracle.

como mostra na imagem abaixo o Cliente(eu) vou conectar no meu Oracle database onde irá ter um DBlink para o Non-Oracle que no nosso caso é o MySQL. Assim podendo efetuar insert,delete,update e select apartir do Oracle lá no MySQL.
Isto tudo é feito apartir de um intermediador que vamos utilizar o ODBC. Particularmente falando não gosto muito, porem para quebrar um galho ajuda p/ caramba. Continue lendo →


Query’s SQL para XML

Rapidinha: O oracle tem como default a função de transformar query's sql para XML

Exemplo:

  1.  
  2. SET long 5000
  3. SET pagesize 5000
  4. SET linesize 132
  5. SELECT
  6. dbms_xmlgen.getxml(SELECT * FROM user_tables’) XMLQUERY
  7. FROM dual;
  8.  

Pivot ou CrossTab (invertendo linhas em colunas)

Imagine você está tentando para criar um jogo de resultados onde os registros precisam ser colunas, ou vice-versa.
A essência, você precisa "girar" os registros em colunas, ou vice-versa.
Muitas vezes há relatorios que exigêm isto e para que não precisarmos de criar functions ou qualquer outra coisa para solucionar isso podemos usar o chamado pivot ou crosstab queries.

Uma questão de pivot simples é realizada fazendo o seguinte:
1. Acrescente algum tipo de conta ou número de fila ao seu questionamento, se necessário para o agrupamento;
2. Então use sua query original como uma sub-query;
3. Use "decode" para se transformar os registros em colunas;
4. Use "MAX ou SUM" para "agrupar" os registros múltiplos em únicas coluna. E não pode ser esquecido de agrupar.

Exemplo prático: Continue lendo →


Criptografando em MD5 no Oracle

Esses dias um cliente me solicitou algo para criptografar senhas, já conhecia a package dba_obfuscation_toolkit e ai fui pesquisar mais sobre e achei uma forma otima para criptografar a senha em MD5, assim fiz a função dentro do owner do sistema que faz esta conversão:

  1. v_input VARCHAR2(2000) := valor;
  2. hexkey VARCHAR2(32) := NULL;
  3. hexkey := RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_RAW.cast_to_raw(v_input)));
  4. RETURN NVL(hexkey,'');
  5.  

Exemplo da utilização:

  1. sql> SELECT md5('senhadorafael') FROM dual;
  2. 70CB17EBF6A8DF8525016F7866075794
  3.