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;