Tag Archives: query

eBS Troubleshooting

Oracle eBS troubleshooting

 

On this webpage, I’ll make a FAQ on common eBS problems and troubleshooting. Probably the format will change over time. But I’ll just start. I use basic flow diagrams, to give an overview of the process. Under the diagrams is an explanation of all the steps.

Ø My concurrent manager does not start

                                                                                       

1)      Check if the Internal Manager is running. On Unix, you can run: ps –ef | grep ‘FNDLIBR FND CPMGR’. If this returns a process, check the start time for the process to make sure it is the correct process.

2)      If a process is returned, and it started at the time you started the managers, the ICM is running.

3)      The ICM is not running. First check the adcmctl.txt file. It is found in the process_log directory. (Check your autoconfig xml file or the adcmctl.sh script). It will show the logs for the start-up scripts of the ICM.

4)      The ICM is running, but can’t start the other managers. Most common cause is  the APPS Listener is down. Check for it running with: ps –ef  | grep ‘tnslsnr APPS_<SID>’

5)      If no process is returned, start the apps listener with adalnctl.sh. Wait a minute afterwards, for the ICM to retry starting the managers.

6)      The ICM is running, or was started successfully before it died. Check the logfile from the ICM in $APPLCSF/$APPLLOG. By default the file is named <SID>_<proc>.mgr, where proc is the process_id from adcmctl.txt.

 

Ø Notifications are not being sent by the notification mailer

                                                                                    

1)      Look for the notification in ‘wf_notifications’. Check the status, mail_status, recipient_role and notification_id.

2)      If Mail_status = ‘SENT’, the message has been mailed already. If status<>’OPEN’ (Note the difference between status and mail_status), the notification is not eligible to be mailed anymore.

3)      The Notification Mailer has already sent this message. Maybe it was sent to the TEST_ADDRESS  from the Notification Mailer.

4)      Check the mail_preference for the recipient_role from wf_roles.

5)      If the preference is ‘QUERY’, ‘SUMHTML’, ‘SUMMARY’ or ‘DISABLED’ then the recipient will not receive notifications by mail.

6)      Change the preference in the Users preferences. Or test with a different user.

7)  The Notification Mailer reads notifications to send from the queue WF_NOTIFICATION_OUT. Select from the queue-view:

select notification_id,msg_state,msg_id,role,corrid,enq_time,deq_time

from  (select msg_id, o.enq_time, o.deq_time, msg_state

              ,(select str_value

                from   table (o.user_data.header.properties)

                where  name = 'NOTIFICATION_ID') notification_id

              , (select str_value

                 from   table (o.user_data.header.properties)

                 where  name = 'ROLE') role

              , (select str_value

                 from   table (o.user_data.header.properties)

                 where  name = 'Q_CORRELATION_ID') corrid

       from   applsys.aq$wf_notification_out o)         

where notification_id=<notification_id>

and rownum=1;

8)      If you received a result on the query, the notification is in the queue.

9)      The status of the notification in the queue should be ‘READY’. If it is ‘PROCESSED’, or ‘ERROR’, the notification is already dequeued or errored. Otherwise check the status of the queue with:

select name,enqueue_enabled,dequeue_enabled

from dba_queues

where name='WF_NOTIFICATION_OUT';

10)   The status of the message should be ‘READY’. The ‘ENQUEUE_ENABLED’ and ‘DEQUEUE_ENABLED’ columns should be ‘YES’.

11)   Reprocess the message with the conc. Request. Or stop/start the queue with dbms_aqadm.

12)   Check the Notification Mailer logfile. The notification is ready for the mailer, but it is unable to pick it up. Check if the mailer shut down due to too many errors.

13)   The message is not in ‘WF_NOTIFICATION_OUT’. It can still be queued on the ‘WF_DEFERRED’ queue. Check this queue with:

select v.msg_id,v.msg_state,v.enq_time,v.deq_time,v.corr_id

from   applsys.aq$wf_deferred v

,      table(v.user_data.parameter_list) t

where v.corr_id like 'APPS:oracle.apps.wf.notification.%'

and name ='NOTIFICATION_ID'

and value = <Notification_id>;

14)   If the query returned results and the msg_state is ‘READY’, the notification is still being processed. Skip to 16

15)   Unfortunately, your notification seems to have gone missing. Consult Oracle Support. Please inform us of the solution in this case, so we can extend the FAQ.

16)   The notification is still in the deferred queue, waiting to be picked up by the ‘Workflow Deferred Notification Agent Listener’. Check the status of this listener. And if needed, check its logfile.

17)   If the Deferred Notification Agent Listener is not running, or erroring

18)   (re)start it.

19)   Check if the Business Event ‘oracle.apps.wf.notification.denormalize’ is enabled and has a subscription to procedure ‘Wf_Notification_Util.Denormalize_RF’.

20)   If either the Business event is disabled, or the subscription non-existent or disabled, go to 21.

21)   Make sure that the Business event is enabled and the subscription is valid and enabled. You will still have to reprocess the notification with the concurrent program to resubmit notifications.

22)   Unfortunately, we can’t locate the issue yet. If you manage to solve the issue please inform us, so we can update this FAQ.

 

Oracle Advanced Queues (AQ)

Oracle Advanced Queuing (AQ)

 

A customer asked me to do a presentation about AQ (Advanced Queuing). This article is a rewrite of that presentation. We’ll be getting some hands-on experience with AQ, and then some tips on the issues that might occur.

As always, your comments to this article are more than welcome. If you enjoy this article, find it useful or maybe not at all, please let me know by leaving a comment on the site.

The article is also available in PDF format from here.

 

Queuing: I don’t want to queue.

 

The title might be true in many cases, but there are also situations where a queue is very convenient. For example in the case of batch processing where a batch process handles multiple incoming messages from an online process. Or when 2 processes need inter-process communication, but still need to function independently of each other.

In eBS we use queues for the workflow system. (Deferred items, notifications for the workflow mailer and the Business Event System). Some more queues are found for concurrent processing and SFM.

 

So whether you like it or not, you’ll have to queue. The trick is to manage these queues to get optimal performance for your system.

 

(Advanced) Queue design

 

Before we can start building queues, there are some things to consider.

AQ supports both point-to-point queues and publish-subscribe-queues (also called multi-consumer queues).

 

Point-to-point queues are emptied by only one specific process. One or more processes can enqueue messages on the queue, but only one process can dequeue them.

 

In contrast, a publish-subscribe queue can have many processes reading the messages in the queue. Either the messages are broadcasted, or the receivers have to subscribe to a certain kind of messages.

Of course the publish-subscribe queue has some very interesting properties. But we’ll start our item with the point-to-point queue.

 

So you’ll first have to decide who the senders and receivers of the queue data will be. In this article, we start with using a point-to-point queue. After that we start using multi-consumer queues.

Another thing to consider is the payload of the message. Of course, the messages will need some content to give it a meaning to the receiver. This content is called the payload. And you can either use a custom type (including XML), or a raw type.

During this article, we’ll see some more features of AQ. But when we decide on the type of queue and the payload type, we can build our own queues. All queues are built on queue-tables. These tables hold the data in the queue. On top of these tables, the actual queue and some management views are built.

To build a queue-table, we use the dbms_aqadm package:

dbms_aqadm.create_queue_table(queue_table =>’<table_name>’

                          ,queue_payload_type => [‘RAW’|<custom_type>]);

 

This creates the queue table including a LOB segment for the payload, some indexes, and an ‘Error queue’:

 

Begin

dbms_aqadm.create_queue_table(queue_table=>’xxx_test’

                           ,queue_payload_type=>’RAW’);

End;

 

Select object_name,object_type from dba_objects where created>sysdate-1/24;

 

OBJECT_NAME                 OBJECT_TYPE

—————————–         ———–

SYS_C0011768                INDEX

XXX_TEST                    TABLE

SYS_LOB0000073754C00029$$   LOB

SYS_LOB0000073754C00028$$   LOB

AQ$_XXX_TEST_T              INDEX

AQ$_XXX_TEST_I              INDEX

AQ$_XXX_TEST_E              QUEUE

AQ$_XXX_TEST_F              VIEW

AQ$XXX_TEST                 VIEW

 

This created the base-table for a point-to-point queue. The table is a regular heap-oriented table. And you are free to create extra indexes on it, if you feel the urge. The necessary indexes have been created already.

 

The queue that is created now is the default error queue. Messages that failed dequeuing will be set on this queue.

 

Now it’s time to create the actual queue. The queue-tables are the infrastructure for storing the messages and related information. The queue can now be created to control the queuing and dequeuing of messages.

 

For both point-to-point as publish-subscriber queues, the command is:

 

dbms_aqadm.create_queue (queue_table =>’<table_name>’

                      queue_name => ‘<queue_name>’);

 

 

So for us we run:

Begin

dbms_aqadm.create_queue (queue_name => 'xxx_test_q'

  ,queue_table => 'xxx_mc_test');

End;

 

 

This creates an object of type QUEUE. This is the object that will control the contents of the underlying tables / IOT’s.

 

Before we can start using our queues, we also have to ‘start’ them. On starting, we indicate whether the queue is available for queuing, dequeuing or both:

 

Begin

 dbms_aqadm.start_queue(queue_name=>’xxx_test_q’

 ,enqueue=>TRUE

 ,dequeue=>TRUE);

End;

 

Our queue is now enabled for both queueing and dequeuing. Let’s first verify if things are working correctly.

 

To enqueue (or dequeue) a message, we use the dbms_aq package. It has an enqueue and dequeue procedure. Both with their own parameters. The parameters include en-/dequeue options, message properties, a message_id and of course the message itself:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.Enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_payload:=Utl_raw.Cast_to_raw('Hello world!');

Dbms_aq.Enqueue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

,Enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(rawtohex(V_msgid));

end;

 

This enqueues a ‘Hello world!’ message, and returns the message id. If everything works correctly, you’ll see the msgid as a hexadecimal string. (Don’t forget to set serveroutput on).

 

We created 2 extra parameters: v_enq_options with the options used to enqueue this message. And v_msg_properties to set additional information about the message.

V_enq_options is of type ‘dbms_aq.enqueue_options_t’. This is a record of:

 

Visibility   BINARY_INTEGER  –Options are: dbms_aq.on_commit and dbms_aq.immediate. This indicates whether the enqueue is part of the current transaction, or done autonomously.

Relative_msgid      RAW(16)             –If the message needs to be enqueued at a specific position, it will be relative to this msgid.

Sequence_deviation BINARY_INTEGER –-Options are: DBMS_AQ.BEFORE, DBMS_AQ.TOP or NULL (default). If before then the message is before the relative_msgid. If top, the message will be the first to be dequeued.

 

V_msg_properties is of type ‘dbms_aq.message_properties_t’. This is a record of:

 

priority        BINARY_INTEGER  — Any integer, to set the priority. Smaller is higher priority. The default is 1.

delay           BINARY_INTEGER  — If the message needs to be delayed before it can be dequeued, set the time in seconds here. The default is dbms_aq.no_delay.

expiration      BINARY_INTEGER  — For messages that need to expire after a certain time, set the expiration time in seconds. (Offset from the delay). The default is dbms_aq.never.

correlation     VARCHAR2(128)   — A free text field that can be used to identify groups of messages.

attempts        BINARY_INTEGER  — Number of failed attempts to dequeue, before the message will be failed and marked as expired.

recipient_list  DBMS_AQ.AQ$_RECIPIENT_LIST_T –- Only valid for multi-consumer queues. Sets the designated recipients.

exception_queue VARCHAR2(51)    — The exception queue to use, when it is not the default.

enqueue_time    DATE                — Set automatically during enqueue

state           BINARY_INTEGER  — Automatically maintained by AQ, to indicate the status of the message.

 

Let’s see if the dequeue also works. For this the procedure dequeue is used, with similar parameters.

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

              ,dequeue_options=>V_deq_options

              ,Payload=>V_payload

             ,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

End;

 

This time, our message should be displayed.

 

For the dequeue, we used v_deq_options of type ‘dbms_aq.dequeue_options_t’. This is a record of:

 

consumer_name  VARCHAR2(30)       — Indicates the consumer for multi-consumer queues.

dequeue_mode   BINARY_INTEGER –- How to dequeue the messages. Either leave it on the queue, or remove it. Either dbms_aq.browse and dbms_aq.remove (default).

navigation     BINARY_INTEGER –- Indicate where to start dequeuing. Dbms_aq.next_message (default), to continue from the previous dequeue. Dbms_aq.first_message to start at the top of the queue. Dbms_aq.next_transaction to skip the rest of this message group.

visibility     BINARY_INTEGER –- same as dbms_aq.enqueue_options_t.visibility.

wait           BINARY_INTEGER –- The time (in seconds) the package should wait if no message is available. Default is dbms_aq.forever.

msgid          RAW(16)     — When specified, only the message with this msgid will be dequeued.

correlation    VARCHAR2(128)  — Only messages with this correlation will be dequeued (may include wildcards).

 

Note how message_properties and payload are now out-parameters.

 

This is probably the simplest queue possible. We enqueued and dequeued a raw message. We didn’t specify the visibility. So your session still needs to commit these actions.

 

Now let’s enqueue our message again, and see how it works behind the curtain.

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.Enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_payload:=Utl_raw.Cast_to_raw('Hello world!');

Dbms_aq.Enqueue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

,Enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line('Msg_id: '||rawtohex(V_msgid));

end;

 

Msg_id: 499CE4809F2641E1BFBC8AFBC8DB5AFA

 

The queue table is an ordinary heap-table, so we can query it.

 

select q_name, rawtohex(msgid) msg_id, priority, state, enq_time, enq_uid

from   xxx_test;

 

q_name     msg_id                         priority state enq_time                   enq_uid

XXX_TEST_Q  499CE4809F2641E1BFBC8AFBC8DB5AFA     1     0 21-03-10 17:24:01,876000000 SYSTEM

 

 

We see our msgid again. A priority flag. A state flag, the time of enqueueing the message, and the user that enqueued the message. The message is also in the table, but since it is a blob, we won’t bother selecting from it yet.

 

There are more columns in the table, that control the order and by who the messages are dequeued. Most of them are still null, so we will see them when needed.

 

A useful alternative to the table is to query the queue-view aq$<table_name>. This will show the translated values of the state. (0 = READY). And especially when using multi-consumer queues, it will use a join to select a more complete picture of the queue.

 

When we dequeue the message, it will disappear from the queue. (And be deleted from the queue table). However, this can be controlled by the retention parameter of the queue.

 

Let’s set this parameter, so we can check the data after the dequeue.

We set the retention time to 240 (seconds):

 

begin

DBMS_AQADM.ALTER_QUEUE(queue_name =>'xxx_test_q'

,retention_time => 240);

end;

 

Now when we dequeue the message, it will remain in the queue:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

              ,dequeue_options=>V_deq_options

              ,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

End;

 

select       queue

,      rawtohex(msg_id)msg_id

,      msg_priority

,      msg_state

,      enq_timestamp

,      enq_user_id

,      deq_timestamp

,      deq_user_id

from   aq$xxx_test

 

QUEU     MSG_ID                         MSG_PRIO MSG_STATE ENQ_TIMESTAMP  ENQ_USER DEQ_TIMESTAMP     DEQ_USER_ID

XXX_TEST_Q AEC2CD2E34514363B6739969E8E8D353    1 PROCESSED 19-03-10 18:31:40 SYSTEM 19-03-10 21:26:45 SYSTEM

 

Now the message has been set to state ‘PROCESSED’, and some dequeue information has been added.

 

It’s time to start navigating queues when there are multiple messages in the queue.

 

Messages are by default dequeued in the order in which they are enqueued. On creation of the queue table, you can set other dequeue orders. But it is also possible to dequeue messages in a different order by navigating the queues, or using filter-criteria.

 

To show the dequeueing order we enqueue 10 different messages.

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.Enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_enq_options.visibility := dbms_aq.immediate;

for i in 1..10 loop

    V_payload:=Utl_raw.Cast_to_raw('This is the '||to_char(to_date(i,'J'),'jspth')||' message');

    Dbms_aq.Enqueue(Queue_name=>'xxx_test_q'

    ,Message_properties=>V_msg_properties

                  ,Enqueue_options=>V_enq_options

                  ,Payload=>V_payload

                        ,Msgid=>V_msgid);

    Dbms_output.Put_line(rawtohex(V_msgid));

end loop;

end;

 

This enqueues the text ‘This is the first message’ till ‘This is the tenth message’. On dequeuing, the messages come out in the same order:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

for i in 1..10 loop

    Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

                 ,Message_properties=>V_msg_properties

                  ,dequeue_options=>V_deq_options

                   ,Payload=>V_payload

                 ,Msgid=>V_msgid);

    Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

end loop;

End;

 

This is the first message

This is the second message

………

This is the tenth message

 

 

When we created the queue table, we choose the default sort order. This is by enqueue_time. We can also build a queue that uses priority dequeuing. First we create a queue:

 

begin

dbms_aqadm.create_queue_table(queue_table=>'xxx_test_prio'

                             ,sort_list => 'PRIORITY,ENQ_TIME'

                             ,queue_payload_type=>'RAW');

dbms_aqadm.create_queue(queue_name=>'xxx_test_prio_q'

                       ,queue_table=>'xxx_test_prio');

dbms_aqadm.start_queue(queue_name=>'xxx_test_prio_q');

end;

 

We indicated a sort_list now. The options are ‘ENQ_TIME’ (default), ‘ENQ_TIME,PRIORITY’,‘PRIORITY’,’PRIORITY,ENQ_TIME‘. Now we enqueue some messages with reversed priorities:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.Enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_enq_options.visibility := dbms_aq.immediate;

for i in 1..10 loop

    V_payload:=Utl_raw.Cast_to_raw('This is the '||to_char(to_date(i,'J'),'jspth')||' message');

    v_msg_properties.priority:=11-i;

    Dbms_aq.Enqueue(Queue_name=>'xxx_test_prio_q'

    ,Message_properties=>V_msg_properties

                  ,Enqueue_options=>V_enq_options

                  ,Payload=>V_payload

                 ,Msgid=>V_msgid);

    Dbms_output.Put_line(rawtohex(V_msgid));

end loop;

end;

 

And we dequeue them again:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

for i in 1..10 loop

    Dbms_aq.dequeue(Queue_name=>'xxx_test_prio_q'

                 ,Message_properties=>V_msg_properties

                  ,dequeue_options=>V_deq_options

                  ,Payload=>V_payload

                 ,Msgid=>V_msgid);

    Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

end loop;

End;

 

This is the tenth message

This is the ninth message

…………

This is the second message

This is the first message

Now it’s time to look at queueing navigation. It is possible to dequeue specific messages from the queue. You can select messages with a specific msg_id, correlation or recipient_list (for mc-queueus).

We’ll first search for a specific correlation and then a message_id. We enqueue ten messages, with different correlations:

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.Enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_enq_options.visibility := dbms_aq.immediate;

for i in 1..10 loop

    V_payload:=Utl_raw.Cast_to_raw('This is the '||to_char(to_date(i,'J'),'jspth')||' message');

    v_msg_properties.correlation:=to_char('Corr'||i);

    Dbms_aq.Enqueue(Queue_name=>'xxx_test_q'

                 ,Message_properties=>V_msg_properties

                  ,Enqueue_options=>V_enq_options

                  ,Payload=>V_payload

                 ,Msgid=>V_msgid);

    dbms_output.Put_line('Msg_id: '||rawtohex(V_msgid)||' Correlation: Corr'||i);

end loop;

end;

 

Msg_id: E8BE83A2A2A04F1EA74863B4A7C78DAF Correlation: Corr1

Msg_id: 7159B80BC3194C7AAA6910AB10E753C5 Correlation: Corr2

Msg_id: 4AF3693CF7EE4994B0F78830371437B9 Correlation: Corr3

Msg_id: 44DBC0CB09C94BB98DF2D7E48971849C Correlation: Corr4

Msg_id: 98F3E119041E47F5BF46604E014120BF Correlation: Corr5

Msg_id: B71B7F097A9E4EDBA696958326BF6300 Correlation: Corr6

Msg_id: C4F5050B02904EEEAD2842405A0BDE2A Correlation: Corr7

Msg_id: E4D923A4CB4B4DF2B64B8421A88FFC42 Correlation: Corr8

Msg_id: BE199053188648AE8FA238A01A5C9CD1 Correlation: Corr9

Msg_id: 8991E793D2DB41F5B3F9D00D283B6F6D Correlation: Corr10

 

Now we can dequeue the 5th (correlation) and 8th (msg_id) message:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_deq_options.correlation:='Corr5';

Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

v_deq_options.correlation:=NULL;

v_deq_options.msgid:='E4D923A4CB4B4DF2B64B8421A88FFC42';

Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

              ,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

End;

 

This is the fifth message

This is the eighth message

 

Note how we have to set the correlation back to NULL for the second dequeue. Otherwise we would be trying to dequeue a message with correlation ‘Corr5’ and the specified msg_id. Since that message does not exist, our procedure will just wait for the message to appear.

 

By default when you dequeue from an empty queue, or try to dequeue a non-available message, the dequeue will wait indefinitely for a message to appear. You can control this behavior with the dequeue options.

 

V_deq_options.wait := 10; — to wait 10 seconds. Any number of 0 or higher is allowed.

V_deq_options.wait := dbms_aq.no_wait; — not waiting for the message.

V_deq_options.wait := dbms_aq.forever; — wait indefinitely 

Do note that when the time-out is reached an ’ORA-25228: timeout in dequeue from <queue> while waiting for a message’ raised. So you will need to handle the exception.

One more feature to consider is the browsing mode. So far we have seen the messages that we dequeued were removed from the queue (or at least got status ‘Processed’). By setting the dequeue options, we can first inspect messages before dequeuing them. Consider the following. We have 8 messages left in our queue:

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_deq_options_rm dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_deq_options.wait:=dbms_aq.no_wait;

v_deq_options.dequeue_mode:=DBMS_AQ.BROWSE;

for i in 1..10 loop

begin

 

   Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

   ,Message_properties=>V_msg_properties

   ,dequeue_options=>V_deq_options

   ,Payload=>V_payload

   ,Msgid=>V_msgid);

   Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

   dbms_output.put_line(v_msg_properties.correlation);

   if v_msg_properties.correlation='Corr6' then

       v_deq_options.dequeue_mode:=DBMS_AQ.REMOVE;

       v_deq_options.msgid:=v_msgid;

       Dbms_aq.dequeue(Queue_name=>'xxx_test_q'

                      ,Message_properties=>V_msg_properties

                      ,dequeue_options=>V_deq_options

                     ,Payload=>V_payload

                      ,Msgid=>V_msgid);

      Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

      v_deq_options.dequeue_mode:=DBMS_AQ.BROWSE;

      v_deq_options.msgid:=NULL;

   end if;

   exception

    when others then

      null;

   end;

end loop;

End;

 

This removed only the 6th message from the queue, and left the others intact.

 

There are more options to the queuing / dequeuing like retrying failed attempts (rollback after a dequeue is considered a failed attempt) and queuing with a delay or an expiration time. But I think the information so far will allow you to test these options on a need-by basis.

 

Multi-consumer or publish-subscribe queues

 

Both ‘publish-subscribe’ and ‘multi-consumer’ are used for these queues. I think ‘multi-consumer’ is most often used informally. That will also be the one I will use in this article (even though ‘publish-subscribe’ is more accurate).

 

We build multi-consumer queues with dbms_aqadm again. But on creating the queue-table, we say that it has to be a multi-consumer queue-table:

 

Begin

dbms_aqadm.create_queue_table (queue_table=>’xxx_mc_test’

                           ,multiple_consumers=>TRUE

                           ,queue_payload_type=>’RAW’);

End;

 

Now we see more objects being created. The most important ones are:

Xxx_mc_test                      The queue table itself.

Table aq$_xxx_mc_test_s with information about the subscribers to the queue

Table aq$_xxx_mc_test_r with information about the rules for the subscriptions

IOT aq$_xxx_mc_test_h with historic information about dequeuing

IOT aq$_xxx_mc_test_i with dequeuing information

 

As you can see, a lot more information is stored for multi-consumer queues. In part this information has to do with the subscription and subscriber mechanism. But there is also the need to keep a history of the dequeuing, to know when a message has been dequeued by all subscribers.

 

We will be seeing the use of all the objects in a few minutes, when we start queuing and dequeuing messages.

 

When we try to enqueue messages on this queue now, we receive an ORA-24033: no recipients for message. This means we need to set up subscribers first. If we enqueue without a recipient list, the message will be made available for all subscribers.

 

To add a subscriber, we use the dbms_aqadm package and a new object_type: sys.aq$_agent.

This type is defined as an object of name , address and protocol. The last 2 are used in inter-system communication only.

 

We can just call the following procedure:

 

DECLARE

V_agent sys.aq$_agent;

BEGIN

   V_agent:= sys.aq$_agent('Agent1',NULL,NULL);

   DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>'xxx_mc_test_q'

      ,subscriber=>v_agent);

END;

 

We can see the subscribers from the view aq$xxx_mc_test_s (or the underlying table: aq$_xxx_mc_test_s):

 

select * from aq$xxx_mc_test_s;

 

QUEUE        NAME   ADDRESS      PROTOCOL TRANSFORMATION

————– —— ——-     ——– ————–

XXX_MC_TEST_Q AGENT1              0    

 

Now let’s enqueue a message:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_payload := utl_raw.cast_to_raw('Hello world, again!');

Dbms_aq.enqueue(Queue_name=>'xxx_mc_test_q'

             ,Message_properties=>V_msg_properties

              ,enqueue_options=>V_enq_options

,Payload=>V_payload

             ,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_msgid));

End;

 

Now when we look at the queue-view, we can see that a subscriber has been selected:

 

select queue,rawtohex(msg_id) msg_id,msg_state,consumer_name from aq$xxx_mc_test;

 

QUEUE        MSG_ID                        MSG_STATE CONSUMER_NAME

————– ——————————– ——— ————-

XXX_MC_TEST_Q BC4C48AC659946428F38F8BC3AB02184 READY     AGENT1

 

Now to dequeue the message, we also need to set the consumer_name in the dequeue_options. When enqueuing a message without a subscriber_name, it can be dequeued by all subscribers. But on dequeueing, the subscriber needs to identify itself.

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_deq_options.consumer_name:='Agent1';

Dbms_aq.dequeue(Queue_name=>'xxx_mc_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

End;

 

Hello world, again!

 

Now when we check the queue contents, we see that the message is still there. Even after a commit, the message has been retained. Maybe you won’t see it on your system immediately. But then run:

 

Begin

Dbms_aqadm.stop_time_manager;

End;

 

And enqueue/dequeue a message again. Now when you look in xxx_mc_test or aq$xxx_mc_test, you will see the message being retained (with status ‘PROCESSED’). When you start the time_manager again, the message will disappear after some time.

 

The reason for this, is that Oracle enhances concurrency by using a separate table (IOT) for the dequeuing. When we enqueue a message again:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_payload := utl_raw.cast_to_raw('Hello world');

Dbms_aq.enqueue(Queue_name=>'xxx_mc_test_q'

,Message_properties=>V_msg_properties

,enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_msgid));

End;

 

We can see the data in the dequeue-IOT:

 

select subscriber#, queue#, msg_enq_time, msgid from Aq$_xxx_mc_test_i;

 

SUBSCRIBER# QUEUE# MSG_ENQ_TIME                MSG_ID

———– —— ————————— ——————————–

          1      0 21-03-10 14:16:00,252000000 75E41875D957455B84D80B55AE06F81C       

 

Here the basic information about our message is recorded. After a subscriber dequeues the message it’s version of the record is deleted only from this table (Please try this yourself, to confirm). The queue-monitors are responsible for cleaning up the queue-table after all subscribers have dequeued the message.

 

Now let’s see what happens when we add a second subscriber for our queue:

 

DECLARE

V_agent sys.aq$_agent;

BEGIN

   V_agent:= sys.aq$_agent('Agent2',NULL,NULL);

   DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>'xxx_mc_test_q'

      ,subscriber=>v_agent);

END;

 

Any messages that were enqueued already, won’t be available for this new subscriber. It can only dequeue messages enqueued after the subscriber was added.

 

Also you can’t just change subscribers in an existing session. If you try, you will get an ORA-25242: Cannot change subscriber name from string to string without FIRST_MESSAGE option.

 

As the message describes further, you need to change the navigation of the dequeue. The default navigation is next_message, which means that Oracle will read the queue in a read-consistent and ordered way. It will take a snapshot of the queue when the first message is dequeued, and will dequeue the messages in that order. Messages that were enqueued after the first dequeue, will be read after reading all the messages in the queue. Even if priority ordering means they are enqueued earlier.

 

An alternative navigation is ‘first_message’. When the navigation is set to ‘first_message’, Oracle will take a new snapshot before every dequeue, and start with the first message eligible for dequeuing.

Because we change subscribers, we need to set navigation to ‘First_message’, to force Oracle to take a new snapshot.

 

(Btw. If you would try ‘first_message’ with dequeue_mode ‘Browse’, you would never get beyond the first message. Try it!)

