[Oracle] - Utilizando regex para consultas

Bom pessoal, essa postagem é para auxiliar na utilização de expressões regulares no Oracle, facilitando a codificação de procedimentos armazenados e funções:

[Oracle] - Procedimento para resetar sequences

Bom Pessoal,
 Essa é mais uma Postagem para ajudar em problemas do dia-a-dia, caso faça alguma restauração e os valores das "sequences" do banco estejam desatualizados, esse procedimento corrige a sequencia certa as "sequences" com base nos valores das tabelas informadas na base de dados. Segue abaixo, procedimento para efetuar está manutenção:

[Oracle] - Manutenção de Triggers Duplicadas

Bom Pessoal,

 Vou compartilhar um procedimento para fazer a exclusão de triggers duplicadas que são utilizadas para entregar id´s para cada registro inserido na tabela, segue abaixo:

[Oracle] - Informações de Data e Hora

Neste artigo estarei disponibilizando uma consulta para facilitar a utilização de informações de Data e Hora no Oracle database:

SELECT extract(YEAR FROM SYSDATE) AS ano,
       extract(MONTH FROM SYSDATE) AS mes,
       extract(DAY FROM SYSDATE) AS dia,
       EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE - trunc(SYSDATE), 'DAY')) AS hora,
       extract(minute FROM systimestamp) AS minuto,
       trunc(extract(SECOND FROM systimestamp)) AS segundo,
       SYSDATE AS data_hora
FROM   dual;

Espero que essa dica, ajudem outros profissionais que trabalham com Oracle, facilitando o dia-a-dia.
Graças e Paz sejam com Todos.

[Oracle] - Função para Retornar partes de um texto(string)

Bom pessoal, vou compartilhar uma função que retorna valores por parte de um texto especifico que estou utilizando, facilitando a utilização de particionamento de texto utilizando um carácter como ponto de particionamento:

CREATE OR REPLACE FUNCTION STRIPART(iTEXT VARCHAR2,

                     iCARA CHAR,

                     iINIC INTEGER,

                     iFINA INTEGER,

                     iTUDO INTEGER DEFAULT 1) RETURN VARCHAR2 AS

      vTEXT VARCHAR2(500) := iTEXT;

      vINIC INTEGER := 0;

      vFINA INTEGER := 0;

   BEGIN

      IF iINIC = 0

      THEN

         -- SE FOR ZERO É INICIO DE STRING SEMPRE

         vINIC := 1;

      ELSE

         -- PEGA A POSIÇÃO DO CARACTER iCARA

         vINIC := INSTR(iTEXT, iCARA, 1, iINIC) + 1;

      END IF;

      IF INSTR(iTEXT, iCARA, 1, iFINA) = 0

      THEN

         -- SE NAO ENCONTRAR O CARACTER FINAL PEGA TODA A STRING

         IF iTUDO = 1

         THEN

            vFINA := LENGTH(iTEXT);

         ELSE

            vFINA := 0;

         END IF;

      ELSE

         vFINA := INSTR(iTEXT, iCARA, 1, iFINA) - vINIC;

      END IF;

      vTEXT := SUBSTR(vTEXT, vINIC, vFINA);

      RETURN vTEXT;

   END;

[Oracle] - Função para Remover caracteres especiais em Textos

Bom pessoal, a função abaixo remover caracteres especiais em textos no Oracle, facilitando o tratamento de dados do tipo texto, auxiliando em consultas e criação de índices.

CREATE OR REPLACE FUNCTION NORMALIZAR(str_in VARCHAR2) RETURN VARCHAR2 IS
   pos           NUMBER(10);
   chars_special VARCHAR2(255);
   chars_normal  VARCHAR2(255);
   str           VARCHAR2(255) := UPPER(str_in);
BEGIN
   chars_special := 'ÁÀÃÂÉÊÍÓÔÕÚÜÇ.-';
   chars_normal  := 'AAAAEEIOOOUUC  ';
   str           := TRIM(upper(str));
   pos           := length(chars_normal);
   WHILE pos > 0
   LOOP
      str := REPLACE(str,
                     substr(chars_special, pos, 1),
                     substr(chars_normal, pos, 1));
      pos := pos - 1;
   END LOOP;
   str := TRIM(str);
   WHILE regexp_like(str, ' {2,}')
   LOOP
      str := REPLACE(str, '  ', ' ');
   END LOOP;
   pos := length(str);
   WHILE pos > 0
   LOOP
      IF regexp_like(substr(str, pos, 1), '[^A-Z0-9Ç@._ +-]+')
      THEN
         str := concat(substr(str, 1, pos - 1), substr(str, pos + 1));
      END IF;
      pos := pos - 1;
   END LOOP;
   RETURN str;
END;
/

[Oracle] - Trabalhando com Listas Dinâmicas

Bom pessoal, vou informar abaixo a implementação de criação e utilização de listas dinâmicas no Oracle, validas para versões 10g, 11g e 12c.

CREATE OR REPLACE TYPE t_id IS TABLE OF VARCHAR2(32000);

/

CREATE OR REPLACE

FUNCTION fnc_gera_lista(lista       VARCHAR2,

                           delimitador VARCHAR2) RETURN t_id IS

      v_id t_id;

   BEGIN

      SELECT regexp_substr(REPLACE(lista, delimitador, ','),

                           '[^,]+',

                           1,

                           LEVEL) AS lista

      BULK   COLLECT

      INTO   v_id

      FROM   dual

      CONNECT BY regexp_substr(REPLACE(lista, delimitador, ','),

                               '[^,]+',

                               1,

                               LEVEL) IS NOT NULL;

      RETURN v_id;

END;

/

----------------------------------------------

--- EXEMPLO DE UTILIZAÇÃO: ---

-- LISTA DE DADOS NUMÉRICOS --

SELECT TO_NUMBER(COLUMN_VALUE) AS LISTA

        FROM   TABLE(FNC_GERA_LISTA('22;19;30;35;40;60;71;92;', ';')); 

-- LISTA DE DADOS ALPHANUMÉRICOS --

SELECT TO_CHAR(COLUMN_VALUE) AS LISTA

        FROM   TABLE(FNC_GERA_LISTA('a;B;C;d;E;F;g;H;', ';'));

-- LISTA DE DADOS ALPHANUMÉRICOS(DATAS) --

SELECT TO_CHAR(TO_DATE(COLUMN_VALUE ,'DD/MM/YYYY'), 'DD/MM/YYYY') AS LISTA

FROM   TABLE(FNC_GERA_LISTA('15/01/2011;11/12/2010;10/10/1999;16/08/1998;01/10/2003;12/12/2012;10/10/2010;11/11/2011;',

                            ';'));

[Oracle] - Formatação de Data para Sistemas e Geração de Senhas

 Bom pessoal, segue abaixo consulta para formatação de dados para visualização em front-ends e um gerador de senhas para oracle:

-- Oracle – Exibição data do sistema no formato extenso
-- exemplo 1
SELECT TO_CHAR(SYSDATE, 'FMDay, DD" de "Month" de "YYYY') AS data_formatada
FROM   dual;
-- exemplo 2
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') AS data_formatada
FROM   dual;
-- Geraçao de senha aleatória no Oracle
-- exemplo 1
SELECT dbms_random.string('U', 2) || trunc(dbms_random.value(1000, 9999)) gera_senha
FROM   dual;

[Oracle] - Job para gerar estatísticas para um schema

No Oracle nós temos o otimizador baseado em regra, que é o mais antigo e hoje na versão 10g nem é mais suportado, eo o basedo em custo. Esse último necessita de estatísticas geradas nos objetos, pois é baseado nelas que ele gera os planos de execução e quanto mais atualizadas elas estiverem teoricamente melhor serão os planos de execução gerados.

