Forum Stats

  • 3,875,396 Users
  • 2,266,910 Discussions
  • 7,912,192 Comments

Discussions

How to send email with attachment, but the attachment shouldn't store in the server

User_DVZDY
User_DVZDY Member Posts: 1 Green Ribbon

PROCEDURE SEND_EMAIL (       

    P_APP_ID IN VARCHAR2,

    P_PAGE_ID IN VARCHAR2,

    P_REGION_ID IN VARCHAR2,

    P_APP_USER IN VARCHAR2,

    P_EMAIL IN VARCHAR2,

    P_REPORT_NAME IN VARCHAR2

) AS


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

    l_page_id      number := TO_NUMBER(P_PAGE_ID);       -- MY APP PAGE

    l_export       apex_data_export.t_export;

    l_region_id    number := TO_NUMBER(P_REGION_ID);

    l_mail_id      number;

    --l_component_id number := 12345567899000;

    l_workspace_id NUMBER;


BEGIN

    SELECT workspace_id

    INTO l_workspace_id

    FROM apex_applications

    WHERE application_id = l_app_id;

     

  wwv_flow_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);


l_mail_id := apex_mail.send(

        p_to        => P_EMAIL,

        p_from      => '[email protected]',

        p_subj      => P_REPORT_NAME||' - Interactive Report',

        p_body      => 'Attached is the excel generated from the '||P_REPORT_NAME||' Interactive Report.',

        p_body_html => 'Attached is the excel generated from the '||P_REPORT_NAME||' Interactive Report.' );


APEX_MAIL.ADD_ATTACHMENT(

        p_mail_id => l_mail_id,

        p_attachment => l_export.content_clob,

        p_filename => P_REPORT_NAME||'.csv',

        p_mime_type => 'text/csv');


    apex_mail.push_queue;

END SEND_EMAIL;

END REPORT_SUBSCRIPTION;

we are using this procedure to send mails in apex application. while using the app we came to know that the attachments that we are sending in this application are storing in server, thus making the application slow. any modifications can be done to this??

Is create session is responsible for this?

or the attachments function?

our aim to send to the mail with attachment, it doesn't have to store in the server.

Answers

  • jariola
    jariola Member Posts: 10,925 Gold Crown

    What is APEX and database version?

    Where and how that code you posted is called?

    while using the app we came to know that the attachments that we are sending in this application are storing in server, thus making the application slow

    What exactly is slow in application and how you have come to conclusion it's caused by sending emails with attachments?

    How big are those attachments?

    fac586
  • User_C5V77
    User_C5V77 Member Posts: 6 Green Ribbon

    Hi jariola ,

    We were using Oracle APEX VERSION - 22

    The code is created as a Procedure (Posted above ) and stored in DB , we are having APEX Application where we have several reports ,if there is any error in any report so in order to send any report to the respective responsible person to resolve any issue of that particular report we have created an application , so we have developed the app and when we select the respective report which needs to be sent as a mail , there are some input parameters and which are needed to run the procedure code posted above , we provide those and the respective report is created as a attachment to the procedure and the email is triggered.

    The main issue that we are facing is, we were able to send the mail but each time the mail is triggered the attachment is getting stored in the server which is consuming huge space and that require regular maintenance, which is not an effective approach, so we wish to find the alternative where we could send the report and should not store the same in server.

    The size of the report varies from 3MB - 800MB as well OfCourse, depending upon the data.


    Regards,

    Pavan

  • jariola
    jariola Member Posts: 10,925 Gold Crown

    Seems this is quite similar question found from below link

    https://community.oracle.com/tech/developers/discussion/4506198/how-to-send-an-email-with-attachment-using-apex-pl-sql-without-storing-the-attachment-in-server#latest

    I propose not sending the data as an attachment. Create APEX reports and email a link to the report to users.

  • User_C5V77
    User_C5V77 Member Posts: 6 Green Ribbon
    edited Nov 28, 2022 8:40AM

    Hi jariola ,

    Thanks for your advise , we will take this suggestion but we have also tried using UTL_SMTP Approach to send mail with the attachment but we are not sure how that approach would be helpful to resolve the issue as we don't have direct access to our DB we havent checked this yet.

    Here is the link to the approach we followd currently - How to send an email with attachment using apex (PL/SQL) without storing the attachment in server — oracle-tech

    seems like you have commented on this blog too ,

    can you help me to confirm me this info that ,

    Creating session using apex meaning

    apex_session.create_session(  

        p_app_id  => l_app_id,  

        p_page_id => l_page_id,  

        p_username => P_APP_USER );  

    using the above function , will the attachments gets stored in the database ?.

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,477 Red Diamond

    The code is created as a Procedure (Posted above ) and stored in DB , we are having APEX Application where we have several reports ,if there is any error in any report so in order to send any report to the respective responsible person to resolve any issue of that particular report we have created an application , so we have developed the app and when we select the respective report which needs to be sent as a mail , there are some input parameters and which are needed to run the procedure code posted above , we provide those and the respective report is created as a attachment to the procedure and the email is triggered.

    The main issue that we are facing is, we were able to send the mail but each time the mail is triggered the attachment is getting stored in the server which is consuming huge space and that require regular maintenance, which is not an effective approach, so we wish to find the alternative where we could send the report and should not store the same in server.

    The size of the report varies from 3MB - 800MB as well

    What information do these "reports" contain? How does one "resolve any issue of that particular report"?

    800MB is the equivalent of 200 Bibles (i.e. ~156,000,000 words). I don't fancy poring over that to find errors.

  • User_C5V77
    User_C5V77 Member Posts: 6 Green Ribbon
    edited Nov 29, 2022 7:27AM

    Hi fac586 ,

    we actually work for an industry , the have many reports on many elements in their industry , so we have created an app in apex where that app contains reports for each of those elements page by page , we were asked to work on the approach to send a mail incase if there is any issue in any page , we need to send the mail to the responsible person for that report to highlight / inform them that there is an issue in the report that we send as an attachment.

    The alternative which was suggested to send mail as a URL of that report was not accepted.

    Kindly help us to resolve this issue.

    Regards,

    Pavan