Tag: ‘procedure’
Enviando e-mail com PL/SQL para múltiplos endereços
A situação mais comum é o envio de e-mail para diversos destinatários, portanto, complementando o código já postado aqui sobre o envio de e-mails utilizando PLSQL.
Uma lista de destinatários formatada normalmente assim:
Luciano <luciano@lucianosilva.com>, Grupos <grupos@lucianosilva.com>, Futebol <futebol@cfb.com.br>
Este é o nosso problema, o Oracle não irá conseguir trabalhar com a String formatada dessa maneira, e irá lançar o erro:
ORA-29279: SMTP permanent error: 501 Bad address syntax
Enfim, para conseguir solucionar o problema do envio de e-mail para multiplos destinatários, utilize a função abaixo, ela irá devolver apenas o endereço de e-mail.
FUNCTION FORMAT_ADDRESS(ADDR_LIST IN OUT VARCHAR2) RETURN VARCHAR2 IS --- ADDR VARCHAR2(256); I PLS_INTEGER; --- FUNCTION LOOKUP_UNQUOTED_CHAR(STR IN VARCHAR2, CHRS IN VARCHAR2) RETURN PLS_INTEGER AS C VARCHAR2(5); I PLS_INTEGER; LEN PLS_INTEGER; INSIDE_QUOTE BOOLEAN; --- BEGIN INSIDE_QUOTE := FALSE; I := 1; LEN := LENGTH(STR); WHILE (I <= LEN) LOOP C := SUBSTR(STR, I, 1); IF (INSIDE_QUOTE) THEN IF (C = '"') THEN INSIDE_QUOTE := FALSE; ELSIF (C = '\') THEN I := I + 1; -- Skip the quote character END IF; GOTO NEXT_CHAR; END IF; IF (C = '"') THEN INSIDE_QUOTE := TRUE; GOTO NEXT_CHAR; END IF; IF (INSTR(CHRS, C) >= 1) THEN RETURN I; END IF; <<NEXT_CHAR>> I := I + 1; END LOOP; RETURN 0; END; BEGIN ADDR_LIST := LTRIM(ADDR_LIST); I := LOOKUP_UNQUOTED_CHAR(ADDR_LIST, ',;'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR_LIST, 1, I - 1); ADDR_LIST := SUBSTR(ADDR_LIST, I + 1); ELSE ADDR := ADDR_LIST; ADDR_LIST := ''; END IF; I := LOOKUP_UNQUOTED_CHAR(ADDR, '<'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR, I + 1); I := INSTR(ADDR, '>'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR, 1, I - 1); END IF; END IF; RETURN ADDR; END FORMAT_ADDRESS;
A mudança do código anterior não é tão drástica, já que o parâmetro é entrada/saída, você pode fazer um looping na lista de endereços para adicionar um-a-um.
UTL_SMTP.MAIL(MAIL_CONN, V_FROM ); WHILE( V_LIST_ADDRESS IS NOT NULL )LOOP UTL_SMTP.RCPT(MAIL_CONN, FORMAT_ADDRESS(V_LIST_ADDRESS) ); END LOOP;
Veja que é uma modificação simples, porém, ganha-se muito quando é necessário deixar o programa o mais abrangente possível
Enjoy!
Enviando e-mail com PL/SQL
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!
Chamando o bash a partir do PL/SQL com Java Stored Procedure
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