Installation of Oracle APEX in Windows/ Oracle XE and configuring ORDS in Tomcat

Oracle APEX 18.2 installation in Oracle XE and configuring ORDS in Tomcat 

               In this article, we are going to see the installation of Oracle APEX in Oracle DB (XE). Oracle APEX is a database driven application that is built using PL/SQL Web Toolkit. Installing Oracle APEX is essentially creating database schemas and database objects.

To start with installation, I have installed Oracle 11g XE and will be using it entirely in this article.

Oracle Application Express release 18.2 requires an Oracle Database release 11.2.0.4 or later, 

including Enterprise Edition and Express Edition (Oracle Database XE).

Oracle DB 9i or later comes with APEX installed in Oracle binaries. You can see the image below which shows the APEX schemas which exist after the installation of Oracle XE 11g

Here we can see 3 different users namely.

• APEX_040000
• APEX_PUBLIC_USER
• FLOWS_FILES

APEX_XXXXXX - This schema is popularly called as APEX Engine. The application pages are dynamically generated from the engine each time a request is made. Here XXXXXX varies based on the version of the APEX. Example: APEX_0500000 indicates version 5 of APEX

APEX_PUBLIC_USER - Schema which is used to connect to the Database and to execute the APEX application. This user has minimal DB privilege granted on creation. In APEX 5.0 these are as follows: Create Session and Select, Insert, Update, delete on WWV_FLOW_FILE_OBJECTS$.

FLOWS_FILES - This schema is the owner of the WWV_FLOW_FILE_OBJECTS$ table. This table is used as a temporary storage area when any file is uploaded in the APEX application.

Before we install the Oracle APEX 18.2 first I am going to uninstall the existing version which is Oracle 4.0.2

select version from dba_registry where comp_id = 'APEX';

To do this download the APEX from Oracle Website.

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

Perform the below steps to uninstall the existing Oracle APEX: 

1. Extract the downloaded zip file.
2. Open cmd prompt
3. Navigate to the extracted APEX folder (Let us call this folder as APEX Home. I will be using convention throughout this article to indicate this folder)
   
   
   
   Note: Once the zip file is extracted I renamed the folder as apex as shown in the pic above.
4. Connect to the SYS user. sqlplus SYS AS SYSDBA
5. Run the following command: @apxremov.sql
   This should take approximately 10mins to uninstall.
   You can also uninstall the APEX instance by running the file in apxremov.sql
   oraclexe\app\oracle\product\11.2.0\server\apex

Perform the below steps to install APEX 18.2

1. Open cmd prompt
2. Navigate to APEX Home folder
3. Connect to the SYS user. sqlplus SYS AS SYSDBA
4. Create a tablespace for APEX Engine
   
   
5. Run the following command
   @apexins.sql APEX_TS APEX_TS TEMP /i/
   
   
   
   @apexins.sql tablespace_apex tablespace_files tablespace_temp images
   Where:
   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.
   Above step should take approximately 30 mins.

Once the installation of APEX is completed check the new users which were created in this process.

APEX_INSTANCE_ADMIN_USER - Ths user provides the REST admin interface. The REST Administration Interface enables Instance administrators to automatically fetch usage metrics for an Oracle Application Express instance with a REST client.

Now it is the time for configuring RESTful Services in Oracle APEX.

Perform the below steps to install to configure the RESTful services.

1. Open cmd prompt
2. Navigate to APEX Home folder
3. Connect to the SYS user. sqlplus SYS AS SYSDBA
Run the following command
@apex_rest_config.sql
                           

As shown in the image above you will be prompted to enter a password for the APEX_LISTENER and APEX_REST_PUBLIC_USER accounts.

APEX_LISTENER: Required only if using RESTful Services defined in Application Express.
The database user used to query RESTful Services definitions stored in Oracle Application Express if RESTful Services defined in Application Express workspaces are being accessed

APEX_REST_PUBLIC_USER: Required only if using 
4. RESTful Services defined in Application Express. 
5. The database user used when invoking Oracle Application Express RESTful Services if RESTful Services defined in Application Express workspaces are being accessed



