Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Configure APEX in Oracle Database 11gR2

Oracle Application Express(APEX) comes pre-installed with the Oracle database from version 11g onwards. Oracle 11gR2, comes with APEX version 3.2.1. However, before you actually start using the pre-installed Oracle APEX, you need to configure it. The installation guide provides the complete details.

I configured APEX to run with the embedded PL/SQL gateway. The steps I followed for this are:

1. Configure the embedded PL/SQL gateway by running the configuration script apxconf.sql under $ORACLE_HOME/apex. When prompted, enter a password for the APEX admin account and either provide a port number for the XML DB HTTP server or choose the default(8080) one.


[oracle@db11gr2 apex]$ cd $ORACLE_HOME
[oracle@db11gr2 db_1]$ cd apex/
[oracle@db11gr2 apex]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 11 00:48:32 2012

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

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @apxconf

PORT
----------
 8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.
Enter a password for the ADMIN user []
Enter a port for the XDB HTTP listener [ 8080]
...changing HTTP Port

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.
Commit complete.

SQL>

2. Unlock the ANONYMOUS account


SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

3. Verify whether the XML DB HTTP server is enabled by checking the port number


SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
 8080

SQL>

In case the port number returns 0, enable the server by issuing the following commands in SQL Plus

EXEC DBMS_XDB.SETHTTPPORT(port_number);
COMMIT;

4. Enable network services for APEX. This is not a mandatory step but is required in case you want to access network services from APEX. Please check the installation document for additional details. One of the results of not implementing this step is that clicking on the Find link in the APEX help page will result in the following error:

To allow access to network services, grant connect privileges to any host for the APEX_030200 database user. This can be done by using the following script. Please check the installation guide on how to allow more fine grained access.


DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_030200
 -- the "connect" privilege if APEX_030200 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
 -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_030200', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_030200', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

5. Enable indexing of online help. This step also is not mandatory, but not implementing this may result in the following error while clicking the Find link in the APEX help pages

Enabling the indexing of online help, requires the permission to use an Oracle Text URL datastore to be granted to the APEX_030200 database user.  To implement this step, first find the database role which has been granted this privilege. If no such role exists, then create a role, assign it to the APEX_030200 database user and grant the required permission to the role. If the role exists, then just grant this role to the APEX_030200 user. In my case, the role did not exist initially.


SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
--------------------------------------------------------------------------------

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_030200;

Grant succeeded.

SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

6. The configuration steps are complete and all that remain are creating the workspace and users. Create an APEX workspace by logging in as the administrator using the all URL, http://hostname:port/apex/apex_admin. Here ‘hostname’ will be the name of the system where the database is installed and ‘port’ is the port number specified in step#1. The username will be ‘admin’ while the password is the one specified in step#1.

To create a new workspace, go to Manage Workspaces> Create Workspace. In Identify Workspace, enter a workspace name and description and click Next.

In Identify Schema, enter the details as required. I opted for the following settings:

Re-use existing Schema? No
Schema Name: xxcustom
Schema Password: xxcustom
Space Quota: 10 MB
This will create a new schema named ‘xxcustom’ and 10MB of space will be assigned to it.

In Identify Administrator, enter the details as required. I entered the following options:

Administrator Username:xxuser
Adminsitrator Password:xxuser
First Name:
Last Name:
Email:test@example.com
This will create the APEX user, ‘xxuser’ since it does not exist. It will be created both as a developer and an workspace administrator.

Confirm the selections and click on Create.

7.  Additional users can be created by logging in as the administrator and navigating to Manage Workspace> Manage Developers and Users> Create.

8. Once an user has been created, they can log in using the URL, http://hostname:port/apex

About these ads

2 responses to “Configure APEX in Oracle Database 11gR2

  1. Pingback: Upgrade APEX 3.2.1 to 4.1.1 on Oracle Database 11gR2 « Oracle Apps Notes

  2. withheld July 11, 2012 at 2:37 pm

    after following your instructions

    http://127.0.0.1:8080/apex/apex_admin

    gives
    HTTP Status 404 – /apex

    type Status report

    message /apex

    description The requested resource (/apex) is not available.
    Apache Tomcat/6.0.35

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: