This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,926 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

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

User_C5V77
User_C5V77 Member Posts: 6 Green Ribbon

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.

Answers

  • jariola
    jariola Architect FinlandMember Posts: 11,045 Gold Crown

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

  • User_C5V77
    User_C5V77 Member Posts: 6 Green Ribbon

    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
    jariola Architect FinlandMember Posts: 11,045 Gold Crown

    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
    User_C5V77 Member Posts: 6 Green Ribbon

    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.