Skip to main content

Dear community. 

I’d like to create a custom event that will be triggered if someone attempts to perform an action that is not allowed by the PLSQL rules. I will use Error_SYS.Record_General to display an error message and perform a hard stop. 

I would like to be able to also trigger an email at this point to a user to let them know that someone tried to perform the action. 

The problem i am finding is that the Error_SYS.Record_General stops the commit transaction and also the email that is generate by COMMAND_SYS.Mail. 

I was thinking of using exception handling etc to get this to work but so far have been unable to. Same goes for creating a second event action, since the Error_sys stops the transaction this didnt appear to work for me either. 

Anyone have any bright ideas?

Many thanks,

David.

 

Well, you can send the email via background task. try using Transaction_SYS.Deferred_Call(); this would help you handy in this kinda situation.

Cheers!

Thanks @EntShehaM. I assume the deferred call will create the background job regardless if the primary transaction was terminated with Error_sys.General() then?

Yes, send the email using deferred call so the job will handle that part in background and the error message will appears. you can use Command_SYS.Mail() method to trigger the mail sending.

Hi @EntShehaM 

I tried this but it didnt appear to kick off the deferred call. Here is the code that is relevant. I get the Error message displayed, but nothing in the background jobs. Maybe I am missing something. 

 

IF not operational_status_ = 'In Operation'  THEN

   Client_SYS.Clear_Attr(arguments_);

   Client_SYS.Add_To_Attr('FROM_USER_NAME', 'IFSAPP' , arguments_);

   Client_SYS.Add_To_Attr('TO_USER_NAME', 'david.harmer@company.com' ,arguments_);

   Client_SYS.Add_To_Attr('TEXT', 'TOOL IS OUT OF OPERATION!!' , arguments_);

   Client_SYS.Add_To_Attr('SUBJECT', 'XX TRIED TO BOOK OUT TOOL X WHICH IS OUT OF CAL BY X' , arguments_);

   

  -- Call the procedure

  transaction_sys.deferred_call(procedure_name_ => 'command_sys.Mail',

                                argument_type_db_ => 'PARAMETER',

                                arguments_ => arguments_,

                                description_ => 'Email QA - Attempt to use Out of Calibration tool (from event)');

   Error_SYS.Record_General('ManufToolDetailUsage', 'Event Cal Tool Usage: Tool ' || mch_code_ || ' is shown to be out of calibration, contact your Supervisor.');

END IF;

Try modify the code I attached herewith, two codes are for two different requirements. from one you can get the email sending method and the other one how to use the background job. you can combine the method and write your own for the scenario. 

also try setup 2 event actions too. one for trigger an email and one for error message this may also work.

hope this would help...

Hi @EntShehaM {Hi bro! :blush: }

Deferred call is the right way to go for for this but since there’s an exception (Error_sys) call at the end, it will rollback the whole thing.

If you use a sub program with autonomous transaction, then the rollback will not affect the posting of background job.

 

@david.harmer , below is the modified code with what I mentioned above, and I noticed the parameters to call command_sys.mail were wrong (missing underscores) and fixed it too :sunglasses: .Pls try whether it works for you.

DECLARE

PROCEDURE send_email_deferred(arguments_ IN VARCHAR2) IS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	transaction_sys.deferred_call(procedure_name_ => 'command_sys.Mail',
                                argument_type_db_ => 'PARAMETER',
                                arguments_ => arguments_,
                                description_ => 'Email QA - Attempt to use Out of Calibration tool (from event)');
	commit;							
END send_email_deferred;
BEGIN

	IF not operational_status_ = 'In Operation'  THEN
	   Client_SYS.Clear_Attr(arguments_);
	   Client_SYS.Add_To_Attr('FROM_USER_NAME_', 'IFSAPP' , arguments_);
	   Client_SYS.Add_To_Attr('TO_USER_NAME_', 'david.harmer@company.com' ,arguments_);
	   Client_SYS.Add_To_Attr('TEXT_', 'TOOL IS OUT OF OPERATION!!' , arguments_);
	   Client_SYS.Add_To_Attr('SUBJECT_', 'XX TRIED TO BOOK OUT TOOL X WHICH IS OUT OF CAL BY X' , arguments_);   

	   -- Call the procedure
	   send_email_deferred(arguments_);

	   Error_SYS.Record_General('ManufToolDetailUsage', 'CEVENTERROR: Event Cal Tool Usage: Tool ' || mch_code_ || ' is shown to be out of calibration, contact your Supervisor.');

	END IF;
END;

 

Cheers!

Damith

Terms & ConditionsCookie settings