4 Replies Latest reply: Jun 4, 2013 2:06 PM by rp0428 RSS

    how to mail attachment(s) using pl/sql

    shuvro
      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
          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
            sorry ......its not working .
            • 3. Re: how to mail attachment(s) using pl/sql
              Jim Smith
              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
                >
                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