O script abaixo cria um job usando a dbms_job que gera estatíticas para um schema todos os dias as 3 da manhã:

O script:

declare
   l_job number;
begin
   dbms_job.submit(
      l_job,
      'dbms_stats.gather_schema_stats( ''SCOTT'' );',
      trunc(sysdate)+1+3/24,
      'trunc(sysdate)+1+3/24' );
end;
/

Explicando o start time e o interval:
trunc(sysdate)
pega o dia atual a meia noite (00:00)
trunc(sysdate)+1
Adiciona um dia quer dizer amanhã meia noite
trunc(sysdate)+1+3/24
Adiciona 3 horas (3/24) o que quer dizer que o job vai rodar pela primeira vez amanhã as 03:00 e nos dias subsequentes nesse mesmo horário.

Fonte:
http://oraclemais.blogspot.com.br/2006/07/job-para-gerar-estatsticas-para-um.html

Procedure para envio de e-mails no Oracle

Pré-requisitos:

- Serviço de enfileiramento de mensagens SMTP instalado no Windows.
- Caso possua algum firewall ou anti-vírus, verifique se o mesmo não impede envios pela porta 25 (SMTP)

Procedure:

create or replace
procedure email_html (from_name varchar2, to_name varchar2,  subject varchar2, message varchar2) is
/***********************************************************************
Criacao     : Anderson Ayres
Objetivo    : Envia um email em formato HTML
Observacoes :
———————————————————————-
Historico das alteracoes:
———————————————————————-
Alteracao   : 10/03/2010 por Anderson Ayres Bittencourt
Objetivo    : Alteração de método de autenticação no servidor de email.
Observacoes : Os métodos possíveis de autenticação são: (validar qual o cliente utiliza)
UTL_SMTP.COMMAND(conn, ‘STARTTLS’); — comum em Exchange 2010.
UTL_SMTP.COMMAND(conn, ‘AUTH LOGIN’); — comum em Exchange 2007 e o mais utilizado por outros servidores de e-mail.
UTL_SMTP.COMMAND(conn, ‘AUTH NTLM’); — método para autenticação NTLM
UTL_SMTP.COMMAND(conn, ‘AUTH PLAIN’); — pouco utilizado… inseguro.
************************************************************************/v_smtp_server      varchar2(20) := ‘mail.superti.org‘;
v_smtp_server_port number  := 25;
v_directory_name   varchar2(100);
v_file_name        varchar2(100);
v_line             varchar2(1000);
crlf               varchar2(2):= chr(13) || chr(10);
mesg               varchar2(32767);
conn               UTL_SMTP.CONNECTION;
type varchar2_table is table of varchar2(200) index by binary_integer;begin
– Open the SMTP connection …
– ————————
conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );– Initial handshaking …
– ——————-
utl_smtp.helo( conn, v_smtp_server );UTL_SMTP.COMMAND(conn, ‘AUTH LOGIN’);
UTL_SMTP.COMMAND(conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(‘usuário_smtp‘))));
UTL_SMTP.COMMAND(conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(‘senha‘))));

utl_smtp.mail( conn, ‘remetente‘ );
utl_smtp.rcpt( conn, to_name );

utl_smtp.open_data (conn);

– build the start of the mail message …
– ———————————–
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘Mime-Version: 1.0′ || utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘Content-Type: Text/html; charset=ISO-8859-1′ || utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘From:remetente@superti.org‘ ||utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘Date:’         ||  TO_CHAR( SYSDATE, ‘dd Mon yy hh24:mi:ss’ ) || utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘To:’         ||  to_name || utl_tcp.CRLF));
UTL_SMTP.WRITE_RAW_DATA( conn,UTL_RAW.CAST_TO_RAW(‘Subject:’     ||  subject || utl_tcp.CRLF));

