luciano silva

Em continuidade a nossa série “as mil-e-uma utilidades do seu banco de dados Oracle”, agora você poderá descobrir como é fácil enviar e-mails a partir do PL/SQL e saber o quanto isto é produtivo.

Vamos lá, para o envio de e-mails é necessário que o pacote UTL_MAIL esteja instalado.

Conectando como sysdba na instância ORCL.

C:\>set oracle_sid=orcl
C:\>sqlplus sys/oracle as sysdba

Instalando o pacote UTL_MAIL. Isto não é nenhuma novidade, mas a variável de ambiente %ORACLE_HOME% indica o caminho a qual o banco de dados foi instalado, neste caso, foi utilizada a versão 10.2.0 – C:\oracle\product\10.2.0\db_1. Os sub-diretórios, contém os pacotes necessários para o UTL_MAIL funcionar.

SQL> set serveroutput on
SQL> @%ORACLE_HOME%/rdbms/admin/utlmail.sql
SQL> @%ORACLE_HOME%/rdbms/admin/prvtmail.plb
SQL> show errors;

Pronto, isto é o suficiente para o nosso próximo passo, a criação da procedure de envio de e-mails.

Crie uma package para agrupar todas as funcionalidades que podem ser comuns a diversos projetos, e reutilize o código quando necessário.

create or replace package PKG_BLOG_UTIL is

-- Author  : LUCIANO
-- Created : 05/07/2009

PROCEDURE enviar_email_auth(p_USUARIO   IN VARCHAR2,
p_SENHA     IN VARCHAR2,
p_TO        IN VARCHAR2,
p_COPIA     IN VARCHAR2,
p_SUBJECT   IN VARCHAR2,
p_MESSAGE   IN VARCHAR2);
end PKG_BLOG_UTIL;

Veja agora a versão 1.0 desta implementação:

CREATE OR REPLACE PACKAGE BODY PKG_BLOG_UTIL IS

PROCEDURE ENVIAR_EMAIL_AUTH(P_HOST    IN VARCHAR2,
P_USUARIO IN VARCHAR2,
P_SENHA   IN VARCHAR2,
P_TO      IN VARCHAR2,
P_COPIA   IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_MESSAGE IN VARCHAR2)
IS

MAIL_CONN   UTL_SMTP.CONNECTION;
V_HEADER    VARCHAR2(4000);
CRLF        VARCHAR2(2) := CHR(13) || CHR(10); -- quebra de linha

BEGIN

-- Abre a conexão
MAIL_CONN   := UTL_SMTP.OPEN_CONNECTION(P_HOST, 25);
UTL_SMTP.HELO(MAIL_CONN, P_HOST);

-- Faz a autenticação para envio de mensagem
UTL_SMTP.COMMAND(MAIL_CONN, 'AUTH LOGIN');
UTL_SMTP.COMMAND(MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USUARIO))));
UTL_SMTP.COMMAND(MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_SENHA))));

-- Prepara o cabeçalho de
V_HEADER := 'Date:'    || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || CRLF ||
'From:'    || p_USUARIO || CRLF ||
'Subject:' || p_SUBJECT || CRLF ||
'To:'      || p_TO      || CRLF ||
'Cc:'      || p_COPIA   || CRLF ||
CRLF || CRLF ||
p_MESSAGE;

--
UTL_SMTP.MAIL(MAIL_CONN, '<' || P_USUARIO || '>');
UTL_SMTP.RCPT(MAIL_CONN, '<' || P_TO || '>');
UTL_SMTP.DATA(MAIL_CONN, V_HEADER);

-- Fecha a conexão
UTL_SMTP.QUIT(MAIL_CONN);

END ENVIAR_EMAIL_AUTH;
END PKG_BLOG_UTIL;

Algumas coisas podem ser melhoradas como, separar o corpo da mensagem do cabeçalho e utilizar o UTL_TCP.CRLF como separador de linhas no lugar da variável CRLF, veja como o código fica mais organizado:

/* APENAS UM TRECHO DO CÓDIGO */
UTL_SMTP.MAIL(MAIL_CONN, '<' || P_USUARIO || '>');
UTL_SMTP.RCPT(MAIL_CONN, '<' || P_TO || '>');

UTL_SMTP.open_data(MAIL_CONN);

-- Prepara o cabeçalho
UTL_SMTP.write_data(MAIL_CONN, 'From'    || ': ' || p_USUARIO || UTL_TCP.CRLF);
UTL_SMTP.write_data(MAIL_CONN, 'To'      || ': ' || p_TO      || UTL_TCP.CRLF);
UTL_SMTP.write_data(MAIL_CONN, 'Cc'      || ': ' || p_COPIA   || UTL_TCP.CRLF);
UTL_SMTP.write_data(MAIL_CONN, 'Subject' || ': ' || P_SUBJECT || UTL_TCP.CRLF);

