XE Database Admin App after upgraded APEX version

When you install the Oracle 11g XE database the "Get Started" shortcut references an url to an APEX application (AppID=4950). If you upgrade your APEX version the application is not migrated to the new APEX release. Using some APEX tools and a few lines of codes we can move the XE Admin application to the new APEX version.

Before upgrading the APEX version we first need to export the XE Admin application using the APEX export command line utility.

REM ***************************************************************** 
REM * PARAMETERS TO MODIFY
REM ***************************************************************** 
set oracleSID=XE
set oracleSystemPass=oracle
set oraclePort=1521
set oracleHost=localhost
set XEOraclePath^
    =C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar
set ApexUtilityPath=C:\apex\apex_4.0.2\apex\utilities
set JAVAPATH=C:\Program Files (x86)\Java\jre6\bin
REM ***************************************************************** 
set AppId=4950
cd %ApexUtilityPath%
set CLASSPATH=%CLASSPATH%;%XEOraclePath%;%ApexUtilityPath%
REM ***************************************************************** 
REM Export the application
REM ***************************************************************** 
"%JAVAPATH%\java" oracle.apex.APEXExport ^
 -db %oracleHost%:%oraclePort%:%oracleSID% ^
 -user system -password %oracleSystemPass% -applicationid %AppId%
pause

Using the above script, my resulting application script (f4950.sql), is in the APEX utilities directory.

After upgrading the APEX version we need to edit the application script file a make a few minor changes. Since I upgraded my APEX version from 4.0 to 4.1 I need my application to reference the new APEX schema. Change the line (~139) which sets the application owner:

  p_owner => nvl(wwv_flow_application_install.get_schema,'APEX_040000'),

  p_owner => nvl(wwv_flow_application_install.get_schema,'APEX_040100'),

Although not needed, let's also change the version shown (if in your export file):

    p_flow_version=> '&PRODUCT_NAME. 4.0.2.00.09',

    p_flow_version=> '&PRODUCT_NAME. 4.1.0.00.32',

My resulting application script can be downloaded here.

Log into the database as the SYS user and run the application script, f4950.sql

Execute the following grants to allow for some additional privileges which are needed for the new APEX schema owner.

grant select on dba_temp_files to APEX_040100;
grant select on v_$temp_extent_pool to APEX_040100;
grant select on v_$temp_space_header to APEX_040100;
grant select on v_$system_parameter to APEX_040100;
grant select on v_$session to APEX_040100;

We now have the XE Admin application in our upgraded APEX installation.

Download Links:

f4950.sql (4.1)
f4950_v420.sql
f4950_v424.sql

Comments

Posted on 30 Jan 2013 by Vadi

Dear Mr Eberhard, Thanks a lot for your blog. I had installed Oracle 11g XE , upgraded to APEX 4.2 and then promptly lost the Getting started page. I followed your instructions and it worked perfectly for me. I now can access the Getting Started page. But the Sessions tab throws an error - ORA-20001: get_dbms_sql_cursor error ORA-00942: table or view does not exist. Also the Search bar and the Go and Action buttons are misaligned but they still work. This is just for your information. Thanks again. Regards Vadi, Bengaluru, India

Posted on 3 Mar 2014 by andreml

Hello, Thank oyu very much! In 4.2.4.00.08 On 127.0.0.1:8080/apex/f?p=4950:4: This exception occurs: "ORA-20001: get_dbms_sql_cursor error ORA-00942: table or view does not exist" The same has been Posted on January 30, 2013 03:55AM by Vadi Still no solution yet? please also see here:

https://community.oracle.com/thread/3523886

Posted on 3 Mar 2014 by andreml

FOLLOW-UP -> SOLUTION: --also add this GRANT as SYS grant select on V_$session to APEX_040200; -- or whatever your Apex-Schema is Regards Andre

Posted on 18 Mar 2014 by dunja

I ve upgraded from xe 11.2 to apex 4.2.4.00.00 Its working excellent ! Juz download file f4950.sql, change to p_owner > nvl(wwv_flow_application_install.get_schema,'APEX_040200'), and p_flow_version=> '&PRODUCT_NAME. 4.2.4.00.00', , grant priviliges -> and one happy person more :) thanks a lot !

