Is there a way to generate an automated email if no changes have been made to a column for a specific period of time?

I’m trying to generate an automated email if no change occurs to a specific column on my database.

I have a “Description” column on my database with different entries but the one that i want to track is “QUOTE SENT”. I want to generate an automated email if no change occurs for a period of 14 days.

The code provided lists all the “work packages” that have a “description” of “QUOTE SENT” and lists them accordingly. but I’m confused with the email generation part.

select wop.SI_NUMBER, wop.wos_auto_key, wos.DESCRIPTION, wop.LAST_STATUS_CHG  
from wo_operation wop
   left join wo_status wos on wos.wos_auto_key = wop.wos_auto_key
where wos.description = 'QUOTE SENT'
and  trunc(LAST_STATUS_CHG) = trunc(sysdate)-14

Answer

Ok, this is oracle, maybe you are looking for the UTL_SMTP.connection commands to send emails.

You will need to create a SP with that query that you have there, and you need to schedule it to run every (x days/hours/etc), whatever you want.

Then, you need to create another SP with the email code, like the following I took from the dba-oracle site.

    sendmail.sql

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2, 
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer; 
msg_from varchar2(50) := 'Oracle9.2'; 
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host 

BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25 
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject || 
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt 
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again'); 
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.'); 
END;
/

You need to know details, like the email server, email server credentials, etc. You can always check from the server itself (assuming you have linux) test the email configuration and send an email using mailx, like echo "Test" | mailx -s yourmail.com

So, in short, you need 2 SPs, 1 for your query, 1 for the email (to keep it organized), and 1 scheduled job.

For testing you can just run manually without the scheduled job.

Good Luck!