-- Escreve a mensagem
UTL_SMTP.write_data(MAIL_CONN, UTL_TCP.CRLF || p_message);
UTL_SMTP.close_data(MAIL_CONN);

Eu preferi adotar este segundo código como o mais recomendável, mesmo assim, alguns detalhes podem ser adaptados a sua realidade, por exemplo, criar uma constante para o Mail Host, e indicando que o envio de e-mails sempre será a partir daquele servidor ou fazer a sobrecarga da procedure – a reutilização é uma benção.

Veja que a assinatura da procedure abaixo é um overload do que foi mostrado acima, isto é um conceito de Orientação-a-Objetos suportado pelo PLSQL, permite que um método seja escrito com diversas assinaturas diferentes, e reaproveitando o código de maneira eficaz.

PROCEDURE ENVIAR_EMAIL_AUTH(P_USUARIO IN VARCHAR2,
P_SENHA   IN VARCHAR2,
P_TO      IN VARCHAR2,
P_COPIA   IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_MESSAGE IN VARCHAR2)
IS
V_MAILHOST CONSTANT VARCHAR2(100) := 'pop.xxxx.com.br';
BEGIN

enviar_email_auth(P_HOST    => v_mailhost,
P_USUARIO => P_USUARIO,
P_SENHA   => P_SENHA,
P_TO      => P_TO,
P_COPIA   => P_COPIA,
P_SUBJECT => P_SUBJECT,
P_MESSAGE => P_MESSAGE);
END ENVIAR_EMAIL_AUTH;

A package completa, está disponível aqui para download.

Não envie spam!

Se você sabe utilizar o recursos do Java Stored Procedure em seu banco de dados, significa que você é uma pessoa abençoada.

A não tão popular JSP compartilha alguns dos recursos da plataforma Java com a linguagem estrutural PL/SQL, eu disse alguns, pois existem limitações. Muitos desenvolvedores se beneficiam do pacote java.io.* quando falamos de Java Stored Procedure, visto que os recursos são mais amplos, quando comparado ao famoso UTL_FILE.

Vamos realizar passo-a-passo utilizando o banco de dados Oracle 10g em ambiente windows, para a criação de uma Procedure, que irá chamar um programa Java (Java Stored Procedure), cujo este permitirá executar comandos do prompt (os mais antigos leiam Bash).

É provavel que você encontre no Google diversas outras maneiras de criar a Java Stored Procedures, porém, o proposito aqui é mais amplo, ajudando a criar todo o ambiente, vamos lá!

Primeiramente, o usuário de banco, owner do objeto JSP, deve ter alguns permissões para manipular os arquivos no sistema operacional. Neste exemplo no nosso schema será blog.

Informe o usuário (schema), tipo de acesso e, opcionalmente o diretório em que será permitida a manipulação do arquivo.