utl_smtp.write_data(conn,’ ‘ || utl_tcp.CRLF);
utl_smtp.write_raw_data(conn,utl_raw.cast_to_raw(utl_tcp.CRLF||message));
utl_smtp.close_data( conn );
utl_smtp.quit( conn );
end;

Fonte:
http://www.superti.org/?p=17

[PL/SQL] - Procedimento para Atualização Manual de Estatísticas do Oracle

Procedimento para Atualização Manual de Estatísticas do Oracle:

BEGIN
FOR rc IN (SELECT T.TABLE_NAME FROM USER_TABLES T)
LOOP
BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS(USER, rc.table_name);
DBMS_STATS.DELETE_TABLE_STATS(USER, rc.table_name);
DBMS_STATS.GATHER_TABLE_STATS(USER, rc.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('ERRO AO ATUALIZAR ESTATÍSTICA DO USUÁRIO: ' ||
USER || '.' || rc.table_name ||
' - ' || SQLERRM);
END;
END LOOP;
END;

[PL/SQL] - Procedimento para Matar sessões de um usuário no Oracle

A procedure PL/SQL abaixo permite matar todas as sessões de um usuário informando o nome ou parte do nome dele:

PROCEDURE killall
( p_who IN varchar2)
IS
cursor c_sessions
is
select sid, serial# serial
from v$session
where lower(username) like '%'||lower(p_who)||'%';
BEGIN
for rec_sessions in c_sessions loop
begin
execute immediate 'alter system disconnect session '''||rec_sessions.sid||','||rec_sessions.serial||''' immediate';
EXCEPTION when others then
dbms_output.put_line('Error executing :');
dbms_output.put_line('alter system disconnect session '''||rec_sessions.sid||','||rec_sessions.serial||''' immediate');
end;
end loop;
END;

 Fonte:
http://oraclemais.blogspot.com.br/2009/07/matar-sessoes-de-um-usuario.html

[PL/SQL] - Habilitando Debug de Conexões no Oracle

Bom pessoal, vou deixar uma dica rápida para aqueles que desenvolvem em PL/SQL Oracle para utilizar a opção de Debug pelas ferramentas de desenvolvimento SQL Developer da Oracle e PL/SQL Developer da Arround Software:

grant debug connect session to teste;
grant debug any procedure to teste;

A dica é simples, mais pode ajudar a desenvolvedores e DBAs iniciantes que precisem utilizar o Debug de Conexões Oracle para desenvolver aplicações em PL/SQL. Que a Graça e Paz estejam com todos.

[PL/SQL] - CURSOR PARA MOVER TABELAS E FAZER REBUILD DOS SEUS RESPECTIVOS INDICES

Bom pessoal, segue abaixo procedimento para mover tabelas para seus respectivos owners e tablespaces.

set linesize 300
set serveroutput on size 100000
set feedback off

spool rebuild.sql
begin
for rTabelas in (
SELECT owner, table_name, tablespace_name
FROM DBA_TABLES
) loop
dbms_output.put_line('alter table '|| rTabelas.owner ||'.'|| rTabelas.table_name ||' move tablespace '||rTabelas.tablespace_name||';');
for rIndex in (select index_name,tablespace_name,owner
from dba_indexes
where table_name = rTabelas.table_name) loop
dbms_output.put_line('alter index '|| rIndex.owner ||'.'|| rIndex.index_name ||' rebuild tablespace '||rIndex.tablespace_name||';');
end loop;
end loop;
end;
/
spool off

Fonte:
http://dicasoracledba.blogspot.com.br/2009/04/cursor-para-mover-tabelas-e-fazer.html

[PL/SQL] - Bloco PL/SQL para dropar objetos do schema

Bom pessoal, vou compartilhar no meu blog informações referentes ao meu atual trabalho como Analista e Programador PL/SQL para facilitar a pesquisa sobre alguns assuntos relacionados a Oracle e Programação PL/SQL.