terça-feira, 19 de abril de 2011

Oracle – Envio de e-mail com anexo

 

-- Script: ces_email_files.sql (for Oracle 9.2 and above)

--

-- Purpose: Sends e-mail (text and/or html, either as a string or from a file)

-- to one or more recipients (including cc and/or bcc recipients), along with

-- up to 3 file attachments (text and/or binary; default is text/plain), using

-- the UTL_SMTP package to send the e-mail, the DBMS_LOB package to read

-- binary file attachments, and the UTL_ENCODE package to convert the binary

-- attachments to BASE64 for character string (non-binary) transmission.

-- BE AWARE THAT A COMMIT MAY BE DONE BY THIS ROUTINE (see HOWEVER... below).

--

-- The complete parameter list for the email_files procedure is shown below:

--    from_name - name and e-mail address to put in the From field

--    to_names - names and e-mail addresses for the To field (separated by

--       commas or semicolons)

--    subject - text string for Subject field

--    message - text string or text file name for Message, if any

--    html_message - html string or html file name for Message, if any

--    cc_names - names and e-mail addresses for the Cc field, if any

--       (separated by commas or semicolons)

--    bcc_names - names and e-mail addresses for the Bcc field, if any

--       (separated by commas or semicolons)

--    filename1 - first unix file pathname to attach, if any

--    filetype1 - mime type of first file (defaults to 'text/plain')

--    filename2 - second unix file pathname to attach, if any

--    filetype2 - mime type of second file (defaults to 'text/plain')

--    filename3 - third unix file pathname to attach, if any

--    filetype3 - mime type of third file (defaults to 'text/plain')

--

-- Sample names and e-mail addresses are: srea (attaches @<localhost>),

-- srea@uaex.edu, <srea@uaex.edu>, Steve Rea <srea@uaex.edu>, and

-- "Steve Rea" <srea@uaex.edu>

--

-- A sample call in PL/SQL is shown below, which sends a text and html message,

-- plus a text file and two binary files (note: the slash after "end;" must be

-- the first character on it's line):

--

--    begin

--       email_files(from_name    => 'oracle' ,

--                   to_names     => 'srea@uaex.edu',

--                   subject      => 'A test',

--                   message      => 'A test message',

--                   html_message => '<h2>A <u><i>test</i></u> message</h2>',

--                   filename1    => '/tmp/web_ptrbdca.txt',

--                   filename2    => '/tmp/password_standards.pdf',

--                   filetype2    => 'application/pdf',

--                   filename3    => '/tmp/wb703.jpg',

--                   filetype3    => 'image/jpeg');

--    end;

--    /

--

-- If the message or html_message string has a file name in it (starting with

-- a forward slash), the text or html file is copied into the e-mail as the

-- message or html message; otherwise, the message or html_message is copied

-- into the e-mail as-is.

--

-- Attachment file types (mime types) that I'vê tested include:

--    text/plain, text/html, image/jpeg, image/gif, application/pdf,

--    application/msword

-- A list of mime types can be seen at:

--    http://www.webmaster-toolkit.com/mime-types.shtml

-- If the mime type does not begin with "text", it is assumed to be a binary

-- file that will be encoded as base64 before transmission.

--

-- This was derived from several sources, including:

--    Dave Wotton (Cambridge UK) - mail_files.sql at

--       http://home.clara.net/dwotton/dba/oracle_smtp.htm

--    Oracle Technology Network - maildemo.sql (demo_mail) at

--       http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

--    Akadia's "Read a file word by word using DBMS_LOB" Tip at

--       http://www.akadia.com/services/read_file_with_dbms_lob.html

--    Tom Kyte of Oracle's AskTom site (http://AskTom.oracle.com) -

--       answer to "File Exists which is not a BFILE" and others.

--

-- NOTE: The user running this must have "create any directory" and "drop any

-- directory" privileges ("create directory" was introduced in Oracle 9iR2),

-- which must be granted from a system or dba account, such as:

--    grant create any directory to scott;

--    grant drop any directory to scott;

--  then:

--    connect / as sysdba

--    grant select on dba_directories to scott;

-- or, for everyone to have directory privileges:

--    grant create any directory to public;

--    grant drop any directory to public;

--  then:

--    connect / as sysdba

--    grant select on dba_directories to public;

-- Any file in any directory accessible to the user can be attached (not just

-- the directories listed for the utl_file_dir parameter in the init.ora file).

-- HOWEVER, if you are using this to send e-mail's with attached files (or

-- reading the message text or message html from a file) from a trigger or