6. Set the XDB port to 0
   EXEC DBMS_XDB.SETHTTPPORT(0);
   
   
7. Unlock the Users APEX_PUBLIC_USER and APEX_REST_PUBLIC_USER
   
   ALTER USER APEX_PUBLIC_USER IDENTIFIED BY ORACLE ACCOUNT UNLOCK;
   ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY ORACLE ACCOUNT UNLOCK;

Apache Tomcat Installation and Configuration:

Download the Apache Tomcat (I used version 9) from the website https://tomcat.apache.org/

Extract the zip file and install it by double-clicking on the .exe file.

You see the below screen. Please click on the components which I have selected in the image below and click Next.

I have used the port 8282 as shown in the image. You can use any port number as per your convenience.

Select the JRE path in the next step.

Once the installation is completed a tiny icon appears in the taskbar tray as shown below. If it is green it depicts that it is running, red for suspended.

Installing ORDS:

Oracle REST Data Services is the recommended web listener for APEX. It allows the creation of Web Services in APEX.

Download ORDS from the Oracle website http://www.oracle.com/technetwork/developer-tools/index.html

Note: ORDS 3 requires Java version 7 or higher.

1. Extract the downloaded zip file.
2. Go to the extracted ORDS folder (Let us call this folder as ORDS Home. I will be using convention throughout this article to indicate this folder).
   Create a new folder and name it as ordsconfig.(This is the location to store the configuration data for the ORDS)
   Here you can find a file named ords_params.properties inside params folder.
   
   Installing ORDS essentially means configuring the ORDS.war file which you can find in the ORDS Home with appropriate parameters
3. Edit ords_params.properties in the text editor  (Optional step). I have added localhost as my hostname with other details such as my Web server port etc.
   
   
4. Open cmd prompt.
5. Navigate to the ORDS Home directory where ords.war file is located.
   Run the following command
   
   java -jar ords.war configdir C:\ords_install\config-ords
   
6. Ensure you are still in the ORDS Home directory in cmd prompt and run the below command.
   
   java -jar ords.war
   
   You will be prompted for inputs throughout the process which you have to type.
   
   I have put the screenshot of the configuration steps below. Please ensure you remember the passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER which you set earlier during configuration of the RESTful Services in Oracle APEX.
   
   
   
   You can notice the above steps creates 2 more new users ORDS_PUBLIC_USER and ORDS_METADATA
   
   ORDS_METADATA: Owner of the PL/SQL packages used for implementing many Oracle REST Data Services capabilities. ORDS_METADATA is where the metadata about Oracle REST Data Services-enabled schemas is stored.
   It is not accessed directly by Oracle REST Data Services; the Oracle REST Data Services application never creates a connection to the ORDS_METADATA schema. The schema password is set to a random string, connect privilege is revoked, and the password is expired.
   
   ORDS_PUBLIC_USER: User for invoking RESTful Services in the Oracle REST Data Services-enabled schemas.
7. To deploy ORDS to Apache Tomcat
   Copy the ords.war from ORDS Home to the webapps folder of Apache Tomcat installation directory which is  C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps in my case.
8. Create a new folder by name 'i' in the location C:\Program Files\Apache Software Foundation\Tomcat 9.0\
   Copy the contents of images folder from APEX_Home into the newly created 'i' folder in Tomcat Server
9. Stop and then Start the Tomcat Service as shown below from the taskbar.
   
10. Now you should be able to run the below URL in your browser
    http://localhost:8282/ords
    
    You should be able to see the below screen.
    
    
11. If you are wondering what password you should be entering here. We are still one step short to finish the installation.
    Open cmd prompt
    Navigate to APEX Home folder
    Connect to the SYS user. sqlplus SYS AS SYSDBARun the following command
    @apxchpwd.sql
    Enter the password when prompted, without changing the Username [ADMIN]
    Use this password to login in the above pic.

Create Multilingual Application in Oracle APEX