Posted on 12 Jun 2014 by mattk

Hi after running the batch file i get the below - do you have any suggestions please?


C:\apex\utilities&gt;&quot;C:\Program Files (x86)\Java\jre7\bin\java&quot;oracle.apex.APEXExport  -db localhost:1521:XE  -user system
 -password oracle -applicationid 4950
'&quot;C:\Program Files (x86)\Java\jre7\bin\java&quot;oracle.apex.APEXExport' is not recognized as an internal or external command
,
operable program or batch file.

Posted on 12 Jun 2014 by Jeff Eberhard

I think there might be a typo in your script. In the line that runs java there should be a space between the quote after java and before oracle.apex.APEXExport

Posted on 12 Jun 2014 by mattk

Thanks for the prompt reply - i seem to be getting the below message now ?


C:\WINDOWS\system32>REM *****************************************************************

C:\WINDOWS\system32>REM * PARAMETERS TO MODIFY

C:\WINDOWS\system32>REM *****************************************************************

C:\WINDOWS\system32>set oracleSID=XE

C:\WINDOWS\system32>set oracleSystemPass=oracle

C:\WINDOWS\system32>set oraclePort=1521

C:\WINDOWS\system32>set oracleHost=localhost

C:\WINDOWS\system32>set XEOraclePath    =C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar

C:\WINDOWS\system32>set ApexUtilityPath=C:\apex\utilities

C:\WINDOWS\system32>set JAVAPATH=C:\Program Files (x86)\Java\jre7\bin

C:\WINDOWS\system32>REM *****************************************************************

C:\WINDOWS\system32>set AppId=4950

C:\WINDOWS\system32>cd C:\apex\utilities

C:\apex\utilities>set CLASSPATH=;;C:\apex\utilities

C:\apex\utilities>REM *****************************************************************

C:\apex\utilities>REM Export the application

C:\apex\utilities>REM *****************************************************************

C:\apex\utilities>"C:\Program Files (x86)\Java\jre7\bin\java" oracle.apex.APEXExport  -db localhost:1521:XE  -user syste
m -password oracle -applicationid 4950
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/jdbc/OracleDriver
        at oracle.apex.APEXExport.main(APEXExport.java:693)
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        ... 1 more

C:\apex\utilities>pause
Press any key to continue . . .

Posted on 12 Jun 2014 by Jeff Eberhard

Change the lines that sets the XEOraclePath to one line:

set XEOraclePath=C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar

Posted on 12 Jun 2014 by mattk

perfect thank you ever so much

Posted on 22 Aug 2015 by "Aubrey"

Jeff, firstly thanks for the detailed and very helpful posting. It has just helped me successfully upgrade Apex from version 4.0.2 to 4.2.6 AND preserve Application 4950. One thing I can add addresses the question raised by Mc and David (back in 2012) about the ORA-06550: line 2, column 26: PLS-00302: component 'USER_IS_DBA' must be declared ORA-06550: line 1, column 44: PL/SQL: ..error that presents when you try to open the Getting Started app after the upgrade and re-import of 4950. This had me scratching my head for a few hours but the answer was to be found in Mike Smithers' excellent blog.

https://mikesmithers.wordpress.com/2015/05/24/migrating-the-xe-database-management-application-to-a-new-version-of-apex/

The error we've seen is attributable to some refactoring that's gone on between the 4.0 and 4.2 versions, so that USER_IS_DBA now resides in a different package, causing the reference to it in the 4.0.2 export script to fail when you attempt to re-import to 4.2.6: I'd recommend reading Mike's article in its entirety, but the crux here is that the line p_scheme=>'return nvl(wwv_flow.g_flow_step_id,-1) in (1,101)'||chr(10)|| ' or wwv_flow_login.user_is_dba(p_username => :APP_USER);'</code> ... needs to be changed to p_scheme=>'return nvl(wwv_flow.g_flow_step_id,-1) in (1,101)'||chr(10)|| ' or wwv_flow_security.user_is_dba(p_username => :APP_USER); Note the change from wwv_flow_login to wwv_flow_security. Apologies for bumping an old posting but here's hoping someone finds this useful. PB aka Aubrey

EberApp Blog