Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

1. Download Software as Zip file from OTN
http://www.oracle.com/technetwork/developer-
tools/apex/downloads/index.html

2.Unzip apex_5.0_en.zip Software to ORACLE_HOME directory,
it will create a directory called Apex

3. Login to SQLPLUS as SYSDBA in database where you will
create the Apex instance. Create a tablespace called
“apex” for the Apex 5 installation

$ cd $ORACLE_HOME/apex

SELECT dbms_xdb.gethttpport FROM dual;

EXEC DBMS_XDB.SETHTTPPORT(0);

4. — Full development environment. Run apexins.sql

@apexins.sql tablespace_apex tablespace_files
tablespace_temp images

tablespace_apex is the name of the tablespace for the
Oracle Application Express application user.

tablespace_files is the name of the tablespace for the
Oracle Application Express files user.

tablespace_temp is the name of the temporary tablespace or
tablespace group.

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apexins.sql apex apex temp /i/

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apxldimg.sql APEX_HOME

APEX_HOME is the directory you specified when unzipping
the file, in our case it is the ORACLE_HOME directory

SQL> @apxldimg.sql /u02/app/oracle/product/11.2.0/dbhome_1

5. Run apxchpwd.sql to create Admin account and Reset the
password —
@apxchpwd.sql

Alter/Unlock the anonymous account


alter user anonymous account unlock;

If you get this error ORA-20001: Password validation
failed.

Then there is a bug in Apex 5 for the password validation

–Execute Below Query and Generate DATA as INSERT
statements–

select t.name, t.value--, t.pref_desc
from apex_040100.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

— Insert Data into APEX_050000 schema and Check again the
values —

select t.name, t.value--, t.pref_desc
from apex_050000.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

—————————————————-
Configuring the PL/SQL Embedded Gateway
—————————————————-

6. Running the apex_epg_config.sql Configuration Script to
the base directory where Apex software was unzipped. In
our case it is the DB home directory

@apex_epg_config.sql
/u02/app/oracle/product/11.2.0/dbhome_1

7. Conifuringthe Oracle XML DB Protocol Server Port

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8080);

8. Enabling Network Services in Oracle Database 11g or
Later

By default, the ability to interact with network services
is disabled in Oracle Database 11g Release 1 or 2 or
later. Therefore, if you are running Oracle Application
Express with Oracle Database 11g Release 1 or 2 or later,
you must use the new DBMS_NETWORK_ACL_ADMIN package to
grant connect privileges to any host for the APEX_050000
database user. Failing to grant these privileges

Grant connect privileges to any host for the APEX_050000
database user

— for Apex 5 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_050000
-- the "connect" privilege if APEX_050000 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;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', 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_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

— For Apex 4 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200
-- 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;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', 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_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Now access the URL for Apex, becuase we configured the
Embedded PL/SQL Gateway, go to:

http://hostname:port/apex/apex_admin

In case you get below error then check if port 8080 is
taken by some other service on the server, change the port
to a new one and restart the database.

“APEX.ERROR.ERROR_PAGE.UNHANDLED_ERROR”

EXEC DBMS_XDB.SETHTTPPORT(8898);

alter system register;

srvctl stop database -d dbname

srvctl start database -d dbname

Advertisements

One thought on “Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

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