The APEX data dictionary

February 26, 2025

2–3 minutes

Anyone who has used APEX will know that in addition to the Builder UI, there is a comprehensive data dictionary for APEX-prefixed views to expose all the metadata associated with APEX applications and workspaces.

SQL> select view_name
  2  from dba_views
  3  where owner = 'APEX_240200'
  4  and view_name like 'APEX%'
  5  order by 1;

VIEW_NAME
--------------------------------------------------------------------------------------------------------------------------------
APEX_APPLICATIONS
APEX_APPLICATION_ALL_AUTH
APEX_APPLICATION_AUTH
APEX_APPLICATION_AUTHORIZATION
APEX_APPLICATION_BC_ENTRIES
APEX_APPLICATION_BREADCRUMBS
APEX_APPLICATION_BUILD_OPTIONS
APEX_APPLICATION_CACHING
APEX_APPLICATION_COMPUTATIONS
APEX_APPLICATION_GROUPS
...
...
APEX_WORKSPACE_SQL_SCRIPTS
APEX_WORKSPACE_STATIC_FILES
APEX_WORKSPACE_UI_TYPES
APEX_WORKSPACE_VECTOR_PROVIDER

But if you go searching in the standard APEX documentation set
and list all of the books

you’ll notice that we do not have a book dedicated as a reference to these data dictionary views.

However, the views themselves are commented rigorously within the database itself, including comments for each column in the view as well.

SQL> select table_name, comments
  2  from   dba_tab_comments
  3  where  owner = 'APEX_240200'
  4  and    table_name like 'APEX%';

TABLE_NAME                               COMMENTS
---------------------------------------- ----------------------------------------------------------------------------------------------------
APEX_APPLICATIONS                        Applications defined in the current workspace or database user.
APEX_APPLICATION_ALL_AUTH                All authorization schemes for all components by Application
APEX_APPLICATION_AUTH                    Identifies the available Authentication Schemes defined for an Application
APEX_APPLICATION_AUTHORIZATION           Identifies Authorization Schemes which can be applied at the application, page or component level

so I figured “Why not make my own reference book?”

So here’s a little SQL routine that will generate an simple to navigate HTML reference guide for the APEX dictionary views. You can get it from my github repo. (I suspect WordPress will garble this SQL because of the embedded tags, so get a clean version from the git repo)


set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set pages 0
set lines 2000
set define off
set trimspool on
set verify off
set feedback off
col seq nopri
set sqlterminator off
with comment_data as (
select v.view_name, tc.column_name, c.comments, tc.column_id, count(*) over ( partition by v.view_name) num_cols, -1 num_views
from dba_views v,
     dba_tab_columns tc,
     dba_col_comments c
where v.owner = 'APEX_240200'
and v.view_name like 'APEX%'
and v.owner = tc.owner
and v.view_name = tc.table_name
and tc.owner = c.owner
and tc.table_name = c.table_name
and tc.column_name = c.column_name
union all
select v.view_name, null, c.comments, 0 column_id, -1 num_cols, count(*) over ( ) num_views
from dba_views v,
     dba_tab_comments c
where v.owner = 'APEX_240200'
and v.view_name like 'APEX%'
and v.owner = c.owner
and v.view_name = c.table_name
)
select '<tr><td width="30%"><a href="#'||view_name||'">'||view_name||'</a></td><td>'||comments||'</td></tr>'||
       case when rownum = num_views then '</table>' end,
       '01-'||rpad(view_name,200)||'00000' seq
from comment_data
where column_id = 0
union all
select 
  case when column_id = 0 then '<h2 id="'||view_name||'">'||view_name||'</h2><p>'||comments||'&nbsp;<a href="#apextop">Back to top</a></p><table>' end||
  case when column_id > 0 then '<tr><td width="30%">'||column_name||'</td><td>'||comments||'</td></tr>' end||
  case when column_id = num_cols then '</table>' end,
  '02-'||rpad(view_name,200)||lpad(column_id,5,'0') seq
from comment_data
order by seq

spool apex_docs.html
pro &lt./style&gt.

pro &lt./head&gt.

pro 

pro &lt.body&gt.

pro &lt.h1 id="apextop"&gt.APEX dictionary views&lt./h1&gt.

pro &lt.table&gt.

/

pro &lt./body&gt.&lt./html&gt.

spool off
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
clear seq clear
set termout on

The resultant HTML document will list each APEX view at the top of the document with its dictionary commentary, and you can click on any view to jump to the column for that view.

You can then re-run the script each time you either patch or upgrade APEX.

Pro Tip: You should be just as rigorous with your table and column comments in your own database schemas. Make life easier for the next person who comes along to use your database model.

5 responses to “The APEX data dictionary”

John Nagtzaam

February 26, 2025 at 3:54 pm

Nice. But what should be the values for the substitution variables you are using in the script?

Reply
1. Connor McDonald
  
  February 26, 2025 at 4:23 pm
  
  Sub variables? There are none.
  The ampersands are for the html and the “set define off” in the script should stop any reference to them
  
  Reply
Zoran Tica

February 27, 2025 at 3:08 pm

Good one!

But as a source of data You may use APEX_DICTIONARY view, which also returns APEX view names, columns and comments. This view requires no additional higher grants like DBA_xxxxxx views do.

SELECT *
FROM apex_dictionary

Reply
1. Connor McDonald
  
  February 28, 2025 at 6:49 am
  
  Filed under “TIL”
  
  Reply
Zoran Tica

February 27, 2025 at 3:37 pm

I wrote a following SELECT statement, which is using APEX_DICTIONARY view and I think it returns the same data as Your SELECT, so it can be easily integrated in Your code.

SELECT apex_view_name as view_name, CASE WHEN column_id > 0 THEN column_name ELSE null END as column_name, comments, column_id, CASE WHEN column_id > 0 THEN count(*) over ( partition by apex_view_name) ELSE -1 END as num_cols, CASE WHEN column_id = 0 THEN count(distinct apex_view_name) over () ELSE -1 END as num_viewsFROM apex_dictionary;

The only difference I noticed is that Your SELECT is returning a few views more. I noticed there are some APEX views, which are not included in apex_dictionary view, but I’m not sure why. That’s the question for an APEX development team…

Reply