APEX : Send email with attachment (Part 2)

Please refer to my previous post related to this, here.

Moving forward, by now you should already completed doing these 2 things:

1. User can insert any attachment into the form
2. The attachment will be save into my own table within the Oracle database.

So now, the next step is to configure APEX to sent-out email together with the attachment.

The email process must be create at point [Processing > Processes] on [Page Processing] , right after the 'CopyFile' process (refer to my previous post)

1. Right click on [Processes] > Create
2. Select [PL/SQL] and Next
3. Give a name to identify the procesess and click Next
4. On the [Enter PL/SQL Page Process], paste below code;

DECLARE
    l_id number;
BEGIN
    l_id := APEX_MAIL.SEND( p_to        => 'fred@flintstone.com',
                            p_from      => 'barney@rubble.com',
                            p_subj      => 'APEX_MAIL with attachment',
                            p_body      => 'Please review the attachment.',
                            p_body_html => '<b>Please</b> review the attachment' );
    FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop
    APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,
                                  p_attachment => c1.blob_content,
                                  p_filename   => c1.filename,
                                  p_mime_type  => c1.mime_type);
    END LOOP;
END;
BEGIN
wwv_flow_mail.push_queue(
   P_SMTP_HOSTNAME => 'localhost',
   P_SMTP_PORTNO => '25'
);
END;

Please see the code and change whenever suit with your table name and page item. For example code below:

FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop

The [attachment_files] comes from my table which I have created previously in so called Part 1.

You can have multiple 'File Browse...'. You may also need to change the PL/SQL code if have more [File Browse]. Do comment below if you still need help.

I will try my best to help. :)

APEX : Send email with attachment

I have found a working solution for sending email with attachment using APEX application. But first, few items have to take note :-

1. User can insert any attachment into the form
2. The attachment will be save into my own table within the Oracle database.

User can insert any attachment

This will be require an item named 'File Browse...'. Simply add the item into the page. I assume at this point, you already know how to it. Lets name it as [P1_ATTACHMENT]

Take a look at the [Settings] tab when u edit this item page. The storage type setting can be either two : 1.Table WWV_FLOW_FILES and 2.BLOB column specified in Item Source attribute

I will choose option No.1. Why I did not choose option No.2? As I had mentioned that the attachment will be save into my own table?

I am not expert, perhaps you can correct me if I am wrong, but I think that insert a attachment files into table will require some additional coding when we want to execute it in APEX environment.

One more reason is, I already has define one table to fetch row in the [Processes] in [After Header] section. So by adding another one more table (attachment table) it will make APEX get confused. Anyway, I have tried it. It does not work.

The attachment will be save into my own table within the Oracle database.

Next step is to save the file attachment to be save in my own attachment table. Lets name my table as ATTACHMENT_FILES. 

CREATE TABLE ATTACHMENT_FILES
(
  ID                 NUMBER,
  BLOB_CONTENT       BLOB,
  MIMETYPE           VARCHAR2(255 BYTE),
  FILENAME           VARCHAR2(255 BYTE),
  LAST_UPDATE        DATE,
  CHARSET            VARCHAR2(128 BYTE),
  UPLOADED_BY        VARCHAR2(10 BYTE),
  DOC_SIZE           NUMBER
)

Since I already have my table ready, I will now need to create one process to copy the file from WWV_FLOW_FILES into ATTACHMENT_FILES.

This process must be create at point [Processing > Processes] on [Page Processing]

1. Right click on [Processes] > Create
2. Select [PL/SQL] and Next
3. Give a name to identify the procesess and click Next (lets name it as 'CopyFile')
4. On the [Enter PL/SQL Page Process], paste below code;

begin
insert into ATTACHMENT_FILES( id, blob_content, mimetype, filename, last_update, doc_size )
select :P1_YOUR_ID, blob_content, mime_type, filename, created_on, doc_size from  wwv_flow_files where  name = :P1_ATTACHMENT;
delete from wwv_flow_files where name = :P1_ATTACHMENT;
end;

So by now, you got the idea already right? First, we let the attachment to be saved into WWV_FLOW_FILES. Then we copied it over to our own table and delete the original data from WWV_FLOW_FILES to avoid consume space and storage.

To avoid any complex coding in PL/SQL, I believe this method is faster. It depends on your needs.

Hurmm..I will talk about sending email as Part 2 later on.

Update : This is the Part 2.