Sending mail of user_jobs query results 2

[cjmartin]

I am trying to create a procedure that accomplishes the following: 1) Queries USER_JOBS for any broken or failing jobs and 2) emails the query output.

I have both the logic for the two separates parts of this, but am having trouble incorporating them.

1)USER_JOBS query:

SELECT job
,failures
,broken
,what job_definition
FROM
user_jobs
WHERE
broken = 'Y' OR NVL(failures,0) > 0
ORDER BY job ;

2) Email script:

DECLARE
v_connection UTL_SMTP.CONNECTION;
BEGIN
v_connection := UTL_SMTP.OPEN_CONNECTION(mailhost,25);
UTL_SMTP.HELO(v_connection,mailhost);
UTL_SMTP.MAIL(v_connection,'sender@domain');
UTL_SMTP.RCPT(v_connection,'recipient@domain');
UTL_SMTP.DATA(v_connection,'This is a test...sent from Oracle');
UTL_SMTP.QUIT(v_connection);
END;


I can run both of these successfully, however don't really know where to start in attempting to incorporate the two into a script/procedure that will email the results of the query.

Thanks for any suggestions you can offer!

 

[karluk]

I don't see what' wrong with having two separate stored procedures - one for monitoring user_jobs and the other for sending e-mail. You would write the e-mail procedure to pass input parameters for things like recipient address and message text. The job monitoring procedure would call the e-mail procedure if it found any broken or failed jobs.

That way your e-mail procedure could be used in lots of different ways. You could write other procedures to monitor for things like tables reaching max extents or tablespaces becoming full. Each procedure would call the e-mail procedure, passing an appropriate message in case it found a problem.

 

[cjmartin]

karluk,

Excellent suggestion. I customized the maildemo8i package (found on OTN) to my environment.

I'm still stuck on passing the results of query (ie select *
from user_jobs where failures = 'Y') to the message or attachment part of a procedure written against the maildemo8i.sql package. I'll follow up with a more specific question...

 

[carp]

As for the results, I would put your query into a cursor, loop through the cursor, and write the contents of each row to a string variable. When you finish processing the cursor, just send your string variable as the body of the email:
.
.
.
CURSOR my_cursor IS
SELECT job
,failures
,broken
,what job_definition
FROM
user_jobs
WHERE
broken = 'Y' OR NVL(failures,0) > 0
ORDER BY job ;
lv_msg VARCHAR2(32000) := NULL;
.
.
.
.
BEGIN
FOR i IN my_cursor LOOP
lv_msg := lv_msg||i.job .........||chr(10);
END LOOP;

v_connection := UTL_SMTP.OPEN_CONNECTION(mailhost,25);
UTL_SMTP.HELO(v_connection,mailhost);
UTL_SMTP.MAIL(v_connection,'sender@domain');
UTL_SMTP.RCPT(v_connection,'recipient@domain');
UTL_SMTP.DATA(v_connection,lv_msg);
UTL_SMTP.QUIT(v_connection);
END;

 

[cjmartin]

carp,

I attempted to use your suggestion, had trouble with it, did some more research, and am now trying to incorporate ideas from a combination of resources (including yourself).

Here my attempt...

DECLARE

CURSOR c1 IS
SELECT job, failures, broken, what
FROM user_jobs
WHERE broken = 'Y' OR NVL(failures,0) > 0
ORDER BY job;

v_connection utl_smtp.connection;

CREATE OR REPLACE PROCEDURE JobsBroken (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2, p_line IN VARCHAR2) IS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection, p_line || UTL_TCP.CRLF);
END JobsBroken;

BEGIN

v_connection := UTL_SMTP.OPEN_CONNECTION(mailhost,25);
UTL_SMTP.HELO(v_connection,mailhost);
UTL_SMTP.MAIL(v_connection,p_sender);
UTL_SMTP.RCPT(v_connection,p_recipient);

UTL_SMTP.OPEN_DATA(v_connection);

JobsBroken('Here is a list of broken/failed jobs');

FOR v_job_rec IN c1 LOOP
JobsBroken(v_job_rec.job || ' ' ||
v_job_rec.failures || ' ' ||
v_job_rec.broken || ' ' ||
v_job_rec.what);
END LOOP;

UTL_SMTP.CLOSE_DATA(v_connection);

UTL_SMTP.QUIT(v_connection);

END;
/


However, I'm receving an error (after the Procedure logic) at the BEGIN preceding v_connection := UTL_SMTP.OPEN_C...(line 19). The error message is:

PLS-00103: Encountered the symbol "BEGIN"

Any thoughts?

 

[sem]

To declare LOCAL procedure within pl/sql block you need not use CREATE OR REPLACE:
....
v_connection utl_smtp.connection;

procedure JobsBroken (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2, p_line IN VARCHAR2) IS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection, p_line || UTL_TCP.CRLF);
END JobsBroken;

BEGIN
...

Though, if you plan to call JobsBroken from somewhere else, you should create it by another statement:

CREATE OR REPLACE PROCEDURE JobsBroken (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2, p_line IN VARCHAR2) IS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection, p_line || UTL_TCP.CRLF);
END JobsBroken;
/
DECLARE

CURSOR c1 IS
SELECT job, failures, broken, what
FROM user_jobs
WHERE broken = 'Y' OR NVL(failures,0) > 0
ORDER BY job;

v_connection utl_smtp.connection;

BEGIN
....

 

[carp]

"I attempted to use your suggestion, had trouble with it"
Could you expand on this? What, exactly, was the problem - does it throw an error, the email look wrong, or what?