用oracle发邮件的方式有好多种，这里说的，是用oracle官方网站上的demo_mail包（可以实现html、PDF、excel等）格式的邮件或附件

一、取得demo_mail的包，当然要根据自身企业的实际情况来做适当调整：

该包分为包头和包体，要修改的仅仅是包头部分的参数设置，以下是包头部分：

CREATE OR REPLACE PACKAGE demo_mail IS
  
  ----------------------- Customizable Section -----------------------
  
  -- Customize the SMTP host, port and your domain name below.
  smtp_host   VARCHAR2(256) := '10.10.10.2';--企业内部邮件服务器ip
  smtp_port   PLS_INTEGER   := 25;
  smtp_domain VARCHAR2(256) := 'sunny-well.com';--域名

  -- Customize the signature that will appear in the email's MIME header.
  -- Useful for versioning.
  MAILER_ID   CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';
 
  --------------------- End Customizable Section ---------------------

  -- A unique string that demarcates boundaries of parts in a multi-part email
  -- The string should not appear inside the body of any part of the email.
  -- Customize this if needed or generate this randomly dynamically.
  BOUNDARY        CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

  FIRST_BOUNDARY  CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
  LAST_BOUNDARY   CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
                                              utl_tcp.CRLF;

  -- A MIME type that denotes multi-part email (MIME) messages.
  MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
                                                  BOUNDARY || '"';
  MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER   := 76 / 4 * 3;

  -- A simple email API for sending email in plain text in a single call.
  -- The format of an email address is one of these:
  --   someone@some-domain
  --   "Someone at some domain" <someone@some-domain>
  --   Someone at some domain <someone@some-domain>
  -- The recipients is a list of email addresses  separated by
  -- either a "," or a ";"
  PROCEDURE mail(sender     IN VARCHAR2,
     recipients IN VARCHAR2,
     subject    IN VARCHAR2,
     message    IN VARCHAR2);

  -- Extended email API to send email in HTML or plain text with no size limit.
  -- First, begin the email by begin_mail(). Then, call write_text() repeatedly
  -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
  -- email in non-ASCII or multi-byte character set. End the email with
  -- end_mail().
  FUNCTION begin_mail(sender     IN VARCHAR2,
          recipients IN VARCHAR2,
          subject    IN VARCHAR2,
          mime_type  IN VARCHAR2    DEFAULT 'text/plain',
          priority   IN PLS_INTEGER DEFAULT NULL)
          RETURN utl_smtp.connection;

  -- Write email body in ASCII
  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,
           message IN VARCHAR2);

  -- Write email body in non-ASCII (including multi-byte). The email body
  -- will be sent in the database character set.
  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,
        message IN            VARCHAR2);
 
  -- Write email body in binary
  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,
          message IN RAW);

  -- APIs to send email with attachments. Attachments are sent by sending
  -- emails in "multipart/mixed" MIME format. Specify that MIME format when
  -- beginning an email with begin_mail().
 
  -- Send a single text attachment.
  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,
      data         IN VARCHAR2,
      mime_type    IN VARCHAR2 DEFAULT 'text/plain',
      inline       IN BOOLEAN  DEFAULT TRUE,
      filename     IN VARCHAR2 DEFAULT NULL,
            last         IN BOOLEAN  DEFAULT FALSE);
 
  -- Send a binary attachment. The attachment will be encoded in Base-64
  -- encoding format.
  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,
        data         IN RAW,
        mime_type    IN VARCHAR2 DEFAULT 'application/octet',
        inline       IN BOOLEAN  DEFAULT TRUE,
        filename     IN VARCHAR2 DEFAULT NULL,
        last         IN BOOLEAN  DEFAULT FALSE);
 
  -- Send an attachment with no size limit. First, begin the attachment
  -- with begin_attachment(). Then, call write_text repeatedly to send
  -- the attachment piece-by-piece. If the attachment is text-based but
  -- in non-ASCII or multi-byte character set, use write_mb_text() instead.
  -- To send binary attachment, the binary content should first be
  -- encoded in Base-64 encoding format using the demo package for 8i,
  -- or the native one in 9i. End the attachment with end_attachment.
  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,
           mime_type    IN VARCHAR2 DEFAULT 'text/plain',
           inline       IN BOOLEAN  DEFAULT TRUE,
           filename     IN VARCHAR2 DEFAULT NULL,
           transfer_enc IN VARCHAR2 DEFAULT NULL);
 
  -- End the attachment.
  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
         last IN BOOLEAN DEFAULT FALSE);
 
  -- End the email.
  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

  -- Extended email API to send multiple emails in a session for better
  -- performance. First, begin an email session with begin_session.
  -- Then, begin each email with a session by calling begin_mail_in_session
  -- instead of begin_mail. End the email with end_mail_in_session instead
  -- of end_mail. End the email session by end_session.
  FUNCTION begin_session RETURN utl_smtp.connection;
 
  -- Begin an email in a session.
  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,
          sender     IN VARCHAR2,
          recipients IN VARCHAR2,
          subject    IN VARCHAR2,
          mime_type  IN VARCHAR2  DEFAULT 'text/plain',
          priority   IN PLS_INTEGER DEFAULT NULL);
 
  -- End an email in a session.
  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
 
  -- End an email session.
  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;