-- from some other SQL that you can't or don't want to have a commit done,

-- you will first need to create an Oracle directory entry for the directory

-- containing the attached files and grant read access to it to public, such

-- as:

--    create directory CESDIR_COMMON as '/home/common';

--    grant read on directory CESDIR_COMMON to public;

--

-- You may also want to create a public synonym for this procedure, from the

-- procedure's owner:

--    create or replace public synonym email_files for email_files;

--    grant execute on email_files to public;

--

-- FYI: I tried using the utl_file package to read binary files:

--    utl_file.get_raw(v_file_handle,data,57);

-- but got "ORA-29284: file read error" on .pdf files, so, I switched to use

-- the dbms_lob package to read binary files:

--    dbms_lob.read(v_bfile_handle,read_bytes,v_pos,data);

--

-- Author: Stephen Rea <srea@uaex.edu>

--    University of Arkansas Cooperative Extension Service

-- Initial release: 12/21/04

--

-- Updates:

-- 1/31/05 - Fixed for triggers and other SQL that can't have a commit by

--    checking for and using already-defined Oracle directories ("create

--    directory" is a DDL statement that does a commit).

--

create or replace procedure AO_SEND_MAIL( from_name varchar2,

                                                  to_names varchar2,

                                                  subject varchar2,

                                                  message varchar2 default null,

                                                  html_message varchar2 default null,

                                                  cc_names varchar2 default null,

                                                  bcc_names varchar2 default null,

                                                  smtp_host varchar2,

                                                  smtp_port number default 25,

                                                  filename1 varchar2 default null,

                                                  filetype1 varchar2 default 'text/plain',

                                                  filename2 varchar2 default null,

                                                  filetype2 varchar2 default 'text/plain',

                                                  filename3 varchar2 default null,

                                                  filetype3 varchar2 default 'text/plain')

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);

   crlf_html          varchar2(30) := '<BR>';

   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;

   addr               VARCHAR2(256);  

   html_message_complemento varchar2(2000);

  

   -- 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

 

      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;

      exception

        when others then

          exibe ('O erro é: ' || sqlcode);

       

   END;

 

   -- 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;

 

   -- Procedure to append a file's contents to the e-mail

 

   PROCEDURE append_file(directory_path 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');

      directory_name  varchar2(100);

      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).

 

         begin

