Apex - General - Apex Upgrade 4.2 to 5.1

The Application Express Installation Guide and Release Notes provide you with information on Changed Behavior, Deprecated Features and Desupported Features. You can use this information to determine which parts of your applications need changes to work correctly with the new Apex version.

While these documents provide some clues on how to find all the parts that need attention, actually finding them is quite a lot of work.
Some time ago I performed an upgrade from Apex 4.2.2 to 5.1.4 for a number of applications. During that process (and later upgrades as well) I created some queries that made my life easier; you can find these below. Not complete by any means, but maybe it is of some use.

Some additional things that required attention during the upgrades can be found here.

Application Express Installation Guide 5.0
Application Express Release Notes 5.0
Application Express Release Notes 5.1
Application Express Release Notes 21.2
Other Findings

https://docs.oracle.com/cd/E59726_01/install.50/e39144/preface.htm#HTMIG801

1.2 Upgrading from a Previous Oracle Application Express Release

1.2.4 About Upgrading Existing Applications

Charts that use custom XML must be updated manually otherwise the custom XML will be lost.

select t.*
from apex_application_page_flash5 t
where dbms_lob.getlength(t.chart_xml) > 0
and   t.application_id < 4000

https://docs.oracle.com/cd/E59726_01/doc.50/e39143/toc.htm#HTMRN233

4 Changed Behavior

4.1.3 Compatibility Mode Changes in Mode 5.0

Page items

The action column in the query below indicates what you need to change to fix this properly.
This is needed because you may have items that qualify, but should not have a Source Type 'Database Column' because there is no DML on the page that uses that setting.

select ( case when p2.page_id is null then 'change Source Type' else 'change Source Used' end ) as action
,      i.*
from apex_application_page_items i
,
(
  select distinct
         p.application_id
  ,      p.page_id
  from apex_application_page_proc p
  where p.process_type_code like 'DML\_%' escape '\' --'
) p2
where i.application_id   = p2.application_id (+)
and   i.page_id          = p2.page_id        (+)
and   i.item_source_type = 'Database Column'
and   i.source_used      = 'Only when current value in session state is null'

4.19 Interactive Report Column ID and Header Value

select da.when_element
,      irc.column_alias
,      irc.column_link_attr
,      da.*
,      irc.*
from apex_application_page_ir     ir
,    apex_application_page_ir_col irc
,    (select * from apex_application_page_da )    da
where ir.region_id = irc.region_id
and   ( da.when_selection_type = 'jQuery Selector' and upper(da.when_element) = '#' || irc.column_alias )
and   nvl(lower(irc.column_link_attr),'x') not like '%id%=%' || irc.column_alias || '%' -- id is not set

4.24 Dynamic Action Set Value Action Set Type JavaScript Expression More Strict

select a.*
from apex_application_page_da      da
,    apex_application_page_da_acts a
where da.dynamic_action_id = a.dynamic_action_id
and   a.action_name = 'Set Value'
and   a.action_code not in ('NATIVE_SET_VALUE')

6 Desupported Features

6.1 Desupported Interactive Report Attributes

select t.*
from apex_application_page_ir t
where t.base_table_or_view is not null
or    t.actions_menu_icon is not null
or    t.finder_icon is not null

6.2 Desupported Report Heading Sort Icons

select t.*
from apex_application_page_ir t
where t.sort_asc_image is not null
or    t.sort_asc_image_attr is not null
or    t.sort_desc_image is not null
or    t.sort_desc_image_attr is not null

6.3 Desupported Default Report Templates

select e.*
from apex_application_page_rpt r
,    apex_application_page_regions e
where r.region_id = e.region_id
and   e.report_template not in ('Standard') -- add what you need to this list

6.5 Desupported Item Types

select i.*
from apex_application_page_items i
where i.display_as = 'Text Field with Calculator Popup'

6.8 Desupport of Flash 3 Charts

select r.*
from apex_application_page_regions r
where r.template = 'Chart Region'
and   r.source_type = 'Flash Chart'
and   r.source_type_code not in ('FLASH_CHART5')

https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-E4353E38-3250-4304-8982-F6D4A7887227

1.5 Changed Behavior