Multilingual Application in Oracle APEX

This article should give you an overview and a brief understanding of translating Oracle APEX applications into multiple languages.

Application Express offers built-in functionality to develop the multilingual application without needing to create another application. 

As a part of the demonstration, we will need a simple application with English as the Application Primary Language. Once we have an application in our disposal follow below steps.

Step 1: 

Navigate to Shared Components -> Application Definition Attributes -> Globalization 

Set the Application Language Derived From to "Item Preference (use item containing preference)"

• Item Preference (use item containing preference) - Determines the translated application language based on an application-level item called FSP_LANGUAGE_PREFERENCE Using this option requires Application Express to determine the appropriate language preference every time the user logs in.

Step 2:

FSP_LANGUAGE_PREFERENCE is an Application Item which the developer has to create to help APEX to determine the language the User prefer every time he logs in.

In order to create the Application Item navigate to Shared Components -> Application Items and Click on Create button

For this demo leave all other fields be their default values.

Step 3:

Creating XLIFF Files

XLIFF is the abbreviation for XML Localization Interchange File Format and it is one of the world's most recognized standards in localization. It specifies the elements and attributes to store content extracted from various original file formats and its corresponding translation.

XLIFF format has been adopted by APEX as the format in the localization process.


To create an XLIFF file Navigate to Shared Components -> Globalization -> Translate Application

In this window, you can see the list of steps necessary to fully translate the application in APEX. Let us call this Translation Homepage.

Click on the first step Define application languages and then click on the Create button.

In the create wizard, enter an application id which is not created in this APEX environment and select the language that you want to translate to. I am selecting German De. Then click on the Create button

Once a shadow translation application is created, return back to the Translation Homepage. Click on the second step in the list Seed translatable text.

Select the previously created shadow application and then Seed. Doing so creates entries in the APEX repository for every translatable text available in the application. This entries later will be used to generate the XLIFF file.


Once seeding is completed, return back to the Translation Homepage. Click on the third step in the list Download XLIFF translation files

Select the language (De in my example) and click on Export XLIFF File for Application

Note: We are exporting all the translatable elements since we are exporting the application for the first time. 

Open the downloaded XLIFF file which looks similar to the pic below. The highlighted target tags should be translated accordingly to the required language (In the pic I have not translated the highlighted text but you should).

Save the XLIFF file after translating the texts, return back to the Translation Homepage. Click on the fifth step in the list Apply XLIFF translation files.

Upload the XLIFF file by clicking on the Upload button.

In the next screen, as shown above, select the translation mapping to apply the XLIFF file.

Then, select the row with the checkbox on the right and click on Apply Checked button

Once the above step is done follow the same except for this time click on the Publish button.

So we are completed with the translation of the application.

Step 4:

Create an application process (Shared Components -> Application Process)

Name: Set Language
Point: On Load Before Header (page template header)

Source:

begin
owa_util.redirect_url('f?p='||:APP_ID||':'||:APP_PAGE_ID||':'||:APP_SESSION);
end;

Condition Type: Request = Expression 1

Expression: LANG

Step 5:

Now the final step is the switching languages. To accomplish that we would create entries in the navigation bar. (Shared Components -> Navigation -> Navigation Bar list)

Select the Navigation Bar list and create entries (2 entries in my case for each language) 

List Entry Label: English or German

Edit: Page should be &APP_PAGE_ID. in the above pic

Step 6:

Please repeat the process of seeding, exporting, translating and importing. This step has to be done every-time any change is performed on the application.

Note: This above example works only if you do not have page level protection on URL in your application.


I will be posting alternate ways in somedays.

Create a context menu/ popup menu in the Interactive Region

Menu Popup

Consider a scenario where you have to perform multiple operations on a row of an Interactive Report.
And you need to house all the operations in one cell of the row or in the more specific term you want to create menu popup.

To create a popup menu in your Oracle Apex application, you must first have a list region on your page which contains the menu items.
Then you need to associate an icon on your page with the menu so it will open the menu when clicked. Please find the detail description for the creation of Menu Popup in IR below.

