The APEX 19.2 readme includes info about a small script check_lovs_for_errors.sql that one should run after the upgrade. It is very easy to overlook this in the readme files.
TL;DR;
run
sqlplus user/pwd@dbconnection
spool apex/utilities/check_lovs_for_errors_results.log
set serveroutput on
@apex/utilities/check_lovs_for_errors.sql APP_ID APP_USER
spool off
exit;
Read the result and correct the suspect LOVs in your application.
Behavioural change
Essentially there was a small change in the behaviour of existing LOVs. If the select that supports the LOV uses the identical name for the display and the return value, then this used to work, but will not anymore in 19.2.
And not work, means we will get an error when running that page.
Here is the relevant section of the release note.
Not only selects that use the same name for display and return value are effected, but also selects that run into an error. LOVs with the first kind of select did work before, LOVs with an error select did not work before. Now such an error might prevent the page from opening. Usually you should not have LOVs of the second type.
Fortunately the APEX team provided the little script check_lovs_for_errors.sql that help us to identify such LOVs.
How to run the script
The script is in the APEX installation folder (where you unzipped the APEX file) in the path apex/utilities/. In case you don’t have access to the script I show it at the end of this blog post. It might help you to identify such LOVs in your own application.
In my environment (18.7 database) the script would not run properly as SYS or as DBA. The reason for this is described in section “how to not run the script”. Instead one needs to connect with the schema of your workspace account. I didn’t test it, but I think you might be able to run it in the SQL workshop.
The script uses two input parameters.
1 = APP_ID = application id
2 = APP_USER = name of an apex user who has access to the application
You won’t need the password for the APP_USER.
Only LOVs from this application will be checked. And the name of the user is used to create an apex_session context.
You can add those parameters while calling the script or later in interactive mode, when the script is running .
Before running the script one must enable serveroutput to see the results.
I suggest to spool the output into a file
[oracle@ODA01 utilities]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 7 09:28:49 2020
Version 18.7.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> connect workspaceschema/pwd@dbconnect
Connected.
SQL> spool check_lovs_for_errors_result.log
SQL> set serveroutput on
SQL> @check_lovs_for_errors.sql
Enter value for 1: 200
Enter value for 2: weller
--------------------
Error #1
Application: 200
Type: Inline
Page: 3110
Name: P3110_SELECT_LIST_ITEM
...
...
--------------------
Action Required! 5 LOV definitions raised an error, please review to see if they need to be fixed to avoid facing the same error in your app at runtime.
PL/SQL procedure successfully completed.
spool off
The output prints the errors that are found to the screen via dbms_output. Thats why spooling it to a file helps. You can always rerun the script to see the same or the corrected results.
Don’t forget to switch spooling off after the script run.
How to not run the script
If you connect with an account (db schema) that is doesn’t have the right to read your apex workspace data, then this error message appears.
ORA-20987: APEX – Security Group ID (your workspace identity) is invalid. –
Contact your application administrator.
If you connect as sys or with a dba account another error will probably happen.
SQL> connect / as sysdba Connected. SQL> alter session set container=pdb01; Session altered. SQL> @check_lovs_for_errors.sql Enter value for 1: 200 Enter value for 2: weller declare * ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at “APEX_190200.WWV_FLOW_EXEC_API”, line 1
ORA-06512: at line 71
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at “APEX_190200.WWV_FLOW_SESSION_API”, line 56
ORA-06512: at line 11
This error will only happen in Oracle DB 12.1 onwards. A new plsql security feature was introduced. SYS and DBA accounts do not have the inherit (any) privileges privilege that they need to execute something using invoker rights.
If you connect with your normal database schema, the script will run.
The problem for SYS connects can be circumented by granting and afterwards revoking the INHERIT PRIVILEGES. I didn’t test this.
grant inherit privileges on user MYSCHEMA to sys;
It could also be that user needs to be the APEX schema APEX_190200.
To restore the original situation, revoke the grant.
revoke inherit privileges on user MYSCHEMA from sys;
Interpret and handle results
The script tries to run all selects that are powering the LOVs inside some apex session context. If an error is encountered, the statement and the error is reported.
A typical final result looks like this:
...
--------------------
Action Required! 5 LOV definitions raised an error, please review to see if they need to be fixed to avoid facing the same error in your app at runtime.
PL/SQL procedure successfully completed.The typical problematic SQL that this script reports is when the two columns (display value and return value) have the same name.
The best way to correct this is (after the upgrade) to convert the LOV into a shared component that uses the new LOV logic. The new LOVs can have more the 2 columns and can declare, which column is used for the DISPLAY and which for the RETURN value and even one for icons. Display and return value could even be the same single column from the select.
Many but not all changes are so easy.
There are a few additional quirks I would like to mention.
Quirk 1 – false positives
A false positive is when the script reports an error, but in reality the query will never error out. This can happen for example if the select uses page items that are set when the LOV is used, but not set, when the script tests this query. Maybe because during the test all page items are empty.
Here is an example of such a case.
select first_name||' '||last_name as display_value,
employee_id as return_value
from hr.employees
where hire_date >= to_date(to_char(:P1_YEAR + 1) || '-01-10' ,'YYYY-MM-DD')
Error: ORA-01841: (full) year must be between -4713 and +9999, and not be
0
The problem in this example is, that the page item P1_YEAR is NULL while testing the query. Using NULL here leads to the ORA-error message. In reality this page item is filled with some value by other means (default, page load process, cascading parent lov, url parameter, …)
There could be many false positives. In my case 2 out of 5 findings were false positives.
If it is easily possible, then I suggest to rewrite such statements into a form that will not break the SELECT when the input items are null. This is not needed, but makes the code more robust, and also avoids that this statement is reported as problematic the next time this script runs.
For the example the solution could be to use an item that delivers the full date string
select first_name||' '||last_name as display_value,
employee_id as return_value
from hr.employees
where hire_date >= to_date(:P1_THREASHOLD_DAY ,'YYYY-MM-DD')
Quirk 2 – the output from the script has broken lines
When you take the reported SQL from the log file, then this SQL might not run, because of wrongly reported line breaks.
Example case (SQL statement was slightly modified and shortened)
original statement
Please notice, that there are several one line comments
select d,r
from (
select anzeige_name||'('||dty_daten_typ||')' d
, spalte_name r
, spalte_anzeige_name rank
from targettable
--where (:P30_TYP,'J') in
where :P30_TYP != 'I'
and (:P30_ART,'J') in
(('R', ck_bedingung1)
,('B', ck_bedingung2)
,('Z', druck_relevant2)
-- ,('T', befehl_bedingung1)
-- ,('I', befehl_relevant)
)
--order by tabelle_name, anzeige_name
Broken spool output from script
select d,r
from (
select anzeige_name||'('||dty_daten_typ||')' d
, spalte_name r
, spalte_anzeige_name rank
from targettable
--where (:P30_TYP,'J') in
where :P30_TYP != 'I'
and (:P30_ART,'J') in
(('R', ck_bedingung1)
,('B',
ck_bedingung2)
,('Z', druck_relevant2)
-- ,('T', befehl_bedingung1)
-- ,('I',
befehl_relevant)
)
--order by tabelle_name,
anzeige_name
Please notice that there are line breaks now that are not in the original select. Some lines are broken in weird places – like inside comments.
The LOV and the statement that is executed by the script does not have this issue.
This is an example SQL that had a not existing column name somewhere. However I first needed to correct the wrong linebreaks, before I could successfully reproduce the issue inside SQL developer.
The error that is shown by the script itself is correct.
Quirk 3 – errors on outdated pages
Sometimes we have pages in our application that are not in use anymore. There is a higher chance for LOVs/Selects erroring on such a page. The error reported is correct, but the application would never run into this error.
Time to clean up and throw out the old stuff! This is sometimes a tough decision to make. We are only upgrading the APEX version, we should keep changes to our code to a minimum. It certainly helps, when all pages are under version control. Then one can always reconstruct the deleted page from VCS.
Additionally one should keep a backup copy of the old images folder. I usually just rename it to /i_old/.
Main query used
This query is used to identify the LOVs. Each LOV is then executed in a dynamic APEX session context
select *
from ( select 'Inline' lov_type,
application_id app_id,
page_id page_id,
item_name name,
lov_definition query
from apex_application_page_items
where lov_definition is not null
and lov_definition not like 'STATIC%'
and lov_definition not like '.%'
union all
select 'Shared Component' lov_type,
application_id app_id,
null page_id,
list_of_values_name name,
list_of_values_query query
from apex_application_lovs
where source_type_code = 'LEGACY_SQL'
)
where app_id = &APP_ID
order by lov_type, app_id, page_id, name
One way to prevent an LOV to be listed here, is to upgrade the LOV to the newer version. Then it will not be marked as “LEGACY_SQL” anymore.
Tip:
It is advisable to upgrade all old LOVs to the new LOVs. This should be an essential part of the upgrade strategy of your application. Do not misunderstand me. You can keep your LOVs as they were before and the application will still run. But if you want to move forward, upgrade all LOVs.
To upgrade a LOV that is already a shared component is very easy. Just press the Convert Legacy SQL button.
And check the column mapping afterwards
LOVs that are not shared components need to be copied into a shared component first. Future APEX versions will probably change this.
Conclusion
After the upgrade to APEX 19.2 control the correctness of your LOVs in all apps by running the /apex/utilities/check_lovs_for_errors.sql script for each app.
The script points out potential LOV issues – analyse and correct those issues.
Take also some time to upgrade old style LOVs into the shared component multicolumn LOV.
The script check_lovs_for_errors.sql
With kind permission from the APEX team I show the script here
Rem Copyright (c) Oracle Corporation 1999 - 2019. All Rights Reserved.
Rem
Rem NAME
Rem check_lovs_for_errors.sql
Rem
Rem DESCRIPTION
Rem Checks List of Values defined both inline on an item, and at shared component level for possible execution errors.
Rem
Rem NOTES
Rem - This utility should be run via SQL*Plus and connected as your application parsing schema.
Rem
Rem Arguments:
Rem Position 1: Application ID you wish to check
Rem Position 2: APEX user name from the workspace the application belongs to
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem arayner 10/09/2019 - Created
set define '&'
set concat on
set concat .
set verify off
set termout on
define APP_ID = &1
define APEX_USER = &2
declare
l_context apex_exec.t_context;
l_error_count number := 0;
l_lov_count number := 0;
function is_plsql( p_sql_or_plsql in varchar2 ) return boolean is
begin
return coalesce( not( upper( substr(trim(p_sql_or_plsql), 1, 6) ) = 'SELECT' or upper(substr(trim(p_sql_or_plsql), 1, 4)) = 'WITH' ), true );
end is_plsql;
begin
apex_session.create_session( &APP_ID, null, '&APEX_USER' );
for l_lov in ( select *
from ( select 'Inline' lov_type,
application_id app_id,
page_id page_id,
item_name name,
lov_definition query
from apex_application_page_items
where lov_definition is not null
and lov_definition not like 'STATIC%'
and lov_definition not like '.%'
union all
select 'Shared Component' lov_type,
application_id app_id,
null page_id,
list_of_values_name name,
list_of_values_query query
from apex_application_lovs
where source_type_code = 'LEGACY_SQL' )
where app_id = &APP_ID
order by lov_type, app_id, page_id, name
)
loop
begin
l_lov_count := l_lov_count + 1;
l_context := apex_exec.open_query_context(
p_location => 'LOCAL',
p_sql_query => case when not is_plsql( l_lov.query) then l_lov.query end,
p_plsql_function_body => case when is_plsql( l_lov.query) then l_lov.query end,
p_max_rows => 0 ); -- We are not interested in the results, we simple want to see if the query executes without error
apex_exec.close( l_context );
exception
when others then
l_error_count := l_error_count + 1;
apex_exec.close( l_context );
sys.dbms_output.put_line( '--------------------' );
sys.dbms_output.put_line( 'Error #' || l_error_count );
sys.dbms_output.put_line( 'Application: ' || l_lov.app_id );
sys.dbms_output.put_line( 'Type: ' || l_lov.lov_type );
if l_lov.page_id is not null then
sys.dbms_output.put_line( 'Page: ' || l_lov.page_id );
end if;
sys.dbms_output.put_line( 'Name: ' || l_lov.name );
sys.dbms_output.put_line( 'LOV (' || case when is_plsql( l_lov.query ) then 'PL/SQL' else 'SQL' end || '):' );
sys.dbms_output.put_line( l_lov.query );
sys.dbms_output.put_line( 'Error: ' || SQLERRM );
sys.dbms_output.put_line( '' );
end;
end loop;
sys.dbms_output.put_line( '--------------------' );
if l_error_count > 0 then
sys.dbms_output.put_line( 'Action Required! ' || l_error_count || ' LOV definitions raised an error, please review to see if they need to be fixed to avoid facing the same error in your app at runtime.' );
else
sys.dbms_output.put_line( 'No action required! All ' || l_lov_count || ' LOV definitions executed successfully.' );
end if;
exception
when others then
apex_exec.close( l_context );
sys.dbms_output.put_line( SQLERRM );
end;
/
svenweller 