Querying Data in Oracle Forms vs APEX

When creating a Data Block on an Oracle Form Canvas, the developer can create a multi-line view of the data which displays a defined number of rows to provide a view of the data for the users.

The native behaviour of this Data Block is to provide enter query and execute query modes. These are accessed through the default menu, function keyboard shortcuts or the developer can create buttons on screen to provide the same functionality.

Users are able to provide search criteria to filter the results to locate certain records however they must be aware of how to use wildcard searches are also where to type them in.

The user is able to filter the results however they are unable to customise the report any further and any changes the user requires must be submitted to IT as a change request.

In comparison, Oracle Application Express (APEX) provides a component called Interactive Reports which allows the user to use all the same searches as Oracle Forms plus Advanced Searching. These options include:

• A search bar to filter results where any column contains the search terms
• A search bar option to filter results where a selected column contains the search terms
• Advanced Filters to filter results where a column: Is Like, Is Null, Contains, Between, Matches a Regular Expression and more.

Not only does an Interactive Report provide filters but it allows the user to customise the report to modify a host of features such as:

• Change the number of rows returns
• Hide/Show columns
• Add a chart
• Pivot the data
• Highlight record under certain criteria
• Add Group By Reports
• Control Break the data and add new Computed Columns
• Save the report as a private or alternate report
• Download to PDF or a variety of formats
• View the data as of x minutes ago
• And lots more

All these settings can be added or removed by the developer; for example a developer could decide that this report cannot be downloaded and remove this option.

In the accompanying video, I demonstrate both the Forms Data-Block approach using wildcard searches and the APEX approach using an Interactive Report on the same table.

In conclusion; an Oracle Form provides a basic search and filter option using wildcard searches in the first row of the search results. In comparison, APEX provides the same basic searches, plus advanced searching, plus much more with report customisation.

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.