A script-enabled browser is required for this page to function properly.

Onetime Where Property

Description

Specifies a one time where clause for the block, overriding any previous ONETIME_WHERE clause. The ONETIME_WHERE clause can include references to global variables, forms parameters, and item values, specified with standard bind variable syntax.

Any WHERE condition specified in the ONETIME_WHERE clause is appended to the query for the next execution only, i.e is a throw-away addition to the WHERE clause. It can be used in place of a Pre-Query trigger to temporarily amend the where clause. It has the advantage over additions to the where clause made in the Pre-Query trigger in that bind variable references (Items, Global
variables and System Variables) can be made in the additional WHERE clause. This behavior premotes cursor reuse and reduces database resource usage.

Applies to form, block

Set Oracle Forms, programmatically

Refer to Built-in

GET_BLOCK_PROPERTY

SET_BLOCK_PROPERTY

Required/Optional optional

Usage Notes

  1. Enclose in single quotes.
  2. The WHERE reserved word is optional.

Onetime Where property examples

Allowing this property to be set for a particular block from any where before a query is executed makes it easier and more user friendly. For example, in a multiblock form, you can set the ONETIME_WHERE clause of a block from a different block followed by an execute-query of the block:

Block1
When-button-pressed
Set_Block_Property('Block2', ONETIME_WHERE, '<condition>');

Go_block('Block2');

Execute_Query();

In this case you don't have to worry about having a Pre-Query trigger for Block2 and having to set the Set_Block_Property.

For an Emp/Dept form with a default-where clause for the Emp block set to 'empno > 7800' and a push button, do the following:

When-Button-Pressed:
Set_Block_Property('emp', ONETIME_WHERE, 'deptno <= :dept.deptno');

If a query is performed after pushing the button, the generated query would like the following using the :SYSTEM.LAST_QUERY:

Select empno, ename, ... From Emp Where empno> 7800 And depnto <= 10 (in this case the deptno of the DEPT block is 10).

Get_Block_Property('emp', DEFAULT_WHERE) would return:

empno > 7800

Get_Block_Property('emp', ONETIME_WHERE) would return:

deptno <= :dept.deptno

If a new query is performed, the generated query would be like:

Select empno, ename, ... From Emp Where empno> 7800

Get_Block_Property('emp', ONETIME_WHERE) would return a NULL string in this case.