Aimstar

AIMSTAR Blog

Installing APEX 5 for use with Oracle REST Data Services (ORDS)

Posted by Theodore Williams on Jun 1, 2017 1:15:00 PM

Installing APEX 5 in Oracle for use with Oracle Rest Data Services (ORDS)

 Installing Oracle APEX 5 for Use with Oracle REST Data Services (ORDS)

 

Oracle databases come with APEX pre-installed.  But the version installed may not be the version that you want.  This article details how to install APEX 5 in an Oracle database.

Relevant Topics:  Oracle DBA, Oracle APEX, Oracle Database Development, Oracle REST Data Services

ORDS-APEX-WebLogic-WhitePaper

 

Check the version of APEX currently in the database as follows:

SQL> select version from dba_registry where comp_id='APEX';

 

VERSION

------------------------------

3.2.1.00.10

 

SQL>

 

In this case, APEX version 3.2 is installed.  We will first uninstall version 3.2 before installing version 5.

If this is the version of APEX installed on database installation, go to the APEX directory of the $ORACLE_HOME.

[oracle@Forge apex]$ cd $ORACLE_HOME/apex

 

Log into Oracle and run the script @apxremov.sql from the $ORACLE_HOME/apex directory.  If this is not the default version of APEX that you wish to remove, please run the @apxremov.sql script in the corresponding apex directory.

[oracle@Forge apex]$ cd $ORACLE_HOME/apex

[oracle@Forge apex]$ ls

apex_epg_config_core.sql  apxdevrm.sql  apxrelod.sql  catapx.sql      owa

apex_epg_config.sql       apxdvins.sql  apxremov.sql  core            patches

apexins.sql               apxe101.sql   apxrtins.sql  coreins.sql     utilities

apexvalidate.sql          apxe102.sql   apxsqler.sql  devins.sql

apxchpwd.sql              apxe111.sql   apxxemig.sql  endins.sql

apxconf.sql               apxldimg.sql  apxxepwd.sql  images

apxdbmig.sql              apxpatch.sql  builder       load_trans.sql

[oracle@Forge apex]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 14:37:11 2017

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> @apxremov.sql

...Removing Application Express

old   1: alter session set current_schema = &APPUN

new   1: alter session set current_schema = APEX_030200

 

Session altered.

 

PL/SQL procedure successfully completed.

 

no rows selected

 

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

 

PL/SQL procedure successfully completed.

 

Session altered.

 

PL/SQL procedure successfully completed.

 

old   1: drop user &APPUN cascade

new   1: drop user APEX_030200 cascade

 

User dropped.

 

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

 

PL/SQL procedure successfully completed.

 

old   5:     if '&UPGRADE' = '1' then

new   5:     if '1' = '1' then

 

PL/SQL procedure successfully completed.

 

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

 

PL/SQL procedure successfully completed.

 

...Application Express Removed

SQL>

 

You can use the query from before to confirm that APEX has been removed.

 

SQL> select version from dba_registry where comp_id='APEX';

 

no rows selected

 

SQL>

 

Now, we can install APEX 5 into the database.  First create new tablespaces:

-- Tablespace for the APEX application user

 

CREATE TABLESPACE APEX_DATA DATAFILE

  '+DATA_DG1/prddb1/datafile/apex_data01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 2048M MAXSIZE 10240M

LOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

 

Tablespace created.

 

-- Tablespace for the APEX files user

 

CREATE TABLESPACE APEX_FILES_DATA DATAFILE

  '+DATA_DG1/prddb1/datafile/apex_files_data01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 2048M MAXSIZE 10240M

LOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

 

Tablespace created.

 

Download and unzip APEX 5.  Change to the apex directory and run the script apexins.sql as follows:

@apexins.sql apex_app_tspace apex_files_tspace temp virtual_images_directory

 

[oracle@Forge apex]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:11:33 2017

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> @apexins.sql APEX_DATA APEX_FILES_DATA TEMP /i/

 

You will get the following message upon successful installation.

 

PL/SQL procedure successfully completed.

 

Since we are going to use Oracle Rest Data Service (ORDS), we must also configure ORDS.  Run the script apex_rest_config.sql to configure ORDS.

 

[oracle@Forge apex]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:32:19 2017

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> @apex_rest_config.sql

 

PL/SQL procedure successfully completed.

 

 

 

 

PL/SQL procedure successfully completed.

 

 

Enter a password for the APEX_LISTENER user              []

Enter a password for the APEX_REST_PUBLIC_USER user              []

...create APEX_LISTENER user

 

PL/SQL procedure successfully completed.

 

...create APEX_REST_PUBLIC_USER user

 

PL/SQL procedure successfully completed.

 

.

.

.

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

Now, we need to create an APEX instance administrator account by running the script apxchpwd.sql.

 

[oracle@Forge apex]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:40:33 2017

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> @apxchpwd.sql

========================================================

This script can be used to change the password of an Application Express

instance administrator. If the user does not yet exist, a user record will be

created.

=========================================================

Enter the administrator's username [ADMIN]

User "ADMIN" does not yet exist and will be created.

Enter ADMIN's email [ADMIN] xxxxxxxx

Enter ADMIN's password []

Created instance administrator ADMIN.

 

SQL>

 

The APEX_PUBLIC_USER account is locked with a random password by default.  We must unlock it and change the password. 

 

[oracle@Forge apex]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 25 17:53:14 2017

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

 

User altered.

 

SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxxxxx;

 

User altered.

 

SQL>

 

Note that the APEX_PUBLIC_USER password will expire in 180 days by default.  Once it expires, your APEX instance will not work until the password is changed.  This can be prevented by changing APEX_PUBLIC_USER to a profile with  a longer or unlimited lifetime (parameter PASSWORD_LIFE_TIME).

At this point, the installation is complete on the database side. 

 

WebLogic-APEX-Oracle Checklist

 

Topics: Oracle DBA, Oracle APEX, Oracle database development, Oracle, Oracle Rest Data Services

Oracle APEX Configuration White Paper

Get detailed information on integrating WebLogic, APEX, and Oracle Rest Data Services

Oracle APEX allows rapid development of database applications utilizing the Oracle database.  Oracle WebLogic may be used as the web server for Oracle APEX applications by utilizing Oracle REST Data Services (ORDS), formerly named the APEX Listener.  This white paper details the steps needed to set up an Oracle APEX environment using Oracle Rest Data Services (ORDS) on Oracle WebLogic.

Please click the button below to get detailed information on:

  • Installing APEX
  • Configuring WebLogic
  • Securing WebLogic
  • Installing ORDS
  • Configuring APEX for LDAP Authentication

 Download This Free White Paper

Subscribe to Email Updates

Recent Posts