Step 1:

Create an Interactive Report with a column say "Sel"

SELECT
        CASE mod(employee_id,2)
            WHEN 0   THEN 'ContextList1'
            ELSE 'ContextList2'
        END
     || '_menu'  Sel,
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    salary
FROM
    employees;

Note: I have suffixed the output of Sel column with "_menu"

In this example, I am generating 2 different types of menu one for Odd numbered rows and another for Even based on the Employee_id

Edit the Column SEL in Property Editor to display an icon as below

Identification ->Type- Plain Text
Column Formatting ->  <a class="#SEL#" href="#" >
                                      <i class='fa fa-chevron-circle-down fa-2x'></i>
                                       </a>


Step 2: 

• Create the List (Static or Dynamic) in the Shared Component.

For my demo, I need to create 2 lists one for odd and another for even.

Step 3:

• Create List Region in the page where you want to display the popup menu and use the list from the shared component as the source.

For my demo, I need to create 2 list regions.

• Change the List template to Menu Popup in the Attributes tab of the List Region

• Edit the Static Id and add the Custom Attributes to the List Region in the Advanced tab of the Property Editor. 

For my demo, I am using "ContextList1" as my Static ID and Custom Attribute is sytle="display:none;"

Step 4:

• Create a Dynamic Action which triggers on the click of the menu column.

In my case, I need to create a 2 DA's with Jquery Selector as ".ContextList1_menu"...

• Change the Scope of the DA to Dynamic with the Static Container as the static id of the IR where the menu resides.

In my case, it is "#Employee_IR"

•  Create an Action: Execute JavaScript Code as below

var event = this.browserEvent;

$("#ContextList1_menu").menu( "toggle", event.pageX, event.pageY );

event.preventDefault();

Here, ContextList1_menu = ID of the List Region which you created earlier + the suffix _menu.



Reference: http://hardlikesoftware.com/weblog/2015/07/13/apex-5-0-custom-menus/

Context Menu Button in Oracle APEX 5.1

There are times when you want to add multiple buttons attributing to the same subject.

For Example: Roles in the organization. You cannot create multiple buttons for each role, rather the good option would be to have a Menu or a menu button.

In this post, I would like to share, how to create a menu button.

I have tried this in the version 5.0/5.1, below are the steps.

Step 1:  Create a button with CSS Classes as js-menuButton in the Appearance section of the Property Editor

Step 2: Add the Custom Attributes in the Advanced section of the Property Editor for the button as 

data-menu="Some_Id_menu" 

In my case, I am naming it as data-menu="Create_Employee_menu"

Step 3: Create a list in the Shared Component which you want to use in the button. It can be either Static or Dynamic as per the requirement. For the demonstration, I am going with the static list.

My list (Named as Role_List) has 3 Entries

1.  Developers
2. Testers
3. Managers

Once the creation of the List in the Shared Component is completed. 

Create a List Region in the page where you have the button with the source as the List which you created in the Shared component.

Enter the Static Id same as the prefix which you used for the Button as the data-menu attribute without _menu in Step 2

In my case, it would be Create_Employee

Step 4: Change the Behaviour of the Button to Action: Defined by Dynamic Action. You should be able to use the Context Menu button.

Step 5: Change the Template of the List Region to Menu Popup.
You can find this in the Attribute tab of the List Region -> Property Editor -> List Template

References: https://apex.oracle.com/pls/apex/apex_pm/r/520052/files/static/v56/jsdoc/menuButton.html

Auto Hide the notification messages in Oracle APEX 5.0

Follow below simple steps to auto-hide the notification message in Oracle APEX.

Environment: Oracle APEX 5.0.3

Step1: Create a Dynamic Action in Global Page (Page 0 in my case)

Step2: Create a JS action and add below code.

$( document ).ready(function() {

      $( "#t_Alert_Success" ).fadeIn( 3000 ).delay( 1500 ).fadeOut( 400 );

  });

Step3: It’s done!!!