Some examples of bulk changes to the apex dictionary tables

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I thought it could be useful to show how to hack certain apex internal tables in order to perform bulk changes of certain elements, especially when such elements are repetitive ones, like region titles, item labels or move stuff around, for example relocating the content of a region into the page level help text.

Warning: these queries update apex internal tables, it is strongly recommended that you do not run them in a production environment and in any case it is highly advisable to take a backup of the whole database before attempting any of such methods!

You'll need either SYSDBA privileges or the possibility to logon as FLOWS_XXXXXX user (which normally isn't possible because the account is locked) where XXXXXX stands for the applicable release of Apex, i.e. 030100 for the currently available 3.1.
The scripts given below have been run on version 3.1.2, prior or future releases might be incompatible.
Needless to say, but just to be clear, i give no explicit warranty about the execution of these statements, therefore you are executing them entirely under your responsibility.

It is also strongly recommended that you perform the queries from SQL Developer, SQL*Plus or other analogous SQL client program and be sure to turn off autocommit, so that you have the time to check if the statement updated the expected records or not before committing the changes.

I find invaluable the help of virtual machines for development environments. The latest versions allow you take various snapshots and easily revert the VM to the state before an important change, just in case something went terribly wrong.

Last but not least, if the application being updated has been translated into other languages, it is recommended that you export XLIFF files beforehand, then seed all mapped applications, apply saved XLIFF files and publish.

So, let's begin with the bulk update of region titles, something extremely useful in case of an application containing many pages with the same region.
You can modify the sample statement below where I'm replacing the title of each and every region called "information" with "help" inside application number 11001.

(don't forget to change the application id, aka the flow_id for your application and
be sure to have read and understood the consequences of this query i gave above!):

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_PAGE_PLUGS
set PLUG_NAME='help'
where FLOW_ID = 11001
and PLUG_NAME='information';

Thereafter i suggest you to check out the result of the update by querying the corresponding apex dictionary view:

select application_id, page_id, region_name
from apex_application_page_regions
where application_id = 11001
and region_name = 'help'
order by page_id;

If everything is ok, you can proceed with the COMMIT;

A similar statement can be used to switch off a region without actually removing it from the repository:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_PAGE_PLUGS
set PLUG_DISPLAY_CONDITION_TYPE='NEVER'
where FLOW_ID = 11001
and PLUG_NAME='help';

A third example, slightly more elaborated, could be the relocation of the content of a region called "help" to the page level help attribute, as follows:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_STEPS p
set p.help_text = (
select r.plug_source
from FLOWS_030100.WWV_FLOW_PAGE_PLUGS r
where r.flow_id = p.flow_id
and r.page_id = p.id
and r.plug_name = 'help')
where flow_id = 11001
and ID > 0;

This technique can be useful to consolidate the help text that for some reason was initially entered in a page region and display it in the same place by creating a region in page zero. At that point it's possible to populate the region dynamically by retrieving the content from the apex dictionary using view APEX_APPLICATION_PAGES.
In this fashion with a single region definition, one can publish the help text across all the desired pages, excluding or including them conditionally.

The downside of this approach is in that currently apex dictionary doesn't support translated applications so, if the primary language is English, the text will always appear in English regardless of the target language. Hopefully this problem will be fixed in a future release.

Now, suppose you are happy with the new catch-all page zero based help region and you wish to get rid of the old disabled regions: how to delete them from the repository in one shot?

If you think about it for a moment, this is going to be not as trivial as the previous update process because a region can contain items and buttons, so you must decide if you want to drop the related items as well or not.

First of all, it can be a good idea to check out if any region items actually exist:

-- items (including button items)
select *
from flows_030100.wwv_flow_step_items i
where
flow_id = 11001
and flow_step_id > 0
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and plug_name = 'help');

-- (buttons)
select *
from flows_030100.wwv_flow_step_buttons b
where
flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and plug_name = 'help');

So, if items exist, you might want to relocate them first to other specific regions, but you can also update the column ITEM_PLUG_ID (for items and button items) or BUTTON_PLUG_ID (for buttons) to NULL, which changes the "status" of the item from region level item to page level item:

-- moves any items belonging to the 'help' region for any page > 0 to page level
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update flows_030100.wwv_flow_step_items i
set item_plug_id = null
where flow_id = 11001
and flow_step_id > 0
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

Relocating region buttons to page level:

-- moves any buttons belonging to the 'help' region for any page > 0 to page level
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update flows_030100.wwv_flow_step_buttons b
set button_plug_id = null
where flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and r.plug_name = 'help');

Deleting items from regions titled 'help'...

-- delete the items right away (but preserving button items)
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_items i
where flow_id = 11001
and flow_step_id > 0
and display_as != 'BUTTON'
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

Deleting buttons of all types from regions named 'help'...

-- delete the buttons right away
-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_items i
where flow_id = 11001
and flow_step_id > 0
and display_as = 'BUTTON'
and item_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = i.flow_id
and r.plug_name = 'help');

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete from flows_030100.wwv_flow_step_buttons b
where flow_id = 11001
and flow_step_id > 0
and button_plug_id in (
select r.id
from flows_030100.wwv_flow_page_plugs r
where r.flow_id = b.flow_id
and r.plug_name = 'help');

finally, deleting 'help' regions from any page above zero:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
delete
from wwv_flow_page_plugs
where flow_id = 11001
and page_id > 0
and plug_name = 'help';

As an additional tip, here is the statement for changing a specific label template across an entire application. In this case i wanted to change from Optional Label with Help to Optional:

-- this SQL statement is potentially dangerous for your Apex installation
-- do not run it unless you are 100% sure that you can revert the DB to
-- its previous state!
update FLOWS_030100.WWV_FLOW_STEP_ITEMS i
set i.ITEM_FIELD_TEMPLATE =
(select t.id
from   FLOWS_030100.wwv_flow_field_templates t
where  t.flow_id = i.flow_id
and    t.template_name = 'Optional')
where i.FLOW_ID = 11001
and  to_char(i.ITEM_FIELD_TEMPLATE) = (
select t.id
from FLOWS_030100.wwv_flow_field_templates t
where t.flow_id = i.flow_id
and t.template_name = 'Optional Label with Help');

Before committing the changes you may want to see if the result is what you expected:

select NAME, FLOW_STEP_ID,
(select t.template_name
from   FLOWS_030100.wwv_flow_field_templates t
where  t.id = to_char(i.ITEM_FIELD_TEMPLATE)
and    t.flow_id = i.flow_id) as TEMPLATE_NAME
from FLOWS_030100.WWV_FLOW_STEP_ITEMS i
where i.FLOW_ID = 11001
and  to_char(i.ITEM_FIELD_TEMPLATE) = (
select t.id
from FLOWS_030100.wwv_flow_field_templates t
where t.flow_id = i.flow_id
 and t.template_name = 'Optional')
order by flow_step_id;