Column Actions

If you want to make sure that all Javascript and CSS files will be reloaded properly in the client browser when you deploy a new version of your Apex appliction, you can use the APP_VERSION substitution string.
The Oracle Apex documentation has some good info on this, and a very nice article on this subject named "Caching APEX static files" can be found at https://cloud.jaris.fi/ords/r/jaris/blog/post?p2_post_id=201803041325416124.

For example, you might include a CSS file using this URL:
/i/my-css/custom.css
Now if you were to upgrade your Apex application (and change its version number) and also make changes to the css file, the clients browser would not know about this and keep using the old version it has in its cache. So if you use this URL instead:
/i/my-css/custom.css?v=#APP_VERSION#
then Apex will substitute #APP_VERSION# with the version of your Apex application (e.g. 1.1), resulting in this URL:
/i/my-css/custom.css?v=1.1
Problem solved - the browser sees a new URL and will download it instead of using the cached version.

As you may have guessed, I needed to implement this for a number of Apex applications recently. The hard part turned out to be finding all the places where changes were needed. Once those (350+) changes were found, the rest was just a lot of repetitive work really :-)
I have gathered some code I wrote for this, that you may find useful:

• View ted_p1146_url_columns_v (see the Report above): Find Apex views with JS and/or CSS URL columns. These indicate all the places in your Apex applications where URLs for external JS and CSS files are stored, and we need these to build the next view, ted_p1146_urls_in_apex_v.
• View ted_p1146_urls_in_apex_v: Query all the places in your Apex applications where URLs for external JS and CSS files are stored.
• View ted_p1146_urls_in_plsql_v: Query all the places in your PL/SQL code where URLs for external JS and CSS files are added using apex_css.add_file or apex_javascript.add_library. Strangely enough these procedures have no option to add this kind of version (p_version adds it to the filename, before the extension, so that doesn't help), so we have add it manually. -- For example: apex_css.add_file(p_name => 'custom' ,p_directory => '/i/my-css/' ,p_version => Null); -- Should become (note we have set p_skip_extension to True, so we have to add the css extension ourselves in p_name): apex_css.add_file(p_name => 'custom.css?v=#APP_VERSION#' ,p_directory => '/i/my-css/' ,p_version => Null ,p_skip_extension => True);
• Note: you may want to double-check if filtering on "?v=" in these views is enough - you may need to add other filters like "?version=", depending on the coding style(s) used in your situation.

Page

Identification
	Page ID	1146
	Name	Apex - HTML / CSS / JavaScript - Browser Cache

Region

Identification
	Sequence	10
	Title	URL Columns
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Table Name	TED_P1146_URL_COLUMNS_V

View

View
	Name	ted_p1146_url_columns_v
	DDL	Select d.apex_view_name ,d.column_name From apex_dictionary d ,all_tab_cols tc ,(Select Max(u1.username) As current_apex_user From all_users u1 Where u1.username Like 'APEX%' And regexp_like(substr(u1.username ,'6' ,1) ,'^[0-9]*$')) u Where d.apex_view_name = tc.table_name And d.column_name = tc.column_name And tc.owner = u.current_apex_user And d.column_id <> 0 And (d.column_name Like '%FILE_URLS%') And d.column_name Not In ('APEX_WORKSPACE_UI_TYPES') -- I think we can safely ignore these

View

View
	Name	ted_p1146_urls_in_apex_v
	DDL	With iv_app As (Select app.* From apex_applications app Where app.workspace = 'TEDSTRUIK' ) -- , iv_page As (Select page.* From iv_app Join apex_application_pages page On (page.workspace = iv_app.workspace And page.application_id = iv_app.application_id) -- ) -- , iv_all_files As ( -- APEX_APPLICATIONS Select iv_app.workspace ,iv_app.application_id ,null as page_id ,'APEX_APPLICATIONS' || ' JS' As file_type ,Null As object_name ,iv_app.javascript_file_urls As file_urls From iv_app -- APEX_APPLICATION_PAGES Union All Select iv_page.workspace ,iv_page.application_id ,iv_page.page_id ,'APEX_APPLICATION_PAGES' || ' JS' As file_type ,Null As object_name ,iv_page.javascript_file_urls As file_urls From iv_page Union All Select iv_page.workspace ,iv_page.application_id ,iv_page.page_id ,'APEX_APPLICATION_PAGES' || ' CSS' As file_type ,Null As object_name ,iv_page.css_file_urls As file_urls From iv_page -- APEX_APPLICATION_TEMP_LIST Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_LIST' || ' JS' As file_type ,atl.template_name As object_name ,atl.javascript_file_urls As file_urls From iv_app Join apex_application_temp_list atl On (atl.workspace = iv_app.workspace And atl.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_LIST' || ' CSS' As file_type ,atl.template_name As object_name ,atl.css_file_urls As file_urls From iv_app Join apex_application_temp_list atl On (atl.workspace = iv_app.workspace And atl.application_id = iv_app.application_id) -- APEX_APPLICATION_TEMP_PAGE Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_PAGE' || ' JS' As file_type ,atp.template_name As object_name ,atp.javascript_file_urls As file_urls From iv_app Join apex_application_temp_page atp On (atp.workspace = iv_app.workspace And atp.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_PAGE' || ' CSS' As file_type ,atp.template_name As object_name ,atp.css_file_urls As file_urls From iv_app Join apex_application_temp_page atp On (atp.workspace = iv_app.workspace And atp.application_id = iv_app.application_id) -- APEX_APPLICATION_TEMP_REGION Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_REGION' || ' JS' As file_type ,atr.template_name As object_name ,atr.javascript_file_urls As file_urls From iv_app Join apex_application_temp_region atr On (atr.workspace = iv_app.workspace And atr.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_REGION' || ' CSS' As file_type ,atr.template_name As object_name ,atr.css_file_urls As file_urls From iv_app Join apex_application_temp_region atr On (atr.workspace = iv_app.workspace And atr.application_id = iv_app.application_id) -- APEX_APPLICATION_TEMP_REPORT Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_REPORT' || ' JS' As file_type ,atr2.template_name As object_name ,atr2.javascript_file_urls As file_urls From iv_app Join apex_application_temp_report atr2 On (atr2.workspace = iv_app.workspace And atr2.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_TEMP_REPORT' || ' CSS' As file_type ,atr2.template_name As object_name ,atr2.css_file_urls As file_urls From iv_app Join apex_application_temp_report atr2 On (atr2.workspace = iv_app.workspace And atr2.application_id = iv_app.application_id) -- APEX_APPLICATION_THEMES Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_THEMES' || ' JS' As file_type ,ate.theme_name As object_name ,ate.javascript_file_urls As file_urls From iv_app Join apex_application_themes ate On (ate.workspace = iv_app.workspace And ate.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_THEMES' || ' CSS' As file_type ,ate.theme_name As object_name ,ate.css_file_urls As file_urls From iv_app Join apex_application_themes ate On (ate.workspace = iv_app.workspace And ate.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_THEMES' || ' CUSTOM' As file_type ,ate.theme_name As object_name ,ate.custom_library_file_urls As file_urls From iv_app Join apex_application_themes ate On (ate.workspace = iv_app.workspace And ate.application_id = iv_app.application_id) -- APEX_APPLICATION_THEME_STYLES Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_THEME_STYLES' || ' CSS' As file_type ,Null As object_name ,ats.css_file_urls As file_urls From iv_app Join apex_application_theme_styles ats On (ats.workspace = iv_app.workspace And ats.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPLICATION_THEME_STYLES' || ' INPUT' As file_type ,Null As object_name ,ats.theme_roller_input_file_urls As file_urls From iv_app Join apex_application_theme_styles ats On (ats.workspace = iv_app.workspace And ats.application_id = iv_app.application_id) -- APEX_APPL_CONCATENATED_FILES Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPL_CONCATENATED_FILES' || ' SINGLE' As file_type ,Null As object_name ,acf.single_file_urls As file_urls From iv_app Join apex_appl_concatenated_files acf On (acf.workspace = iv_app.workspace And acf.application_id = iv_app.application_id) -- APEX_APPL_PLUGINS Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPL_PLUGINS' || ' JS' As file_type ,apn.display_name As object_name ,apn.javascript_file_urls As file_urls From iv_app Join apex_appl_plugins apn On (apn.workspace = iv_app.workspace And apn.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPL_PLUGINS' || ' CSS' As file_type ,apn.display_name As object_name ,apn.css_file_urls As file_urls From iv_app Join apex_appl_plugins apn On (apn.workspace = iv_app.workspace And apn.application_id = iv_app.application_id) -- APEX_APPL_USER_INTERFACES Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPL_USER_INTERFACES' || ' JS' As file_type ,Null As object_name ,aui.javascript_file_urls As file_urls From iv_app Join apex_appl_user_interfaces aui On (aui.workspace = iv_app.workspace And aui.application_id = iv_app.application_id) Union All Select iv_app.workspace ,iv_app.application_id ,Null As page_id ,'APEX_APPL_USER_INTERFACES' || ' CSS' As file_type ,Null As object_name ,aui.css_file_urls As file_urls From iv_app Join apex_appl_user_interfaces aui On (aui.workspace = iv_app.workspace And aui.application_id = iv_app.application_id) -- ) Select iv_all_files.application_id ,iv_all_files.page_id ,iv_all_files.file_type ,iv_all_files.object_name ,Trim(s.column_value) As file_line ,lck.locked_by ,lck.locked_on From iv_all_files Left Join apex_application_locked_pages lck On (lck.workspace = iv_all_files.workspace And lck.application_id = iv_all_files.application_id And lck.page_id = iv_all_files.page_id) Cross Join Table (apex_string.split(Replace(iv_all_files.file_urls ,chr(13) ,'') ,chr(10))) s -- split string with list of file_urls into individual urls Where Trim(iv_all_files.file_urls) Is Not Null And Trim(s.column_value) Is Not Null -- And (Case When Trim(s.column_value) Like '%?v=#APP_VERSION#' Then 'N' -- already OK When Trim(s.column_value) Like '%?v=#APEX_VERSION#' Then 'N' -- handled by Apex version When s.column_value Like '#THEME_IMAGES#less/theme/%' Then 'N' -- handled by Apex version When s.column_value Like '%/font-awesome/4.7.0/%' Then 'N' -- hardcoded version number When s.column_value Like '%/'||'&'||'F_CODEMIRROR_VERSION./%' Then -- ||'&'|| used to avoid substitution problems 'N' -- version number handled by Apex item Else 'Y' End) = 'Y' -- changes needed Order By iv_all_files.application_id ,iv_all_files.page_id Nulls First ,iv_all_files.file_type ,iv_all_files.object_name ,s.column_value

View

View
	Name	ted_p1146_urls_in_plsql_v
	DDL	With iv_add As (Select t1.* From user_source t1 Where lower(t1.text) Like lower('%apex_css.add_file%') Or lower(t1.text) Like lower('%apex_javascript.add_library%') -- ) -- , iv_v_line As (Select iv_add.name ,iv_add.type ,iv_add.line ,iv_add.text ,Min(t2.line) As v_line From iv_add Left Join user_source t2 On (t2.name = iv_add.name -- And t2.type = iv_add.type -- And t2.line Between iv_add.line And iv_add.line + 5 -- And lower(t2.text) Like lower('%p_skip_extension%=>%true%')) Group By iv_add.name ,iv_add.type ,iv_add.line ,iv_add.text -- ) -- , iv_v_text As (Select t3.* From user_source t3 Join iv_v_line On (iv_v_line.name = t3.name And iv_v_line.type = t3.type And t3.line = iv_v_line.v_line) -- ) -- Select iv_v_line.name ,iv_v_line.type ,iv_v_line.line ,iv_v_line.text ,iv_v_line.v_line ,iv_v_text.text As v_text From iv_v_line Left Join iv_v_text On (iv_v_text.name = iv_v_line.name And iv_v_text.type = iv_v_line.type And iv_v_text.line = iv_v_line.v_line) Where (Case When (iv_v_line.text Like '%.css?v=%' Or iv_v_line.text Like '%.js?v=%') And iv_v_text.text Is Not Null Then 'Y' Else 'N' End) = 'N' -- changes needed Order By iv_v_line.name ,iv_v_line.type ,iv_v_line.line