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

Category: Uncategorized

Tags:

5 comments

    1. hi..i am getting error while executing apexins.sql file
      ORA-12911: permanent tablespace cannot be temporary tablespace

      1. Check your syntax, maybe you are adding the APEX tablespace name where the TEMP tablespace should be defined.

  1. Hello Shadab…still facing error related to “there is a problem with your environment because the application files has not been loaded properly” …Earlier it worked…now after providing the credentials…it will prompt for it again and again…and finally gives this error

Leave a Reply

Article by: Shadab Mohammad