DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, February 5, 2018

Install and Configure Oracle Application Express Apex 5.0.x with 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 temp_tablespace 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.

temp_tablespace 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/


@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  /home/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;
ALTER USER XDB ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER FLOWS_FILES ACCOUNT UNLOCK;


—————————————————-
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 /home/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(7888);

[orabssd1@wwibssdb301~]$ netstat -nlp |grep 7888

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.  

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;