APEX Patches and NLS_LENGTH_SEMANTICS


I’m going to post this as a reminder to myself as much as anything else. A couple of times – most recently this week – I’ve had to apply patches for Oracle APEX into various databases that I work with and received the following error almost as soon as the apxpatch.sql script started to run:

…create flow views
…wwv_flow_current_sgid view
from table(apex_050100.wwv_flow_t_varchar2(‘X’)) t
*
ERROR at line 5:
ORA-22813: operand value exceeds system limits

…wwv_flow_user_activity_log view
from wwv_flow_current_sgid sgid,
*
ERROR at line 6:
ORA-04063: view “APEX_050100.WWV_FLOW_CURRENT_SGID” has errors

from wwv_dual100
ORA-04063: view “APEX_050100.WWV_FLOW_DUAL100” has errors

ORA-04063: view “APEX_050100.WWV_MULTI_COMPONENT_EXPORT” has errors
ORA-04063: view “APEX_050100.WWV_FLOW_COLLECTIONS” has errors
ORA-04063: view “APEX_050100.WWV_FLOW_MONTH_MONTHS” has errors
ORA-04063: view “APEX_050100.WWV_FLOW_YEARS” has errors
ORA-04063: view “APEX_050100.WWV_FLOW_HOURS_12” has errors

The end result is a LOT of invalid objects in the APEX_050100 schema that can’t be recompiled.

After restoring from backup and reaching out to Oracle Support, it was discovered that this is actually a known bug for patching APEX 5.1.x on Oracle 11.2.0.x. Bug 9848227 : TABLE FUNCTION NOT CREATED CORRECTLY WHEN NLS_LENGTH_SEMANTICS SET. It occurs when the NLS_LENGTH_SEMANTICS parameter is set to ‘CHAR’ instead of ‘BYTE’.

The work-around is to perform the following steps just before running the patch script:

1. Log in to sqlplus as sysdba.

2. Complete the following SQL commands:

alter session set current_schema = apex_050100;
alter session set nls_length_semantics = byte;
create or replace type wwv_flow_t_varchar2 as table of varchar2(32767);
/
alter view wwv_flow_dual100 compile;

3. From the same session, run the apxpatch.sql script.

The script now runs to completion without any errors and the patch application is successful. Hopefully this will be the last time I have to install an APEX patch on an 11g system…

One thought on “APEX Patches and NLS_LENGTH_SEMANTICS

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.