Oracle Workflows

The purpose of this article is two fold.
For the beginners of Oracle Workflow, it shows a quick demo of how you can create Business Events and Test their pl/sql subscription.
Also, a quick video to show you how you can debug the Business Event processing.

Broadly speaking, following steps will be executed
1. Create a Business event
2. Subscribe a PL/SQL Function to this Business Event
3. Test this Business Event
4. Debug the Business event processing
[The debug API used is available from 11.5.10CU4 onwards]



Step 1.
create a Business event named xx.temp.oracle.try


Step 2.
Create a table to prove that subscription was executed.
The subscription of event in Step 1 will insert record into this temporary table.
Just for the purpose of demo, I am creating the tables in apps schema itself.
create table xx_temp_for_demo( msg varchar2(4000), creation_date DATE default sysdate ) ;


Step 3
Create a pl/sql function that will be called when subscription is executed
create or replace FUNCTION xx_temp_demo_subscribe
(
p_subscription_guid In RAW,
p_event IN OUT NOCOPY WF_EVENT_T
)
RETURN VARCHAR2 IS
BEGIN
insert into xx_temp_for_demo
(msg)
values
('Event ' || p_event.getEventName() || ' received for Key=>' ||
p_event.geteventkey());
Return 'SUCCESS';
END xx_temp_demo_subscribe;



Step 4
Confirm that subscription was indeed executed
select * from xx_temp_for_demo ;

For video demo of Step 1-4, please click this link

Now for debugging, you can either use the API below
or alternately, you can query applsys.aq$wf_deferred


Step 5
Create a table that will store debug information in HTML Format into a clob column.
You may optionally store this straight into html file using wf_diagnostics api
create table xxtemp_clob ( event_name VARCHAR2(1000) , event_key varchar2(100) , event_msg clob ) ;


Step 6
Create a procedure that captures the debug HTML Content
You may alternately save the html output into a file
DECLARE
l_content CLOB;
l_event VARCHAR2(240) := '&event';
l_evt_key VARCHAR2(240) := '&key';
BEGIN
wf_diagnostics.get_bes_debug(l_event
,l_evt_key
,l_content);
INSERT INTO xxtemp_clob
(event_name
,event_key
,event_msg)
VALUES
(l_event
,l_evt_key
,l_content);
COMMIT;
END;
/



Step 7
Now view the HTML text that shows the business event debug information
select * from xxtemp_clob


See the video for above two Step5-7 from this link


In case, you wish to run SQL itself, to see the deferred queue, then run SQL Below
SELECT a.msg_state, a.user_data.event_name, a.user_data.send_date, COUNT(*)
FROM applsys.aq$wf_deferred a
WHERE a.user_data.event_name LIKE 'xx%'
AND a.user_data.send_date > SYSDATE - 1
GROUP BY a.msg_state, a.user_data.event_name, a.user_data.send_date
ORDER BY 1, 2 ;

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel