Hello, APEX team!
I know you are busy with making our favourite tool better and more powerful, but I am here to help.
It happened I needed to migrate a working 11g XE environment to the brand new 18c XE version, and I got myself into an issue.
According to the documentation (https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinl/exporting-and-importing-data-apex-users.html ), the one have been using APEX on 11g XE, has to run the fixing script apxfix.sql after the importing process on the new 18c XE environment (and they indeed must run it, because otherwise APEX won't work at all, most of its internal objects will be in incomplilable state).
So, I downloaded the script following the link in documentation (https://www.oracle.com/technetwork/developer-tools/apex/application-express/apxfix-5137274.zip ) and tried to run it.
I immediately got tons of exceptions. It looked as following:
Session altered.
...Create validate procedure in SYS schema and start registration
...validate_apex
Warning: Procedure created with compilation errors.
Errors for PROCEDURE VALIDATE_APEX:
LINE/COL ERROR
-------- -----------------------------------------------------------------
76/59 PLS-00103: Encountered the symbol "APEX_180100" when expecting
one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol "* was inserted before "APEX_180100" to continue.
271/38 PLS-00103: Encountered the symbol "APEX_180100" when expecting
one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or default
LINE/COL ERROR
-------- -----------------------------------------------------------------
like like2 like4 likec as between from using || multiset
member submultiset
The symbol ", was inserted before "APEX_180100" to continue.
274/35 PLS-00103: Encountered the symbol "APEX_180100" when expecting
one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
The symbol "* was inserted before "APEX_180100" to continue.
LINE/COL ERROR
-------- -----------------------------------------------------------------
grant inherit any privileges to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Installing SYS views
...sys_core_views.sql
View created.
core_grants.sql
...grant APEX owner core privileges
grant execute on sys.dbms_application_info to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
grant execute on sys.dbms_assert to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
grant execute on sys.dbms_db_version to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
grant execute on sys.dbms_flashback to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
grant execute on sys.dbms_ldap to upper('APEX_180100')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
... (many more rows like these)
And so on..
The problem was that the last version (6th of February 2019) of the script contains define APPUN = upper('^1'), which, I suppose, is wrong. I understand the intention of the change, but it was done incorrectly.
So, after changing this line to define APPUN = ^1 everything worked and the script was applied successfully (of course I provided it with my schema name in UPPERCSASE).
Guys, please, fix the fixing script.
@"Jstraub-Oracle", @"joelkallman-Oracle"