![Another VPD challenge with our Oracle Designer generated Web Forms application: No Data Found when synchronizing server derived values 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Another VPD challenge with our Oracle Designer generated Web Forms application: No Data Found when synchronizing server derived values 1")
Virtual Private Database is a quite powerful concept. It lets you set up a mechanism in the database that dynamically appends additional filter-conditions to the queries performed by end users and applications against tables that are subject to so called VPD Policies. The dynamic part of VPD lies in the fact that these policies can make use of context-settings. These settings can range from the name of the currently active user or the role of the user to the specific module or task that is currently performed.
We make pretty heavy use of VPD in our current WebForms 10g, Designer 10g, RDBMS 10g project. I have written a few earlier posts to discuss some of the challenges we have faced and some of the solutions we implemented. This one presents our latest challenge. It goes like this:
We have a Form that is used to Maintain Request Forms. Such Request Forms can only be manipulated by users that have mutation privileges – defined in an application table – and only while the Request Form has the status In Progress. As soon as the status is changed to either Submitted or Accepted, Denied or Withdrawn, the Request Form can no longer be edited.
In an ON-LOGON trigger, we set up a context with the userid of the current user that we derive from our own table of application users, based on the Oracle Database User Name. When we start any Form in the application, it will set up another value in the Application Context by calling a database package informing it on the form name and possibly the current mode. We have set up VPD Policies that govern the data made available from any table given the circumstances.
This includes a VPD Policiy on the table with Request Forms. This policy will only return Request Forms with Status In Progress and whith mutation privileges for the current user when the Form Maintain Request Forms is running. When the Form Browse Request Forms is running, all Request Forms are returned. So far so good. It works quite allright. However, when the status of the Request Form record is changed from In Progress to Submitted, we have an issue: immediately after saving the change, we get an Oracle 1403 No Data Found error. For making an Update!
Upon closer investigation, we found out the issue: the table definition for Request Forms in Oracle Designer has several Server Derived Columns. The Designer Form Generator has generated a Synchronization Program Unit in the Form that is invoked from the post-update trigger to retrieve the values of these columns immediately following the update. So after the Update has changed the status of the Request Form from In Progress to Submitted, the Form will select the request form to find out about the values of the server derived columns. However, our VPD policy hits relentlessly and instantaneously: this form for editing Request Forms can only retrieve Request Forms with status In Progress. And the have just changed the status of the Request Form that we are no trying to retrieve in this generated piece of synchronization logic. The synchronization code does not have an WHEN NO_DATA_FOUND exception handler – as I have been taught you should always have – and now VPD lightning strikes.
We are not certain yet on the best way to fix this. We are not even entirely sure about what we would like to see: should the Request Form record we have just updated vanish immediately from the Form? That might be confusing to the end-user. On the other hand, we do not want him changing a record with status Submitted, so he should not see the record anymore, since that would be confusing too. Simply handling the NO-DATA-FOUND exception can be done by either bulk-manipulatinig all synchronization program units generated by Oracle Designer through a JDAPI program that we can run on all generated forms. We can also add logic to the POST-INSERT and POST-UPDATE triggers to either handle the exception or set some sort of special synchronization mode in the application context that will temporarily relax the VPD policy.
We previously ran into problems with VPD DML policies in combination with Forms – see post Violation of VPD Policy on DML raises FRM-40654 :Record Has Been Changed by Another User error in Oracle Forms: the Form expects an update of one record, finds that zero records were updated and raise an error. So it would take me from the rain into the drip…. (we used to have VPD DML policies but removed them because of this error; currently we use CDM RuleFrame business rules to enforce DML rules).
You can have different policies for SELECT, INSERT, UPDATE and DELETE. Why not make your VPD rules so that you can still SELECT the record, but no longer UPDATE it. Can you keep it so simple that the VPD policy will allow SELECT request with all statusses, have the restriction on In Progress requests in the WHERE clause of the block in Forms. You can have a more restricted UPDATE (and DELETE or INSERT?) rule that will not allow DML on anything but In Progress requests.
PS. This will also be tricky to handle in Forms, since it will just try to UPDATE the record. The VPD policy will add a predicate to the UPDATE statement resulting in 0 rows being update. This is (unfortunately) not checked by Oracle Forms, so it will just inform you of a succesfull update of 1 record.
Hi Lucas,
There is another option: overriding the generated synch-code either with a if..then construction which synchronises the record only when allowed by the policies (no_data_found wouldn’t occur) or adding an proper exception handling (i.e. when no_data_found then null;) as you propose, but without the neccety to now the JDAPI-framework.
On the other hand, the disadvantage of overriding generated code in my opinion is always that you should check that code for changes. This is not easy as you must check if no new server derived items are added on the views/tables that are used in the module component. And manually add them to the synch-code.