How to send an email with attachment using apex (PL/SQL) without storing the attachment in server

User_C5V77

Hi all, I wanted to send an email with attachment from the oracle apex workspace, currently i am using the APEX_MAIL package which includes (APEX_ATTACHMENT, APEX_EXPORT functions). But the issue is, we are creating an CSV file and adding it as a mail attachment while sending the email and the attachments are sometimes larger in size which are getting stored in server each time the mail is triggered. This issue is causing a regular maintenance for the DBA to clear the data in the server.

Can anyone suggest is there any alternative for the current approach in order to come out of the issue.

Thanks in advance.

jariola

What is reason to send data as CSV instead of displaying it in e.g. APEX interactive report?

User_C5V77

We need to send the report to the intended person who is responsible to clear/sort the issue and to make them get to know that there is an error in that particular report as we shall have greater than 100 reports in the application.

Currently we are able to send the CSV, but it is getting stored in the Database Server.

Please let me know if you need any info additionally.

jariola

If you like continue send data via email, I think you need write your own procedure to send emails using utl_smtp.

I would create reports and just send email notification with link to APEX report to users.

APEX mails are queued to internal table. I'm not sure can you purge send mails using APEX_INSTANCE_ADMIN package. See procedure TRUNCATE_LOG if that e.g. do it

User_C5V77

I have already written procedure using UTL_SMTP , but struggling to export the report into CSV , when i try to run the procedure created we were facing an error stating below ,

When i try to execute the below procedure i am getting a error as ,

"Access to session state is disabled , attempt to read/write."

Below is the procedure we had created and tried execute ,

CREATE OR REPLACE PROCEDURE c_send_mail (

                    p_to     IN VARCHAR2,

                    p_from    IN VARCHAR2,

                    p_subject   IN VARCHAR2,

                    p_text_msg  IN VARCHAR2 DEFAULT NULL,

                    p_attach_name IN VARCHAR2 DEFAULT NULL,

                    p_attach_mime IN VARCHAR2 DEFAULT NULL,

 P_APP_ID   IN VARCHAR2, 

                    P_PAGE_ID   IN VARCHAR2, 

                    P_REGION_ID  IN VARCHAR2, 

                    p_smtp_host  IN VARCHAR2,

                    p_smtp_port  IN NUMBER DEFAULT 25)  

AS

 l_mail_conn   UTL_SMTP.connection;

 l_boundary   VARCHAR2(50) := '----=*#abc1234321cba#*=';

 l_step     PLS_INTEGER ; -- make sure you set a multiple of 3 not higher than 24573

 l_len      PLS_INTEGER;

 l_app_id    number := TO_NUMBER(P_APP_ID);  -- MY APP  

 l_page_id    number := TO_NUMBER(P_PAGE_ID);

 l_region_id   number := TO_NUMBER(P_REGION_ID);

 l_export    apex_data_export.t_export; 

 p_attach_clob  CLOB DEFAULT NULL;

 l_workspace_id NUMBER; 

 P_APP_USER   VARCHAR2(50) := 'HDQ\ctk0061'; -- EXAMPLE_USER

BEGIN

SELECT workspace_id  

  INTO l_workspace_id  

  FROM apex_applications  

  WHERE application_id = l_app_id;  

    

  wwv_flow_api.set_security_group_id( l_workspace_id );

 apex_session.create_session(  

    p_app_id  => l_app_id,  

    p_page_id => l_page_id,  

    p_username => P_APP_USER );  

 

l_export := apex_region.export_data (  

      p_format    => apex_data_export.c_format_csv,  

      p_page_id   => l_page_id,  

      p_region_id  => l_region_id,  

      p_as_clob   => true);  

  

p_attach_clob := l_export.content_clob;

 l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

 UTL_SMTP.helo(l_mail_conn, p_smtp_host);

 UTL_SMTP.mail(l_mail_conn, p_from);

 UTL_SMTP.rcpt(l_mail_conn, p_to);

 

  

UTL_SMTP.open_data(l_mail_conn);

  

 UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

 UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  

 IF p_text_msg IS NOT NULL THEN

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, p_text_msg);

  UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

 END IF;

  

 IF p_attach_name IS NOT NULL THEN

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

l_len := DBMS_LOB.getlength(p_attach_clob);

l_step :=1;

   

WHILE l_step <= l_len

  LOOP

    UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.SUBSTR(p_attach_clob, 32000, l_step));

    l_step := l_step + 32000;

  END LOOP;

  UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

 END IF;

  

 UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

 UTL_SMTP.close_data(l_mail_conn);

 UTL_SMTP.quit(l_mail_conn);

END;

-----------------------------------PROCEDURE EXECUTION ---------------------------------------------------

BEGIN

 c_send_mail(p_to     => '[email protected]',

      p_from    => '[email protected]',

      p_subject   => 'Test Message',

      p_text_msg  => 'This is a test message.',

      p_attach_name => 'test.xls',

      p_attach_mime => 'text/plain',

       '105',

      '3',

      '9201572456996013',

      p_smtp_host  => 'smtp.abc.com');

END;

Please let me know if you find any errors/corrections and also if this approach can be improved by any means

Thanks in advance , this would be really helpful to me.