Oracle Application Express (APEX) Install, Upgrade and use with DPLS and EPG: December 2011

Friday, December 9, 2011

APEX Errors During the Workspace Creation

I encountered the following error. May be other people created the workspace and some were hanging in there.

Errors 1: ORA-00001 APEX APEX_040100.WWV_FLOW_PROV_COMPANY_NAME

Error 2: ORA-20002: Request 2703406260663232 could not be processed. -1 ORA-00001: unique constraint (APEX_040100.WWV_FLOW_COMPANIES_IDX1) violated

How I solved it:

I traced the Index and issued the following statements:

select COMPANY_NAME from apex_040100.WWV_FLOW_PROVISION_COMPANY ;

select SHORT_NAME from apex_040100.WWV_FLOW_COMPANIES;

They returned the value same as I was entering. However that workspace and company does not exist anywhere in the DB, so I did following :

delete from apex_040100.WWV_FLOW_COMPANIES where SHORT_NAME='WORK';

delete from apex_040100.WWV_FLOW_PROVISION_COMPANY where COMPANY_NAME='WORK';

commit;

-- and Voila - the issue resolved !!

APEX Listener 1.2 with JBOSS7.0.2 (Jboss7)

APEX with JBOSS 7:

Well, I spent 3 days to get this going. It is not pretty but it works and I am sharing all the mess I had to go through to make it work.

1- Download JDK 7 from here - http://www.oracle.com/technetwork/java/javase/downloads/index.html

2- Download JBOSS 7 from here http://www.jboss.org/jbossas/downloads/
I downloaded 7.0.2 final

3- unzip JDK 7 and asign th eunzipped directorya s JAVA_HOME and add to the path. For me:

export JAVA_HOME=/home/oracle/jdk1.7.0_01
export PATH=$JAVA_HOME/bin:$PATH

4- unzip the jboss and assign this as JBOSS_HOME. for me it is:

JBOSS_HOME=/home/oracle/jboss702

(Please do not start the jboss yet)

5- Please copy the images for APEX as defined in step 4 here - http://apexdplsepg.blogspot.com/2011/12/install-upgrade.html

6- Untar the images from step5 to $JBOSS_HOME/standalone/deployments. for me it is:

/home/oracle/jboss702/standalone/deployments

7- Download the APEX listener and extract the apex.war from here -

http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html

8- Please do this as indicated below - Very important steps below.

cd $JBOSS_HOME/standalone/deployments

mkdir apex.war

cd apex.war

jar -xvf /homne/oracle/dwnld/apex/apex.war

(Please note that I downloaded and unzipped the APEX listener at /home/oracle/dwnld/apex location)

mkdir /home/oracle/aptmp

cd /home/oracle/aptmp

jar -xvf $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/lib/apex.jar

cd META-INF

vi oracle.dbtools.rt.services

delete the following line from it:

oracle.dbtools.rt.uri.WarURIResolver

save and exit

now repackage this as:

cd ../ to go to /home/oracle/aptmp

jar -cvf /home/oracle/apex.jar *

(This will create apex.jar in the /home/oracle direvctory)

rm -f $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/lib/apex.jar

cp apex.jar $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/lib/

cd $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF

vi adminTemplate.html

Replace all scripts to /i/scripts. This is because for some reason when JBOSS deploys the apex.war there is no directory access inside the apex.war and I did not want to put a lot of time to find out as where this permission is set. I look arounf in the web.xml and others but could not find it. However i.war is accessible for other APEX images so I decided to do this way.

Mine looks like:

amghost3:Oracle ->> grep scripts adminTemplate.html

<link rel="shortcut icon" type="image/vnd.microsoft.icon" href="/i/scripts/images/oracle.ico" />
<link rel="stylesheet" type="text/css" href="/i/scripts/css/styles.css" />
<link rel="stylesheet" type="text/css" href="/i/scripts/css/jquery-ui-1.8.5.custom.css" />
<script type="text/javascript" src="/i/scripts/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="/i/scripts/js/jquery-ui-1.8.5.custom.min.js"></script>
<img src="/i/scripts/images/oracle_logo.gif" width="133" height="18"alt="Oracle" class="banner" />

