Column Sorting in Oracle Forms

Posted by Tobias on September 10, 2010 00:00 CEST Updated on April 9, 2013 19:28 CEST 3 Comments Add a comment

Contents

Sorting a table by clicking the header in a certain column is very natural in almost any program. For some reason, support for this is absent from Oracle Forms. Luckily this is quite easy to accomplish using a bit of Java magic. It’s important to note that the GUI in Oracle Forms is written in Java and the same rules apply. Using this method you can enable column sorting using a single PL/SQL call per column.

Fig. 1
Figure 1. Column sorting in Oracle Forms

So how does this work? It would be much easier if the column headers or prompts themselves were instances of some Java class. Unfortunately, this is not the case. However, the text fields are instances of VTextField and you can address them individually so we first need to create a class which extends VTextField and set the implementation class of the columns to our own class. We’ll call this class ‘TextItem’ and its main purpose is to create an instance of a second class which will draw an arrow and also change color when we hover the mouse over it. We’ll call this second class ‘FormsColumnHeader’ and once instantiated; it will position itself above the top text field.

Pluggable Java components in Forms 11 has the ability to send events back to Forms, in 10g however we need to solve this in another way. The solution is to have a bean handle this. So let’s create a bean and add it to our form, we’ll call this ‘RelayBean’. When our TextItem class has created an instance of FormsColumnHeader its second task is to notify the RelayBean. This is necessary so the RelayBean can start listening for events from the FormsColumnHeader. Once an event is raised, the RelayBean can notify Forms.

Event Flow

  1. Our form calls CREATE_HEADER for the top TextItem in each column
  2. TextItem creates FormsColumnHeader
  3. TextItem notifies RelayBean
  4. RelayBean starts listening for events
  5. User clicks the header
  6. FormsColumnHeader raises an event
  7. RelayBean notifies Forms
  8. The SELECT statement of the block is changed
  9. The new query is executed

Custom Properties on TextItem

CREATE_HEADER: Call this to create a header above the text field.
Example:

Set_Custom_Property(‘EMP.ENAME’, 1, ‘CREATE_HEADER’, ‘EMP.ENAME’);

SORT_MODE: Call this to programmatically set the sorting mode. This will only affect the orientation of the arrow and remove the arrow from other columns.
Example:

Set_Custom_Property(‘EMP.ENAME’, 1, ‘SORT_MODE’, ‘DESC’);

Custom Properties on RelayBean

DEBUG: Enable or disable debugging messages.
Example:

Set_Custom_Property(‘BLOCK1.BEAN_AREA1’, 1,’DEBUG’, ‘TRUE’);

HEADER_LISTEN: Makes the bean look for new headers. There is really no need to call this unless you’re creating headers dynamically.
Example:

Set_Custome_Property(‘BLOCK1.BEAN_AREA1’, 1, ‘HEADER_LISTEN’, ‘’);

HEADER_CLICKED: Event which is raised when a user clicks a header.
Example: See included Forms example (WHEN-CUSTOM-ITEM-EVENT).

EVENT_VALUES: Property holding the event variables.
Example: See included Forms example (WHEN-CUSTOM-ITEM-EVENT).

Installation

Copy the FormsColumnSort.jar file to your ‘forms\java’ folder then add it to your ‘formsweb.cfg’ file.

Compile Sources

To compile the sources, edit the location of your JDK and frmall.jar in the makefile (make.cmd) then execute it. It is also possible to create a JDeveloper project and add the source files.

Version History

Download

Download binaries and sources v1.1.0 (35KB)

Download binaries and sources v1.0.0 (27KB)

Subversion repository at code.google.com

Post your comment

3 Comments (newest first)

Posted by Senthilnathan on October 24, 2016 10:55 CEST

Hi thanks but suppose we did sorting in form then open one more if we do sorting it is throwing error. This file again going 1st screen and giving such a block not there. Actually we are in 2nd screen it should not go to 1st screen.

Posted by Chris D. on March 27, 2014 19:13 CET

Great bean, and thanks for making it available. I'm trying to implement it in two different blocks on the same form, and am running into an issue where the form doesn't seem to understand which data block initiated the event. Do you have recommendations or instructions for implementing this in multiple blocks?

Posted by Esteban on August 27, 2013 16:39 CEST

This is awesome, thank you so much.

Read More