Oracle APEX_APPLICATION synonyms are worth exploring. As is often said, APEX is written in APEX. That means means that any APEX developer can undertake using some of the tools that the APEX development teams left for us in this tool kit. Let’s take a look and find a few uses for the Oracle public synonyms that lay at our fingertips.

Public Synonyms

The first step is finding them. With SQL Developer, I use filters on Public Synonyms. In the illustration, I show a filter for NAME LIKE APEX_%

Oracle Public Synonyms

Best Targets

Many (or most) of these synonyms are views pulling data from multiple tables in the APEX home schema. These are relatively safe to explore because a developer can not update tables through these views.

select * from apex_applications;
select * from apex_application_tables;

What are the best targets? Start with familiar and identifiable data such as all applications, then take a look at the pages for applications.

The APEX Team Development data are available here as well:

  • APEX_TEAM_BUGS
  • APEX_TEAM_FEATURES
  • APEX_TEAM_FEEDBACK
  • APEX_TEAM_FEEDBACK_FOLLOWUP
  • APEX_TEAM_MILESTONES
  • APEX_TEAM_TODOS

How, Why, Where to Use?

There are ample opportunities to take advantage of the data within the tables. You can use query responses within APEX to regulate behavior of an application. Alternatively, you can query the data externally to evaluate the consistency of development work performed. The ambitious amongst us might plot to put together their own “advisor” tool set or capture activity logs as part of a quality assurance process.

Synonyms in an Application

A few examples to kick things off… Let’s envision a region defined on an Oracle APEX Global Page. I want it to display only when the page mode is “Normal”. I don’t want to waste space on dialogue boxes.

With my region, I add a “Rows Returned” condition with the following query:

select page_id
from apex_application_pages
where application_id = :APP_ID
and page_id = :APP_PAGE_ID
and page_mode like 'Normal%'

Ad-Hoc Advisor

If a team wants to explore the consistency of their work, a few queries may help. I have used it to find old button CSS from APEX 4. In the example below, I want to discover how many pages do not have help text:

select 
	page_id,
	page_name,
	page_title
from apex_application_pages 
where application_id = 101 
	and (
	help_text is null or
	help_text like 'No help is available for this page.'
			);

You can do the same for page access protection, pages requiring authentication, or button styles, or page item styles. Pretty much anything within your application, you can see if your approach has been consistent.

Are all of my cancel buttons grey?

Are all of my save buttons hot?

Here is an example for seeing how complete the help text is for items…

select
	page_id,
	page_name,
	item_name,
	display_as,
	label,
	item_help_text
from apex_application_Page_items
where application_id = 101
	and display_as <> 'Hidden'
	and display_as <> 'Display Only';

Quality Assurance Tools

The Oracle APEX development team provides us a view called APEX_DEVELOPER_ACTIVITY_LOG. This view provides insights on all development efforts within APEX. I have spent month and months exploring these data with the question: How do I improve the quality management process within APEX. Part of the answer sits here.

If I want to link:

  • Applications
  • to Releases
  • to Bug/Features
  • to coding efforts

Then part of my answer is in this view: APEX_DEVELOPER_ACTIVITY_LOG. (The other part exists as database changes that I can get from database views). Like a plastic shovel on a beach, I can now scoop all of the changes, additions, deletions made by developers into a table. I can link that activity with bugs or release data and the developer responsible. I can go further and use these data to develop a test plan. I pack these granular bits into my bucket and tip out structures that assist a development team. It is not just a change log. It is not just Big Brother watching, or another token in a Blame-Game of who buggered what. It become a means of linking building and supporting an application with the release notes, the change log, the test plan. It is a building block in robust software development.

At Storm Petrel, we’ve started, but that is it.

What Next?

Please ask me what would make these synonyms/views more powerful?

First, to really work with these data we do need a primary key, that unique think that allows use to link local data to these behind-the-scenes data. While some data are inherently unique, some just are not. The APEX_DEVELOPER_ACTIVITY_LOG is one such view. If you copy data from this view to a local table, the process of linking back is cumbersome.

Second, I have an easy time envisioning a packaged application that brings Team Development tools and developer activities together such that the application and its related data are portable. We lost such stuff when we moved from 11g to 12c. Team Development data was lost when we moved from 4.x to 5.x. And we are developers, we “improving” stuff (whether or not it actually improved!). We’re looking at this potential from the edge. APEX is a awesome tool.

-qed