EXEC DBMS_JAVA.grant_permission('BLOG', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
EXEC Dbms_Java.Grant_Permission('BLOG', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('BLOG', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

Feito isto, os objetos de banco são simples, crie o programa abaixo com o usuário blog.

create or replace and compile java source named Bash as
package com.lucianosilva.oracle;
import java.io.*;
import java.lang.*;
public class Bash {

public static void command(String command) throws Exception {
final Process process = Runtime.getRuntime().exec(command);
process.waitFor();
process.destroy();
}

public static String commandReturn(String command) throws Exception {
final Process process = Runtime.getRuntime().exec(command);
process.waitFor();

String retCode = Integer.toString( process.exitValue() );

if ( retCode.equals("0") ){
retCode = retCode + " Executado com sucesso.";
}else{
retCode = retCode + " Erro durante a execucao.";
}

process.destroy();
return retCode;
}

public static void executeAsynchronousCommand(String command) throws Exception {
Runtime.getRuntime().exec( command );
}
}

O método commandReturn não retorna o resultado do comando bash e sim uma mensagem informando se o comando foi bem executado ou não.

Agora vamos testar, ainda como blog, faça:

CREATE OR REPLACE FUNCTION EXECUTAR_COMAND_BASH(P_COMMAND IN VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'com.lucianosilva.oracle.Bash.commandReturn(java.lang.String) return java.lang.String';

Isto é tudo! Um exemplo muito prático, sem dúvidas, já que dependendo da solução adotada a integração de procedures PLSQL com o crontab é muito comum e isto pode ajudar.

Sayonara

Well, faz um tempo que não passo por aqui mas, o motivo é justo, final da graduação, muita correira com o trabalho de conclusão de curso e alguns novos desafios.
E… por falar em desafio, eu encarei um recentemente, não é uma coisa tão complexa, porém exigiu muita pesquisa e testes, a proposta era a seguinte “recuperar uma tablespace sem backup”. Auto-explicativo, não?

Vamos ao que interessa, primeiramente vou criar o cenário, ativando o archive log, em seguida será criada uma tablespace qualquer, forçaremos uma alternância de logs para que o redo log receba o objeto.

Passo 1 – Inicie o banco de dados normalmente e verifique se está no modo de arquivamento automático

archive log list;

Se o banco não estiver no modo de arquivamento faça isto

alter database archivelog
alter system set log_archive_start=true scope=spfile;

alter database open;

É necessário reiniciar o banco pq o escopo da alteração foi o spfile

shutdown immediate;
startup

Passo 2 – Como SYS crie uma simples tablespace TB_CURSO

create tablespace tb_curso datafile 'c:\oracle\oradata\DBWORK2\tb_curso.dbf'
size 2m
autoextend on;

Com o usuário scott crie uma tabela qualquer, apenas para que a tablepace tenha um conteúdo.

conn scott/tiger@dbwork2

-- criando tabela OPS
create table ops (id number) tablespace tb_curso;

-- populando a tabela
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
insert into ops values(1);
commit;

select count(*) from ops;

Reconecte como SYS e force a alternância de logs

conn sys/oracle@dbwork2 as sysdba

-- alternância de log
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Passo 4 – Apagando a tablespace, neste momento será proposital mas talvez um dia você passe por uma situação desesperadora e acidental, ou não. Enfim, apague o arquivo físico da tablespace, antes feche o banco de dados.

– fechando o banco

shutdown immediate;

– Apague o datafile ‘c:\oracle\oradata\DBWORK2\tb_curso.dbf’

Passo 5 – Recuperando a tablespace.

– Inicie o banco de dados como SYS

startup

Neste momento você irá receber uma mensagem de erro, que indicará o problema no datafile, não se desespere!
Aqui está o segredo, basta você criar um novo datafile como se você o apagado, desta maneira.

alter database create datafile
'C:\ORACLE\ORADATA\DBWORK2\tb_curso.DBF' as
'C:\ORACLE\ORADATA\DBWORK2\tb_curso01.DBF';

Bem, ainda não está tudo resolvido, você deverá aplicar o recover dos dados da tablespace que estão no redo log.
recover tablespace tb_curso;

Após aparecer a mensagem “Recuperação de mídia concluída”, abra o banco de dados e verifique a tabela OPS do usuário scott.

-- abra o banco novamente
alter database open;

-- consultando os registros
select * from scott.ops;

That’s all!

Acredite! É fácil e vc é capaz de fazê-lo!
Porém, fácil não siginifica que não seja trabalhoso!

Para a criação deste tutorial foi utilizado a versão do Banco de Dados Oracle 9i distribuição gratuita e Windows XP SP2.
Faça download do Oracle 9i aqui, em seguida faça a instalação. Não vamos detalhar o processo de instalação do Oracle, mas no Windows é simples seguindo as instruções do wizard é possível concluir em alguns minutos.

Premissas
ORACLE_HOME é o caminho de instalação do Oracle, normalmente no Windows é C:\oracle\ora92\.
ORA_SID ou ORACLE_SID é a mesma coisa, faz referência a instância que será utilizada.
Leia-se DB1WORK no lugar de <sid>.

1º Passo – Criando o pfile
A partir de uma instância ativa crie um novo pfile copie e renomeie o arquivo para %ORACLE_HOME%\database\init<sid>.ora
No novo arquivo substitua todas as referências da outra instância para o seu novo SID, ou seja, db_name, instance_name, control_files, etc. Veja aqui um exemplo como o arquivo ficará.

2º Passo – criando Estruturas de Diretórios
É necessário criar os diretórios básicos para os processos de background. Crie-os com o nome do SID nos seguintes caminhos:

%ORACLE_HOME%\oradata\<sid>
%ORACLE_HOME%\admin\<sid>
%ORACLE_HOME%\admin\<sid>\bdump
%ORACLE_HOME%\admin\<sid>\cdump
%ORACLE_HOME%\admin\<sid>\udump

Certifique-se que o usuário do Sistema Operacional que irá criar o banco de dados tenha permissão de escrita nesses diretórios.

3º Passo – Criando Serviço
Crie um serviço para o novo banco de dados, no Windows este passo é fundamental. Vá até o prompt de comando do windows e, faça-o da seguinte maneira:

set oracle_sid=<sid>
oradim -new -sid <sid> -intpwd nive

Muitos tutoriais omitem este procedimento mas, no Windows sem o Serviço Oracle relacionado a nova instância não é possível conectar a instância inativa, ou seja, impossibilitando a criação do banco.

4º Passo – Conectando Instância Inativa

set oracle_sid=<sid>
sqlplus “/ as sysdba”

Já no SQLPlus, após conectar na instância faça o STARTUP no modo NOMOUNT.

startup nomount pfile=”%ORACLE_HOME%\database\init<sid>.ora”

É possível iniciar uma instância nos modos NOMOUNT, MOUNT, OPEN, RESTRICT, RECOVER e FORCE.
NOMOUNT - Inicia a instância mas não monta o banco de dados.
MOUNT – Inicia a instância e monta o banco de dados mas não abre.
OPEN – Inicia a instãncia, monta e abre o banco de dados.
RESTRICT – Inicia a instância, monta e abre o banco de dados, permitindo conexão de usuários apenas que tenham o previlégio de session restrict.
RECOVER – Inicia a instância, deixa o banco de dados fechado para inicio de recuperação de falhas.
FORCE – Em caso de problemas para iniciar e parar o banco de dados, esse modo força o SHUTDOWN ABORT e em seguida STARTUP OPEN.

5º Passo – Criando o Banco de Dados

CREATE DATABASE DB1WORK CONTROLFILE REUSE
Logfile
Group 1 (‘c:/oracle/admin/DB1WORK/bdump/log01a.log’,
‘c:/oracle/admin/DB1WORK/bdump/log01b.log’) size 50M,
Group 2 (‘c:/oracle/admin/DB1WORK/bdump/log02a.log’,
‘c:/oracle/admin/DB1WORK/bdump/log02b.log’) size 50M
DATAFILE ‘c:/oracle/oradata/DB1WORK/system1.dbf’ size 250M
AUTOEXTEND ON MAXSIZE 50M
UNDO TABLESPACE UNDOTBS2
DATAFILE ‘c:/oracle/oradata/DB1WORK/undo1.dbf’ size 50M
DEFAULT TEMPORARY TABLESPACE DB1WORK1TEMP TEMPFILE ‘c:/oracle/oradata/DB1WORK/mydb1temp1.dbf’ size 75M
MAXLOGFILES 32
MAXLOGHISTORY 1
MAXDATAFILES 500;

6º Passo – Finalizando
Após criar o banco de dados basta executar os scripts abaixo:

Criação do dicionário de dados, views e sinonimos de sistema.
@%ORACLE_HOME%/rdbms/admin/catalog.sql

Executa todos os scripts usados em Pl/Sql
@%ORACLE_HOME%/rdbms/admin/catproc.sql

@%ORACLE_HOME%/sqlplus/admin/pupbld.sql

Eu tentei demonstrar neste post que criar o banco de dados Oracle manualmente em ambiente Windows não é nenhum bicho-de-sete-cabeças e, que com base em um passo-a-passo é possível fazê-lo. Os conceitos de tamanho de arquivos físicos e nomeclaturas não foram discutidos, visto que foi uma decisão pessoal que o autor baseou-se no seu pouco conhecimento.

As View Materialized assim conhecidas desde a versão 9i, são nada mais que objetos do banco de dados Oracle que contém dados locais de tabelas remotas ou são usadas pra criar uma agregação de tabelas em um determinado intervalo de tempo.

Uma curiosidade nas versões precedentes à 9i as Views Materializadas eram conhecidas como Snapshots.

Como meu professor Milton Goya já comentou, Snapshot é o nome utilizado na versão 7 do Oracle.
O comando abaixo demonstra como podemos criar uma View Materializada:

CREATE MATERIALIZED VIEW vm_employees
REFRESH FAST
START WITH SYSDATE + 1
NEXT SYSDATE + 1/24
WIDTH PRIMARY KEY
AS
SELECT empno, ename, job, salary
FROM emp;

Na sequência será detalhada cada linha do comando demonstrado acima.

CREATE MATERIALIZED VIEW vm_employees

Inicio da sintaxe para criar a visão chamada de vm_employees.

REFRESH FAST

O REFRESH indica o tipo de atualização que sofrerão os dados contidos na visão.
Existem três estados ou métodos sendo FAST, COMPLETE e FORCE.

FAST: Atualiza somente as linhas da tabela original que sofreram atualizações.

COMPLETE: Recria toda a estrutura da view materializada mesmo que não seja necessário.

FORCE: Se não for específicado o método de atualização, o padrão é FORCE, que tentará fazer uma atualização FAST se não for possível irá fazer a COMPLETE.

START WITH SYSDATE + 1

Indica que quando o banco irá fazer a primeira replicação.

NEXT SYSDATE + 1/24

Determina o intervalo entre a próxima replicação.

WIDTH PRIMARY KEY

É utilizado para criar a Chave-Primária, que é baseada na chave-primária da tabela master.

Concluí-se com esses post-tutorial que view materializadas são extremamente uteís no dia-a-dia de uma companhia que trabalha com centenas de milhares de tuplas e, o acesso à elas acaba ficando custoso. Este tipo de objeto ajuda a diminuir acesso as tabelas master de uma maneira segura e íntegra.