I haven’t seen this particularly well documented, so I’m going to write about it.  TL;DR – there’s a table that handles the mapping between database columns to APEX reports.

I recently had the problem where I needed to find the equivalent to all_tab_columns in the database.  all_tab_columns is a table that stores information on tables that has stats about a lot of random stuff:

Frequently, this table is used as a source of information and not one that is directly queried upon a lot.  This table describes, among other things, useful information about dependencies and data model (easier to find instances where columns are used on views without directly using SQL Developer), column and table names (to cross reference), etc as well as a number of somewhat useless stats (looking at you, average column length).

There’s also a user_tab_columns that allows for pull on schema:

(Writer’s Note: while testing for these things, I ran into ‘SP2-0749: Cannot resolve circular path of synonym “user_tab_columns”‘, which apparently is also an extremely poorly documented error as well.  This is non-malicious, and resolved by rerunning the describe.  I assume it’s problematic only because my connection cut while running the query twice. A Google search on this yields precisely three results.  Maybe we can make the 4th!)

As one would expect, this is the same table as above but without the SYS tables/views. In fact this pretty much just has the owner’s tables/views because the database is smart enough to know what schema you’re in.   Either way, these are tremendously useful.

Meanwhile, in Frontendland, you have APEX Report Pages.  These interactive reports (IR for short) are built upon a query on your database.  Your report query has a lot of niches that a traditional SQL or PL/SQL cannot do, but it is nice being to quickly raise a page that end users can view.

Let’s assume we have a table called employees with five columns:

1. ID
2. DEPT_NO
3. NAME
4. SALARY
5. YEARS_WORKED

Using this, we can do a number of things.  For example:

• APEX can create an interactive report titled Senior Employees and sort on number of years with some conditional logic; employees here can be sorted into buckets of <5, 5-10, 10-20, 20-30 with appropriate titles: entry, senior, executive, and senior executive based on years worked.  On Apex, this interactive report might look like:
  • Table: Seniority
  • Columns: Name, Department Number, Seniority (Virtual Col. based on above logic)
• APEX can create a registry for departments.  We can generate three reports based on departments that are authorized for each department’s users:
  • E.g.: Accounting Registry, Sales Registry, Product Development Registry
  • HR would be given access to each of these authorizations
• APEX can create a tiered salary auditing system for parity’s sake – when handing out raises, managers can query on Salary and Years worked.

Let’s assume that Luke’s Board Game company has all of the above.  Each of these reports happen on different pages in APEX.  Thus, we have our database columns hacked and mashed into different tables that show up for the end users.  However, these aren’t displayed as what they are in the database.  However, APEX doesn’t really store anything, really.  It’s all done in the database, and APEX is just a front end handler.  How does APEX hack this together?

Queue a magic table: APEX_APPLICATION_PAGE_IR_COL.

As you can see, this table is broken hierarchically.  This table saves workspace, application ID, and application name, and loads all of these things when displaying them on the graphical interface for building on APEX.   All of the above changes would be stored in this table.

In the above scenarios

• APEX can create an interactive report titled Senior Employees and sort on number of years with some conditional logic; employees here can be sorted into buckets of <5, 5-10, 10-20, 20-30 with appropriate titles: entry, senior, executive, and senior executive based on years worked.  On Apex, this interactive report might look like:
  • Table: Seniority | Really not a ‘table’ to us technical folk.  This would be stored under Region Name, and the page would be stored here as well.  Each region (the region displaying this) has a unique ID and the columns themselves have a unique ID in this table.  
  • Columns: Name, Department Number, Seniority (Virtual Col. based on above logic) | This table is the table that stores the mapping between Department Number and dept_no is stored.  Note that this stores column aliases, so if you have virtual columns, they’re stored as the name you give them
• APEX can create a registry for departments.  We can generate three reports based on departments that are authorized for each department’s users: 
  • E.g.: Accounting Registry, Sales Registry, Product Development Registry | stores “accounting_registry” if you give it that alias.  This does NOT store the base column name.
  • HR would be given access to each of these authorizations | Slightly divorced from authorizations, but this table also stores the permissions for things like sorting, filtering, group bys, charting, and other interactive reporting features. 
• APEX can create a tiered salary auditing system for parity’s sake – when handing out raises, managers can query on Salary and Years worked. 

Additionally, the Component Signature column stores a lot of the deep-dive page-level information.

I will probably do a follow-up post on this, but this is a very powerful table to get to know.

 

-L

 

 

This was written using APEX 5.1 and Oracle SQLDeveloper 4.2 on Aug 14 2017.