Enviar E-mail com Anexo do Sistema Operacional

Ter, 11 Mai 2010 8:55 am

Pessoal, bom dia.

Gostaria de saber se alguém aqui já desenvolveu algo do tipo, enviar um e-mail com anexo(s) do Sistema Operacional, em PL/SQL.

O que eu gostaria era, pegar uns arquivos em uma pasta e, enviar à um destinatário por e-mail.

Vi vários exemplos na net mas, nenhum que funcionava de tal forma.

Muito obrigado.

Ter, 11 Mai 2010 9:10 am

Oi Trevisolli

você já tentou esse exemplo do forum?

http://glufke.net/oracle/viewtopic.php? ... viar+email

Um paliativo também seria mandar para o usuario o link do arquivo no email....

Não sei se serve para você...

Ter, 11 Mai 2010 9:50 am

Valeu pela dica Paulo.
Não tinha testado ainda. Com Jpeg já deu certo. Vou continuar os testes e posto pra vocês.

Qui, 13 Mai 2010 12:10 pm

Pessoal, acabei fazendo aqui e, vou deixar registrado para quem precisar.
Utilizei uma máquina com Sistema Operacional Windows XP para a montagem do DIRECTORY que nomeei como 'DIR_ARQUIVOS'.

Dicas:
Criar o DIRECTORY em Letras Maiúsculas.

Chamada:

Selecionar tudo

begin
  -- Call the procedure
  pck_envia_email.send_mail(pfrom => paulotrevisolli@ig.com.br,
                            pto => endereco1@email.com,endereco2@email.com
                            pcc => null,
                            pbcc => null,
                            psubject => 'Teste de envio com anexo',
                            pmessage => 'Anexos enviados no email.',
                            pfiles => 'nf.xls;Orca.zip;',      -- separar nome dos arquivos por ";" e colocar um ";" no final
                            pdirectory_name => 'DIR_ARQUIVOS', -- Directory apontando para o diretório dos arquivos
                            psmtp_host => '192.168.0.1',       -- Informar SMTP Utilizado
                            psmtp_port => 25);                 -- Porta (A padrão é a 25)
end;

Table:

Selecionar tudo


CREATE TABLE ger_mime
    (extensao                       VARCHAR2(10) NOT NULL,
    mime_type                      VARCHAR2(1000))
/