cd ../ (One step up to /home/oracle/jboss702/standalone/deployments/apex.war)

cp -R scripts ../i.war/

now cd $JBOSS_HOME/bin

edit the jboss-admin.sh and change the last line as:

eval \"$JAVA\" $JAVA_OPTS -jar \"$JBOSS_HOME/jboss-modules.jar\" -logmodule "org.jboss.logmanager" -
mp \"$JBOSS_HOME/modules\" org.jboss.as.cli '--connect controller=amghost3.cup.com:9009 "$@"'

(Please note that I decided to run the web server on 8008 port and 9009 and 9008 for Admin. console)

vi standalone.conf

change the JAVA_OPTS. Mine is:

if [ "x$JAVA_OPTS" = "x" ]; then
JAVA_OPTS="-Xms64m -Xmx512m -XX:MaxPermSize=256m -Djava.net.preferIPv4Stack=true -Dorg.jboss.resolver.warning=true -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000"
JAVA_OPTS="$JAVA_OPTS -Djboss.modules.system.pkgs=$JBOSS_MODULES_SYSTEM_PKGS -Djava.awt.headless=true -Dapex.home=/home/oracle/apex -Dapex.images=/home/oracle/jboss702/standalone/deployments/i.war"

(Please note that the reason I pu the apex variables here is because when we do the listenerConfigure - it will create the conf file in /tmp/apex - so to make it appropriate I did this)

now cd to $JBOSS_HOME/standalone/configuration and edit teh standalone.xml file. Please note the following changes. These changes are to have HTTP/1.1 in UTF-8 format and Admin and Manager Roles and users for APEX.

Just below </extension at the top of the file add following:

<system-properties>
<property name="org.apache.catalina.connector.URI_ENCODING" value="UTF-8"/>
<property name="org.apache.catalina.connector.USE_BODY_ENCODING_FOR_QUERY_STRING" value="true"/>
</system-properties>

then I changed the Admin console port and locked them to use the username as:

<native-interface interface="management" port="9009" security-realm="PropertiesMgmtSecurityRealm"/>
<http-interface interface="management" port="9008" security-realm="PropertiesMgmtSecurityRealm"/>

Now look for the security-domain where other is mentioned change it as :

<security-domains>
<security-domain name="other" cache-type="default">
<authentication>
<login-module code="UsersRoles" flag="required"/>
</authentication>
</security-domain>
<security-domain name="form-auth" cache-type="default">
<authentication>
<login-module code="UsersRoles" flag="required">
<module-option name="usersProperties" value="users.properties"/>
<module-option name="rolesProperties" value="roles.properties"/>
</login-module>
</authentication>
</security-domain>
</security-domains>

Now at the interfaces change the 127.0.0.1 to the name like:

<interfaces>
<interface name="management">
<inet-address value="${jboss.bind.address.management:amghost3.cup.com}"/>
</interface>
<interface name="public">
<inet-address value="${jboss.bind.address:amghost3.cup.com}"/>
</interface>
</interfaces>

and the right beneath it the web port:

<socket-binding name="http" port="8008"/>

Now create followin 2 files "users.properties" and "roles.properties" as:

amghost3:Oracle ->> more users.properties
#Format: username=password
#
adminlsn=jboss7
mnglsn=jboss7

amghost3:Oracle ->> more roles.properties
adminlsn=Admin
adminlsn.Roles=Admin
mnglsn=Manager
mnglsn.Roles=Manager

Now edit the mgmt-users.properties and uncomment the admin as:

admin=jboss7

Now remove the xmpparser from $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/lib as:

rm -f $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/lib/xmlparserv2-11.2.0.jar

Oh Well - Bear with me - couple more steps ---

Now start the JBOSS as cd $JBOSS_HOME/bin

./standalone.sh

Now cd $JBOSS_HOME/standalone/deployments

touch i.war.dodeploy

wait until you see "i.war.deployed"

Now touch apex.war.dodeploy

Now wait until it says apex.war.deployed

Now the magic part :

http://<host>:8008/apex/listenerConfigure

and after inputs above you will end up at:

Now shutdown the JBOSS and do following:

