Database Software

Hi,

- In Workflow Notification Message I am using an CLOB Document for building the
message Body

- This CLOB value is going to be SELECTed and initialized from a TABLE
(having CLOB column) during the document related procedure execution
NOTE: In order to select from a CLOB we need to issue FOR UPDATE
to have read consistency
(otherwise it will give LOB not locked for update error during notification display)

Here the issue I am facing is :

The same notification is going to be sent to Multiple users (thru Notification
to a Apps.Responsibility i.e. WF Role)

When USER1 opens the notification, the CLOB Table row gets a lock
(due to FOR UPDATE) and is not released until ROLLBACK/COMMIT
which will not happen as it is a workflow (if I put this Select FOR UPDATE
in a AUTONOMOUS transaction then it gives error as LOCATOR id cannot
spawn between transactions during the notification display)

When USER2/USER3 opens the same notification which he received being a member
of the Responsibility , the notificaiton screen gets hanged as it again
tries to issue SELECT FOR UPDATE and sees USER1 holds the lock.

My Requirement is:

When first time anyone user opens the notification, it will not find a record in table
so it builds the notification and stores the CLOB record into the table.

Next time onwards all the users should get the stored record from the table
(Read CLOB value from Table and show in the Notification Message Body)

Please help me in resolving the SELECT FOR UPDATE of a CLOB within Workflow
when multiple users need to issue without the need of a COMMIT/ROLLBACK

NOTE: Even I am only Reading the CLOB value from a Table, without
having any need of updating it, still I need to Issue FOR UPDATE
as per Oracle CLOB constraint.

thanks,
Shashi