(Btw2. The same goes for changing the filter options like correlation.)

 

Let’s start a new session, and enqueue a new message:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_enq_options Dbms_aq.enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_payload := utl_raw.cast_to_raw('Hello agents!');

Dbms_aq.enqueue(Queue_name=>'xxx_mc_test_q'

             ,Message_properties=>V_msg_properties

,enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_msgid));

End;

 

The message is still the same in xxx_mc_test:

 

select q_name, rawtohex(msgid) msg_id,state,enq_time,enq_uid from xxx_mc_test

 

Q_NAME        MSG_ID                           STATE ENQ_TIME                    ENQ_UID             

————- ——————————– —– ————————— ——-

XXX_MC_TEST_Q 45F11423444747B99600BCD8E9B3141E     0 21-03-10 14:33:23,783000000 SYSTEM

 

But in the queue view, we now see 2 records:

 

select queue,msg_id,msg_state,enq_time,enq_user_id,consumer_name from aq$xxx_mc_test;

 

QUEUE          MSG_ID                           STATE ENQ_TIME          ENQ_USER_ID CONSUMER_NAME

————– ——————————– —– —————– ———– ————-

XXX_MC_TEST_Q 45F11423444747B99600BCD8E9B3141E READY 21-03-10 14:33:24 SYSTEM      AGENT1

XXX_MC_TEST_Q 45F11423444747B99600BCD8E9B3141E READY 21-03-10 14:33:24 SYSTEM      AGENT2

 

One record for each subscriber. We can see the same in the dequeue_iot and in the history table:

 

select subscriber#,queue#,msg_enq_time,msgid from aq$_xxx_mc_test_i;

 

SUBSCRIBER# QUEUE# MSG_ENQ_TIME                MSGID

———– —— ————————— ——————————–

          1      0 21-03-10 14:33:23,783000000 45F11423444747B99600BCD8E9B3141E

         21      0 21-03-10 14:33:23,783000000 45F11423444747B99600BCD8E9B3141E

 

Select Msgid,Subscriber#,Name,Dequeue_time,Dequeue_user From Aq$_xxx_mc_test_h;

 

MSGID                            SUBSCRIBER# NAME DEQUEUE_TIME DEQUEUE_USER

——————————– ———– —- ———— ————

45F11423444747B99600BCD8E9B3141E           1    0          

45F11423444747B99600BCD8E9B3141E          21    0          

 

Now when we dequeue the message, the queue table is not updated:

Select Rawtohex(Msgid) Msg_id,State,Enq_time,Enq_uid,deq_time,deq_uid From Xxx_mc_test;

 

MSG_ID                           STATE ENQ_TIME                    ENQ_UID DEQ_TIME DEQ_UID             

——————————– —– ————————— ——- ——– ——-

45F11423444747B99600BCD8E9B3141E     0 21-03-10 14:33:23,783000000 SYSTEM

 

However, the queue view reflects that the message has been dequeued by one subscriber.

 

Select Queue,Msg_id,Msg_state,Enq_time,Enq_user_id,Consumer_name From Aq$xxx_mc_test;

 

QUEUE         MSG_ID                           MSG_STATE ENQ_TIME          ENQ_USER CONSUMER_NAME

————- ——————————– ——— —————– ——– ————-

XXX_MC_TEST_Q 45F11423444747B99600BCD8E9B3141E PROCESSED 21-03-10 14:33:24 SYSTEM   AGENT1

XXX_MC_TEST_Q 45F11423444747B99600BCD8E9B3141E READY     21-03-10 14:33:24 SYSTEM   AGENT2

 

The record for Agent1 has been deleted from the dequeue-IOT:

 

select subscriber#,queue#,msg_enq_time,msgid from aq$_xxx_mc_test_i;

 

SUBSCRIBER# QUEUE# MSG_ENQ_TIME                MSGID

———– —— ————————— ——————————–

         21      0 21-03-10 14:33:23,783000000 45F11423444747B99600BCD8E9B3141E

 

And the history table also shows the dequeue:

 

Select msgid, subscriber#, Dequeue_time,Dequeue_user From Aq$_xxx_mc_test_h;

 

MSGID                            SUBSCRIBER# DEQUEUE_TIME                DEQUEUE_USER

45F11423444747B99600BCD8E9B3141E          21   21-03-10 14:33:23,783000000 SYSTEM

45F11423444747B99600BCD8E9B3141E           1

 

To dequeue the message for ‘Agent2’. We of course need to set the navigation to ‘First_message’:

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_deq_options.Wait := Dbms_aq.No_wait;

V_deq_options.Navigation:=Dbms_aq.First_message;

v_deq_options.consumer_name:='Agent2';

Dbms_aq.dequeue(Queue_name=>'xxx_mc_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_payload));

End;

 

Now after the QMON has processed the queue, the records will be deleted from all queues. (When a retention time has been set, the records will of course be retained for that time).

 

Rules for multi-consumer queues

 

So far we have seen different kinds of filtering for dequeuing messages. A new option comes with multi-consumer queues, where different subscribers can put a filter on their subscriptions. These filters (rules) can take the form of (complex) predicates that return a Boolean value. The rule can reference both message_properties as payload. To reference the payload, use a qualifier of ‘tab.user_data’.

 

Let’s build a new queue. To make optimal use of the ‘rule’-functionality we’ll use a custom type that can be referred to in the ‘rules’. The type that we’ll use is loosely based on the emp table.

 

create type t_emp as object

(empno   number

,ename   varchar2(10)

,job     varchar2(9)

);

 

BEGIN

DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'xxx_rule_test'

,queue_payload_type => 't_emp');

DBMS_AQADM.CREATE_QUEUE(queue_name => 'xxx_rule_test_q'

 ,queue_table => 'xxx_rule_test');

DBMS_AQADM.START_QUEUE (queue_name => 'xxx_rule_test_q');

END;

 

We add 2 subscribers to this queue.

 

DECLARE

V_agent sys.aq$_agent;

BEGIN

V_agent:= sys.aq$_agent('HR_President',NULL,NULL);

DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>'xxx_rule_test_q'

   ,subscriber=>v_agent

   ,rule=>'tab.user_data.job=''President''');

V_agent:= sys.aq$_agent('HR_Employee',NULL,NULL);

DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>'xxx_rule_test_q'

   ,subscriber=>v_agent);

END;

 

Note how the agent ‘HR_President’ has a rule added to its subscription. Only messages where the job attribute of the payload is ‘President’ are eligible for dequeuing by this agent. Let’s enqueue some messages on this queue.

 

Declare

V_payload t_emp;

V_msgid Raw(200);

V_enq_options Dbms_aq.enqueue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_payload := t_emp(1,'Jones','Manager');

Dbms_aq.enqueue(Queue_name=>'xxx_rule_test_q'

             ,Message_properties=>V_msg_properties

,enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_msgid));

v_payload := t_emp(2,'King','President');