amghost3:Oracle ->> cd /home/oracle/apex
amghost3:Oracle ->> ls
apex-config.xml bdb

vi apex-config.xml

and remove following line from the top of the fikle:

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

save and exit

Now copy users.properties and roles.properties from $JBOSS_HOME/standalone/configuration to $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/classes

(we will have to make this directory)

amghost3:Oracle ->> mkdir $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/classes

amghost3:Oracle ->> pwd
/home/oracle/jboss702/standalone/configuration

amghost3:Oracle ->> cp users.properties $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF/

amghost3:Oracle ->> cp roles.properties $JBOSS_HOME/standalone/deployments/apex.war/WEB-INF

amghost3:Oracle ->> pwd
/home/oracle/jboss702/standalone/deployments/apex.war/WEB-INF/classes
amghost3:Oracle ->> ls
roles.properties users.properties
amghost3Oracle ->>

Now start the jboss agin and test the listenerAdmin tio verify the last puzle of user and roles:

cd $JBOSS_HOME/bin

./standalone.sh

and Voila !! it works ..

It should work on Jboss 7.1 as well but if it does not then please comment and I will try tp see what need to be fixed !!

Happy sharing !!

Thursday, December 8, 2011

APEX listener as web interface

Well this blog is getting bigger and bigger. However, I thought that there are some tricks for APEX as itself as Web interface - so sharing it. Linux x86-64 is my platform.

1- I installed xvfb so that when I start the apex listener - it will stay up. xvfb can be installed from open source here - ftp://ftp.xfree86.org/pub/XFree86/4.2.0/binaries/Linux-ix86-glibc22/

(Please note that it needs some fonts those can be found in above download as well. You need 75,100 fonts at the minimum)

2- Install JDK 6. i installed JDK6 update 29 from here - http://www.oracle.com/technetwork/java/javase/downloads/index.html

3- amghost3:Oracle ->> java -jar apex.war --help
java [options] -jar apex.war [--help]
Options:
-Dapex.home=/path/to/apex : Path to the folder used to store the
web container runtime, defaults to:
${java.io.tmpdir}/apex
-Dapex.port=nnnn : HTTP listen port, default 8080
-Dapex.ajp=nnnn : AJP (mod_jk) listen port, default none
If an AJP Port is specified then HTTP access is disabled
-Dapex.images=/images/location : Path to the folder containing static
resources required by APEX
-Dapex.erase=true : Erase the contents of ${apex.home}
before launching
--help : Print this usage message

4- Unzip/untar the images file those were done during the install/upgarde of APEX. Refere here at step 4 - http://apexdplsepg.blogspot.com/2011/12/install-upgrade.html

5- Install, configure and run

amghost3:Oracle ->>java -Dapex.home=/home/oracle/apex -Dapex.port=8888 -Dapex.images=/home/oracle/apex/images -jar apex.war
INFO: Starting: /home/oracle/dwnld/apex/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /home/oracle/apex
INFO: Using classpath: file:/home/oracle/apex/apex/____embedded/start.jar:file:/home/oracle/apex/apex/WEB-INF/lib/apex.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojmisc.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ucp.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/home/oracle/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/home/oracle/apex/apex/WEB-INF/lib/je-4.0.103.jar:file:/home/oracle/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:
INFO: Starting Embedded Web Container in: /home/oracle/apex
Enter a username for the APEX Listener Administrator [adminlistener]: adminlsn
Enter a password for adminlsn:
Confirm password for adminlsn:
Enter a username for the APEX Listener Manager [managerlistener]: mnglsn
Enter a password for mnglsn:
Confirm password for mnglsn:
Dec 9, 2011 8:37:22 AM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/home/oracle/apex/apex/WEB-INF/web.xml
Dec 9, 2011 8:37:22 AM ____bootstrap.Deployer deploy
INFO: deployed application path=/home/oracle/apex/apex/WEB-INF/web.xml
Using config file: /home/oracle/apex/apex-config.xml
APEX Listener version : 1.1.3.243.11.40
APEX Listener server info: Grizzly/1.9.18-o
Dec 9, 2011 8:37:23 AM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Fri Dec 09 08:37:23 PST 2011
INFO: Please complete configuration at: http://localhost:8888/apex/listenerConfigure
/usr/lib64/firefox-3.0.5/run-mozilla.sh: line 131: 18517 Segmentation fault "$prog" ${1
+"$@"}

