How to repeat query in Oracle Forms upon dynamically changing ORDER_BY clause?

Question

I have an Oracle Forms 6i form with a data block that consists of several columns.

------------------------------------------------------------------------------
| FIRST_NAME | LAST_NAME | DEPARTMENT | BIRTH_DATE | JOIN_DATE | RETIRE_DATE |
------------------------------------------------------------------------------
|            |           |            |            |           |             |
|            |           |            |            |           |             |
|            |           |            |            |           |             |
|            |           |            |            |           |             |
------------------------------------------------------------------------------

The user can press F7 (to Enter in Query Mode, for example, he/she types JOH% in the first_name and H% in the DEPARTMENT field) , then F8 to execute the query and see the results. In this example, a list of all employees with their last name starting with JOH and working in any department starting with H will be listed. Here is a sample output of that query

------------------------------------------------------------------------------
| FIRST_NAME | LAST_NAME | DEPARTMENT | BIRTH_DATE | JOIN_DATE | RETIRE_DATE |
------------------------------------------------------------------------------
| MIKE       | JOHN      | HUMAN RES. | 05-MAY-82  | 02-FEB-95 |             |
| BEN        | JOHNATHAN | HOUSING    | 23-APR-76  | 16-AUG-98 |             |
| SMITH      | JOHN      | HOUSING    | 11-DEC-78  | 30-JUL-91 |             |
|            |           |            |            |           |             |
------------------------------------------------------------------------------

I then added a small button on top of each column to allow the user to sort the data by the desired column, by executing WHEN-BUTTON-PRESSED trigger:

set_block_property('dept', order_by, 'first_name desc');

The good news is that the ORDER_BY does change. The bad news is that the user never notice the change because he/she will need to do another query and execute to see the output ordered by the column they selected. In other words, user will only notice the change in the next query he/she will execute.

I tried to automatically execute the query upon changing the ORDER_BY clause like this:

set_block_property('dept', order_by, 'first_name desc');
go_block('EMPLOYEE');
do_key('EXECUTE_QUERY');
/* EXECUTE_QUERY  -- same thing */

but what happens is that all data from the table is selected, ignoring the criteria that the user has initially set during the query mode entry.

I also searched for a solution to this problem and most of them deal with SYSTEM.LAST_QUERY and default_where. The problem is, last_query can refer to a different block from a different form, that is not valid on the currently displayed data bloc.

How can do the following in just one button press:

1- Change the ORDER_BY clause of the currently active datablock

and: 2- Execute the last query that the user has executed, using the same criteria that was set?

Any help will be highly appreciated.

Alen Oblak Alen Oblak · Accepted Answer · 2013-01-14T08:42:49

You can get the last query of the block with get_block_property built-in function:

GET_BLOCK_PROPERTY('EMPLOYEE', LAST_QUERY);

How to repeat query in Oracle Forms upon dynamically changing ORDER_BY clause?

2 Answers