--            line := directory_path;

            select dd.my_path into directory_name from table_with_path dd;

 

         exception

            when no_data_found then

               directory_name := generated_name;

         end;

         if directory_name = generated_name then

            execute immediate 'create or replace directory ' || directory_name ||

               ' as ''' || directory_path || '''';

            execute immediate 'grant read on directory ' || directory_name ||

               ' to public';

         end if;

         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('AO_SEND_MAIL: 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);

      else

         utl_file.fclose(file_handle);

      end if;

      if directory_name = generated_name then

         execute immediate 'drop directory ' || directory_name;

      end if;

 

   end;

 

begin

 

   -- Load the three filenames and file (mime) types into an array for

   -- easier handling later

 

   file_array(1) := filename1;

   file_array(2) := filename2;

   file_array(3) := filename3;

   type_array(1) := filetype1;

   type_array(2) := filetype2;

   type_array(3) := filetype3;

 

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

 

   conn := utl_smtp.open_connection(smtp_host,smtp_port);

   utl_smtp.helo(conn,smtp_host);

   recipients := from_name;

   utl_smtp.mail(conn,get_address(recipients));

 

   recipients := to_names;

  

   <<para>>

   begin

     while recipients is not null loop

        utl_smtp.rcpt(conn,get_address(recipients));

     end loop;

  

     exception

        when others then

              if sqlcode =  -29279 then -- E-mail inexistente         

                  recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; 

                  html_message_complemento := addr || crlf;

                  goto para;

              end if;

   end;

  

   recipients := cc_names;

    

   <<copia>>

   begin

     while recipients is not null loop

        utl_smtp.rcpt(conn,get_address(recipients));

     end loop;

 

     exception

        when others then

              if sqlcode =  -29279 then -- E-mail inexistente         

                  recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; 

                  html_message_complemento := addr || crlf;

                  goto copia;

              end if;

   end;

    

   recipients := bcc_names;

 

   <<oculta>> --copia oculta

   begin

     while recipients is not null loop

        utl_smtp.rcpt(conn,get_address(recipients));

     end loop;

 

     exception

        when others then

              if sqlcode =  -29279 then -- E-mail inexistente         

                  recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; 

                  html_message_complemento := addr || crlf;

                  goto oculta;

              end if;

   end;

       

   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: ' || from_name || crlf ||

      'Subject: ' || subject || crlf ||

      'To: ' || to_names || crlf;

   if cc_names is not null then

      mesg := mesg || 'Cc: ' || cc_names || crlf;

   end if;

   if bcc_names is not null then

      mesg := mesg || 'Bcc: ' || bcc_names || 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 message 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(message,1,1) = '/' then

         split_path_name(message,directory_path,file_name);

         append_file(directory_path,file_name,'text',conn);

         utl_smtp.write_data(conn,crlf);

      else

         utl_smtp.write_data(conn,message || crlf);

      end if;

   end if;

 

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

 

   if html_message_complemento is not null then -- Contém e-mails inexistentes

      html_message_complemento := '<FONT face= "Courier New" SIZE=2 COLOR="#ff0000">' ||

                                  '---------------------------------------------------------------------' || crlf_html ||

                                  ' PROBLEMAS NO ENVIO DE E-MAIL  ' || crlf_html ||

                                  '---------------------------------------------------------------------' || crlf_html ||

                                  'A lista abaixo contem e-mails que apresentam problemas de envio.' || crlf_html ||

                                  'Estes e-mails devem ser verificados e/ou excluidos pelo aplicativo' || crlf_html ||

                                  'Manutencao de Excecoes.' || crlf_html || crlf_html ||

                                   html_message_complemento || crlf_html ||

                                  '---------------------------------------------------------------------' || crlf_html ||

                                  '</FONT>';

                                     

      html_message_complemento := nvl(html_message,'.') || crlf_html || html_message_complemento;

   end if;

     

   if html_message_complemento is not null then 

      mesg := '--' || boundary || crlf ||

         'Content-Type: text/html; name="message.html"; charset=US-ASCII' ||

         crlf ||

         'Content-Disposition: inline; filename="message.html"' || crlf ||

         'Content-Transfer-Encoding: 7bit' || crlf || crlf;

      utl_smtp.write_data(conn,mesg);

      if substr(html_message_complemento,1,1) = '/' then

         split_path_name(html_message_complemento,directory_path,file_name);

         append_file(directory_path,file_name,'text',conn);

         utl_smtp.write_data(conn,crlf);

      else

         utl_smtp.write_data(conn,html_message_complemento || crlf);

      end if;

   end if;

 

   -- Append the files

 

   for i in 1..3 loop

 

      -- If the filename has been supplied ...

 

      if file_array(i) is not null then

 

         split_path_name(file_array(i),directory_path,file_name);

 

         -- Generate the MIME boundary line according to the file (mime) type

         -- specified.

 

         mesg := crlf || '--' || boundary || crlf;

         if substr(type_array(i),1,4) != 'text' then

            mesg := mesg || 'Content-Type: ' || type_array(i) ||

               '; name="' || file_name || '"' || crlf ||

               'Content-Disposition: attachment; filename="' ||

               file_name || '"' || crlf ||

               'Content-Transfer-Encoding: base64' || crlf || crlf ;

         else

            mesg := mesg || 'Content-Type: application/octet-stream; name="' ||

               file_name || '"' || crlf ||

               'Content-Disposition: attachment; filename="' ||

               file_name || '"' || 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,file_name,type_array(i),conn);

         utl_smtp.write_data(conn,crlf);

 

      end if;

   end loop;

 

   -- 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;

2 comentários :

  1. ocorreu um erro na linha :
    select dd.my_path into directory_name from ao_edp_367_path dd;
    Pode me da um ajuda nesse teu procedimento?

    rdgalvesrodrigues@hotmail.com

    ResponderExcluir
  2. Olá rdgalvesrodrigues,

    eu lhe enviei um e-mail a cerca de dois meses solicitando o erro ocorrido, você já corrigiu o problema?

    Abraço

    ResponderExcluir

Related Posts Plugin for WordPress, Blogger...