以下是包体部分

CREATE OR REPLACE PACKAGE BODY demo_mail IS
  
  -- Return the next email address in the list of email addresses, separated
  -- by either a "," or a ";".  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 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;
     
   <>
   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;

  -- Write a MIME header
  PROCEDURE write_mime_header(conn  IN OUT NOCOPY utl_smtp.connection,
            name  IN VARCHAR2,
            value IN VARCHAR2) IS
  BEGIN
    utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
  END;

  -- Mark a message-part boundary.  Set to TRUE for the last boundary.
  PROCEDURE write_boundary(conn  IN OUT NOCOPY utl_smtp.connection,
         last  IN            BOOLEAN DEFAULT FALSE) AS
  BEGIN
    IF (last) THEN
      utl_smtp.write_data(conn, LAST_BOUNDARY);
    ELSE
      utl_smtp.write_data(conn, FIRST_BOUNDARY);
    END IF;
  END;

  ------------------------------------------------------------------------
  PROCEDURE mail(sender     IN VARCHAR2,
     recipients IN VARCHAR2,
     subject    IN VARCHAR2,
     message    IN VARCHAR2) IS
    conn utl_smtp.connection;
  BEGIN
    conn := begin_mail(sender, recipients, subject);
    write_text(conn, message);
    end_mail(conn);
  END;

  ------------------------------------------------------------------------
  FUNCTION begin_mail(sender     IN VARCHAR2,
          recipients IN VARCHAR2,
          subject    IN VARCHAR2,
          mime_type  IN VARCHAR2    DEFAULT 'text/plain',
          priority   IN PLS_INTEGER DEFAULT NULL)
          RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
  BEGIN
    conn := begin_session;
    begin_mail_in_session(conn, sender, recipients, subject, mime_type,
      priority);
    RETURN conn;
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,
           message IN VARCHAR2) IS
  BEGIN
    utl_smtp.write_data(conn, message);
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,
        message IN            VARCHAR2) IS
  BEGIN
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,
          message IN RAW) IS
  BEGIN
    utl_smtp.write_raw_data(conn, message);
  END;

  ------------------------------------------------------------------------
  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,
      data         IN VARCHAR2,
      mime_type    IN VARCHAR2 DEFAULT 'text/plain',
      inline       IN BOOLEAN  DEFAULT TRUE,
      filename     IN VARCHAR2 DEFAULT NULL,
            last         IN BOOLEAN  DEFAULT FALSE) IS
  BEGIN
    begin_attachment(conn, mime_type, inline, filename);
    write_text(conn, data);
    end_attachment(conn, last);
  END;

  ------------------------------------------------------------------------
  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,
        data         IN RAW,
        mime_type    IN VARCHAR2 DEFAULT 'application/octet',
        inline       IN BOOLEAN  DEFAULT TRUE,
        filename     IN VARCHAR2 DEFAULT NULL,
        last         IN BOOLEAN  DEFAULT FALSE) IS
    i   PLS_INTEGER;
    len PLS_INTEGER;
  BEGIN
   
    begin_attachment(conn, mime_type, inline, filename, 'base64');

    -- Split the Base64-encoded attachment into multiple lines
    i   := 1;
    len := utl_raw.length(data);
    WHILE (i < len) LOOP
       IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
   utl_smtp.write_raw_data(conn,
      utl_encode.base64_encode(utl_raw.substr(data, i,
      MAX_BASE64_LINE_WIDTH)));
       ELSE
   utl_smtp.write_raw_data(conn,
     utl_encode.base64_encode(utl_raw.substr(data, i)));
       END IF;
       utl_smtp.write_data(conn, utl_tcp.CRLF);
       i := i + MAX_BASE64_LINE_WIDTH;
    END LOOP;
   
    end_attachment(conn, last);

  END;

  ------------------------------------------------------------------------
  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,
           mime_type    IN VARCHAR2 DEFAULT 'text/plain',
           inline       IN BOOLEAN  DEFAULT TRUE,
           filename     IN VARCHAR2 DEFAULT NULL,
           transfer_enc IN VARCHAR2 DEFAULT NULL) IS
  BEGIN
    write_boundary(conn);
    write_mime_header(conn, 'Content-Type', mime_type);

    IF (filename IS NOT NULL) THEN
       IF (inline) THEN
    write_mime_header(conn, 'Content-Disposition',
      'inline; filename="'||filename||'"');
       ELSE
    write_mime_header(conn, 'Content-Disposition',
      'attachment; filename="'||filename||'"');
       END IF;
    END IF;

    IF (transfer_enc IS NOT NULL) THEN
      write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
    END IF;
   
    utl_smtp.write_data(conn, utl_tcp.CRLF);
  END;

  ------------------------------------------------------------------------
  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
         last IN BOOLEAN DEFAULT FALSE) IS
  BEGIN
    utl_smtp.write_data(conn, utl_tcp.CRLF);
    IF (last) THEN
      write_boundary(conn, last);
    END IF;
  END;

  ------------------------------------------------------------------------
  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    end_mail_in_session(conn);
    end_session(conn);
  END;

  ------------------------------------------------------------------------
  FUNCTION begin_session RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
  BEGIN
    -- open SMTP connection
    conn := utl_smtp.open_connection(smtp_host, smtp_port);
    utl_smtp.helo(conn, smtp_domain);
    RETURN conn;
  END;

  ------------------------------------------------------------------------
  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,
          sender     IN VARCHAR2,
          recipients IN VARCHAR2,
          subject    IN VARCHAR2,
          mime_type  IN VARCHAR2  DEFAULT 'text/plain',
          priority   IN PLS_INTEGER DEFAULT NULL) IS
    my_recipients VARCHAR2(32767) := recipients;
    my_sender     VARCHAR2(32767) := sender;
  BEGIN

    -- Specify sender's address (our server allows bogus address
    -- as long as it is a full email address (xxx@yyy.com).
    utl_smtp.mail(conn, get_address(my_sender));

    -- Specify recipient(s) of the email.
    WHILE (my_recipients IS NOT NULL) LOOP
      utl_smtp.rcpt(conn, get_address(my_recipients));
    END LOOP;

    -- Start body of email
    utl_smtp.open_data(conn);

    -- Set "From" MIME header
    write_mime_header(conn, 'From', sender);

    -- Set "To" MIME header
    write_mime_header(conn, 'To', recipients);

    -- Set "Subject" MIME header
    write_mime_header(conn, 'Subject', subject);

    -- Set "Content-Type" MIME header
    write_mime_header(conn, 'Content-Type', mime_type);

    -- Set "X-Mailer" MIME header
    write_mime_header(conn, 'X-Mailer', MAILER_ID);

    -- Set priority:
    --   High      Normal       Low
    --   1     2     3     4     5
    IF (priority IS NOT NULL) THEN
      write_mime_header(conn, 'X-Priority', priority);
    END IF;

    -- Send an empty line to denotes end of MIME headers and
    -- beginning of message body.
    utl_smtp.write_data(conn, utl_tcp.CRLF);

    IF (mime_type LIKE 'multipart/mixed%') THEN
      write_text(conn, 'This is a multi-part message in MIME format.' ||
  utl_tcp.crlf);
    END IF;

  END;

  ------------------------------------------------------------------------
  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    utl_smtp.close_data(conn);
  END;
   
  ------------------------------------------------------------------------
  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    utl_smtp.quit(conn);
  END;