Conteúdo da Table (Com os Mime Types

Selecionar tudo

INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('323','text/h323')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('acx','application/internet-property-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ai','application/postscript')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('aif','audio/x-aiff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('aifc','audio/x-aiff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('aiff','audio/x-aiff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('asf','video/x-ms-asf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('asr','video/x-ms-asf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('asx','video/x-ms-asf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('au','audio/basic')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('avi','video/x-msvideo')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('axs','application/olescript')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('bas','text/plain')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('bcpio','application/x-bcpio')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('bin','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('bmp','image/bmp')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('c','text/plain')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cat','application/vnd.ms-pkiseccat')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cdf','application/x-cdf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cer','application/x-x509-ca-cert')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('class','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('clp','application/x-msclip')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cmx','image/x-cmx')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cod','image/cis-cod')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('cpio','application/x-cpio')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('crd','application/x-mscardfile')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('crl','application/pkix-crl')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('crt','application/x-x509-ca-cert')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('csh','application/x-csh')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('css','text/css')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dcr','application/x-director')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('der','application/x-x509-ca-cert')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dir','application/x-director')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dll','application/x-msdownload')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dms','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('doc','application/msword')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dot','application/msword')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dvi','application/x-dvi')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('dxr','application/x-director')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('eps','application/postscript')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('etx','text/x-setext')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('evy','application/envoy')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('exe','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('fif','application/fractals')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('flr','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('gif','image/gif')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('gtar','application/x-gtar')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('gz','application/x-gzip')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('h','text/plain')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('hdf','application/x-hdf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('hlp','application/winhlp')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('hqx','application/mac-binhex40')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('hta','application/hta')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('htc','text/x-component')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('htm','text/html')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('html','text/html')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('htt','text/webviewhtml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ico','image/x-icon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ief','image/ief')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('iii','application/x-iphone')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ins','application/x-internet-signup')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('isp','application/x-internet-signup')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('jfif','image/pipeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('jpe','image/jpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('jpeg','image/jpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('jpg','image/jpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('js','application/x-javascript')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('latex','application/x-latex')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('lha','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('lsf','video/x-la-asf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('lsx','video/x-la-asf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('lzh','application/octet-stream')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('m13','application/x-msmediaview')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('m14','application/x-msmediaview')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('m3u','audio/x-mpegurl')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('man','application/x-troff-man')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mdb','application/x-msaccess')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('me','application/x-troff-me')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mht','message/rfc822')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mhtml','message/rfc822')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mid','audio/mid')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mny','application/x-msmoney')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mov','video/quicktime')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('movie','video/x-sgi-movie')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mp2','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mp3','audio/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpa','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpe','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpeg','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpg','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpp','application/vnd.ms-project')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mpv2','video/mpeg')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ms','application/x-troff-ms')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('mvb','application/x-msmediaview')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('nws','message/rfc822')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('oda','application/oda')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p10','application/pkcs10')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p12','application/x-pkcs12')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p7b','application/x-pkcs7-certificates')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p7c','application/x-pkcs7-mime')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p7m','application/x-pkcs7-mime')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p7r','application/x-pkcs7-certreqresp')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('p7s','application/x-pkcs7-signature')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pbm','image/x-portable-bitmap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pdf','application/pdf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pfx','application/x-pkcs12')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pgm','image/x-portable-graymap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pko','application/ynd.ms-pkipko')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pma','application/x-perfmon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pmc','application/x-perfmon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pml','application/x-perfmon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pmr','application/x-perfmon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pmw','application/x-perfmon')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pnm','image/x-portable-anymap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pot,','application/vnd.ms-powerpoint')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ppm','image/x-portable-pixmap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pps','application/vnd.ms-powerpoint')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ppt','application/vnd.ms-powerpoint')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('prf','application/pics-rules')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ps','application/postscript')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('pub','application/x-mspublisher')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('qt','video/quicktime')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ra','audio/x-pn-realaudio')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ram','audio/x-pn-realaudio')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ras','image/x-cmu-raster')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('rgb','image/x-rgb')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('rmi','audio/mid')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('roff','application/x-troff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('rtf','application/rtf')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('rtx','text/richtext')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('scd','application/x-msschedule')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sct','text/scriptlet')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('setpay','application/set-payment-initiation')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('setreg','application/set-registration-initiation')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sh','application/x-sh')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('shar','application/x-shar')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sit','application/x-stuffit')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('snd','audio/basic')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('spc','application/x-pkcs7-certificates')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('spl','application/futuresplash')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('src','application/x-wais-source')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sst','application/vnd.ms-pkicertstore')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('stl','application/vnd.ms-pkistl')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('stm','text/html')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('svg','image/svg+xml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sv4cpio','application/x-sv4cpio')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('sv4crc','application/x-sv4crc')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('swf','application/x-shockwave-flash')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('t','application/x-troff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tar','application/x-tar')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tcl','application/x-tcl')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tex','application/x-tex')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('texi','application/x-texinfo')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('texinfo','application/x-texinfo')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tgz','application/x-compressed')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tif','image/tiff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tiff','image/tiff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tr','application/x-troff')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('trm','application/x-msterminal')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('tsv','text/tab-separated-values')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('txt','text/plain')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('uls','text/iuls')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('ustar','application/x-ustar')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('vcf','text/x-vcard')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('vrml','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wav','audio/x-wav')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wcm','application/vnd.ms-works')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wdb','application/vnd.ms-works')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wks','application/vnd.ms-works')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wmf','application/x-msmetafile')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wps','application/vnd.ms-works')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wri','application/x-mswrite')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wrl','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('wrz','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xaf','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xbm','image/x-xbitmap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xla','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xlc','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xlm','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xls','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xlt','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xlw','application/vnd.ms-excel')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xof','x-world/x-vrml')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xpm','image/x-xpixmap')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('xwd','image/x-xwindowdump')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('z','application/x-compress')
/
INSERT INTO GER_MIME
("EXTENSAO","MIME_TYPE")
VALUES
('zip','application/zip')
/

