Scribd
Upload
29 views

Email The Output of A Concurrent Program Using UTL - MAIL

The document describes a procedure to email the output of a concurrent program using UTL_MAIL. It submits the concurrent program, waits for it to complete, retrieves the output file contents, and emails it as an attachment using UTL_MAIL.send_attach_varchar2.

Uploaded by

ARYAN RAJ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
29 views

Email The Output of A Concurrent Program Using UTL - MAIL

The document describes a procedure to email the output of a concurrent program using UTL_MAIL. It submits the concurrent program, waits for it to complete, retrieves the output file contents, and emails it as an attachment using UTL_MAIL.send_attach_varchar2.

Uploaded by

ARYAN RAJ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Email the output of a concurrent program using UTL_MAIL

1. Write a procedure that will submit the concurrent program whose output has to be sent as an Email and once the
program completes, send the output as Email using UTL_MAIL.send_attach_varchar2.
2. Register this procedure as a concurrent program so that this program can be run from Oracle Applications which
will email a concurrent program’s output.

Detailed explanation with sample code:


1. Write the below procedure which submits the desired concurrent program and waits until it completes and then
sends the output of that program to the specified Email address using the utilityUTL_MAIL.send_attach_varchar2

CREATE OR REPLACE PROCEDURE apps.XXAA_send_email (


errbuf VARCHAR2,
retode NUMBER,
p_concurrent_program_name VARCHAR2,
p_parameter1 NUMBER)
IS
/*Variable declaration*/
fhandle UTL_FILE.file_type;
vtextout VARCHAR2 (32000);
text VARCHAR2 (32000);
v_request_id NUMBER := NULL;
v_request_status BOOLEAN;
v_phase VARCHAR2 (2000);
v_wait_status VARCHAR2 (2000);
v_dev_phase VARCHAR2 (2000);
v_dev_status VARCHAR2 (2000);
v_message VARCHAR2 (2000);
v_application_id NUMBER;
v_concurrent_program_id NUMBER;
v_conc_prog_short_name VARCHAR2 (100);
v_conc_prog_appl_short_name VARCHAR2 (100);
v_output_file_path VARCHAR2 (200);
BEGIN
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output,
'Conc Prog: ' || p_concurrent_program_name);
fnd_file.put_line (fnd_file.output, 'Parameter 1:' || p_parameter1);

/* Get Concurrent_program_id of the desired program and application_id */


BEGIN
SELECT concurrent_program_id, application_id
INTO v_concurrent_program_id, v_application_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name = p_concurrent_program_name;

fnd_file.put_line (fnd_file.LOG,
'Conc Prog ID:' || v_concurrent_program_id);
fnd_file.put_line (fnd_file.LOG,
'Application ID: ' || v_application_id);

/* Get the program's Short name */


SELECT concurrent_program_name
INTO v_conc_prog_short_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = v_concurrent_program_id;

fnd_file.put_line (fnd_file.LOG,
'Conc Prog Short Name: ' || v_conc_prog_short_name);

/* Get the Application Short name */


SELECT application_short_name
INTO v_conc_prog_appl_short_name
FROM fnd_application
WHERE application_id = v_application_id;

fnd_file.put_line (
fnd_file.LOG,
'Application Short Name:' || v_conc_prog_appl_short_name);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error: ' || SQLERRM);
END;

/* Calling fnd_request.submit_request to submit the desired the concurrent program*/


v_request_id :=
fnd_request.submit_request (v_conc_prog_appl_short_name,
v_conc_prog_short_name,
NULL, --Description
NULL, --Time to start the program
FALSE, -- sub program
p_parameter1);
fnd_file.put_line (fnd_file.LOG,
'Concurrent Request Submitted
Successfully: ' || v_request_id);
COMMIT;

IF v_request_id IS NOT NULL


THEN
/*Calling fnd_concurrent.wait_for_request to wait for the
program to complete */
v_request_status :=
fnd_concurrent.wait_for_request (request_id => v_request_id,
INTERVAL => 10,
max_wait => 0,
phase => v_phase,
status => v_wait_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
MESSAGE => v_message);
v_dev_phase := NULL;
v_dev_status := NULL;
END IF;

/* Getting the path where output file of the program is


created */
SELECT outfile_name
INTO v_output_file_path
FROM fnd_concurrent_requests
WHERE request_id = v_request_id;

/* Open the output file in Read mode */


fhandle :=
UTL_FILE.fopen (
'/opt/oracle/OACRP1/common/admin/out/OACRP1_dtuusebs14',
'o' || v_request_id || '.out',
'r');

IF UTL_FILE.is_open (fhandle)
THEN
DBMS_OUTPUT.put_line ('File read open');
ELSE
DBMS_OUTPUT.put_line ('File read not open');
END IF;

/* Get the contents of the file into variable “text”*/


LOOP
BEGIN
UTL_FILE.get_line (fhandle, vtextout);
text := text || vtextout || UTL_TCP.crlf;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;

UTL_FILE.fclose (fhandle);

/*Calling UTL_MAIL.send_attach_varchar2 to send the output as Email attachment */


UTL_MAIL.send_attach_varchar2 (
sender => 'XXXXXXX.XXXXXX@gmail.com',
recipients => 'xxxxxx.yyyyyy@gmail.com',
subject => 'Testmail',
MESSAGE => 'Hello',
attachment => text,
att_inline => FALSE);
END;

You might also like