Friday, December 16, 2011

Sending concurrent request output by e-mail

This post describes how to send a request output using e-mail.
First, create two directories. The first one should point to the output directory.
CREATE DIRECTORY xx_output AS '/u02/oracle/VISION/inst/apps/VISION/logs/appl/conc/out';
GRANT ALL ON DIRECTORY xx_output TO PUBLIC;
The second one should be any directory that the database user can access.
CREATE DIRECTORY xx_maildir AS '/home/oracle/tmp';
GRANT ALL ON DIRECTORY xx_maildir TO PUBLIC;
Then create a generic procedure that can send mail with attachments.
create or replace
PROCEDURE xx_send_mail_file(p_subject     IN VARCHAR2,
                            p_message     IN VARCHAR2,
                            p_recipient   IN VARCHAR2,
                            p_max_size    IN NUMBER   DEFAULT 9999999999,
                            p_filename1   IN VARCHAR2 DEFAULT NULL,
                            p_filename2   IN VARCHAR2 DEFAULT NULL,
                            p_filename3   IN VARCHAR2 DEFAULT NULL,
                            p_return_desc OUT VARCHAR2) IS

  -- to store the ip address of the smtp server
  l_smtp_server                 VARCHAR2(50);

  -- to store the smtp port of the smtp server
  l_smtp_server_port            NUMBER;

  -- to store the path / directory name of the file
  l_directory_name              VARCHAR2(200);

  -- to store the filename
  l_file_name                   VARCHAR2(100);

  -- to store the contents of the line read from the file
  l_line                        VARCHAR2(1000);
  crlf                          VARCHAR2(2):= CHR(13) || CHR(10);

  -- to store the p_message
  l_mesg                        VARCHAR2(32767);

  -- smtp connection variable
  conn                          UTL_SMTP.CONNECTION;

  -- to store the list of recipeints
  l_msg_to                      VARCHAR2(2000);

  -- to store the name of the sender
  l_sender_name                 VARCHAR2(200);
  v_numberOf                    NUMBER := 1;
  v_addr                        VARCHAR2(500);

  TYPE varchar2_table IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

  -- an array to store the file names
  file_array                    VARCHAR2_TABLE;

  -- array index
  i                             BINARY_INTEGER;

  -- file pointer
  l_file_handle                 UTL_FILE.FILE_TYPE;

  -- to store the position of \ in the file name
  l_slash_pos                   NUMBER;

  -- to store the lenght of the p_message
  l_mesg_len                    NUMBER;

  -- user defined exception
  abort_program                 EXCEPTION;

  -- boolean variable to trap if the p_message lenght is exceeding
  mesg_length_exceeded          BOOLEAN := FALSE;


  -- variable to store the error p_message. to be returned to the calling program
  l_return_desc1                VARCHAR2(2000);

  -- this procedure fetches the values for miscellaneous parameters
  PROCEDURE fetch_misc IS
  BEGIN
     l_return_desc1  := '11 - E: PARAMETER NOT MAINTAINED IN   MISC FOR AM_KEY1 = SMTP SERVER. ';
     l_smtp_server := 'localhost';

     l_return_desc1       := '22 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = SMTP PORT. ';
     l_smtp_server_port := '25';

     l_return_desc1   := '33 - E: PARAMETER NOT MAINTAINED IN MISC FOR '||
                         'AM_KEY1 = TICKET_EMAIL AND KEY2 =SENDER EMAIL. ';
     l_sender_name  := 'mailer@ebs.com';
  EXCEPTION
    WHEN others THEN
      RAISE ABORT_PROGRAM;
  END fetch_misc;

BEGIN
   -- fetching miscellaneous parameters
   FETCH_MISC;

   -- assigning file names to array
   file_array(1) := p_filename1;
   file_array(2) := p_filename2;
   file_array(3) := p_filename3;

   l_return_desc1  := '10 - E: THERE WAS AN ERROR IN OPENING CONNECTION. ';

   -- open connection on the server
   CONN:= UTL_SMTP.OPEN_CONNECTION( L_SMTP_SERVER, L_SMTP_SERVER_PORT );

   -- do the initial hand shake
   UTL_SMTP.HELO( CONN, L_SMTP_SERVER );

   UTL_SMTP.MAIL( CONN, L_SENDER_NAME );

   l_return_desc1  := '20 - E: THERE WAS AN ERROR IN CREATING RECIPIENTS.';

   IF (instr(p_recipient,';') = 0) THEN
     UTL_SMTP.RCPT(CONN, p_recipient);
   -- handles morde then one recipient, separated by ;
   ELSE
     WHILE (instr(p_recipient, ';', v_numberOf) > 0) LOOP

       v_addr := substr(p_recipient, v_numberOf, instr(substr(p_recipient, v_numberOf),';')-1);
       v_numberOf := v_numberOf + instr(substr(p_recipient, v_numberOf), ';');

       UTL_SMTP.RCPT(CONN, v_addr);

     END LOOP;
   END IF;

   UTL_SMTP.OPEN_DATA ( CONN );

   -- generate the mime header
   l_return_desc1  := '30 - E: THERE WAS AN ERROR IN GENERATING MIME HEADER. ';

   l_mesg:= 'Date: '||TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss')||crlf||
            'From: '||l_sender_name||crlf||
            'Subject: '||p_subject||crlf||
            'To: '||l_msg_to||crlf||
            'Mime-Version: 1.0'||crlf||
            'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"'||crlf||''||crlf||
            'This is a Mime message, which your current mail reader may not'|| crlf||
            'understand. Parts of the message will appear as text. If the remainder'|| crlf||
            'appears as random characters in the p_message body, instead of as'|| crlf||
            'attachments, then you''ll have to extract these parts and decode them'|| crlf||
            'manually.'|| crlf||''||crlf||
            '--DMW.Boundary.605592468'||crlf||
            'Content-Type: text/plain; name="p_message.txt"; charset=US-ASCII'||crlf||
            'Content-Disposition: inline; filename="p_message.txt"'||crlf||
            'Content-Transfer-Encoding: 7bit'||crlf||''||crlf||
            p_message||crlf||crlf||crlf;

   l_mesg_len := LENGTH(l_mesg);

   IF l_mesg_len > p_max_size THEN
      MESG_LENGTH_EXCEEDED := TRUE;
   END IF;

   l_return_desc1  := '40 - E: THERE WAS AN ERROR IN WRITING p_message TO CONNECTION. ';
   UTL_SMTP.WRITE_DATA ( CONN, l_mesg );

   -- start attaching the files
   FOR I IN  1..3 LOOP

       EXIT WHEN MESG_LENGTH_EXCEEDED;

       IF file_array(I) IS NOT NULL THEN
          BEGIN

             l_slash_pos := INSTR(file_array(I), '/', -1 );

             IF l_slash_pos = 0 THEN
                l_slash_pos := INSTR(file_array(I), '\', -1 );
             END IF;

             l_directory_name := 'XX_MAILDIR';

             l_file_name      := SUBSTR(file_array(I), l_slash_pos + 1 );

             l_return_desc1   := '50 - E: THERE WAS AN ERROR IN OPENING FILE. ';

             l_file_handle    := UTL_FILE.FOPEN(l_directory_name, l_file_name, 'R' );

             l_mesg           := crlf || '--DMW.Boundary.605592468'||crlf||
                                'Content-Type: application/octet-stream; name="'||
                                 l_file_name||'"'||crlf||
                                'Content-Disposition: attachment; filename="' ||
                                 l_file_name||'"'||crlf||
                                'Content-Transfer-Encoding: 7bit' || crlf || crlf ;

             l_mesg_len       := l_mesg_len + LENGTH(l_mesg);

             UTL_SMTP.WRITE_DATA ( CONN, l_mesg );

             LOOP
                 l_return_desc1  := '60 - E: THERE WAS AN ERROR IN READING FILE. ';
                 UTL_FILE.GET_LINE(l_file_handle, l_line);

                 IF l_mesg_len + LENGTH(l_line) > p_max_size THEN

                    l_mesg := '*** truncated ***' || crlf;
                    UTL_SMTP.WRITE_DATA ( CONN, l_mesg );
                    MESG_LENGTH_EXCEEDED := TRUE;
                    EXIT;

                 END IF;

                 l_mesg := l_line || CRLF;
                 UTL_SMTP.WRITE_DATA ( CONN, l_mesg );
                 l_mesg_len := l_mesg_len + LENGTH(l_mesg);
             END LOOP;
          EXCEPTION
             WHEN no_data_found THEN
               NULL;
             WHEN utl_file.invalid_path THEN
               RAISE ABORT_PROGRAM;
             WHEN others THEN
               RAISE ABORT_PROGRAM;
          END;

          l_mesg := crlf;

          UTL_SMTP.WRITE_DATA ( CONN, l_mesg );
          UTL_FILE.FCLOSE(l_file_handle);
        END IF;
   END LOOP;

   l_return_desc1  := '70 - E: THERE WAS AN ERROR IN CLOSING MIME BOUNDARY. ';
   l_mesg := crlf || '--DMW.Boundary.605592468--' || crlf;

   UTL_SMTP.WRITE_DATA ( CONN, l_mesg );
   UTL_SMTP.CLOSE_DATA( CONN );
   UTL_SMTP.QUIT( CONN );
EXCEPTION
  WHEN abort_program THEN
    p_return_desc := l_return_desc1;
  WHEN others THEN
    p_return_desc := l_return_desc1;
END xx_send_mail_file;
In the above code you need to change mailserver, port and sender so it suites your needs.

Next, create a procedure that will submit a request and send the output.
create or replace
PROCEDURE xx_send_mail_outp (errbuf        OUT VARCHAR2,
                             retcode       OUT VARCHAR2) IS

  v_response            VARCHAR2(1000);
  v_request_id          NUMBER;
  no_req                EXCEPTION;

  v_wait_status         BOOLEAN;

  v_request_phase       VARCHAR2(1000);
  v_request_status      VARCHAR2(1000);
  v_dev_request_phase   VARCHAR2(1000);
  v_dev_request_status  VARCHAR2(1000);
  v_request_status_mesg VARCHAR2(1000);
BEGIN
  v_request_id := fnd_request.submit_request('FND',       -- Application
                                             'FNDSCARU',  -- Request (Active Responsibilities and Users)
                                             '','',FALSE,
                                             '','','','','','','','',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '', '', '', '', '', '', '', '', '',
                                             '', '');

  -- Verify that the concurrent request was launched
  -- successfully.
  IF (v_request_id = 0) THEN
    raise no_req;
  ELSE
    fnd_file.put_line(fnd_file.log, 'Submitted request: '||v_request_id);
  END IF;

  COMMIT;

  -- wait for request to finish
  v_wait_status := fnd_concurrent.wait_for_request(v_request_id,
                                                   2,  -- interval in sec
                                                   60, -- total wait time in sec
                                                   v_request_phase,
                                                   v_request_status,
                                                   v_dev_request_phase,
                                                   v_dev_request_status,
                                                   v_request_status_mesg);


  -- copy file from output dir to tmp dir
  -- also renaming the file in the same operation
  utl_file.fcopy('XX_OUTPUT', 'o'||v_request_id||'.out', 'XX_MAILDIR', 'Active_Resp_'||
                              TO_CHAR(SYSDATE,'YYMMDD')||'.pdf');

  -- send the mail
  xx_send_mail_file('Sending the output of request Active Responsibilities and Users',
                    'Result is attached.'||chr(10)||'Sysadmin',
                    'yourname@yourdomain.com',
                    9999999999,
                    'Active_Resp_'||TO_CHAR(SYSDATE,'YYMMDD')||'.pdf',
                    NULL,
                    NULL,
                    v_response);

  fnd_file.put_line(fnd_file.log, 'Mail response: '||nvl(v_response,'SUCCESS'));

EXCEPTION
  WHEN no_req THEN
    fnd_file.put_line(fnd_file.log, 'ERROR: Could not submit request');
    retcode := '2';
  WHEN others THEN
    fnd_file.put_line(fnd_file.log, 'ERROR: '||SQLERRM);
    retcode := '2';
    RAISE;
END xx_send_mail_outp;
In the example above I am submitting the request Active Responsibilities and Users. The recipient is hardcoded but can easily be extended to be more dynamic, the code also handles more then one recipient (separate the addresses with ;). The procedure xx_send_mail_outp is created to be a request it self and if you would like to test this you need to do that setup.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. ORACLE error 29283 in FDPSTP

    Cause: FDPSTP failed due to ORA-29283: invalid file operation
    ORA-06512: at "APPS.XX_SEND_MAIL_OUTP", line 78
    ORA-06512: at line 1
    .

    The SQL statement being executed at the time of the error was: SELECT R.Conc_Login_Id, R.Re
    +---------------------------------------------------------------------------+
    Start of log messages from FND_FILE
    +---------------------------------------------------------------------------+
    Submitted request: 1597303
    ERROR: ORA-29283: invalid file operation
    +---------------------------------------------------------------------------+
    End of log messages from FND_FILE
    \\
    could you help

    ReplyDelete

javascript:void(0)