Objeto SPEC:

Selecionar tudo

PACKAGE PCK_ENVIA_EMAIL
  IS
/*
 *@Objeto: PCK_ENVIA_EMAIL
 *@Descricao: Package responsável por Envio de E-mail (Com Anexo(s) ou Não)
 *@Autor: Trevisolli
 *@Data: 12/05/2010
*/
  --
  PROCEDURE SEND_MAIL (PFROM           IN VARCHAR2 
                      ,PTO             IN VARCHAR2 
                      ,PCC             IN VARCHAR2 DEFAULT NULL -- with copy
                      ,PBCC            IN VARCHAR2 DEFAULT NULL -- with hide copy 
                      ,PSUBJECT        IN VARCHAR2 DEFAULT NULL
                      ,PMESSAGE        IN VARCHAR2 DEFAULT NULL
                      ,PFILES          IN VARCHAR2 DEFAULT NULL 
                      ,PDIRECTORY_NAME IN VARCHAR2 DEFAULT 'DIR_ARQUIVOS'
                      ,PSMTP_HOST      IN VARCHAR2 DEFAULT '174.133.172.226'
                      ,PSMTP_PORT      IN NUMBER DEFAULT 25);
  --
  FUNCTION GET_ADDRESS(ADDR_LIST IN OUT VARCHAR2) RETURN VARCHAR2;
  --
  PROCEDURE SPLIT_PATH_NAME(FILE_PATH IN VARCHAR2
                           ,DIRECTORY_PATH OUT VARCHAR2
                           ,FILE_NAME OUT VARCHAR2);
  --                           
  PROCEDURE APPEND_FILE(DIRECTORY_PATH IN VARCHAR2
                       ,DIRECTORY_NAME IN VARCHAR2
                       ,FILE_NAME IN VARCHAR2
                       ,FILE_TYPE IN VARCHAR2
                       ,CONN IN OUT UTL_SMTP.CONNECTION);
  --                       
  FUNCTION ELEMENTO( p_string VARCHAR2 
                   , p_elemento PLS_INTEGER 
                   , p_separador VARCHAR2 DEFAULT ';' 
                   ) RETURN VARCHAR2;
  --                   
  FUNCTION FUN_QTD_ARQUIVOS (PSTRING IN VARCHAR2
                            ,PDELIMITADOR IN VARCHAR2 DEFAULT ';')
  RETURN NUMBER;    
  --
  FUNCTION FUN_RETORNA_MIME(PEXTENSAO IN VARCHAR2)
  RETURN VARCHAR2;
  --
END; -- Package spec

Objeto BODY:

Selecionar tudo

PACKAGE BODY PCK_ENVIA_EMAIL
IS
/*
/*
 *@Objeto: SEND_MAIL
 *@Descricao: Procedure responsável por Envio de E-mail (Com Anexo ou Não)
 *@Autor: Trevisolli
 *@Data: 12/05/2010
 *@Notas: PTO: Mais que 1 destinatário: Separar por vírgula (,)
 *        PFILE: Mais que 1 arquivo: Separar por ponto-e-vírgula (;)
*/ 
  crlf               varchar2(2):= chr(13) || chr(10); 
   
  PROCEDURE SEND_MAIL (PFROM           IN VARCHAR2 
                      ,PTO             IN VARCHAR2 
                      ,PCC             IN VARCHAR2 DEFAULT NULL -- with copy
                      ,PBCC            IN VARCHAR2 DEFAULT NULL -- with hide copy 
                      ,PSUBJECT        IN VARCHAR2 DEFAULT NULL
                      ,PMESSAGE        IN VARCHAR2 DEFAULT NULL
                      ,PFILES          IN VARCHAR2 DEFAULT NULL 
                      ,PDIRECTORY_NAME IN VARCHAR2 DEFAULT 'DIR_ARQUIVOS'
                      ,PSMTP_HOST      IN VARCHAR2 DEFAULT '174.133.172.226'
                      ,PSMTP_PORT      IN NUMBER DEFAULT 25)
  IS
   -- CHANGE THE BOUNDARY STRING, IF NEEDED, WHICH DEMARCATES BOUNDARIES OF 
   -- PARTS IN A MULTI-PART EMAIL, AND SHOULD NOT APPEAR INSIDE THE BODY OF 
   -- ANY PART OF THE E-MAIL: 
 
   BOUNDARY           CONSTANT VARCHAR2(256) := 'CES.BOUNDARY.DACA587499938898'; 
 
   RECIPIENTS          VARCHAR2(32767); 
   DIRECTORY_PATH      VARCHAR2(256); 
   FILE_NAME           VARCHAR2(256); 
   CRLF                VARCHAR2(2):= CHR(13) || CHR(10); 
   MESG                VARCHAR2(32767); 
   CONN                UTL_SMTP.CONNECTION; 
   TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; 
   FILE_ARRAY          VARCHAR2_TABLE; 
   TYPE_ARRAY          VARCHAR2_TABLE; 
   I                   BINARY_INTEGER; 
   VQTD_ANEXOS         NUMBER := 0;
   VINDICE             NUMBER := 0;
   VMIME_TYPE          VARCHAR2(1000);
   VNOME_ARQ           VARCHAR2(1000);
   VEXTENSAO_ARQ       VARCHAR2(50);
  
  BEGIN

   -- Open the SMTP connection and set the From and To e-mail addresses 

   conn := utl_smtp.open_connection(PSMTP_HOST,PSMTP_PORT); 
   utl_smtp.helo(conn,PSMTP_HOST); 
   recipients := PFROM; 
   utl_smtp.mail(conn,get_address(recipients)); 
   recipients := PTO; 
   while recipients is not null loop 
      utl_smtp.rcpt(conn,get_address(recipients)); 
   end loop; 
   recipients := PCC; 
   while recipients is not null loop 
      utl_smtp.rcpt(conn,get_address(recipients)); 
   end loop; 
   recipients := PBCC; 
   while recipients is not null loop 
      utl_smtp.rcpt(conn,get_address(recipients)); 
   end loop; 
   utl_smtp.open_data(conn); 

   -- Build the start of the mail message 

   mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf || 
      'From: ' || PFROM || crlf || 
      'Subject: ' || PSUBJECT || crlf || 
      'To: ' || PTO || crlf; 
   if PCC is not null then 
      mesg := mesg || 'Cc: ' || PCC || crlf; 
   end if; 
   if PBCC is not null then 
      mesg := mesg || 'Bcc: ' || PBCC || crlf; 
   end if; 
   mesg := mesg || 'Mime-Version: 1.0' || crlf || 
      'Content-Type: multipart/mixed; boundary="' || boundary || '"' || 
      crlf || crlf || 
      'This is a Mime message, which your current mail reader may not' || crlf || 
      'understand. Parts of the message will appear as text. If the remainder' || crlf || 
      'appears as random characters in the message body, instead of as' || crlf || 
      'attachments, then you''ll have to extract these parts and decode them' || crlf || 
      'manually.' || crlf || crlf; 
   utl_smtp.write_data(conn,mesg); 

   -- Write the text message or message file, if any 

   if PMESSAGE is not null then 
      mesg := '--' || boundary || crlf || 
         'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || 
          crlf || 
         'Content-Disposition: inline; filename="message.txt"' || crlf || 
         'Content-Transfer-Encoding: 7bit' || crlf || crlf; 
      utl_smtp.write_data(conn,mesg); 
      if substr(PMESSAGE,1,1) = '/' then 
         split_path_name(PMESSAGE,directory_path,file_name); 
         append_file(directory_path,PDIRECTORY_NAME,file_name,'text',conn); 
         utl_smtp.write_data(conn,crlf); 
      else 
         utl_smtp.write_data(conn,PMESSAGE || crlf); 
      end if; 
   end if; 

   VQTD_ANEXOS := FUN_QTD_ARQUIVOS(LTRIM(RTRIM(PFILES)),';');
   -- Append the files 
   
   IF VQTD_ANEXOS <> 0 THEN
     
     for i in 1..VQTD_ANEXOS loop 
        --
        VNOME_ARQ     := NULL;
        VMIME_TYPE    := NULL;
        VEXTENSAO_ARQ := NULL;
        --
        VINDICE    := VINDICE + 1;
        --
        VNOME_ARQ  := LTRIM(RTRIM(ELEMENTO(PFILES,VINDICE, ';')));
        --
        VEXTENSAO_ARQ := LOWER(RTRIM(LTRIM(SUBSTR(VNOME_ARQ,INSTR(VNOME_ARQ,'.')+1,10))));
        --
        VMIME_TYPE := FUN_RETORNA_MIME(VEXTENSAO_ARQ);
           -- Generate the MIME boundary line according to the file (mime) type 
           -- specified. 
  
           mesg := crlf || '--' || boundary || crlf; 
           if VEXTENSAO_ARQ <> 'text' then 
              mesg := mesg || 'Content-Type: ' || VMIME_TYPE || 
                 '; name="' || VNOME_ARQ || '"' || crlf || 
                 'Content-Disposition: attachment; filename="' || 
                 VNOME_ARQ || '"' || crlf || 
                 'Content-Transfer-Encoding: base64' || crlf || crlf ; 
           else 
              mesg := mesg || 'Content-Type: application/octet-stream; name="' || 
                 VNOME_ARQ || '"' || crlf || 
                 'Content-Disposition: attachment; filename="' || 
                 VNOME_ARQ || '"' || crlf || 
                 'Content-Transfer-Encoding: 7bit' || crlf || crlf ; 
           end if; 
           utl_smtp.write_data(conn,mesg); 
  
           -- Append the file contents to the end of the message 
  
           append_file(directory_path,PDIRECTORY_NAME,vNOME_ARQ,VMIME_TYPE,conn); 
           utl_smtp.write_data(conn,crlf); 
  
