August 03, 2007

Set Record-Status to Query after a POST-QUERY

In a block with a POST-QUERY-trigger you often have the problem, that the Record-Status changes to CHANGED, if non-basetable-items were changed through the POST-QUERY.

Setting the recordstatus back to QUERY is a good method to solve this problem. First create a procedure for setting the record-status to QUERY:


PROCEDURE Set_Record_Query_Status IS
BEGIN
Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
STATUS,
QUERY_STATUS);
END;

then use it in your POST-QUERY-triggers:

BEGIN
SELECT someColumns
INTO :myBlock.nonBasetable_Item
FROM myTable
WHERE someFilter;

Set_Record_Query_Status;
END;

After resetting each record to QUERY-Status you don't have problems with the fetched data in this block.

Important: If the POST-QUERY changes Basetable-Items in the record you create record-locks, if the block-locking is Immediate. This problem can be solved:


BEGIN
Set_Block_Property ('myBlock', LOCKING_MODE, Delayed);

SELECT someColumns
INTO :myBlock.nonBasetable_Item
FROM myTable
WHERE someFilter;

Set_Block_Property ('myBlock', LOCKING_MODE, Immediate);

Set_Record_Query_Status;
END;



try it
Gerd

11 comments:

Patrick Wolf said...

Hi Gerd,

but be warned that Forms already did the locking of the record in the background. So you are just fighting the symptoms of the problems. Just open two runforms and query the same data, I'm sure in the second one you will get errors.

If I remember correct you have to do a
SET_ITEM_PROPERTY('XXX', LOCK_RECORD_ON_CHANGE, PROPERTY_FALSE);
do-your-assignment;
SET_ITEM_PROPERTY('XXX', LOCK_RECORD_ON_CHANGE, PROPERTY_TRUE);
Set_Record_Query_Status;

to avoid the locking.

Greetings
Patrick

Gerd Volberg said...

thx for the hint. But lock_record_on_change is per default FALSE and so it can't help us here.

The block-locking-mode is useful:

SET_BLOCK_PROPERTY('XXX', LOCKING_MODE, Delayed);
do-your-assignment;
SET_BLOCK_PROPERTY('XXX', LOCKING_MODE, Immediate);
Set_Record_Query_Status;

Anonymous said...

Hi,
I have a simmilar situation here.
Am using a form which has 2 non-DB fields on the canvas and two DB fields on null canvas. While fetching the record I want to show derived values in Non-DB fields based on DB records. So i used Post query.
Now Every time it ask me 'Do you want to save your records?'every time when i hit F4.
Also If i made a function with function parameter Query_only = 'Y' and hit Ctl+F11 it says you cannot edit this record. and stuck on those two fields.
Am using Set_Record_Query_Status in post_query and still facing the same issue. Do you have any solution here?
Thanks,
Adi

Gerd Volberg said...

best way to discuss such non-trivial problems is in the forms-section of oracle's OTN. So others can learn from problem and the solutions we found too.

http://forums.oracle.com/forums/forum.jspa?forumID=82

come join us at OTN
Gerd

Binuraj said...

Hi Gerd,

Thanks for this useful article.

Regards
Binuraj

Anonymous said...

Hello Gerd,
your hint was useful for me too.
Thanks a lot.

Adam

bakee said...

Hi Gerd !!

Your really help me :D

THXANKS !!

Muhammad Ishaque Attari said...

very nice, really helped me

Unknown said...

Hi
Thanks for the info. It helped a lot.

God bless!

Halo said...

Thank you for the post.

Halo said...

Hi Gerd,

Thank you very much.

Halil