Monday, October 06, 2008

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;

4 comments:

tylermuth said...

Wow, where to begin. I think this is a pretty irresponsible post, as you really don't provide enough warning about the damage you could do to your whole APEX installation. One wrong predicate and you can mess up every application, including the builder itself. Do you recommend users update the data dictionary tables directly as well?

A much safer way to achieve the same goal is export the app use a text editor to perform search and replace operations.

Sorry to be so harsh, but I really think you should amend this post with a much bigger warning. There are people out there who will read this who are not nearly as skilled as you and could do a lot of damage.

Byte64 said...

Tyler,
thanks for your comment, i'll add other warnings, i thought it was pretty clear that this SQL statements shouldn't be executed unless you were absolutely sure of what you were doing and in any case after making a backup of your instance, just in case.
As my development environments are based on VMs, it's really straightforward to take a snapshot before an important change and rollback in case something went wrong.
So i'll put warnings in red and big characters.

Flavio

Scott said...

Flavio,

I'd also caution that even if you make a seemingly harmles change, you really don't quite know if it really had any impact elsewhere.

I've always advised peoiple NOT to edit data in the FLOWS_XXXXXX schemas unless specifically instructed to do so by Oracle, as any such modification pretty much ends your support.

Thanks,

- Scott -

Byte64 said...

Hi Scott,
thanks for your remarks, i understand the point.
For the specific cases i described in my posting i inspected the code executed by Apex itself, just to be sure that i wasn't missing something, so there was very little room for my fantasy.
Clearly what is valid for 3.1.2 may be no longer valid for 3.2, 4.0 or earlier releases, as i warned.

I understand the objections to the method and certainly i am the first to say to not use this stuff for changing a couple of label names or attributes, but i think that by this time it should be clear enough that these "shortcuts" are meant to be used in a very controlled environment, when you have a very large number of identical definitions and in any case they are not to be tried out by anyone who doesn't have a good understanding of database backup and recovery and has taken all the necessary precautions.

Ciao,
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles