2 Replies Latest reply: Dec 15, 2013 3:19 PM by fac586 RSS

    APEX_MAIL.ADD_ATTACHMENT - Code Check

    Vladimir Bartos

      Hi all,

       

      I would like to ask, if someone can see my code and let me know where I got an issue which cause this error:

       

      1 error has occurred

      • ORA-20022: Null value supplied for parameter p_mail_id.

       

      My email code worked just fine until I added there the below bold Attachment code. Seems that there is missing something.

       

      My Email code:

      ----------------------------------------------------------------------------------Code-------------------------------------------------------------------------

       

      DECLARE

      l_id number;

      l_body CLOB;

      l_body_html CLOB;

      subject CLOB;

       

      BEGIN

      if :P20_SEND_EMAIL_BOX_C = 'Yes' and :P20_CLOSE_COMMENT is not null then

       

      l_body:=l_body ||'Quote'||utl_tcp.crlf;

       

      l_body_html:=l_body_html || '<pre style="font-family:arial"><p> '||:P20_CLOSE_COMMENT ||'

      </p></pre>'||utl_tcp.crlf;

       

       

      -- Place your email in the outbox

         htmldb_mail.send(p_to => :P20_REQUESTER_EMAIL,

                          p_from => :P20_QUOTE_ANALYST_EMAIL,

                          p_bcc => :P20_QUOTE_ANALYST_EMAIL,

       

                          p_subj => 'Closed Ticket: '||:P20_TICKET_ID ||' | Quote: '||:P20_QUOTE_ID ||' | Customer: '||:P20_CUSTOMER_NAME ||' | Request: '||:P20_REQUEST_TYPE ||' ',

                          p_body => l_body,

                          p_body_html => L_BODY_HTML);

       

      -- Add Attachment to the email

         FOR c1 IN (select FILENAME, ATTACHMENT, MIMETYPE

                       from QTMT_ATTACHMENTS

                      where TICKET_ID = :P20_TICKET_ID order by ATTACH_LAST_UPDATE desc) loop

              --

              APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,

                                        p_attachment => c1.ATTACHMENT,

                                        p_filename   => c1.FILENAME,

                                        p_mime_type  => c1.MIMETYPE);

          END LOOP;

       

      -- Send you email(s) now

        htmldb_mail.push_queue(p_smtp_hostname => :P20_HOSTNAME, 

                                p_smtp_portno => :P20_PORT);

      end if;

      END;

       

      ----------------------------------------------------------------------------------Code-------------------------------------------------------------------------

       

      Thank you for your support and best regards,

       

      Vladimir