I removed the out of the box procedure EBH_ON_ECP_REWORKED from the OnWorkTaskReworking event handler for a test. When I went to add the procedure back onto the handler, it did not appear in the list of procedures that could be added. It appears the parameter types are not matching up. The handler expects the ps_sender input to be an NVARCHAR2 value. However when running the EBH_ON_ECP_REWORKED procedure on its own in SQL Developer, I see that ps_sender is a VARCHAR2 value. Because these parameter types are not matching up, I cannot add EBH_ON_ECP_REWORKED back onto the OnWorkTaskReworking event handler. The same problem exists for the ETMH_ON_REWORKING_TASK procedure, which is also on the event handler.
Why are the parameter types different in the procedures than what is required in order to use them in the event handler? This is preventing us from completing UA testing.
Ron Seydler said the following:
You need to add them back in manually . i have no idea why they are different but have come across this also. Would be nice if they were marked as system procs so that an end user couldn't remove them using the GUI. /* REGISTER SP as an eB Event Handler */ BEGIN DECLARE @sp_name sysname = N'EBH_ON_ECP_REWORKED' ,@db_event_name nvarchar(255) = N'OnWorkTaskReworking' ,@li_event_id int ; SET @li_event_id = (SELECT event_id FROM dbo.db_events (NOLOCK) WHERE name = @db_event_name ); IF NOT EXISTS ( SELECT 1 FROM dbo.db_event_handlers (NOLOCK) WHERE event_id = @li_event_id AND code_ref = @sp_name ) EXEC dbo.ebp_add_db_event_handler @li_event_id, @sp_name, 0, N'N', 1 ; END; GO amend the two variables as required for anything else they removed.
Answer Verified By: Scott Musselwhite
This did not quite resolve the issue for me. It appears the script you sent me was written for MSSQL, however we have an Oracle database and this script will not work.
Ok let me see if I can get one for Oracle.
Hi Scott, do you have a Database Administrator on site. That person will be able to convert it to Oracle