END;

二、创建调用demo_mail的允许格式的附件处理过程，这里是用的html的方式

CREATE OR REPLACE PROCEDURE sp_html_email2(
    p_to            IN VARCHAR2 default 'dba<dba@xxxx.com>',
    p_from          IN VARCHAR2 default 'ERP Alert <erp.alert@xxx.com>',
    p_subject       IN VARCHAR2 default 'Testing with attachment',
    p_text          IN VARCHAR2 DEFAULT NULL,
    p_attachfilename IN VARCHAR2 default 'swassettopc.xls',
    p_smtp_hostname IN VARCHAR2,
    p_smtp_portnum  IN VARCHAR2)
IS
  att_file utl_file.file_type;
  vchFile VARCHAR2(100) := 'swassettopc.xls'; --- The filename
  vchLoc  VARCHAR2(100) := 'ORAHOME';         --- The Folder/Directory
  conn utl_smtp.connection;
  raw_data RAW(200);
  nmbLength  NUMBER;
  bExists    BOOLEAN;
  nmbBlock   NUMBER;
  nmbPos     NUMBER:=1;
  l_attachtype  VARCHAR2(10);
  l_mimetype  VARCHAR2(100);
  vchMessage VARCHAR2(100);
BEGIN
  vchFile := p_attachfilename;
  l_attachtype := upper(substr(p_attachfilename, instr(p_attachfilename,'.',-1)+1));
  if l_attachtype = 'XLS' then
      l_mimetype := 'application/vnd.ms-excel';
  end if;
  if l_attachtype = 'DOC' then
      l_mimetype := 'application/vnd.ms-word';
  end if;
  if l_attachtype = 'PDF' then
      l_mimetype := 'application/pdf';
  end if;
  if l_attachtype in ('HTML','HTM') then
      l_mimetype := 'text/html';
  end if;
  --l_mimetype := 'application/vnd.ms-excel';
  dbms_output.put_line(l_attachtype||':'||l_mimetype||','||demo_mail.MULTIPART_MIME_TYPE);

  conn := demo_mail.begin_mail( 
        sender => p_from,
        recipients => p_to,
        subject => p_subject,
        mime_type => demo_mail.MULTIPART_MIME_TYPE);

