Install Oracle XE 11g, update APEX to 4.2.6 and runn Oracle REST Data Services with Tomcat server

Step 1. Install Oracle XE 11g

1. Download Oracle XE (oracle-xe-11.2.0-1.0.x86_64.rpm.zip) from Oracle official website. You need an account to do so.
2. unzip and enter the unzipped folder Disk1, run

sudo rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

3. configure oracle

/etc/init.d/oracle-xe configure
# provide port for Apex:8080
# port for database listener (1521) 
# password to be used for both SYS and SYSTEM users:tiger
# whether you’d like to have the database start automatically at system start:y

4. Edit ~/.bashrc file and add the following contents:

#ORACLE XE 11g paths
export ORACLE_SID=XE
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_TERM=xterm
export NLS_LANG=american_america.utf8
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

and make the donfiguration take effect

.  ~/.bashrc

5. Now we have finished installing Oracle XE 11g on CentOS.

After fresh installing Oracle, when you run

 sqlplus / as sysdba 

, you might encounter

 ORA-01031: insufficient privileges.

[htang@centos6vm apex]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 14 10:35:45 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges 

Solution: Add dba group for your current session user.

usermod -a -G dba htang

Step 2. Follow my previous post here, to update Apex to 4.2.6

6. If You want to continue use Embedded PL/SQL Gateway (EPG), you are all set and stop here.

Step 3. Deploy Oracle REST Data Services on Tomcat 

Oracle REST Data Services is a Java EE-based alternative for Oracle HTTP Server and mod_plsql.
7. Install Tomcat if needed

su -
cd /etc/yum.repos.d
wget 'http://www.jpackage.org/jpackage50.repo' 

yum update
yum install tomcat6 tomcat6-webapps tomcat6-admin-webapps
/etc/init.d/tomcat6 start 

7.1 add admin account for Tomcat

/etc/tomcat6/tomcat-users.xml
#add the following line before </tomcat-users> tag
<user name="apex" password="apex123" roles="manager,admin,Manager,Admin" />

8. Shutdown Apex-EPG

sqlplus /nolog
connect sys as sysdba
#type in password
#run the following command
execute dbms_xdb.sethttpport(0);

9. prepare APEX_PUBLIC_USER account for installing Oracle rest Data Services

sqlplus / as sysdba
#then run
CREATE PROFILE UNLIMITED_PASSWORD_LIFETIME LIMIT
  SESSIONS_PER_USER DEFAULT
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME DEFAULT
  IDLE_TIME DEFAULT
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS DEFAULT
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME DEFAULT
  PASSWORD_REUSE_MAX DEFAULT
  PASSWORD_LOCK_TIME DEFAULT
  PASSWORD_GRACE_TIME DEFAULT
  PASSWORD_VERIFY_FUNCTION DEFAULT;
#and run
alter user apex_public_user profile unlimited_password_lifetime account unlock;
alter user apex_public_user identified by apexpass;

10. download oracle oracle REST data Services from oracle which replaces oracle listener

unzip ords.2.0.9.224.01.05.zip -d ords
cd ords
#rename file ords.war to apex.war
#in order to use http://example.com:8080/apex instead of http://example.com:8080/ords
mv ords.war apex.war
#create ords configuration folder
sudo mkdir /u01/app/oracle/product/ords_config
#configure config.dir for ords
java -jar apex.war configdir /u01/app/oracle/product/ords_config/
#Sep 30, 2014 9:41:17 PM oracle.dbtools.common.config.cmds.ConfigDir execute
#INFO: Set config.dir to /u01/app/oracle/product/ords_config/ in: /home/htang/oracle/ords/apex.war
#verify config.dir
java -jar apex.war configdir
#Sep 30, 2014 9:41:30 PM oracle.dbtools.common.config.cmds.ConfigDir execute
#INFO: The config.dir value is /u01/app/oracle/product/ords_config/

11. make configure

sudo java -jar apex.war 

Outputs are:

Sep 30, 2014 9:41:39 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/app/oracle/product/ords_config/apex
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:
Enter the database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:apexpass
Confirm password:apexpass
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USE same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:2
Sep 30, 2014 9:42:10 PM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

12. change owner of configuration dir /u01/app/oracle/product/ords_config/ to Tomcat. Or you will see "FAIL - Application at context path /apex could not be started" in Tomcat manager page.

sudo chown tomcat:tomcat -R /u01/app/oracle/product/ords_config/

13. copy configured apex.war file into tomcat webapp folder and restart tomcat

sudo cp apex.war /var/lib/tomcat6/webapps/

copy image files from unpacked apex/images folder into tomcat webapps folder. Or you will encounter blank front page.

