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
from table(apex_050100.wwv_flow_t_varchar2(‘X’)) t
ERROR at line 5:
ORA-22813: operand value exceeds system limits
from wwv_flow_current_sgid sgid,
ERROR at line 6:
ORA-04063: view “APEX_050100.WWV_FLOW_CURRENT_SGID” has errors
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…