I have a simple form with some items and I have a procedure on sending notification when the form is submitted.
How should I proceed if I want to receive another e-mail notification every time this request is updated and some of form's items are modified?
I want to send a notification only for those items that have been updated by the user, not the entire form...
Its a cool idea... however I am extremely new in Oracel apex. Is this the Oracle apex form? If yes than can you please share the code? If you are able to send the code with some explanations (some screenshots) in my email id than it will be very useful to me.. my email id is email@example.com.
Thanks alot for your help. It will save me.
On my page I using a Shuttle to allow the users to select one or more recipients. As you know when using a Select List with multiple selections allowed the return values are colon delimited.
Within my Insert and Update triggers on my tables I declared variables to hold the values selected from the Shuttle.
DECLARE new_email_recipients HTMLDB_APPLICATION_GLOBAL.VC_ARR2; v_message VARCHAR2(4000) := NULL; v_subject VARCHAR2(500) := NULL;
Then in the execution part I'm populating my variable from the page.
BEGIN new_email_recipients := HTMLDB_UTIL.STRING_TO_TABLE(:new.email_notification); v_subject := '<Your Subject>';
Within each field I'm comparing the NEW with the OLD to see if there was a change and if so I'm creating my message.
At the end I'm calling a package that has "SendMail" procedure.
IF (:new.<column_name> != :old.<column_name>) THEN --First message IF (v_message IS NULL) THEN v_message := '<your message (ie) Some field was Modified:'||CHR(10)||CHR(13); END IF; --There's an existing message so just continue building the message v_message := v_message || '<your message>' ; END IF; IF (v_message IS NOT NULL) THEN --means that I have a message to email FOR i in 1..new_email_recipients.count LOOP SendMail(new_email_recipients(i), v_subject, v_message); END LOOP; END IF;
The "SendMail" procedure is:
procedure SendMail(p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2) IS l_mail_conn utl_smtp.connection; v_sender varchar2(20) := c_default_mail; BEGIN l_mail_conn := utl_smtp.open_connection(c_mailhost, 25); utl_smtp.helo(l_mail_conn, c_mailhost); utl_smtp.mail(l_mail_conn, v_sender); utl_smtp.rcpt(l_mail_conn, p_recipient); utl_smtp.open_data(l_mail_conn); utl_smtp.write_data(l_mail_conn, 'From: ' || nvl(v_sender, 'EDW') || chr(13) || chr(10)); utl_smtp.write_data(l_mail_conn, 'To: ' || nvl(p_recipient, c_default_mail) || chr(13) || chr(10)); utl_smtp.write_data(l_mail_conn, 'Subject: ' || nvl(p_subject, '(no subject)') || chr(13) || chr(10) || chr(13) || chr(10)); utl_smtp.write_data(l_mail_conn, p_message); --dbms_output.put_line('message is ' || p_message); utl_smtp.close_data(l_mail_conn); utl_smtp.quit(l_mail_conn); EXCEPTION WHEN OTHERS THEN -- catches all errors RAISE_APPLICATION_ERROR(-20001, 'SENDMAIL: ' || SQLERRM); END SendMail;
Hope that helps get you on your way.
The :NEW and :OLD values are an attribute of the trigger and do not need to be declared. When the trigger fires those values are passed to the trigger automatically and can be referenced.
I'm not using tables. The condition, IF (:new.<column_name> != :old.<column_name>), is used to determine if there was a change to the that specific column. If the data was changed on that column I'm creating my message to send in the email. Keep in mind that the :NEW and :OLD values of the column are passed into the trigger automatically.
Hope that's clearer.