Dbms_aq.enqueue(Queue_name=>'xxx_rule_test_q'

             ,Message_properties=>V_msg_properties

,enqueue_options=>V_enq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(utl_raw.cast_to_varchar2(V_msgid));

End;

 

Now we have 2 messages. Only one of which matches the rule for the ‘HR_President’ subscriber. The ‘HR_Employee’ subscriber does not have any rule, and is thus eligible for all messages. We can see this when we query the queue-view:

 

select queue,rawtohex(msg_id) msg_id,msg_state,consumer_name from aq$xxx_rule_test;

 

QUEUE        MSG_ID                              MSG_STATE CONSUMER_NAME

————— ——————————– ——— ————-

XXX_RULE_TEST_Q 4D0FF7A800834559809AD90AFCA81444 READY     HR_EMPLOYEE

XXX_RULE_TEST_Q E5A2FDFD8EE942349E9BC9DEE88CEB10 READY     HR_EMPLOYEE

XXX_RULE_TEST_Q E5A2FDFD8EE942349E9BC9DEE88CEB10 READY     HR_PRESIDENT

 

We see that both messages are enqueued for the ‘HR_Employee’. But only the message with the job ‘President’ is enqueued for the the ‘HR_President’.

 

Let’s dequeue the messages as ‘HR_President’ first, then as ‘HR_Employee’.

 

Declare

V_payload t_emp;

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_deq_options.wait := dbms_aq.no_wait;

v_deq_options.consumer_name:='HR_President';

Dbms_aq.dequeue(Queue_name=>'xxx_rule_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(v_payload.empno||' '||v_payload.ename);

end;

 

2 King

 

This time the first message enqueued was ignored for this subscriber. Only the message that met its rule was dequeued. When dequeuing as the ‘HR_Employee’ both messages will be dequeued.

 

Declare

V_payload t_emp;

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

v_deq_options.wait := dbms_aq.no_wait;

v_deq_options.navigation := dbms_aq.first_message;

v_deq_options.consumer_name:='HR_Employee';

Dbms_aq.dequeue(Queue_name=>'xxx_rule_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(v_payload.empno||' '||v_payload.ename);

v_deq_options.navigation := dbms_aq.next_message;

Dbms_aq.dequeue(Queue_name=>'xxx_rule_test_q'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(v_payload.empno||' '||v_payload.ename);

end;

 

1 Jones

2 King

 

After these dequeues, the queue is empty for these subscribers. The only message eligible for ‘HR_President’ was the message with ‘2,King,President’. ‘HR_Employee’ was eligible for both messages.

Remember that the ‘Rule’ must evaluate to a Boolean value. Valid references are to ‘tab.user_data.’, for object_type payloads. Also columns like ‘priority’ or ‘correlation’ from the message properties can be referenced in the rule.

 

Common issues with queues and troubleshooting

 

Above we already saw several error messages related to queues. Most of them can be expected, and should be handled in the code.

 

The most common issues with queues are from queues not started, or not started for enqueuing or dequeuing. The error messages for this should be quite clear, and you can just start the queue with the ‘dbms_aqadm.start_queue’ package. Note that when the queue is started for enqueuing or dequeuing only, you need to stop it first, then start again with the correct options enabled.

 

Another issue may occur because of the AQ error handling system. A dequeue with dequeue_mode ‘REMOVE’ that needs to roll back afterwards, is considered a failed attempt. When the number of failed attempts exceed the retry count of the queue, the message will be moved to the Exception queue. The message remains in the queue table, but with status 3: Expired. The exception_queue field will be set to the name of the exception queue.

 

These messages are not available for dequeuing anymore. They must be dequeued from the exception queue.

 

To dequeue from an exception queue, it first needs to be enabled for dequeuing. (It cannot be enabled for enqueuing). Also no subscriber_name is allowed for the dequeue.

 

Begin

Dbms_aqadm.Start_queue(Queue_name=>'aq$_xxx_mc_test_e',Enqueue=>False,Dequeue=>True);

end;

 

Declare

V_payload Raw(200);

V_msgid Raw(200);

V_deq_options Dbms_aq.dequeue_options_t;

v_msg_properties dbms_aq.message_properties_t;

Begin

V_deq_options.Wait := Dbms_aq.No_wait;

Dbms_aq.dequeue(Queue_name=>'AQ$_XXX_MC_TEST_E'

,Message_properties=>V_msg_properties

,dequeue_options=>V_deq_options

       ,Payload=>V_payload

,Msgid=>V_msgid);

Dbms_output.Put_line(Utl_raw.Cast_to_varchar2(V_payload));

End;

 

The last issue to note with queues (especially in eBS systems) is described in Metalink note  267137.1. If multi-consumer queues are created in an ASSM tablespace, or when using freelist groups, QMON will not perform space management on the IOT’s. This will result in ever growing IOT’s and eventually in deteriorating performance.

 

Selecting from custom types in (workflow) tables

 

After a long silence, it’s time to write another blog entry. I received a request to write about Oracle Reports. And I think that will be a new series (Even though I didn’t finish the workflow series yet). 

 

But the last few days, I’ve been working on cleaning up workflow tables. 

Most of these tables are very straightforward and you can find queries and descriptions in the workflow series. However, there are some more complex cases. There are the advanced queuing (aq) tables. And also some data hidden in wf_item_attribute_values for items started by the Business Event System (BES) (reminder to self: Write that article about BES too).

 

In the aq-tables, the payload of the message (i.e. the data transferred by the message) is stored in a custom type. The same goes for  the event_data in wf_item_attribute_values. In this article we’ll see how we can get the data from those ‘strange’ columns. 

 

Let us start with wf_item_attribute_values. Processes that are started from a business event, store the data from the originating business event in the column ‘EVENT_VALUE’. This has a type ‘WF_EVENT_T’. When you query it in a sql*plus session, you will see a huge column filled with something like: 

 

EVENT_VALUE(PRIORITY, SEND_DATE, RECEIVE_DATE, CORRELATION_ID, PARAMETER_LIST(NAME, VALUE), EVENT_NA
----------------------------------------------------------------------------------------------------
WF_EVENT_T(0, '17-SEP-09', NULL, NULL, WF_PARAMETER_LIST_T(WF_PARAMETER_T('TASK_ID', '16719879'), WF('ABORT_WORKFLOW', 'N'), WF_PARAMETER_T('SUB_GUID', '73BAB9A51BAF5307E04400144F687CA0')), 'oracle.ap, NULL, NULL)

 

The problem that many ebs-dba’s are facing is how to select the data inside this column. In this case, it would be the task_id that we are interested in. Oracle delivers several API's for use in PL/SQL. But sometimes you want plain SQL. 

One way to do it is to use a clever substr/instr construction or a regular expression. But the efficient way to do it, is to tell Oracle which info you want. 

Let’s take a look at the custom type. As mentioned, it is WF_EVENT_T. We can find the description in the DBA_TYPES table.

 

select owner,type_name,typecode,attributes,methods 
from dba_types 
where type_name='WF_EVENT_T';

OWNER                          TYPE_NAME                      TYPECODE                       ATTRIBUTES    METHODS
------------------------------ ------------------------------ ------------------------------ ---------- ----------
APPS                           WF_EVENT_T                     OBJECT                                 13         31

 

 

This tells us that the WF_EVENT_T is an object type. 

We can find its attributes in DBA_TYPE_ATTRS: 

select owner,type_name,attr_name,attr_type_owner,attr_type_name,length 
from dba_type_attrs 
where type_name=’WF_EVENT_T’;

OWNER		TYPE_NAME	ATTR_NAME         	ATTR_TYPE_OWNER		ATTR_TYPE_NAME              
---------	------------	-------------------	----------------	--------------
APPS   		WF_EVENT_T      CORRELATION_ID					VARCHAR2                    
APPS     	WF_EVENT_T      ERROR_MESSAGE       				VARCHAR2                    
APPS     	WF_EVENT_T      ERROR_STACK         				VARCHAR2                    
APPS     	WF_EVENT_T      ERROR_SUBSCRIPTION  				RAW                         
APPS     	WF_EVENT_T      EVENT_DATA          				CLOB                        
APPS     	WF_EVENT_T      EVENT_KEY					VARCHAR2                    
APPS     	WF_EVENT_T      EVENT_NAME          				VARCHAR2                    
APPS     	WF_EVENT_T      FROM_AGENT         	APPS			WF_AGENT_T
APPS     	WF_EVENT_T      PARAMETER_LIST    	APPS			WF_PARAMETER_LIST_T
APPS     	WF_EVENT_T      PRIORITY         				NUMBER
APPS     	WF_EVENT_T      RECEIVE_DATE     				DATE
APPS     	WF_EVENT_T     	SEND_DATE        				DATE
APPS     	WF_EVENT_T     	TO_AGENT         	APPS			WF_AGENT_T

You see that the attributes are defined including their datatype, which can be a seeded datatype (VARCHAR2) or a custom one (WF_PARAMETER_LIST_T). Now that we now the attributes of the type, we can select them directly. To select the ‘PRIORITY’, just use an extra qualifier: 

select v.event_value.priority
from   wf_item_attribute_values v 
where  ROWNUM = 1;

EVENT_VALUE.PRIORITY
--------------------
                   0

But how about the ‘PARAMETER_LIST’? That is where the task_id was stored. Let’s check the WF_PARAMETER_LIST_T definition: 

select owner,type_name,typecode,attributes,methods 
from   dba_types 
where  type_name ='WF_PARAMETER_LIST_T';

OWNER	  TYPE_NAME         	TYPECODE  		ATTRIBUTES  METHODS
--------- -------------------	-------------------	----------- -------	
APPS      WF_PARAMETER_LIST_T	COLLECTION       		  0       0

 

This time, the type is a collection. We can find more info about a collection with: 

select type_name,coll_type,elem_type_owner,elem_type_name 
from 	 dba_coll_types 
where  type_name='WF_PARAMETER_LIST_T';

TYPE_NAME                      COLL_TYPE                      ELEM_TYPE_OWNER                ELEM_TYPE_NAME
------------------------------ ------------------------------ ------------------------------ --------------
WF_PARAMETER_LIST_T            VARYING ARRAY                  APPS                           WF_PARAMETER_T

 

So the WF_PARAMETER_LIST_T is a Varray of WF_PARAMETER_T. Before we look at selecting from Varrays, we first check what WF_PARAMETER_T looks like:

select owner,type_name,typecode,attributes,methods 
from   dba_types 
where  type_name ='WF_PARAMETER_T';

OWNER	  TYPE_NAME           TYPECODE	ATTRIBUTES METHODS
--------- ------------------- ---------	---------- ----------
APPS  	  WF_PARAMETER_T      OBJECT             2          4

 

That is an object type again. So we select: 

select owner,type_name,attr_name,attr_type_owner,attr_type_name
from   dba_type_attrs 
where  type_name='WF_PARAMETER_T';

OWNER	TYPE_NAME             ATTR_NAME	ATTR_TYPE_OWNER	    ATTR_TYPE_NAME      
--------- ------------------- --------- ------------------- --------------
APPS	WF_PARAMETER_T        NAME			    VARCHAR2            
APPS	WF_PARAMETER_T	      VALUE			    VARCHAR2           

 

Ok. We now know the whole structure of the parameter list. Back to the Varray.  A Varray (Varying Array) is of course an array structure. Since this is similar to a table structure, you can cast the Varray into a table. Then you use the casted table to select your data. Let’s do that to get the names from the parameter list.

select t.name
from   wf_item_attribute_values v
,      table(v.event_value.parameter_list) t
Where  v.event_value IS NOT NULL 
And    ROWNUM = 1;

NAME
------------------------------
TASK_ID

 

Now that’s a neat trick. We can join our table to its own column! 

In our case, we only have a task_id parameter. We could do the same again, to get the value of the parameter from the value column.

But to join wf_item_attribute_values to itself is a very expensive operation. Take a look at the explain plan: 

PLAN_TABLE_OUTPUT
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name                     | Rows  | Bytes | Cost  | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |     1 |    62 |   432M|       |       |
|*  1 |  COUNT STOPKEY                      |                           |       |       |       |       |       |
|   2 |   NESTED LOOPS                      |                           |   207G|    11T|   432M|       |       |
|   3 |    PARTITION RANGE ALL              |                           |       |       |       |     1 |    77 |
|   4 |     PARTITION HASH ALL              |                           |       |       |       |     1 |     8 |
|*  5 |      TABLE ACCESS FULL              | WF_ITEM_ATTRIBUTE_VALUES  |    25M|  1453M|   948K|     1 |   616 |
|   6 |    COLLECTION ITERATOR PICKLER FETCH|                           |       |       |       |       |       |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   5 - filter(SYS_OP_NOEXPAND("V"."EVENT_VALUE") IS NOT NULL)
 
Note: cpu costing is off  	 

 

 

That can kind of hurt if it,were it not for the NL with a COUNT STOPKEY (Because of the rownum=1). The reason for the expensive plan, is because for every row from wf_item_attribute_values, Oracle needs to get the data from event_value. It basically does a carthesian join with itself.

So here is another way to get your data: 

select v.item_key
,      (select value
        from table(v.event_value.parameter_list) t
        where t.name='TASK_ID' ) task_id
from   wf_item_attribute_values v
where  v.event_value is not null
and    rownum=1;

ITEM_KEY						TASK_ID
-------------------------------------------------	--------
oracle.apps.jtf.cac.task.createTask-155563676		16719879

 

Now we use a scalar subquery, where Oracle will access only the event_value for the rows that will be returned to the user. You can see this in the explain plan by the collection iterator picklefetch (= the operation that collects the data from a collection type) being pushed up to just before the select. 

PLAN_TABLE_OUTPUT
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                     | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |     1 |    60 |   948K|       |       |
|*  1 |  COLLECTION ITERATOR PICKLER FETCH|                           |       |       |       |       |       |
|*  2 |  COUNT STOPKEY                    |                           |       |       |       |       |       |
|   3 |   PARTITION RANGE ALL             |                           |       |       |       |     1 |    77 |
|   4 |    PARTITION HASH ALL             |                           |       |       |       |     1 |     8 |
|*  5 |     TABLE ACCESS FULL             | WF_ITEM_ATTRIBUTE_VALUES  |    25M|  1453M|   948K|     1 |   616 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)='TASK_ID')
   2 - filter(ROWNUM=1)
   5 - filter(SYS_OP_NOEXPAND("SYS_ALIAS_1"."EVENT_VALUE") IS NOT NULL)
 
Note: cpu costing is off

 

Still expensive. But much better.

 

So that will let us peek into the  event_value column on wf_item_attribute values. Be aware that different processes will enter different parameters into the event_value column. But with the information above you should be able to understand how to select the data and use it in your own queries. 

 

Now let’s take a look at the AQ-tables? 

As I mentioned, they carry their payloads in custom types too. One that most ebs-dba’s will have seen is WF_NOTIFICATION_OUT. When the workflow engine generates a notification, it will be stored in ‘WF_NOTIFICATIONS’. However, a message will be posted on ‘WF_NOTIFICATION_OUT’ too. This message will be read by the workflow notification mailer, which will use it to get the relevant data from WF_NOTIFICATIONS.

 

If you haven’t run the notification mailer for a long time, you might want to clean up WF_NOTIFICATION_OUT a bit. There is a script available from Metalink to do the job. (There is also a quicker but unsupported way). But you might want to see the records in WF_NOTIFICATION_OUT related to WF_NOTIFICATIONS to decide if a cleanup is appropriate. 

 

Let’s look at WF_NOTIFICATION_OUT:

Name                    Null?    Type
----------------------- -------- ----------------
Q_NAME                           VARCHAR2(30)
MSGID                   NOT NULL RAW(16)
CORRID                           VARCHAR2(128)
PRIORITY                         NUMBER
STATE                            NUMBER
DELAY                            DATE
EXPIRATION                       NUMBER
TIME_MANAGER_INFO                DATE
LOCAL_ORDER_NO                   NUMBER
CHAIN_NO                         NUMBER
CSCN                             NUMBER
DSCN                             NUMBER
ENQ_TIME                         DATE
ENQ_UID                          NUMBER
ENQ_TID                          VARCHAR2(30)
DEQ_TIME                         DATE
DEQ_UID                          NUMBER
DEQ_TID                          VARCHAR2(30)
RETRY_COUNT                      NUMBER
EXCEPTION_QSCHEMA                VARCHAR2(30)
EXCEPTION_QUEUE                  VARCHAR2(30)
STEP_NO                          NUMBER
RECIPIENT_KEY                    NUMBER
DEQUEUE_MSGID                    RAW(16)
SENDER_NAME                      VARCHAR2(30)
SENDER_ADDRESS                   VARCHAR2(1024)
SENDER_PROTOCOL                  NUMBER
USER_DATA                        SYS.AQ$_JMS_TEXT _MESSAGE

 

The regular AQ-information is there. And our payload in USER_DATA. This time it’s an AQ-type. 

Let’s follow the same procedure: 

select type_name,typecode,attributes,methods
from   dba_types
where  type_name='AQ$_JMS_TEXT_MESSAGE';

TYPE_NAME                      TYPECODE                       ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
AQ$_JMS_TEXT_MESSAGE           OBJECT                                  4         34

An object type. So let’s see its attributes: 

select type_name,attr_name,attr_type_owner,attr_type_name
from   dba_type_attrs 
where  type_name='AQ$_JMS_TEXT_MESSAGE';

TYPE_NAME                      ATTR_NAME                      ATTR_TYPE_OWNER                ATTR_TYPE_NAME      
------------------------------ ------------------------------ ------------------------------ --------------
AQ$_JMS_TEXT_MESSAGE           HEADER                         SYS                            AQ$_JMS_HEADER
AQ$_JMS_TEXT_MESSAGE           TEXT_LEN                                                      INTEGER
AQ$_JMS_TEXT_MESSAGE           TEXT_LOB                                                      CLOB                
AQ$_JMS_TEXT_MESSAGE           TEXT_VC                                                       VARCHAR2      

 

We can already read the text_len, text_lob and text_vc. The last 2 contain an XML with a reference to the notification. But the information that I want to show you is in the header. This is a type ‘AQ$_JMS_HEADER’. When we check this one, we see that it again is an object with these attributes:

select type_name,typecode,attributes,methods
from   dba_types
where  type_name='AQ$_JMS_HEADER';

TYPE_NAME                      TYPECODE                       ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
AQ$_JMS_HEADER                 OBJECT                                  7         31

select type_name,attr_name,attr_type_owner,attr_type_name
from   dba_type_attrs 
where  type_name='AQ$_JMS_HEADER';

TYPE_NAME                      ATTR_NAME                      ATTR_TYPE_OWNER                ATTR_TYPE_NAME
------------------------------ ------------------------------ ------------------------------ -------
AQ$_JMS_HEADER                 APPID                                                         VARCHAR2
AQ$_JMS_HEADER                 GROUPID                                                       VARCHAR2
AQ$_JMS_HEADER                 GROUPSEQ                                                      INTEGER
AQ$_JMS_HEADER                 PROPERTIES                     SYS                            AQ$_JMS_USERPROPARRAY
AQ$_JMS_HEADER                 REPLYTO                        SYS                            AQ$_AGENT
AQ$_JMS_HEADER                 TYPE                                                          VARCHAR2
AQ$_JMS_HEADER                 USERID                                                        VARCHAR2

 

As you can imagine, we need to drill down into ‘PROPERTIES’. The other attributes might or might not contain any data, depending on the notification. properties is a Varray of ‘AQ$_JMS_USERPROPERTY’:

select type_name,typecode,attributes,methods
from   dba_types
where  type_name='AQ$_JMS_USERPROPARRAY';

TYPE_NAME                      TYPECODE                       ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
AQ$_JMS_USERPROPARRAY          COLLECTION                              0          0

select type_name,coll_type,elem_type_owner,elem_type_name 
from 	 dba_coll_types 
where  type_name='AQ$_JMS_USERPROPARRAY';

TYPE_NAME                      COLL_TYPE                      ELEM_TYPE_OWNER                ELEM_TYPE_NAME
------------------------------ ------------------------------ ------------------------------ --------------------
AQ$_JMS_USERPROPARRAY          VARYING ARRAY                  SYS                            AQ$_JMS_USERPROPERTY

 

One more level to check: 

select type_name,typecode,attributes,methods
from   dba_types
where  type_name='AQ$_JMS_USERPROPERTY';

TYPE_NAME                      TYPECODE                       ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
AQ$_JMS_USERPROPERTY           OBJECT                                  5          0

select type_name,attr_name,attr_type_owner,attr_type_name
from   dba_type_attrs 
where  type_name='AQ$_JMS_USERPROPERTY';

TYPE_NAME                      ATTR_NAME                      ATTR_TYPE_OWNER                ATTR_TYPE_NAME
------------------------------ ------------------------------ ------------------------------ -------
AQ$_JMS_USERPROPERTY           JAVA_TYPE                                                     INTEGER
AQ$_JMS_USERPROPERTY           NAME                                                          VARCHAR2
AQ$_JMS_USERPROPERTY           NUM_VALUE                                                     NUMBER
AQ$_JMS_USERPROPERTY           STR_VALUE                                                     VARCHAR2
AQ$_JMS_USERPROPERTY           TYPE                                                          INTEGER

 

So let’s see what properties we have. I just query the whole contents of properties for the first row in wf_notification_out (This particular system doesn’t have a WF-mailer running. If it is running, chances are that you won’t have any records in WF_NOTIFICATION_OUT).

select p.*
from   (select * 
        from wf_notification_out 
        where rownum=1) n
,      table(n.user_data.header.properties) p;

NAME	              	TYPE  STR_VALUE	                                   NUM_VALUE JAVA_TYPE                             
----------------------- ----- -------------------------------------------- --------- ---------
BES_EVENT_NAME	      	  100 oracle.apps.wf.notification.send                              27
BES_EVENT_KEY	      	  100                                               39388680        27
BES_PRIORITY	      	  200                                                     50        23
BES_SEND_DATE	       	  100 2009/03/06 01:12:23		                            27
BES_RECEIVE_DATE	  100 2009/03/06 01:12:34		                            27
BES_FROM_AGENT	      	  100 WF_NOTIFICATION_OUT@TESTDB.STIJF.COM                          27
BES_ERROR_SUBSCRIPTION    100 C10E7C2EF71253C1E0340800208D03E1		           	    27
NOTIFICATION_ID	      	  100 39388680		                                            27
ROLE		       	  100 FND_RESP535:21704		                                    27 
GROUP_ID		  100 39388680		                                            27
Q_CORRELATION_ID	  100 XDPWFSTD		                                            27

There you go. Among others, the ‘NOTIFICATION_ID’ is there. It maps to the notification_id on ‘WF_NOTIFICATIONS’. And by now, we have seen enough to select it directly in our queries:

select n.msgid,(select str_value 
                from   table(n.user_data.header.properties) 
		where  name='NOTIFICATION_ID') notification_id
from   wf_notification_out n
where  rownum=1;

MSGID			    	 NOTIFICATION_ID
-------------------------------- ---------------
64672D4985E57075E04400144F687CA0	39388680

That concludes this article. Based on the above, you will be able to select the data you want.

More on 1 != 1

I was reading the WorkflowFaq Blog today, where they discuss when 1 != 1 in Oracle.

Of I did some testing with this. First of course I reproduced the issue easy enough:

 

SQL> select 1/3*3 from dual;

 

     1/3*3

----------

         1

 

SQL> select * from dual where 1/3*3=1;

No rows selected.

It is obviously a rounding issue. So what happens if we force Oracle to round the result?

 

SQL> select * from dual where round(1/3*3)=1;

 

D

-

X

 

If the rounding went wrong, it will most likely be on the low side (0.3333……*3=0.9999……)

 

SQL> select trunc(1/3*3) from dual where ceil(1/3*3)=1;

 

D

-

0

 

We can see the real contents by dumping the result:

 

SQL> select dump(1/3*3) d from dual
  2  union
  3  select dump(1) d from dual;

 

D

--------------------------------------------------------------------------------

Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10

0,100,100,100,100

 

Typ=2 Len=2: 193,2 

I will go into the internal number format in a while. For now, it is sufficient to say that the 1/3*3 is indeed 0.9999999…….

Now how many decimals can we get?

 

SQL> select to_char(1/3*3,'9.99') from dual;

 

TO_CH

-----

 1.00

 

SQL> select to_char(1/3*3,'9.99999999999999999999999999999999999999999') from dual;

 

TO_CHAR(1/3*3,'9.999999999999999999999999999

--------------------------------------------

  .99999999999999999999999999999999999999990

 

There is the real value of 1/3*3.

The Oracle documentation says that the precision of the number format is 126 binary digits, approximately 38 decimals digits.

So when we force a mask with more decimals, Oracle will show the real value.

Àn interesting 'feature'. And definitely something to keep in mind.

One last note on the dumping of numbers.
dump(<number>,10) will dump the decimal value.
dump(<number>,16) will dump the hexadecimal value.

How do we translate it back to the real value then?
Let's do the excercise for a few numbers: 1234.5678 and -1234.5678.

There is some difference between positive and negative values. So let's do the positive one first.

 

SQL> select dump(1234.5678,10),dump(1234.5678,16) from dual;

 

DUMP(1234.5678,10)           DUMP(1234.5678,16)

---------------------------- --------------------------

Typ=2 Len=5: 194,13,35,57,79 Typ=2 Len=5: c2,d,23,39,4f

 

We start with the second to the last numbers. And we subtract 1:
194,12,34,56,78

Ignore the 194 for now, and put the decimal . after the second number: 12.345678

Now subtract 193 from the first value: 194-193=1.

Multiply the decimal value this number of times by 100: 12.345678 * 100 = 1234.5678.
(In our case we multiply by 100 once if the first value would be higher, mulitply more often).

The negative values are a little different:

 

SQL> select dump(-1234.5678,10),dump(-1234.5678,16) from dual;

 

DUMP(-1234.5678,10)             DUMP(-1234.5678,16)

------------------------------- ------------------------------

Typ=2 Len=6: 61,89,67,45,23,102 Typ=2 Len=6: 3d,59,43,2d,17,66

 

The 102 at the end means it is a negative value. Discard the 102 for the rest of the calculation.

61,89,67,45,23.

Subtract the second to the last number from 101.
101-89=12, 101-67=34, 101-45=56,101-23=78  gives
61,12 34 56 78

Put the decimal . after the second number. So we get 12.345678

Now subtract 62 from the first value and multiply by 100: 61-62 = -1.

Finally we divide the value this number of times by 100. In our case we have a negative, so we
multiply instead:

12.345678 * 100 = 1234.5678 -> And we had a negative. So -1234.5678

Workflow for eBS DBA’s. (Part 2)

This is part 2 of our series Workflow for eBS DBA’s. The first part can be found here.

In this part, we are going to see how the process we built in the first part can be started. And how we find information about it in the wf_ tables.

In the previous part we built an itemtype and a process. You’ll remember that the itemtype is the container that holds the other elements.

We can find the itemtype in our database with:
select wi.name
,      wi.persistence_type
,      wi.persistence_days
,      wit.display_name
,      wit.description
From   wf_item_types wi
,      wf_item_types_tl wit
where  wi.name=wit.name
and    wit.language='US'
and    wi.name='DBA_TYPE';

NAME     PERSISTE PERSISTENCE_DAYS DISPLAY_NAME    DESCRIPTION
-------- -------- ---------------- --------------- -------------------------------------------------
DBA_TYPE TEMP     0                DBA Itemtype    Itemtype to hold DBA processes and functions

So what can we find out about this item_type. We created one process within this item_type. Because processes can also be activities of another process, we need to identify the runnable processes. This is done by looking for the ‘ROOT’ processes:

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      instance_label
From   wf_process_activities
where  process_item_type='DBA_TYPE'
and    process_name='ROOT';

PROCESS_NAME       PROCESS_VERSION    ACTIVITY   ACTIVITY_NAME                  INSTANCE_ID   INSTANCE_LABEL
------------------ ------------------ ---------- ------------------------------ ------------- -----------------
ROOT                                1 DBA_TYPE   DBA_MAIN_PROCESS                      244747 DBA_MAIN_PROCESS

This shows us that there is a runnable process within this item_type, called: ‘DBA_MAIN_PROCESS’.

So how do we find out the definition of this process? First we need to find the right process_version.

select item_type
,      name
,      version
,      type
,      begin_date
,      end_date
From   wf_activities
where  name ='DBA_MAIN_PROCESS'
and    end_date is null;

Note that I selected the version (which is 1 now). And I added ‘end_date is null’. When we updated the process_definition, workflow will add new versions and end_date the previous one. This datamodel allows workflow to run it’s own versioning.

Once a process is started (actually an item created), it will check for the latest version of the process. And it will keep that definition of the process. Even if halfway the runtime of the item, a new definition of the process is loaded to the database.

Now that we know the version (1), we can start looking for the starting point. This is where the start (and end) activities come into the play.

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      instance_label
From   wf_process_activities
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    process_version=1
and    start_end='START';

PROCESS_NAME                   PROCESS_VERSION ACTIVITY ACTIVITY_NAME                  INSTANCE_ID INSTANCE_LABEL
------------------------------ --------------- -------- ------------------------------ ----------- --------------
DBA_MAIN_PROCESS                             1 DBA_TYPE START                               244748 START

This selects the first activity for our version of the process.  I also selected the instance_id. The instance_id is unique for the combination of item_type, process_name, process_version and activity_name. In other words it uniquely defines our activity in time (and space / item_type).

From this point we can follow the process through wf_activity_transitions:

select from_process_activity
,      to_process_activity
,      result_code
from   wf_activity_transitions wit
connect by from_process_activity=prior to_process_activity
start with from_process_activity=244748;

FROM_PROCESS_ACTIVITY TO_PROCESS_ACTIVITY RESULT_CODE
--------------------- ------------------- ------------------------------
244748              244752 *
244752               244750 *

This table includes the process_activities connected to each other. The default result_code is ‘*’. Later on we will see different result_codes.

In our flow, we see that the flow goes from process_activity instance_id  244748  to 244752 to 244750. In your system, the id’s will be different, of course.

Here is a query to show the flow, including the detail on the process_activities:

select l1
,      process_item_type
,      process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      result_code
,      to_process_activity
from (select t2.*
,       case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,       from_process_activity
,       result_code
,       to_process_activity
From    wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=244748
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,     wf_process_activities wpa
where wf_proc.pa=wpa.instance_id;

Even though it is interesting to see the flow. We still can’t see what is actually being done. That information is stored in the wf_activities:

select l1
,      process_item_type
,      process_name
,      process_version
,      wpa.instance_id
,      wf_proc.result_code
,      wa.item_type
,      wa.name
,      wa.type
,      wa.function
from  (select t2.*
,      case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,      from_process_activity
,      result_code
,      to_process_activity
From   wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=244748
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,    wf_process_activities wpa join wf_activities wa on (wpa.activity_item_type=wa.item_type and wpa.activity_name=wa.name and wa.end_date is null)
where wf_proc.pa=wpa.instance_id

I took a little shortcut here. Can you see which one? Let’s have a quiz to see who which additions and perfections you can make to this query.

So far we have seen the definition of the process. There are a few items left out. Remember that we created an attribute in the previous part? This is stored in wf_attributes.

Now that we have seen the definition of the process. It’s time to make it run. To run an item, we call the wf_engine.

The easiest procedure is the ‘launchprocess’. This creates an item, and starts executing it. Alternatively you can use the separate ‘createprocess’ and ‘startprocess’ procedures. For this article, I stick to the ‘launchprocess’.

The parameters for the procedure are: itemtype, itemkey, process, userkey and owner.

Itemtype is the itemtype to which our process belongs.

Itemkey is a unique identifier for items within this itemtype.

Process is the name of the process that we want to run. Later on, we’ll see how we can let the workflow engine choose the process automatically.

Userkey is an optional extra key to the item.

Owner is an optional ‘owner role’. We’ll see more about this later again.

Before we actually start the workflow, we still need to build the xxx_wf_dba.init procedure that is used as one of our activities. For now, I used an empty function:

CREATE OR REPLACE PACKAGE BODY XXX_WF_DBA AS
PROCEDURE init (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
Begin
NULL;
p_result:=wf_engine.eng_completed;
END;
END XXX_WF_DBA;

(Obviously you’ll have to create the package header too).

Finally we can start our workflow:

Begin
Wf_engine.launchprocess(itemtype=>’DBA_TYPE’
,itemkey=>’1’
,process=>’DBA_MAIN_PROCESS’);
End;

The workflow engine does not issue a commit.

Note that the itemkey is a varchar2 value. This is important to remember when querying the tables, to avoid implicit type-conversions.

Of course we just had a dummy process, but we can see that it has actually run. First an item was created:

select item_type
,      item_key
,      root_activity
,      root_activity_version
,      begin_date
,      end_date
,      user_key
from   wf_items
where  item_type='DBA_TYPE';

ITEM_TYP ITEM_KEY   ROOT_ACTIVITY                  ROOT_ACTIVITY_VERSION BEGIN_DA END_DATE USER_KEY
-------- ---------- ------------------------------ --------------------- -------- -------- ---------
DBA_TYPE 1          DBA_MAIN_PROCESS                                   1 20-07-09 20-07-09

So we see that our item has ran and finished.

To see what was actually executed, we need to look at wf_item_activity_statuses:

select item_type
,      item_key
,      process_activity
,      activity_status
,      activity_result
,      begin_date
,      end_date
,      execution_time
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='1'
order by begin_date,execution_time;

Here we see the activities that were executed. Already from the 4 rows you can deduce that the process itself is also executed.

Some things to note: The process_activity refers to the instance_id on wf_process_activities. This is part of the workflow versioning.

The execution_time is set on creation and update of the item_activity_status. It is a ‘global’ counter for the number of times an item_activity_status is updated during the runtime of the workflow engine. So an update of an item_activity_status for any workflow item will update the counter.

That way you will always get a correct sequence from wf_item_activity_statuses by ordering on begin_date,execution_time. (Assuming it takes more than a second to bounce your application server).

A little bit more complete query to see the progress of the item:

select wias.item_type
,      wias.item_key
,      wat.display_name
,      case when wpa.start_end is not null then wpa.start_end else wa.function end function
,      wias.begin_date
,      wias.end_date
,      wias.activity_status
,      wias.activity_result_code
from wf_item_activity_statuses wias
join wf_process_activities wpa on (wias.process_activity=wpa.instance_id)
join wf_activities wa on (wpa.activity_item_type=wa.item_type and wpa.activity_name=wa.name)
join wf_activities_tl wat on (wa.item_type=wat.item_type and wa.name=wat.name and wat.language='US')
where wias.item_type='DBA_TYPE'
order by wias.begin_date,wias.execution_time

At the end of this article a few items remain.

We still need to see the versioning in action. And it’s time to look at item_attributes. Item_attributes are the variables for the workflow engine.

Of course one follows from the other, because when we add item_attributes we will also create a new version. To demonstrate the effect, we first create an item, without starting it:

Begin
Wf_engine.createprocess(itemtype=>’DBA_TYPE’, itemkey=>’2’,process=>’DBA_MAIN_PROCESS’);
End;
Commit;

Now we return to the workflow builder. Here we create an attribute:

We will then update our dummy procedure to assign a value to this attribute.

CREATE OR REPLACE PACKAGE BODY XXX_WF_DBA AS
PROCEDURE init (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
v_process_version number;
Begin
if p_funcmode='RUN' then
select process_version
into   v_process_version
from   wf_process_activities
where  process_item_type=p_item_type
and    instance_id=p_actid;

if  v_process_version >= 2 then
wf_engine.SetItemAttrText(itemtype=>p_item_type
,itemkey =>p_item_key
,aname=>’INSTANCE_NAME’
,avalue=>sys_context(‘USERENV’,’DB_NAME’));
end if;
end if;
p_result:=wf_engine.eng_completed;
END;
END XXX_WF_DBA;

The test for p_funcmode will be explained further later on in this series. Basically, the workflow engine can call functions in different modes likes ‘RUN’ or ‘CANCEL’. Even though at the moment our function will only be ran in ‘RUN’ mode, it is good practice to test for the mode.

Then we have to test for the version of the workflow. This is needed, because of course the first version of the workflow did not have any item_attributes. So assigning a value to it would cause the workflow to error. The p_actid contains the instance_id from the process_activity. We can then get the process version.

The final interesting thing is the call to the workflow engine to assign a value to an item attribute. The WF_ENGINE has functions for all possible types of item_attributes. In this case we used the one for text assignment. The itemtype and itemkey are already passed from the workflow engine.

We still need to assign the attribute name (aname) and the value to be assigned (in this case the DB_NAME).

In the same way it is possible to read item_attributes with wf_engine.GetItemAttrText. This is a function that will return the value (so don’t use an avalue).

Now when we load the itemtype to the database, we will see a different version:

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      instance_label
from   wf_process_activities wpa
join   wf_activities wa on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wpa.process_version=wa.version)
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    start_end='START'
and    wa.end_date is null;

PROCESS_NAME                   PROCESS_VERSION ACTIVITY ACTIVITY_NAME                  INSTANCE_ID INSTANCE_LABEL
------------------------------ --------------- -------- ------------------------------ ----------- --------------
DBA_MAIN_PROCESS                             2 DBA_TYPE START                               244755 START

As you can see the process version is now 2.

Also pay attention to the join condition. We now have to add the version, since both the activity as the process_activity have a new version.

Based on this query, I  also extended our query to see the item_type definition:

select l1
,      process_item_type
,      process_name
,      process_version
,      wpa.instance_id
,      wf_proc.result_code
,      wa.item_type
,      wa.name
,      wa.type
,      wa.function
from  (select t2.*
,      case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,      from_process_activity
,      result_code
,      to_process_activity
From   wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=(select instance_id
from   wf_process_activities wpa
join   wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wpa.process_version=wa.version)
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    start_end='START'
and    wa.end_date is null)
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,    wf_process_activities wpa join wf_activities wa on (wpa.activity_item_type=wa.item_type and wpa.activity_name=wa.name and wa.end_date is null)
where wf_proc.pa=wpa.instance_id;

We can also already see the newly created attribute.

select item_type,name,type,format,text_default
from   wf_item_attributes
where  item_type='DBA_TYPE';

ITEM_TYP NAME                           TYPE     FORMAT   TEXT_DEFAULT
-------- ------------------------------ -------- -------- --------------------
DBA_TYPE INSTANCE_NAME                  VARCHAR2 10

But of course there are no values assigned to it yet. Let’s first run the item that we created a few minutes ago. This item will still run the first version of the process:

begin
wf_engine.startprocess(itemtype=>'DBA_TYPE',itemkey=>'2');
end;

Then we can use a slightly modified version of our script:

select wias.item_type
,      wias.item_key
,      wa.version
,      wat.display_name
,      case when wpa.start_end is not null then wpa.start_end else wa.function end function
,      wias.begin_date
,      wias.end_date
,      wias.activity_status
,      wias.activity_result_code
from wf_item_activity_statuses wias
join wf_process_activities wpa on (wias.process_activity=wpa.instance_id)
join wf_activities wa on (wpa.activity_item_type=wa.item_type and wpa.activity_name=wa.name and wpa.process_version=wa.version)
join wf_activities_tl wat on (wa.item_type=wat.item_type and wa.name=wat.name and wa.version=wat.version and wat.language='US')
where wias.item_type='DBA_TYPE'
and   wias.item_key='2'
order by wias.begin_date,wias.execution_time

ITEM_TYP ITEM_KEY     VERSION DISPLAY_NAME     FUNCTION        BEGIN_DA END_DATE ACTIVITY ACTIVITY_RES
-------- ---------- --------- ---------------- --------------- -------- -------- -------- -----------
DBA_TYPE 2                  1 DBA Main process                 20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 2                  1 Start            START           20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 2                  1 Initialize flow  XXX_WF_DBA.init 20-07-09 20-07-09 COMPLETE COMPLETE
DBA_TYPE 2                  1 End              END             20-07-09 20-07-09 COMPLETE #NULL

Here we see that the version is still 1. If we would query the process_activities we would see that the instance_id’s are still the same as in the first item (item_key=’1’).

Of course we still need to see the item_attribute. So we start a new item.

begin
wf_engine.launchprocess(itemtype=>'DBA_TYPE',itemkey=>'3',process=>'DBA_MAIN_PROCESS');
end;

Now when we run the query above we see that it is indeed version 2

ITEM_TYP ITEM_KEY     VERSION DISPLAY_NAME     FUNCTION        BEGIN_DA END_DATE ACTIVITY ACTIVITY_RES
-------- ---------- --------- ---------------  --------------- -------- -------- -------- -----------
DBA_TYPE 3                  1 DBA Main process                 20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 3                  2 Start            START           20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 3                  2 Initialize flow  XXX_WF_DBA.init 20-07-09 20-07-09 COMPLETE COMPLETE
DBA_TYPE 3                  2 End              END             20-07-09 20-07-09 COMPLETE #NULL

Only the root process has kept it’s original version. The others are indeed version 2.

That would mean that a value has been assigned to the item_attribute. Let’s take a look:

select item_type
,      item_key
,      name
,      text_value
from   wf_item_attribute_values
where  item_type='DBA_TYPE'
and    item_key in ('2','3')
and    name='INSTANCE_NAME'

ITEM_TYP ITEM_KEY   NAME               TEXT_VALUE
-------- ---------- ------------------ --------------------------
DBA_TYPE 3          INSTANCE_NAME      CRMOTPO2

With that we end part 2 of this series. In the next part we are going to look at the workflow constructs.