--        end if; 
     end loop; 
   END IF;
   -- Append the final boundary line 

   mesg := crlf || '--' || boundary || '--' || crlf; 
   utl_smtp.write_data(conn,mesg); 

   -- Close the SMTP connection 

   utl_smtp.close_data(conn); 
   utl_smtp.quit(conn); 

  END SEND_MAIL;
  

/*
 *@Objeto: GET_ADDRESS
 *@Descricao: Função responsável pelo retorno da lista de e-mails
 *@Autor: Oracle - Adaptação: Trevisolli
 *@Data: 12/05/2010
 *@Notas:   
 * -- Function to return the next email address in the list of email addresses, 
 * -- separated by either a "," or a ";".  From Oracle's demo_mail.  The format 
 * -- of mailbox may be in one of these: 
 * --    someone@some-domain 
 * --    "Someone at some domain" <someone@some-domain> 
 * --    Someone at some domain <someone@some-domain> 
*/   
  FUNCTION GET_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 IS 
        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 GET_ADDRESS; 


/*
 *@Objeto: SPLIT_PATH_NAME
 *@Descricao: Procedure responsável por separar o diretório do nome do arquivo
 *@Autor: Oracle - Adaptação: Trevisolli
 *@Data: 12/05/2010
 *@Notas:   
 * -- Procedure to split a file pathname into its directory path and file name 
 * -- components. 
*/

  PROCEDURE SPLIT_PATH_NAME(FILE_PATH IN VARCHAR2
                           ,DIRECTORY_PATH OUT VARCHAR2
                           ,FILE_NAME OUT VARCHAR2) IS 

     pos number; 

  begin 

     -- Separate the filename from the directory name 

     pos := instr(file_path,'/',-1); 
     if pos = 0 then 
        pos := instr(file_path,'\',-1); 
     end if; 
     if pos = 0 then 
        directory_path := null; 
     else 
        directory_path := substr(file_path,1,pos - 1); 
     end if; 
     file_name := substr(file_path,pos + 1); 

  END SPLIT_PATH_NAME; 


/*
 *@Objeto: APPEND_FILE
 *@Descricao: Adiciona os arquivos do SO no email.
 *@Autor: Oracle - Adaptação: Trevisolli
 *@Data: 12/05/2010
 *@Notas:   
 *-- Procedure to append a file's contents to the e-mail 
*/
  PROCEDURE APPEND_FILE(DIRECTORY_PATH IN VARCHAR2
                       ,DIRECTORY_NAME IN VARCHAR2
                       ,FILE_NAME IN VARCHAR2
                       ,FILE_TYPE IN VARCHAR2
                       ,CONN IN OUT UTL_SMTP.CONNECTION) 
  IS 

     generated_name  varchar2(30) := 'CESDIR' || to_char(sysdate,'HH24MISS'); 
     file_handle     utl_file.file_type; 
     bfile_handle    bfile; 
     bfile_len       number; 
     pos             number; 
     read_bytes      number; 
     line            varchar2(1000); 
     data            raw(200); 
     my_code         number; 
     my_errm         varchar2(32767); 

  begin 

     begin 

        -- Grant access to the directory, unless already defined, and open 
        -- the file (as a bfile for a binary file, otherwise as a text file). 
        line := directory_path; 
        
        if substr(file_type,1,4) != 'text' then 
           bfile_handle := bfilename(directory_name,file_name); 
           bfile_len := dbms_lob.getlength(bfile_handle); 
           pos := 1; 
           dbms_lob.open(bfile_handle,dbms_lob.lob_readonly); 
        else 
           file_handle := utl_file.fopen(directory_name,file_name,'r'); 
        end if; 
   
        -- Append the file contents to the end of the message 
   
        loop 
   
           -- If it is a binary file, process it 57 bytes at a time, 
           -- reading them in with a LOB read, encoding them in BASE64, 
           -- and writing out the encoded binary string as raw data 
   
           if substr(file_type,1,4) != 'text' then 
              if pos + 57 - 1 > bfile_len then 
                 read_bytes := bfile_len - pos + 1; 
              else 
                 read_bytes := 57; 
              end if; 
              dbms_lob.read(bfile_handle,read_bytes,pos,data); 
              utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data)); 
              pos := pos + 57; 
              if pos > bfile_len then 
                 exit; 
              end if; 
   
           -- If it is a text file, get the next line of text, append a 
           -- carriage return / line feed to it, and write it out 
   
           else 
              utl_file.get_line(file_handle,line); 
              utl_smtp.write_data(conn,line || crlf); 
           end if; 
     
        end loop; 
   
     -- Output any errors, except at end when no more data is found 
   
     exception 
        when no_data_found then 
           null; 
        when others then 
           my_code := SQLCODE; 
           my_errm := SQLERRM; 
           dbms_output.put_line('Error code ' || my_code || ': ' || 
              my_errm); 
     end; 

     -- Close the file (binary or text) 

     if substr(file_type,1,4) != 'text' then 
        --dbms_lob.close(bfile_handle); 
        NULL;
     else 
        utl_file.fclose(file_handle); 
     end if; 
     if directory_name = generated_name then 
        execute immediate 'drop directory ' || directory_name; 
     end if; 

  END APPEND_FILE; 

