This discussion is archived
4 Replies Latest reply: Jun 4, 2013 12:06 PM by rp0428 RSS

how to mail attachment(s) using pl/sql

shuvro Newbie
Currently Being Moderated
Hello,
I am using Database 10g.

can you guys tell me, how to mail attachment by using pl/sql procedure (code) ?
waiting for you relpy .
  • 1. Re: how to mail attachment(s) using pl/sql
    Jim Smith Expert
    Currently Being Moderated
    This is the forum for Oracle's SQL Developer tool, not for general SQL and PL/SQL questions.
    Questions like this will get a better response in the SQL and PL/SQL forum.

    Having said that, here is an example.
    procedure sendmail_attachment(subject in varchar2, content in varchar2, attachment in blob, addresses in addresslist)
     is
     conn utl_smtp.connection;
    
     smtp_server varchar2(30):='xxx.xxx.xxx.xxx;
      v_raw raw(57);
        v_length integer := 0;
        v_buffer_size integer := 57;
        v_offset integer := 1;
     begin
      dbms_output.put_line('Opening connection');
      conn:=utl_smtp.open_connection(smtp_server,25);
      dbms_output.put_line('Handshaking');
    
      utl_smtp.helo(conn,'pias.com');
      dbms_output.put_line('Sending mail');
      utl_smtp.mail(conn,'xxxxxx@xxxx.com');
      for i in addresses.first..addresses.last loop
        utl_smtp.rcpt(conn,addresses(i));
      end loop;
      utl_smtp.open_data(conn);
      utl_smtp.write_data(conn,'Subject:'||subject);
       utl_smtp.write_data( conn, utl_tcp.crlf );
    
      utl_smtp.write_data( conn, 'Content-Disposition: attachment; filename="attachment"' || utl_tcp.crlf);
        utl_smtp.write_data( conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
        utl_smtp.write_data( conn, utl_tcp.crlf );
    
        v_length := dbms_lob.getlength(attachment);
    
        <<while_loop>>
        while v_offset < v_length loop
          dbms_lob.read( attachment, v_buffer_size, v_offset, v_raw );
          utl_smtp.write_raw_data( conn, utl_encode.base64_encode(v_raw) );
          utl_smtp.write_data( conn, utl_tcp.crlf );
          v_offset := v_offset + v_buffer_size;
        end loop while_loop;
    
        utl_smtp.write_data( conn, utl_tcp.crlf );
      utl_smtp.write_data(conn,utl_tcp.crlf||utl_tcp.crlf);
      utl_smtp.write_data(conn,content);
      utl_smtp.write_data(conn,utl_tcp.crlf||utl_tcp.crlf);
    
      utl_smtp.close_data(conn);
      dbms_output.put_line('Quitting');
      utl_smtp.quit(conn);
      dbms_output.put_line('Done');
    
    end;
  • 2. Re: how to mail attachment(s) using pl/sql
    shuvro Newbie
    Currently Being Moderated
    sorry ......its not working .
  • 3. Re: how to mail attachment(s) using pl/sql
    Jim Smith Expert
    Currently Being Moderated
    Works for me.

    In what way isn't it working?
    What errors do you get?
    Have you tried debugging it?
  • 4. Re: how to mail attachment(s) using pl/sql
    rp0428 Guru
    Currently Being Moderated
    >
    sorry ......its not working .
    >
    Then please mark this question ANSWERED and repost it in the SQL and PL/SQL forum where it belongs.
    SQL and PL/SQL

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points