The above error can be ignored as it is trying to start the mozilla. What it means that you shouls get the mozilla installed and the mozill.sh should be in the path at the Linux box where you have this apex listener launched. ALso do all this with VNC so that it does not go to XVFB port but to come live at the VNC port. I have done this. If need Please comment and I will post the steps then.

6- Now you can run http://amghost3.cup.com:8888/apex/listenerAdmin
and test your adminlsn and password to get in.

At this same time on the windows where APEX listener is started - you will see

Database connection not yet configured
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
MaxConnectionReuseCount=1000

7- Now Stop and start again

To Stop - you need to ctrl+C out from where it was run.

To start - you have 2 options:

A- Since java is in my path, I can simply do this:

amghost3:Oracle ->> java -version
java version "1.6.0_29"
Java(TM) SE Runtime Environment (build 1.6.0_29-b11)
Java HotSpot(TM) 64-Bit Server VM (build 20.4-b02, mixed mode)

amghost3:Oracle ->> java -Dapex.home=/home/oracle/apex -Dapex.port=8888 -Dapex.images=/home/oracle/apex/images -jar apex.war
INFO: Starting: /home/oracle/dwnld/apex/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /home/oracle/apex
INFO: Using classpath: file:/home/oracle/apex/apex/____embedded/start.jar:file:/home/oracle/apex/apex/WEB-INF/lib/apex.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojmisc.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ucp.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/home/oracle/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/home/oracle/apex/apex/WEB-INF/lib/je-4.0.103.jar:file:/home/oracle/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:
INFO: Starting Embedded Web Container in: /home/oracle/apex
Dec 9, 2011 8:59:10 AM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/home/oracle/apex/apex/WEB-INF/web.xml
Dec 9, 2011 8:59:11 AM ____bootstrap.Deployer deploy
INFO: deployed application path=/home/oracle/apex/apex/WEB-INF/web.xm
Using config file: /home/oracle/apex/apex-config.xml
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
MaxConnectionReuseCount=1000
APEX Listener version : 1.1.3.243.11.40
APEX Listener server info: Grizzly/1.9.18-o
Dec 9, 2011 9:02:20 AM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Fri Dec 09 09:02:20 PST 2011
INFO: http://localhost:8888/apex started.

-- and it starts as it is already installed and configured...It will take 3 to 5 minutes to start though - keep an eye on until you see the last line.

B- Directly as

./apex.war

Please note that when you do this - it is going to do all defaults. Like home will be /tmp/apex - images path will be asked - port will be 8080 and will ask you to configure again as this is treated as a new install. So if you have done the new install like this then next time you can start teh APEX listener like this as well.

To avoid this and set the environment variables to where we already installed the APEX at the first place - we do as indicated below.

amghost3:Oracle ->>export _JAVA_OPTIONS="-Dapex.home=/home/oracle/apex -Dapex.port=8888 -Dapex.images=/home/oracle/apex/images"

and then type:

amghost3:Oracle ->> ./apex.war
Picked up _JAVA_OPTIONS: -Dapex.home=/home/oracle/apex -Dapex.port=8888 -Dapex.images=/home/oracle/apex/images
INFO: Starting: /home/oracle/dwnld/apex/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /home/oracle/apex
INFO: Using classpath: file:/home/oracle/apex/apex/____embedded/start.jar:file:/home/oracle/apex/apex/WEB-INF/lib/apex.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojmisc.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ucp.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/home/oracle/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/home/oracle/apex/apex/WEB-INF/lib/je-4.0.103.jar:file:/home/oracle/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:
INFO: Starting Embedded Web Container in: /home/oracle/apex
Dec 9, 2011 9:21:03 AM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/home/oracle/apex/apex/WEB-INF/web.xml
Dec 9, 2011 9:21:04 AM ____bootstrap.Deployer deploy
INFO: deployed application path=/home/oracle/apex/apex/WEB-INF/web.xml
Using config file: /home/oracle/apex/apex-config.xml
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
MaxConnectionReuseCount=1000
APEX Listener version : 1.1.3.243.11.40
APEX Listener server info: Grizzly/1.9.18-o
Dec 9, 2011 9:24:13 AM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Fri Dec 09 09:24:13 PST 2011
INFO: http://localhost:8888/apex started.

