Database Software

I have a table with an XMLType column. Into that table is inserted an XML document. I have a set of mappings that then shred the XML document into relational data in various tables. This is all governed by a parent process flow that orchestrates the whole lot. So I can insert a row into my table and manually kick off this parent process flow and it all works very nicely.

Now the next step is to automate this so that whenever a row is inserted into my table the process flow is kicked into action automatically. Aha, methinks. An 'after insert trigger' on the table should do the job. So I create my trigger:

CREATE OR REPLACE TRIGGER edv.tr1 AFTER INSERT ON edv.documents
DECLARE
r INTEGER;
cc_owner VARCHAR2(10) := 'OWB_OWNER';
BEGIN
EXECUTE immediate 'begin :r := '||cc_owner||'.wb_rt_api_exec.run_task(''EDV_OWF_LOC'', ''PROCESSFLOW'', ''AD_PKG/SHRED_XML'','''', '''',0,1); end;' USING OUT r;
END;

Now, this doesn't work because to execute the API I need to have the roles OWB_O_OWB_OWNER and OWB_D_OWB_OWNER set. Solution: create a function that sets the roles and then calls the API. The trigger then calls the function (which is called f_execute task and which resides in a package called process_manager) instead of calling WB_RT_API_EXEC.RUN_TASK directly:

CREATE OR REPLACE TRIGGER edv.tr1 AFTER INSERT ON edv.documents
DECLARE
r INTEGER;
cc_owner VARCHAR2(10) := 'OWB_OWNER';
BEGIN
r := process_manager.f_execute_task (cc_owner, 'EDV_OWF_LOC', 'PROCESSFLOW', 'AD_PKG/SHRED_XML', '', '');
END;

The function f_execute_task contains the line

EXECUTE immediate 'set role OWB_D_'||cc_owner||', OWB_O_' || cc_owner;

and it works fine if I call it from an anonymous block in SQL*Plus. But running it from the trigger causes a problem. I get ORA-06565: cannot execute SET ROLE from within stored procedure.

This whole mechanism needs to be asynchronous, so that a document can be inserted into the table and the originating process is not left hanging until the process is complete (the shredding could take some time). Has anyone got any suggestions or alternative ways of automating such a process?