sudo mkdir /var/lib/tomcat6/webapps/i
sudo cp apex/apex/images/* /var/lib/tomcat6/webapps/i/

14. restart Tomcat and visit http://example.com:8080/apex/

sudo /etc/init.d/tomcat restart

[4/13/2015] 503-Service Unavailable and solution

 I noticed "Oracle Rest Data Service 503-Service Unavailable " error message when I try to connect to my Apex application. Further investigate Tomcat error log file at

/var/log/tomcat6/catalina.out

, from

 java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: ORA-28001: the password has expired 

, I realized that the error is caused by the expired password of user APEX_PUBLIC_USER. Solution 1. change password for user APEX_PUBLIC_USER.

alter user APEX_PUBLIC_USER identified by apexpass;

2. delete old configuration files and apex.war files from

 /u01/app/oracle/product/ords_config

and

/var/lib/tomcat6/webapps/

, then follow step 9 to reinstall restful data service.

We could also disable password expiration by doing: 
1. To alter the password expiry policy for a certain user profile in Oracle first check wich profile the user is in using:

select profile from DBA_USERS where username = '<username>';

If you want to previously check the limit you may use:

select resource_name,limit from dba_profiles where profile='<profile_name>';

Then you can change the limit to never expire using:

alter profile <profile_name> limit password_life_time UNLIMITED;

Set Default Column Display Order For Oracle Apex IR

In order to set default display order for interactive report columns, you need go to run the application and go to the active report which you want to set default column display order.
1. Click "Actions" button then "Select Columns" to adjust the order of columns the way you wanted.
2. From "Actions" button, choose "Save Report", save "as default report settings".

Oracle Apex Manually Populate a Form

I had a hard time debugging why my form does not auto populate as designed. In my design, I am using a process of type PL/SQL, which was set to be executed "On Load - Before Regions", to retrieve values for populating the form from database.

FOR C1 in (SELECT ename, sal
FROM emp WHERE ID=:P2_ID)
LOOP     
     :P2_ENAME := C1.ename;
     :P2_SAL := C1.sal;
END LOOP;

The value of P2_ID was passed from the page who is calling this form. It was suppose to change the values of P2_ENAME and P2_SAL according to the value of P2_ID. However, it does not. After switching the "source used" of P2_ID from "Always, replacing any existing value in session state" to "Only when current value in session is null". It works!

Oracle Apex Use Region Display Selector As in-page Tabs

Use Region Display Selector in oracle Apex we could display one sub region a time like tabs for a website.

1. To create a region display selector in Oracle Apex 4.2. Go to page and create region "Region Display Selector","UI Details Report Selector" in my case.
2. Create at least two sub regions, make sure that you choose the region display selector you created in step 1 as the parent region and choose "Yes" for region display selector in attributions section.

3. The final region hierarchy should look like

4. How to get rid of "Show All" tab created by Region Display Selector? Go to Edit Page >> javaScript >>Execute when Page Loads and put the following in

$('.apex-rds li:first-child').remove();
$('.apex-rds li:first-child').addClass('apex-rds-first');
$('.apex-rds li:first-child > a').trigger('click');

Use Values from APEX Checkbox with IN Opearator in SQL where clause

In my Oracle Apex application, I am using a checkbox to filter query results. The idea query should look like:

select * from emp e where e.job in :P1_JOB_CHECKBOX;

However when I put this query string in report region of my application, nothing shows up. Upon checking the return value of P1_JOB_CHECKBOX, I noticed the returning string is "MANAGER:CLERK".

Solution

Use PL/SQL process to put jobs into an Apex collection.

1. Define a process "Process Checkboxes" at Page Processing section of  page 1 using "On Submit - Before Computations and Validations" as process point.

2. Put the following into the source section of the process

declare
    JOB_Checkbox_array apex_application_global.vc_arr2;

begin
    JOB_Checkbox_array := apex_util.string_to_table (:P1_JOB_CHECKBOX, ':');
    apex_collection.create_or_truncate_collection ('P1_JOB');
    apex_collection.add_members ('P1_JOB', JOB_Checkbox_array);

end;

3. Modify the query string in active report region

select * from emp e where e.job in (select c001 from apex_collections where collection_name = 'P1_JOB');

Highlight Search Term in APEX Interactive Report

Here is a tutorial on how to set up highlight search term in interactive report created by oracle Apex 4.2.
1. Download jquery.highlight-4.js and copy the content of this file to "Function and Global Variable Declaration" section under your page JavaScript tab (To go here, right click your page and choose Edit).

2. Go to "CSS" tab and add the following line into section "inline"

.highlight { background-color: yellow }

3.Create the first dynamic action

Name: before refresh
Event: Before Refresh
Select Type: Region
Region: choose the region you want to apply
True Actions: Execute JavaScript Code
Code: 
gv_search = $("#apexir_SEARCH").val();
Fire On Page Load: Yes

4. Create the second dynamic action

Name: after refresh
Event: After Refresh
Select Type: Region
Region: choose the region you want to apply
True Actions: Execute JavaScript Code
Code:
if (gv_search) {
$('.apexir_WORKSHEET_DATA td').highlight(gv_search);
}

Fire On Page Load: Yes

Connect to APEX Newly Created Oracle Schema with Toad

Here is a tutorial of creating apex workspace, schema and import application.
1. Go to apex site and log into internal space use admin/password.
From manage workspace>> create workspace >> name workspace as "DASH01" and go to next step.


2.  Make sure you choose 'NO' for Re-use existing schema. Fill in the rest required filed. Here Schema name and schema password will be used to connect to this schema from Toad.

3. Fill in Admin user name, password and email as required. Click create workspace in the next step.

4.  Start Toad, create new connection
User/Schema: DASH01
password: where you provided as in step 2
In Dire Tab, provide Host/Port/SID.

5.  Go to old APEX application site as regular user.
Go to Home>>SQL Workshop>> Utilities>>Generate DDL and click create script. In the "Object Type" step, check Table. Select "Check All" in "Object Name" step.






6. The generated script will be at SQL Workshop>> SQL Scripts
Open and execute the script in Toad will create all the tables in new APEX.

7. Connect to old APEX schema in Toad. Choose Database>>Schema Browser
Select all tables and>>choose "copy data to another schema" from right click.