0 Replies Latest reply: Jan 14, 2013 12:51 PM by Stuart Fleming RSS

    REPORT_DESTYPE,Mail  send email with a message (body)

    Stuart Fleming
      Hello,
      This code sends an email with an attachment. The attachment is a pdf with just a text field that shows the parameter P_MSG.

      What is missing is a way to specify the body of the email. I looked in the documentation http://docs.oracle.com/cd/E21764_01/bi.1111/b32121/pbr_cla005.htm#autoId30

      for how to specify the body of the email message, but there does not seem to be any.

      Would anyone have any ideas? I have tried UTL_MAIL: Send_Attach_Raw without success.....


      DECLARE
      report_message VARCHAR2(100);
      rep_status VARCHAR2(100);

      PL_ID                     PARAMLIST;
      REPORT_ID               REPORT_OBJECT;
      VREPORT_NAME     VARCHAR2(55) ;
      STRSQL                    VARCHAR2(2500) ;
      VSIGN                         VARCHAR2(60) ;
      --------------
      VERR_LOC                         NUMBER                    := 9000 ;
      VERR                                   VARCHAR2(88)     := 'RUN_REPORT: ' ;
      --------------
      BEGIN

      ------------------------------------------
      PL_ID := GET_PARAMETER_LIST('TMPDATA');
      IF NOT ID_NULL(PL_ID) THEN
                     DESTROY_PARAMETER_LIST('TMPDATA');
      END IF;
      PL_ID := CREATE_PARAMETER_LIST('TMPDATA');
      ------------------------------------------
      STRSQL :='this is a test of sending an email with an attachment!!'||chr(10)||'http://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_cla002.htm' ;
      ------------------------------------------
      ADD_PARAMETER( PL_ID, 'P_MSG'          , TEXT_PARAMETER     , STRSQL );
      ------------------------------------------


      REPORT_ID := FIND_REPORT_OBJECT     ( 'REPORT' );
      SET_REPORT_OBJECT_PROPERTY ( REPORT_ID , REPORT_FILENAME , 'REPORT_MSG.REP' ) ;

      IF INSTR(      GET_FORM_PROPERTY ( :SYSTEM.CURRENT_FORM , FILE_NAME ) , 'U:\' ) > 0 THEN
                al.ok( 'REPORT CANNOT BE RUN FROM CLIENT SERVER' ) ;
                RAISE FORM_TRIGGER_FAILURE;
      END IF ;


      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,Mail);
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESNAME,'"stuart.fleming@njdge.org" from="me2@njdge.org" subject="got sending an email with attachment to work-- but cant add a message!" body="body" ');
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,'PDF');
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,'DGEREPSERVER');

      report_message:=run_report_object(report_id, PL_ID);


      EXCEPTION
      WHEN FORM_TRIGGER_FAILURE then
      SET_APPLICATION_PROPERTY (cursor_style,'DEFAULT'); -- CURSOR: NORMAL
      RAISE ;     
      WHEN OTHERS THEN
      SET_APPLICATION_PROPERTY (cursor_style,'DEFAULT'); -- CURSOR: NORMAL
      MESSAGE     (sqlerrm ||' - ('||VERR||VERR_LOC||')') ;
      RAISE FORM_TRIGGER_FAILURE;
      END ;