7 Replies Latest reply: May 3, 2013 8:02 AM by InoL RSS

    Multiple PDF attachmentsof reports in MAIL

    Raivis
      Hi, i have 1 problem.

      I have 2 reports.
      But when i push button and reports generate as PDF attachments in mail somethin isn't going right.

      1) Generates first report as PDF attachment in MAIL ... when click 'X' (close) button in MAIL then starts generate second report PDF attachment and MAIL opens again.

      I want that both PDF attachments could generate in one form.
      What should i do ?

      BR,
      Raivis
        • 1. Re: Multiple PDF attachmentsof reports in MAIL
          InoL
          Since every report is run separately, it will create separate emails when using standard Reports mail functionality. So, you need to run the two reports, save them, and kick off some other process after the two reports are finished.
          That process can be anything. I have used database package APEX_MAIL to make emails with multiple attachment. Since every database has Apex installed, you can easily use this package. It has a procedure called apex_mail.add_attachment that you call multiple times.

          Another option might be to use some command line mail tool that you call after running the reports.
          • 2. Re: Multiple PDF attachmentsof reports in MAIL
            Raivis
            Hi,

            Isn't there any kind of examples ?
            With loop cycles mby it could be done ?

            BR,
            Raivis
            • 3. Re: Multiple PDF attachmentsof reports in MAIL
              InoL
              If you need more help, you have to give more details, like:
              - What Forms version are you using?
              - What is the code you have so far?
              • 4. Re: Multiple PDF attachmentsof reports in MAIL
                Raivis
                Hi,

                Code sample i will give tomorow, because now i am at home...
                I am using Oracle Forms 6i and Oracle Report 6.

                BR,
                Raivis
                • 5. Re: Multiple PDF attachmentsof reports in MAIL
                  Raivis
                  Hi,

                  Here is code which generates report:
                   GENERATE(report1,  --1st report name
                                   report2,  --2nd report name
                                   '4', --MAIL
                                  'PDF',
                                  NULL,
                                  NULL,
                                 1);
                  PROCEDURE GENERATE(p_filename       IN VARCHAR2,
                                  p_filename3      IN VARCHAR2,
                                  p_destype        IN VARCHAR2,
                                  p_desformat      IN VARCHAR2,
                                  p_desname        IN VARCHAR2,
                                  p_desname3       IN VARCHAR2,
                                  p_execution_mode IN VARCHAR2) IS
                  
                  
                  
                      list_id     ParamList;
                      rep_id      Report_Object;
                      grp_id      RecordGroup;
                      dbs         VARCHAR2(80);
                      destype     VARCHAR2(20);
                      desformat   VARCHAR2(20);
                      desname     VARCHAR2(100);
                      background  VARCHAR2(20);
                      v_rep       VARCHAR2(100);
                      other_par   VARCHAR2(100);
                      report_mode VARCHAR2(100);
                      cmd         VARCHAR2(2048);
                      cmd2        VARCHAR2(2048);
                  
                      alert_id    Alert;
                      alert_but   NUMBER;
                    BEGIN
                     
                  
                       other_par := 'PARAMFORM=NO';
                  
                       IF p_destype = '4' THEN
                          Set_Report_Object_Property(rep_id,REPORT_DESTYPE,MAIL);
                          Set_Report_Object_Property(rep_id,REPORT_DESFORMAT,p_desformat);
                       END IF;
                  
                        
                        list_id := Get_Parameter_List('report_params');
                        IF Id_Null(list_id) THEN
                          list_id := Create_Parameter_List('report_params');
                        END IF;
                  
                        grp_id := Find_Group('report_params');
                        IF NOT Id_Null(grp_id) THEN
                          Group_To_Parameter(grp_id,list_id);
                        END IF;
                  
                  
                        Add_Parameter(-----);  --adding parametrs but that isn't necessary to show it i think
                       
                        
                       
                  
                      IF p_desname IS NOT NULL THEN
                             Set_Report_Object_Property(rep_id,REPORT_DESNAME,p_desname);
                                   ELSE
                             Set_Report_Object_Property(rep_id,REPORT_DESNAME,'');
                      END IF;
                  
                      
                        IF p_filename IS NOT NULL THEN
                          v_rep := Run_Report_Object(rep_id,list_id);
                        END IF;
                  
                         
                        IF p_filename3 IS NOT NULL THEN
                          Set_Report_Object_Property(rep_id,REPORT_FILENAME,p_filename3);
                          Set_Report_Object_Property(rep_id,REPORT_DESNAME,p_desname3);
                     
                          v_rep := Run_Report_Object(rep_id,list_id);
                        END IF;
                  
                            
                  
                        Destroy_Parameter_List(list_id);
                  
                    EXCEPTION
                      WHEN OTHERS THEN
                        RAISE;
                    END;
                  Edited by: Raivis on 2013.3.5 00:34

                  Edited by: Raivis on 2013.3.5 01:20
                  • 6. Re: Multiple PDF attachmentsof reports in MAIL
                    Raivis
                    Have someone idea ?
                    • 7. Re: Multiple PDF attachmentsof reports in MAIL
                      InoL
                      Do not use destype=MAIL, but FILE. You create two files and later put them in one email. Using APEX_MAIL is just an option. APEX_MAIL is a simple wrapper around more complicated UTL_SMTP:
                      http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php

                      Create the two output files in a directory that is accessible from the database. That way you can create an external table to get the files in a BLOB:
                      http://www.oracle-base.com/articles/10g/external-tables-containing-lob-data.php

                      If all this gets too complicated, the easiest option might be to merge the two reports into just one report.