1.5.5 Calendar Changed Behavior

Note that you can't make these changes before upgrading.

select i.*
from apex_application_page_items i
where i.display_as = 'Date Picker'
and   i.attribute_03 is not null -- max_date

1.7 Desupported Features

1.7.5 Desupported Classic Report Attributes

select rc.*
from apex_application_page_rpt_cols rc
where (    rc.display_as_code =    'DATE_PICKER'
        or rc.display_as_code =    'DISPLAY_AND_SAVE'
        or rc.display_as_code like 'PICK\_DATE\_%' escape '\' --'
        or rc.display_as_code like 'POPUP%'
        or rc.display_as_code like 'RADIOGROUP%'
        or rc.display_as_code =    'SIMPLE_CHECKBOX'
        or rc.display_as_code =    'TEXT'
        or rc.display_as_code =    'TEXTAREA'
        or rc.display_as_code =    'CHECKBOX' -- Row Selector
        or rc.display_as_code =    'HIDDEN'
      )

1.7.6 Desupported Tabular Form Attributes

select rc.*
from apex_application_page_rpt r
,    apex_application_page_rpt_cols rc
where r.region_id = rc.region_id
and   r.source_type = 'Tabular Form'
and   rc.display_as_code like 'PICK\_DATE\_%' escape '\' --'
order by rc.application_id
,        rc.page_id

1.7.7 Desupported Region Attributes

-- draft query
select distinct r.source_type
,      r.source_type_code
from apex_application_page_regions r

1.7.8 Desupported Item Attributes

Note that you can't make these changes before upgrading.

select i.*
from apex_application_page_items i
where (    i.display_as_code = 'NATIVE_DATE_PICKER_CLASSIC'
        or 0 = 1 -- Stop and Start Grid Layout ???
        or ( i.display_as_code = 'NATIVE_FILE' and i.attribute_01 = 'WWV_FLOW_FILES' )
      )

1.7.9 Desupported Button Attributes

select b.*
from apex_040200.wwv_flow_step_items b
where b.display_as = 'BUTTON'
and   (  b.pre_element_text is not null
      or b.post_element_text is not null
      )

1.7.10 Desupported Condition Types

This is a bit tricky. I needed a query to create part of the next query :-)
Note that the Condition "Always' can be ignored, which might save a lot work.

with a as
(
  select v.view_name
  ,      tc.column_name
  ,      count(*) over (order by v.view_name, tc.column_name) as n
  from dba_views v
  ,    dba_tab_cols tc
  where v.view_name = tc.table_name
  and   v.view_name like 'APEX_APPLICATION_PAGE_%'
  and   tc.column_name like '%CONDITION%TYPE'
  and   v.owner = tc.owner
  and   v.owner = 'APEX_040200'
)
select a.*
,         ( case when a.n > 1 then 'union all ' else null end )
       || 'select application_id'
       || ', page_id'
       || ', ''' || a.view_name || ''' as v'
       || ', ' || ( case a.view_name
                    when 'APEX_APPLICATION_PAGE_BUTTONS' then 'button_name'
                    when 'APEX_APPLICATION_PAGE_ITEMS' then 'item_name'
                    when 'APEX_APPLICATION_PAGE_REGIONS' then 'region_name'
                    when 'APEX_APPLICATION_PAGE_PROC' then 'process_name'
                    -- etc
                    else 'null'
                    end
                  )
               || ' as i'
       || ', ''' || a.column_name || ''' as c'
       || ', c.r'
       || ' from ' || a.view_name
       || ', c'
       || ' where ' || a.column_name || ' = c.r'
       || ' or ' || a.column_name || ' = c.d'
       as x
from a

-- use the SQL in the X column in the next query:

with c as
(
select *
from apex_standard_conditions
where d in ( 'DAD_NAME_EQ_CONDITION', 'DAD_NAME_NOT_EQ_CONDITION', 'HTTP_HOST_EQ_CONDITION', 'HTTP_HOST_NOT_EQ_CONDITION', 'SERVER_NAME_EQ_CONDITION', 'SERVER_NAME_NOT_EQ_CONDITION'
           , 'BROWSER_IS_MSIE', 'BROWSER_IS_MSIE_OR_NSCP', 'BROWSER_IS_NSCP', 'BROWSER_IS_OTHER'
           , 'MAX_ROWS_LT_ROWS_FETCHED', 'MIN_ROW_GT_THAN_ONE'
           , 'WHEN_THIS_PAGE_SUBMITTED', 'WHEN_THIS_PAGE_NOT_SUBMITTED'
           , 'DISPLAY_COND_EQUAL_COND_TEXT', 'DISP_COND_NOT_EQUAL_COND_TEXT'
           , 'CONDITION1_IN_VALUE_OF_ITEM_IN_CONDITION2', 'DISPLAY_COND_IN_COND_TEXT', 'DISPLAY_COND_NOT_IN_COND_TEXT'
           , 'ALWAYS'
           )
)
select application_id, page_id, 'APEX_APPLICATION_PAGE_BRANCHES' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_BRANCHES, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_BUTTONS' as v, button_name as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_BUTTONS, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_COMP' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_COMP, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_DA' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_DA, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_FLASH5_S' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_FLASH5_S, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_IR' as v, null as i, 'DETAIL_LINK_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_IR, c where DETAIL_LINK_CONDITION_TYPE = c.r or DETAIL_LINK_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_IR_COL' as v, null as i, 'DISPLAY_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_IR_COL, c where DISPLAY_CONDITION_TYPE = c.r or DISPLAY_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_IR_COND' as v, null as i, 'CONDITION_EXPR_TYPE' as c, c.r from APEX_APPLICATION_PAGE_IR_COND, c where CONDITION_EXPR_TYPE = c.r or CONDITION_EXPR_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_IR_COND' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_IR_COND, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_ITEMS' as v, item_name as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_ITEMS, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_ITEMS' as v, item_name as i, 'READ_ONLY_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_ITEMS, c where READ_ONLY_CONDITION_TYPE = c.r or READ_ONLY_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_PROC' as v, process_name as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_PROC, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_REGIONS' as v, region_name as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_REGIONS, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_REGIONS' as v, region_name as i, 'READ_ONLY_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_REGIONS, c where READ_ONLY_CONDITION_TYPE = c.r or READ_ONLY_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_RPT_COLS' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_RPT_COLS, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGES' as v, null as i, 'CACHE_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGES, c where CACHE_CONDITION_TYPE = c.r or CACHE_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGES' as v, null as i, 'READ_ONLY_CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGES, c where READ_ONLY_CONDITION_TYPE = c.r or READ_ONLY_CONDITION_TYPE = c.d
union all select application_id, page_id, 'APEX_APPLICATION_PAGE_VAL' as v, null as i, 'CONDITION_TYPE' as c, c.r from APEX_APPLICATION_PAGE_VAL, c where CONDITION_TYPE = c.r or CONDITION_TYPE = c.d

1.7.11 Desupported Branch Attributes

select application_id
,      page_id
,      t.branch_name
,      t.branch_action
from apex_application_page_branches t
where t.save_state_before_branch <> 'No'
and   substr(t.branch_action, instr(t.branch_action, ':', 1, 6)+1 , instr(t.branch_action, ':', 1, 7) - instr(t.branch_action, ':', 1, 6) -1) is not null

1.7.13 Desupported Synchronous Ajax in Dynamic Actions

Fixing the "Before Page Submit" is a bit tricky. How to do this is explained here: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/5741/index-en.html.
Basically, you need to convert the "Before Page Submit" Dynamic Action into one that is triggered some other way (e.g. when a button is clicked) and put both the original Dynamic Action code AND the Submit are done within the Dynamic Action itself. That way the Submit is triggered after the original code has been completed.

select t.application_id
,      t.page_id
,      t.dynamic_action_name
from apex_application_page_da t
,    apex_application_page_da_acts t2
where t.dynamic_action_id = t2.dynamic_action_id
and   t.when_event_internal_name in ('unload', 'apexbeforepagesubmit')
and   t2.action_code = 'NATIVE_JAVASCRIPT_CODE'

1.7.16 Desupported Rich Text Editor Attribute Values

select t.application_id
,      t.page_id
,      t.attribute_01
from apex_application_page_items t
where t.display_as_code = 'NATIVE_RICH_TEXT_EDITOR'
and   t.attribute_01 in ('FCKEDITOR2', 'IE')

1.7.19 Desupport of jsTree Implementation in Tree Region

-- draft
select t.application_id
,      t.page_id
from apex_application_page_trees t

https://docs.oracle.com/en/database/oracle/application-express/21.2/htmrn/index.html#HTMRN-GUID-657FF369-756E-4F31-96F9-B2D92C3DE084

5.1 Deprecated Features

5.1.1 Deprecated Page Positions

I changed these with a search-and-replace in an application export file.
Took only 3 minutes and worked perfectly (which saved a lot time, there were 189 hits in my application...)

select r.page_id
,      r.display_sequence
,      r.region_name
,      r.display_position_code
from apex_application_page_regions r
where r.display_position_code in ('BODY_1', 'BODY_2', 'BODY_3','AFTER_HEADER','BEFORE_HEADER')
and   r.parent_region_id is null -- excluded because these will be moved with their parent
order by 1,2

5.1.2 Deprecated Region Positions

select b.page_id
,      b.region
,      b.button_sequence
,      b.button_name
,      b.display_position
from apex_application_page_buttons b
where b.display_position in ('TOP', 'BOTTOM', 'ABOVE_BOX', 'BELOW_BOX')
order by 1,2,3

5.1.4 Legacy Substitution Strings Deprecated

These can be found by searching in the application itself, but I changed these with a couple of search-and-replace actions in an application export file.

Other things that needed attention during the upgrade from 4.2 to 5.x:

The apex_collection.update_member_attribute procedures (there are a few): parameters p_seq and p_attr_number were varchar2, they are now numbers. This may lead to "PLS-00307: too many declarations of 'UPDATE_MEMBER_ATTRIBUTE' match this call" errors.
The apex_application package does not contain global g_protected_page_ids (and a few others) anymore.
If you need this global to create urls with checksums using apex_util.prepare_url, this link https://community.oracle.com/thread/4102805 may be of interest.
The selected date of the "Easy Calendar" region is not highlighted anymore. select r.* from apex_application_page_regions r where r.source_type = 'Easy Calendar'
Buttons with a non-standard image specification can cause errors like "Error during rendering of page item P200_ITEM_NAME. ORA-06502: PL/SQL: numerieke fout of fout in waarde : character string buffer too small.". select t.* from apex_application_page_buttons t where t.application_id < 4000 and not ( t.image_name like '%/i/themes/%' or t.image_name like '/i/%' or t.image_name like 'r/tedstruik/25384/files/static/v1/%' ) order by t.application_id , t.page_id
You may need to configure your firewall/proxy to allow urls that use the (new) function wwv_flow.js_messages; e.g. in urls like this: https://[servername]/pls/apx/wwv_flow.js_messages?p_app_id=115&p_lang=nl&p_version=5.1.4.00.08-1
Also for the firewall/proxy: urls like https://[servername]/pls/apx/wwv_flow_file_mgr.get_file?[...] now look like this: https://[servername]/pls/apx/r/[workspace]/files/static/ or https://[servername]/pls/apx/r/[workspace]/files/plugin/
If you use function [apex_050100.]wwv_flow_epg_include_mod_local to allow local procedures, this function will be overwritten during the upgrade. So you will have to fix/revert that afterwards.
The modernizr library version has changed, so any customized (page) templates that refer to /i/libraries/modernizr/2.5.3/modernizr.min.js have to be updated (for Apex 5.1.4. the new version is 2.6.2).
Pay attention to the wwv_flow_preferences$ table. It will be filled after the upgrade, while Oracle probably thinks it is empty and will therefore do a full table scan every time it is queried. And it is used a lot, so that could be very costly. To fix, make Oracle do an "analyse table".
This is a pre-5.1 issue, but for me it only surfaced after the ugrade so I include it here anyway: Using apex_collection.create_or_truncate_collection may result in this error: "ORA-00001: unique constraint (APEX_050100.WWV_FLOW_COLLECTIONS_UK) violated". See https://community.oracle.com/thread/2528757 for a workaround.