APEX 19.2 upgrade quick tip: run check_lovs_for_errors.sql

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.

screenshot LOV conversion - before

And check the column mapping afterwards

screenshot LOV conversion - after

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;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.