--        conn => conn, mime_type => 'application/vnd.ms-excel',
       
  demo_mail.begin_attachment(
        conn => conn, mime_type => l_mimetype,
        inline => TRUE, filename => vchFile,
        transfer_enc => 'base64');
       
  utl_file.fgetattr(vchLoc, vchFile, bExists, nmbLength, nmbBlock); -- Check the existence of the file
 
  IF bExists THEN
    vchMessage   := 'Open File !';
    att_file     := utl_file.fopen(vchLoc, vchFile, 'r', 32767);
    vchMessage   := 'Loop data !';
    WHILE (nmbPos < nmbLength)
    LOOP
      IF nmbPos + demo_mail.MAX_BASE64_LINE_WIDTH < nmbLength THEN
        utl_file.get_raw(file => att_file, buffer => raw_data, LEN => demo_mail.MAX_BASE64_LINE_WIDTH);
      ELSE
        utl_file.get_raw(att_file, buffer => raw_data);
      END IF;
      utl_smtp.write_raw_data(conn,utl_encode.base64_encode(raw_data));
      utl_smtp.write_data(conn, utl_tcp.CRLF);
      nmbPos := nmbPos + demo_mail.MAX_BASE64_LINE_WIDTH;
    END LOOP;
    vchMessage := 'Close File !';
    utl_file.fclose(att_file);
  END IF;
  demo_mail.end_attachment( conn => conn );
  demo_mail.end_mail( conn => conn );
  dbms_output.put_line('success.....');
EXCEPTION
WHEN OTHERS THEN
  utl_file.fclose_all;
  dbms_output.put_line(nmbPos);
  dbms_output.put_line('Error...'||vchMessage||' '||sqlerrm);
END;

三、上面这个过程，我们有用到oracle的Directory，所以呢，我们还要确认是否已经在oracle中是否创建了这个目录，如果没有的话，则需要创建

create or replace directory ORAHOME as '/home/oracle';

四、最后，我们就可以根据我们的实际业务需求生成目标文件，然后发送邮件了

以下样例供参考：

create or replace procedure sp_testing
as
  ReceiverAddress varchar2(200):='dba@xxx.com';

  P_ENCODE  varchar2(10):='base64';                                                      

  Title     varchar2(200);
  vText     varchar2(12000);
  vHtml     varchar2(32767);
  vHtml1     varchar2(2000);
  vHtml2     varchar2(2000);
  nstatus   number;
  Ptype     varchar2(1);

--  vText       VARCHAR2(4000);
  vSubject    VARCHAR2(400);
  vSender     VARCHAR2(200);
  vRecipient  VARCHAR2(200);
  vApplicant  VARCHAR2(200);
  vFollowupList VARCHAR2(200);
  vApplicantName  VARCHAR2(40);
  vL1         VARCHAR2(200);
  vL2         VARCHAR2(200);
  vEmail      VARCHAR2(200);
  vDBNAME     VARCHAR2(15);
  --vDept       varchar2(20);
  vCount      INTEGER;
 
  CURSOR cur_row IS
    select pono, podate
    from purchaseorder
    where approved='Y' and a.approvedtime=sysdate-1;

  cur_rec  cur_row%rowtype;

  l_attachfile  utl_file.file_type;
  l_attachloc   varchar2(100);
  l_attachfilename varchar2(100);
  v_recCount    numeric(4);
begin
  l_attachfilename := 'check_alert.html';
  l_attachloc := 'ORAHOME';

  vSender := 'erp.alert@xxx.com';
  vL1 := ' ';
  vL2 := ' ';
  vApplicant := ' ';
  v_recCount := 0;

  vtext := 'File is attached, pls check!';
  title:= '[ ' || vDBNAME || ' ]PO List report';
  vHtml1 := ''
        || '
';
  vHtml1 := vHtml1 || '

  vHtml1 := vHtml1 || '

  --vDept := '***';
  l_attachfile := utl_file.fopen(l_attachloc, l_attachfilename,'W');
  utl_file.put_line(l_attachfile, vHtml1); -- add html heading
 
  for cur_rec in cur_row
  loop
    v_recCount := v_recCount + 1;

    vHtml := ''
        ||chr(13)||''||cur_rec.pono||''
        ||chr(13)||''||to_char(cur_rec.podate,'YYYY-MM-DD')||''
        ||chr(13)||'';
    utl_file.put_line(l_attachfile, vHtml);
  end loop;
 
 utl_file.put_line(l_attachfile, vHtml2);  --add html footing
 utl_file.fclose(l_attachfile);
      
  if v_recCount>0 then
        dtradeuser.SP_HTML_EMAIL2(
            P_TO => ReceiverAddress,
            P_FROM => 'erp.alert@xxx.com',
            P_SUBJECT => title,
            P_TEXT => vtext,
            P_ATTACHFILENAME => L_ATTACHFILENAME,
            P_SMTP_HOSTNAME => null,
            P_SMTP_PORTNUM => null
          );
  end if;