/*
 *@Objeto: ELEMENTO
 *@Descricao: Quebra por arquivo, a string enviada contendo a lista de
 *            Todos os anexos, separados por ponto-e-vírgula ";" .
 *@Autor: Dr_Gori Adaptação: Trevisolli
 *@Data: 12/05/2010
 *@Notas: NÃO ESQUECER de colocar um ponto-e-vírgula no último nome.
 *        Utilizar o separador como ponto-e-vírgula.
 *@Exemplo: Arquivo.txt;Arquivo2.jpg;Arquivo3.zip;
*/

  FUNCTION ELEMENTO( p_string VARCHAR2 
                   , p_elemento PLS_INTEGER 
                   , p_separador VARCHAR2 DEFAULT ';' 
                   ) RETURN VARCHAR2 AS 
    v_string VARCHAR2(5000); 
  BEGIN 
    v_string := p_string || p_separador; 
    FOR i IN 1 .. p_elemento - 1 
    LOOP 
      v_string := SUBSTR(v_string,INSTR(v_string,p_separador)+LENGTH(p_separador)); 
    END LOOP; 
    
    RETURN SUBSTR(v_string,1,INSTR(v_string,p_separador)-1); 
  END ELEMENTO; 

/*
 *@Objeto: FUN_QTD_ARQUIVOS
 *@Descricao: Conta elementos em uma lista
 *@Autor: Trevisolli
 *@Data: 12/05/2010
 *@Notas: NÃO ESQUECER de colocar um ponto-e-vírgula no último nome.
 *        Utilizar o separador como ponto-e-vírgula.
 *@Exemplo: Arquivo.txt;Arquivo2.jpg;Arquivo3.zip;
*/
  FUNCTION FUN_QTD_ARQUIVOS (PSTRING IN VARCHAR2
                            ,PDELIMITADOR IN VARCHAR2 DEFAULT ';')
  RETURN NUMBER    
  --                       
  IS                           
    VCONTADOR NUMBER := 0; 
    --
    VIND      NUMBER := 0;
  BEGIN
    FOR X IN 1..LENGTH(PSTRING) LOOP
      VIND := VIND + 1;
      IF SUBSTR(PSTRING,VIND,1) = PDELIMITADOR THEN
        VCONTADOR := VCONTADOR + 1;
      END IF;
    END LOOP;
    --
    RETURN VCONTADOR;
    --
  END FUN_QTD_ARQUIVOS;  

