Oracle Apex: APEX version of ALL_TAB_COLUMNS

1
votes

Is there a table that holds values of columns in APEX similar to all_tab_columns?

all_tab_columns shows column headers like they are in a database, but on APEX, a lot of these headers are formatted to be a bit more user-friendly in interactive reports. I wanted to find if there was a table like this that showed the following mappings:

Database --> APEX

Column Name: dept_no --> Department Number

My hunch is that this data is stored somewhere in a many-to-many mapping where on Interactive Report for a given page, department_no may be mapped to the string "Department Number" or, if filtered, this could be mapped to "Accounting Department Number" for example.

2
sqloracleoracle-apex

2 Answers

1
votes

Yes, everything defined in APEX is held in a table somewhere. For convenience, APEX also exposes these through various views: the view you want for Interactive Report columns is APEX_APPLICATION_PAGE_IR_COL and the columns of interest are:

  • APPLICATION_ID
  • PAGE_ID
  • COLUMN_ALIAS
  • REPORT_LABEL

You can find these views via the APEX Builder tool under Workspace Utilities -> Application Express Views. You can also find them via SQL tools - they belong to a schema called APEX_nnnnnn where nnnnnn represents the version of APEX you are using e.g. APEX_050100.

1
votes

Another way to locate these dictionary views is to use a query like this

select * from apex_dictionary where apex_view_name like '%IR%' and column_id = 0;

I use these views all the time

http://davidsgale.com/the-apex-data-dictionary/