-- WOW moments !!

I copied the apex.war to /home/oracle/apex - so that I can start it as and when needed with _JAVA_OPTIONS set in my .profile.

Hope it Helps ...

APEX Using Tomcat 7 & DPLS(mod_plsql)

Oracle Application Express (APEX) Using Tomcat 7 & DPLS (mod_plsql. This is done on linux x86-64 platform.

I am going to discuss methods for using APEX. This can be useful for all mod_plsql applications. I have discussed in detail for DPLS and tomcat7. Please refer that here -http://modplsql.blogspot.com/

Steps:

1- Please download and install JDK6 - http://www.oracle.com/technetwork/java/javase/downloads/index.html

2-Please download and install tomcat7 as oper the URL provided above

3- Please download the dpls.war and deploy in the tomcat7 as per the URL provided above.

4- cd $CATALINA_HOME/webapps/dpls/WEB-INF and edit the prism.xconf as indicated below:

Variable section at the top:

<variable name="demo.db" value="jdbc:oracle:thin:@amghost2.cup.com:1521:soa"/>

Now go to DAD_APEX and change following:

<category name="DAD_apex">
<property name="dbusername" value="APEX_PUBLIC_USER"/>
<property name="dbpassword" value="apex11g"/>

5- copy ojdbc6.jar to $CATALINA_HOME/lib and $CATALINA_HOME/webapps/dpls/WEB-INF/lib

6- Restart the tomcat and try following URLS ( You can restart the DPLS apps from the tomcat management as an other option):

7- Make sure the APEX users in the 11g DB where it comes with APEX install - has ACL and account acess. Well, it does not hurt running following:

USERS:

SQL> alter user APEX_PUBLIC_USER account unlock;

User altered.

SQL> alter user ANONYMOUS account unlock;

User altered.

SQL> alter user ANONYMOUS identified by apex11g;

User altered.

SQL> alter user APEX_PUBLIC_USER identified by apex11g;

User altered.

SQL> select owa_util.get_version from dual; ## It will return the version - if so you are all good. If not then something is wrong and follow the URL for APEX install and Upgrade here - http://apexdplsepg.blogspot.com/2011/12/install-upgrade.html

SQL> @apxchpwd.sql ## Will change password for the apex admin user atemporary password that you will then change at the apex_admin URL below at first login.

ACL:

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_040100
-- the "connect" privilege if APEX_040100 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' 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_040100'
-- 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_040100',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040100', TRUE, 'connect');
END IF;

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

8- Following are the URL to access APEX.

http://<host>:<port>/dpls/apex ## APEX developer page

http://<host>:<port>/dpls/apex/apex_admin # APEX administrator page

Hope it Helps ...

Monday, December 5, 2011

APEX with Oracle EPG

The APEX is installed by default on a 11g Oracle database. Please see post here for install and upgrade on APEX. This is done on a Linux x86-64 machine.

I will use the Embedded Pl/SQL gateway from Oralce to access the APEX from the database server directly. Please check my other post on EPG here - http://oegmodplsql.blogspot.com/

Steps:

1- Make sure that the EPG is installed and running by executing this script
$ORACLE_HOME/rdbms/admin/epgstat.sql

2- Make sure that listener.ora has the following lines:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amghost2)

(PORT=8080))(Presentation=HTTP)(Session=RAW))

3- Fix the ACLs as:

4- Unlock the ANONYMOUS user and change its password.

5- Execute following steps:

A- execute this as sysdba

exec DBMS_XDB.setHTTPPort(8080);

## You can set any port that you desire the EPG to listen at. However this port and the listener.ora port should be same and if you choose to go port 80 then root privileges need to be sorted out.

B- Add above line in listener.ora and restart the listener.

6- Now access the APEX as.

http://<db server name>:8080/apex

Enjoy !!