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.
