A complete list of APEX’s new features can be found here: http:\/\/www.oracle.com\/technetwork\/developer-tools\/apex\/application-express\/apex-41-new-features-459652.html<\/a><\/p>\n As described in the introduction, the task is a migration from an apex 3.0 to an apex 4.1 environment. In order to prepare such a migration, it is best practice to start with a database backup and an applicative export as presented below. These steps can be really useful in case of problems or mistakes.<\/p>\n Connection as admin on the \u00ab\u00a0apex_admin\u00a0\u00bb page: http:\/\/server.domain:port\/pls\/apex\/apex_admin<\/a><\/p>\n \u00a0 The first thing to do before doing any migration command is to export the workspace(s). According to the Oracle documentation, a workspace is “a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private.” In fact a workspace export contains user details such as username\/password, default schema, rights, e-mail address, etc.<\/p>\n Then you have to select the workspace you want to export, in this specific case the schema name is \u00ab\u00a0BSANET\u00a0\u00bb.<\/p>\n Depending on your platform, you have to choose between DOS and UNIX file format. Then you simply have to click on “Save File” to dump your workspace.<\/p>\n Application(s) still have to be exported. An application is a collection of pages linked together using tabs, buttons, or hypertext links. To export the application(s) you have to connect to the following URL: http:\/\/server.domain:port\/pls\/apex\/<\/a><\/p>\n Once connected, you have to click on the application you wish to export. You then need to click on the export\/import tool and select “Export>Application” as demonstrated below:<\/p>\n Select the right “File Format” according to your infrastructure and the click “Export Application”.<\/p>\n Finally, before starting to install your new APEX version, it is best practice to also backup your apex directory.<\/p>\n oracle@myserver:\/u00\/app\/<\/a> [DEV112] tar -cvf apexbck.tar apex<\/span><\/p>\n Once each workspace and application are exported and the directory is backed up, you can start the APEX migration safely.<\/p>\n The first thing to check in order to migrate is your database version. Apex 4.1 is compatible with Oracle database starting from Oracle 10.2.0.3 as specified in the file apexins.sql<\/p>\n \u00a0<\/span><\/p>\n Additionally, it is always good to know which is your current APEX version.<\/p>\n Now, you can download APEX from the following URL: http:\/\/www.oracle.com\/technetwork\/developer-tools\/apex\/downloads\/index.html<\/a><\/p>\n Copy your Apex zip file you just downloaded in the path you want to install it in (e. g.: ORACLE_BASE\/product\/) and then unzip it.<\/p>\n oracle@myserver:\/home\/oracle\/ [DEV112] mv apex_4.1.zip \/u00\/app\/oracle\/product\/<\/span> Create your tablespace(s) where you want to install the APEX application and the APEX file. In our case we specified the same tablespace, APEX_41.<\/p>\n Finally, install APEX with the following script which is located in your “APEX_HOME” directory which in our case is \/u01\/app\/oracle\/product\/apex_4.1.0. The installer’s arguments are the following:<\/p>\n Arguments: Once the installation is finished, some post-installation tasks have to be performed. If you are using a Database Access Descriptor (DAD), you still have to specify where your images directory is located. If you miss this step, you will get a blank page even if you will probably see some HTML code in the page source:<\/p>\n oracle@atchoum:\/home\/oracle\/ [DEV112] vi \/u00\/app\/oracle\/product\/10.2_comp\/Apache\/modplsql\/conf\/dads.conf<\/span> If you want to change your old password, you can execute the script \u00ab\u00a0apxchpwd.sql\u00a0\u00bb.<\/p>\n According to APEX documentation: “By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 or 2.” Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 or 2, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040100 database user. Failing to grant these privileges results in issues with:<\/p>\n In order to avoid those issues, you have to execute the script below:<\/p>\n DECLARE If you want to grant connect privilege only to the localhost you can replace:<\/p>\n HOST=’*\u00a0‘ and DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’*’);<\/samp><\/p>\n by<\/p>\n HOST=’localhost’ and DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’localhost’);<\/samp><\/samp><\/p>\n To import a workspace, you have to log in as administrator (admin user) using the following URL: http:\/\/server.domain:port\/pls\/apex\/apex_admin. Once logged in, you can import your workspace from the menu “Manage Workspaces”. You simply have to click on “Import Workspace”:<\/p>\n In the Import Workspace menu, you finally have to select your export\/import file containing the workspace description – and follow the assistant:<\/p>\n Of course, you can import applications in your new APEX 4.1 environment. In order to import a schema you have to login into your workspace with your username\/password on the following URL: http:\/\/server.domain:port\/pls\/apex\/<\/a><\/p>\n Once logged in, click on the \u201cApplication Builder\u201d icon.<\/p>\n You should see all your applications on this screen and you have the possibility to import a new application.<\/p>\n Now, you simply have to follow the assistant in order to import your application. Do not forget to specify the right character set.<\/p>\n Click “Next” and once all parameters are validated, you can finish by clicking \u201cInstall\u201d:<\/p>\n In my example, I got the following errors the first time I started the apexins.sql script:<\/p>\n \u00a0<\/samp><\/p>\n \u00a0<\/samp><\/p>\n After some investigations, I found the following piece of code in the coreins.sql script which is launched by apexins.sql. UFROM was set with the value FLOWS_0301000 which was obviously wrong according to the APEX version (3.0.0.00.20).<\/p>\n \u00a0<\/samp><\/p>\n Indeed, I saw that several \u00ab FLOWS_0XXXXX \u00bb users were present in my database. That was the root cause of the error specified above.<\/p>\n In order to know which is the right schema, I tried to get more information, such as objects present in each schema:<\/p>\n Then you simply remove your installation with the script apxremov.sql<\/em> and start the installation anew.<\/p>\n Oracle Application Express version 4.1 offers many possibilities and introduces a lot of new features, as described in the introduction. The new interface is easy and allows to develop professional applications quickly. I hope this documention will help you handle APEX migration projects. Note that if you want to test the new features and the interface, you can create a free account on http:\/\/apex.oracle.com<\/a>. \u00a0<\/p>\n Oracle Application Express – APEX 4.1 Introduces many new features such as :<\/p>\nPre migration steps<\/h3>\n
![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/001.jpg\" "\\"Apex")
<\/p>\n![\"Workspace](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/002.jpg\" "\\"Workspace")
<\/p>\n![\"Workspace](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/003.jpg\" "\\"Workspace")
<\/p>\n![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/004.jpg\" "\\"Apex")
<\/p>\n![\"APEX](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/005.jpg\" "\\"APEX")
<\/p>\n![\"Export](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/006.jpg\" "\\"Export")
<\/p>\n![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/007.jpg\" "\\"Apex")
<\/p>\nApex installation and migration<\/h3>\n
SQL> select * from v$version;\nBANNER\n-----------------------------------------------------------------------------\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production\nPL\/SQL Release 11.2.0.1.0 - Production\nCORE\u00a0\u00a0\u00a0 11.2.0.1.0\u00a0\u00a0\u00a0\u00a0\u00a0 Production\nTNS for Linux: Version 11.2.0.1.0 - Production\nNLSRTL Version 11.2.0.1.0 - Production<\/pre>\n
SQL> select version from dba_registry where comp_id='APEX';\nVERSION\n------------------------------\n3.0.0.00.20<\/pre>\n
\noracle@myserver:\/u00\/app\/oracle\/product\/ [DEV112] unzip apex_4.1.zip<\/span>
\noracle@myserver:\/u00\/app\/oracle\/product\/ [DEV112] mv apex apex_4.1.0<\/span><\/p>\nSQL> create tablespace APEX_41 datafile '\/u01\/oradata\/DEV112\/APEX_41.dbf' size 200M autoextend on maxsize 500M;\nTablespace created.<\/pre>\n
\nPosition 1: Name of tablespace for Application Express application user
\nPosition 2: Name of tablespace for Application Express files user
\nPosition 3: Name of temporary tablespace
\nPosition 4: Virtual directory for APEX images<\/p>\nSQL> @apexins.sql APEX_41 APEX_41 TEMP \/i\/\n\u2026\n\u2026\n...Begin key object existence check 12:55:17\n...Completed key object existence check 12:55:17\n...Setting DBMS Registry 12:55:17\n...Setting DBMS Registry Complete 12:55:17\n...Exiting validate 12:55:17\ntiming for: Validate Installation\nElapsed: 00:03:09.62\ntiming for: Development Installation\nElapsed: 00:11:39.75\nDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production<\/pre>\n
\n\u2026<\/span>
\n\u2026<\/span>
\n#Alias \/i\/\u00a0 “\/u00\/app\/apex\/images\/” #Old image directory<\/span>
\nAlias \/i\/\u00a0 “\/u00\/app\/oracle\/product\/apex_4.1.0\/images\/” # New image directory<\/span>
\n\u2026<\/span>
\n\u2026<\/span><\/p>\nSQL> @apxchpwd\nEnter a value below for the password for the Application Express ADMIN user.\nEnter a password for the ADMIN user\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 []\nSession altered.\n...changing password for ADMIN\nPL\/SQL procedure successfully completed.\nCommit complete.<\/pre>\n
\n
\nACL_PATH\u00a0 VARCHAR2(4000);
\nACL_ID\u00a0\u00a0\u00a0 RAW(16);
\nBEGIN
\n— Look for the ACL currently assigned to ‘*’ and give APEX_040100
\n— the “connect” privilege if APEX_040100 does not have the privilege yet.
\nSELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
\nWHERE HOST = ‘*’ AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
\n— Before checking the privilege, ensure that the ACL is valid
\n— (for example, does not contain stale references to dropped users).
\n— If it does, the following exception will be raised:
\n—
\n— ORA-44416: Invalid ACL: Unresolved principal ‘APEX_040100’
\n— ORA-06512: at “XDB.DBMS_XDBZ”, line …
\n—
\nSELECT SYS_OP_R2O(extractValue(P.RES, ‘\/Resource\/XMLRef’)) INTO ACL_ID
\nFROM XDB.XDB$ACL A, PATH_VIEW P
\nWHERE extractValue(P.RES, ‘\/Resource\/XMLRef’) = REF(A) AND
\nEQUALS_PATH(P.RES, ACL_PATH) = 1;
\nDBMS_XDBZ.ValidateACL(ACL_ID);
\nIF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, ‘APEX_040100’,
\n‘connect’) IS NULL THEN
\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
\n‘APEX_040100’, TRUE, ‘connect’);
\nEND IF;
\nEXCEPTION
\n— When no ACL has been assigned to ‘*’.
\nWHEN NO_DATA_FOUND THEN
\nDBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘power_users.xml’,
\n‘ACL that lets power users to connect to everywhere’,
\n‘APEX_040100’, TRUE, ‘connect’);
\nDBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’*’);
\nEND;
\n\/
\nCOMMIT;<\/p>\nImporting APEX workspaces<\/h3>\n
![\"Import](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/importworkspace.png\" "\\"Import")
<\/p>\n![\"Import](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/importworkspace2.png\" "\\"Import")
<\/p>\nImporting APEX applications<\/h3>\n
![\"Importing](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/008.jpg\" "\\"Importing")
<\/p>\n![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/009.jpg\" "\\"Apex")
<\/p>\n![\"Application](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/010.jpg\" "\\"Application")
<\/p>\n![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/011.jpg\" "\\"Apex")
<\/p>\n![\"Apex](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/012.jpg\" "\\"Apex")
<\/p>\nErrors & Problems – ORA-04042 and ORA-06512<\/h3>\n
<\/samp>User altered.\nUser altered.\nbegin\n*\nERROR at line 1:\nORA-04042: procedure, function, package, or package body does not exist\nORA-06512: at line 3<\/pre>\n
alter user ^APPUN account lock password expire;\nalter user FLOWS_FILES account lock password expire;\n--\n-- In the event of an upgrade, grant execute on the prior versions wwv_flows_version\n-- procedure to the current APEX schema\n--\nbegin\nif '^UPGRADE' = '2' then\n execute immediate 'grant execute on ^UFROM..wwv_flows_version to ^APPUN';\nend if;\nend;\n\/<\/pre>\n
SQL> select username from dba_users where lower(username) like 'flows%';\nUSERNAME\n------------------------------\nFLOWS_FILES\nFLOWS_030100\nFLOWS_030000<\/pre>\n
SQL> select object_name from dba_objects where owner='FLOWS_030100';\nno rows selected\nObviously the schema FLOWS_030100 was obsolete. I dropped it.\nSQL> drop user FLOWS_030100 cascade;\nUser dropped.<\/pre>\n
Conclusion<\/h3>\n
\n<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"