Tuesday, February 2, 2016

Oracle Apex internals: template, source, conditional SQL storage

Abstract

While working with Apex I was building, backing up, customizing applications. I also had a few custom themes, theme-rebuilds. Now I need to update interfaces for 22 applications. Update includes custom CSS, JS'es, styles and HTML customizations for regions, region sources, templates, items, buttons. My applications contain hundreds of regions and items, its time to start digging Apex internals. Some of them can be found documented in forums, some of them I was not able to locate.

Warning

Any mistype or abuse may damage Apex IDE, usability, upgradability and your own application sources. Consider yourself warned.

General remarks

Flow_id - Apex application ID, consider always including in your query or at least limiting to a range below 4000.
Flow_step_id - application page ID.
Make database and application backups. At least copy some stuff to comments column.
If you have at least a couple applications most of the columns are self explanatory and follow same naming notation across all Apex tables.

Main Apex tables and their purpose

Apex schemas are named HTMLDB or APEX with trailing version number (examples: HTMLDB_020000, APEX_030200, APEX_040100, APEX_050000, dont recall if WEBDB had its own naming pattern). Tables are mainly starting with WWV_(FLOW) prefix.

WWV_FLOW_STEP_ITEMS - page items (like P101_USERNAME), I mainly use this table for mass item template changes (ITEM_FIELD_TEMPLATE), label alignment change (LABEL_ALIGNMENT), some very specific template changes (I use simple items to display clickable icons). My personally favorite field is TAG_ATTRIBUTES - custom HTML, CSS, Javascript code input.

WWV_FLOW_REGION_REPORT_COLUMN - report-type region column description and customization. I mainly use report columns for links with icons (COLUMN_LINKTEXT), other uses may include massive label or value alignment changes.

WWV_FLOW_ROW_TEMPLATES - report templates (not regions), I use them for massive HTML/CSS changes, class updates. Almost every column in this table is obvious and usable.

WWV_FLOW_STEP_BUTTONS - page buttons (obviously). I mainly use this table when I change button template (remember that application template ID's are different - I change one item in Apex, then update the rest based on changes in table). Column BUTTON_IMAGE. Can also be used for massive button alignment, condition or image changes.

WWV_FLOW_PAGE_PLUGS - hardest one to find or come up with, but also one of the most important ones - Region definition and sources. I do have some conditional SQL which displays certain icons (URL from webserver storage), I use PLUG_SOURCE column for mass updates when changing design templates. Can be used for condition, template or title changes.

WWV_FLOW_WORKSHEET_COLUMNS - this one may also be tricky to locate - interactive report region column desciptions. I mainly use this table for icon/image changes, mass link or interactive report column alignment changes.