/*
 *@Objeto: FUN_RETORNA_MIME
 *@Descricao: Retorna o MIME TYPE de uma determinada extensão de arquivo.
 *@Autor: Oracle - Adaptação: Trevisolli
 *@Data: 12/05/2010
*/  
  FUNCTION FUN_RETORNA_MIME(PEXTENSAO IN VARCHAR2)
  RETURN VARCHAR2
  IS
    VRET GER_MIME.MIME_TYPE%TYPE;
  BEGIN 
    --
    SELECT MIME_TYPE
      INTO VRET
      FROM GER_MIME
     WHERE EXTENSAO = PEXTENSAO ;
    -- 
    IF VRET IS NOT NULL THEN
      RETURN VRET;
    ELSE
      RETURN 'application/octet-stream;';
    END IF;
    -- 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN 'application/octet-stream;';
  END FUN_RETORNA_MIME;  
  
    -- Enter further code below as specified in the Package spec.
END;

Nota:
Só fiz funcionar... precisa melhorar vários aspectos, tratar erros etc.. mas, está ai e, enviando anexos.
Espero que ajude ai....
qualquer coisa, só falar...

ricards · Qui, 13 Mai 2010 10:14 pm

Fala Trevisolli beleza?

Cara não sei se pra você ser desse jeito mas

Você pode baixar um aplicativo .exe em VB nesse site

http://www.petri.co.il/sendmail.htm

Baixe o sendmail.zip e extrai o arquivo exe em uma pasta.

Você pode chamar esse arquivo via linha de comando de qualquer aplicação, via forms, java, etc.

Via forms você pode utilizar o comando HOST ou a API de SHELL da pll D2KWUTIL chamando o metodo WINEXEC

Dai você passa a seguinte linha de comando:

Exemplo:

Esse comando deve ser executado como linha de comando.

Selecionar tudo

seu diretório sendmail /smtpserver mailsrv01 /to nome@seudominio.com /from nome@seudominio.com /subject mensagem subject /body corpo /attachment c:\temp\att.zip

Assim você enviar email de qualquer aplicação ou via processo batch do windows.

Até +
[/b]

Sex, 14 Mai 2010 8:29 am

Fala Ricardo, beleza brother?

Valeu mesmo pela dica!
Deixei esse exemplo em PL pois procurei bastante coisa na net e, alguns exemplos não funcionaram legal.
O único impecilho com o exemplo que passou, seria ficar na "mão" de um sistema operacional específico, mas, pra quem roda em ambiente Windows, show de bola.
Bom saber que temos mais de uma opção.
Muito obrigado mesmo.

Enviar E-mail com Anexo do Sistema Operacional

Quem está online