11-2. E-mailing Output from a Scheduled Job
Problem
You have a scheduled job that runs a stored procedure at a regular interval. The procedure produces output that ordinarily would be sent to the screen via the DBMS_OUTPUT.PUT_LINE procedure, but since it runs as a nightly batch job, you want to send the output to a distribution list as an e-mail message.
Solution
Save the output in a CLOB variable and then send it to the target distribution list using the UTL_MAIL.SEND procedure. For example, suppose you want to audit the employee table periodically to find all employees who have not been assigned to a department within the company. Here's a procedure to do that:
CREATE OR REPLACE PROCEDURE employee_audit AS
CURSOR driver IS -- find all employees not in a department
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id is null
ORDER BY last_name, first_name;
buffer CLOB := null; -- the e-mail message
BEGIN
FOR rec IN driver LOOP -- generate the e-mail message
buffer := buffer ||
rec.employee_id || ' ' ||
rec.last_name || ', ' ||
rec.first_name || chr(10);
END LOOP;
-- Send the e-mail
IF buffer is not null THEN -- there are employees without a department
buffer := 'Employees with no Department' || CHR(10) || CHR(10) || buffer;
UTL_MAIL.SEND (
SENDER=>'[email protected]',
RECIPIENTS=>'[email protected]',
SUBJECT=>'Employee Audit Results',
MESSAGE=>buffer);
END IF;
END;
How It Works
The procedure is very straightforward in that it finds all employees with no department. When run as a scheduled job, calls to DBMS_OUTPUT.PUT_LINE won't work because there is no “screen” to view the output. Instead, the output is collected in a CLOB variable to later use in the UTL_MAIL.SEND procedure. The key to remember in this recipe is there is no screen output from a stored procedure while running as a scheduled job. You must store the intended output and either write it to an operating system file or, as in this example, send it to users in an e-mail.