Thursday, 11 July 2019

Creating a mobile app with APEX - Part 5: Refining the Form page

After refining the List View now the form will be improved for mobile use:

  • screen space usage will be optimized
  • the buttons will be reorganized
  • default values will be provided
  • success messages will be made to disappear after a few seconds
  • validations for time items are created

Bugfix for trigger

There was an error in the original trigger on the view. Download a new create script here and run the script to replace the trigger.

Screen space optimization

The previous actions to improve the use of screen space also have effect on the form page. The only thing that remains to be done here is to hide the region title:
  • Open region Activity
  • Hide header by settting Appearance > Template Defaults > Header to Hidden

Buttons

For this step a new version of the CSS file is needed. Download apex_mobile.css and upload to the Application Static Files.

Below the form are three buttons, the Cancel, Delete and Create/Apply Changes button. 
The Cancel button is not needed because the List View can be reached using the Menu Bar. So this button can be Deleted.
  • delete the Cancel button
The Create/Apply Changes button will be replaced by the Save action in the Menu Bar. The CREATE and SAVE buttons are mutually exclusive. The CREATE button is shown for a new rows, the SAVE button for existing rows. 
The connect the Menu Bar Save action to the buttons a CSS save_button class is applied:
  • select the SAVE button
  • set Appearance > CSS Classes to save_button
  • select the CREATE button
  • set Appearance > CSS Classes to save_button
The action on Menu Option Save is defined as: javascript:$('.save_button').click();
This means that the element with class save_button is selected and the click action associated with this element is performed. This way either the SAVE or the CREATE button action is executed depending on which one is present on the page. 

So only the Delete button remains. This buttons will be styled in a more mobile fashion:
  • select DELETE button
  • change Layout > Button Position to Below Region
  • set Appearance > Template Options:
    • Size: Large
    • Type: Danger White font on red background
    • Width: Stretch Makes button use full width
  • set Appearance > CSS Classes: delete_button
Run the application and notice the changes. Save a new or existing activity using the Menu Bar Save option. 


Default values

Entering data on the on-screen keyboard is not easy or fast, so it is an advantage when data is prefilled. We will provide some defaults for the items.

The first one is the date. The obvious default for this is the current date:
  • open P15_ACT_START_DATE
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Expression: sysdate
The Project of the last entered Activity can serve as default value for Project:
  • open P15_ACT_START_DATE
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Function Body: ttm_alg.last_used_project
The Start time can be filled with the last End time of today, or a default value like 9:00 if this is your usual starting time. 

  • open P15_ACT_START_TIME
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Function Body: ttm_alg.default_start_time
Run the application and enter a new activity. 

We will review the possibility to use the GPS location to determine a default value for Location in a coming post. 

Success message

Standard the APEX success messages that are displayed at the top of the page must be removed manually. On mobile this is not logical. The success message indicates that there were no errors so after a short time the message can disappear.  
For this function a new version of apex_mobile.js must be uploaded to the Application Static Files. After this:

  • open Page 10
  • add to JavaScript > Execute when Page loadsset_success_message_fade();

Now if an activity is created or changed the success message will show for 2 seconds and then disappear.

Checking input

Of course the user input has to be checked.

The date value will be checked automatically.
Because the project is implemented as a select list it need not be checked.
The Description and Location are character items and need not be checked.

That leaves us both the Time items. They should be filled with a string in the form hh24:mi. Furthermore the End Time should be after the Start Time.
We will build the necessary validations using functions from the package ttm_alg. For this functionality a new version of the package ttm_alg is needed. Download the create file ttm_alg.sql and execute this file on the schema.

After this the validations can be created:
  • Open the Processing column on page 15
  • Right click on Validating > Validations and select Create Validation
  • Change the Validation:
    • Name: Check start time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 
      • return(ttm_alg.check_time(:P15_ACT_START_TIME,'Start time'));
  • Right click again on Validating > Validations and select Create Validation
  • Change the Validation:
    • Name: Check end time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 
      • return(ttm_alg.check_time(:P15_ACT_END_TIME,'Start time'));
  • Right click another time on Validating > Validations and select Create Validation
    • Name: Check Start Time before End Time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 


Now the format of the time items and the relation between start and end time are checked. If an invalid value is entered on a meaningful error message is displayed.



Tuesday, 9 July 2019

Creating a mobile app with APEX - Part 4: Refining the List View

After having taken care of the general layout this post will focus on the refining of the List View page. The following actions will be performed:

  • loading a new version of the CSS file
  • adding a date picker to filter the activities
  • changing the List View query to react on the date picker
  • adding a Dynamic Action to synchronize the List View
  • optimizing screen space
  • removing the create button
  • applying advanced formatting on the List View elements

Loading new version CSS

A new version of the apex_mobile.css file should be downloaded here and uploaded to the Application Static Files.

Adding the date picker

We will create a new region and add a date picker item. The background color of the date picker region will be green. 
  • open Page 10
  • right click on Content Body and select Create Region
  • change the region:
    • Name: Parameters
    • Appearance > Template Options:
      • check Remove Body Padding
      • set Header to Hidden
    • Advanced > Static ID: parameters this will style the region
  • drag this region above the Activities region
  • right click on the region Parameters and chose Create Page Item
  • make the following changes to the new item:
    • Name: P10_DATE
    • Type: Date Picker
    • Label: Date in week
    • Default:
      • Type: PL/SQL Expression
      • PL/SQL Expression: sysdate
    • Warn on unsaved changes: Ignore Prevent warning when leaving page

Changing the List View query

The source query of the List View should be changed to reflect the values of the date item. 
Add the following where-clause to the query:


from   ttm_activities_vw     act
where  trunc(act_start_date,'iw') = trunc(to_date(:P10_DATE,'dd-mm-yyyy'),'iw')
order by trunc(act_start_date) desc, act_start_time

With this where clause only activities in the same week as P10_DATE are shown.

Dynamic Action to synchronize List View

When at this point the date is changed there is no effect on the List View.
We will need to define this behavior with a dynamic action:

  • Right click on the item P10_DATE
  • Pick Create Dynamic Action
  • Change the Name of the DA to P10_DATE change
  • Change the first True Step:
    • Action: Execute PL/SQL Code
    • PL/SQL Code: null;
    • Items to Submit: P10_DATE this is needed to load the value into session state
  • Right click on this step and chose Create Action
  • Change the new action:
    • Action: Refresh
    • Affected Elements > Selection Type: Region
    • Affected Elements > Region: Activities
Now a change of the date item results in a requery of the Activities region.

Optimizing screen space

There still is some screen space that is not used effectively. We do not need the region title as it shows in the Top Bar. The Create button can be deleted as this function is provided in the Menu Bar. And there still is excess of white space...
So let's get going:
  • Open the region Activities:
    • Edit Template Defaults:
      • Check Remove Body Padding
      • Set Header to Hidden
      • Set Style to Remove borders
    • Edit the region's Attributes
      • Uncheck Settings > Inset List 
  • Delete the Create button
You can clearly see the difference between the page now and before:



Even though the date item has been added the vertical space taken up by the List View is less than before.

Advanced formatting

In the design in Part 1 of this series we have defined that the List View elements should have a colored bar left of the text. 

Also the Location should be part of the information shown, behind the Project name. But location is not as important as the Project, so we need to decrease the emphasis on Location, or increase it on Project. 
Increasing emphasis can be done by using Bold text, decreasing can be accomplished by a lighter font color, usually a grey tone or a smaller font size.
To apply this on one line we need to use Advanced Formatting within List View.

The base query for the List View has to change because we need separate elements which were combined in the previous view:

select act.act_id
     , to_char(act_start_date,'fmDay, fmMonth fmddth yyyy ')    
                                     as  start_date
     , act_prj_name                  as  project_name
     , case act_prj_id
           when 1 then 'cornflowerblue'
           when 2 then 'green'
           when 3 then 'yellow'
       end                           as  project_color
     , act_location                  as  location
     , act_start_time
       || ' - ' ||act_end_time       as  period
     , act_description               as  description
from   ttm_activities_vw     act
where  trunc(act_start_date,'iw') = trunc(to_date(:P10_DATE,'dd-mm-yyyy'),'iw')
order by trunc(act_start_date) desc, act_start_time

To apply the advanced formatting:

  • Open the Activities region
  • Apply the new query
  • Go to Attributes
  • In Settings:
    • check Advanced Formatting You see that the available items change
    • Fill in the values from the table below:
List Entry Attributesstyle="margin-left:15px; border-left:5px solid &PROJECT_COLOR."
Text Formatting
Supplemental Information Formatting


The columns from the query are referenced by name preceeded by an ampersand and with a trailing dot, like &PROJECT_NAME.. For clarity the styling is done in-line, normally you would CSS classes. 
When running the application you can notice the difference between the various parts of the List View elements.
The colored bar is created by the List Entry Attributes. Note that you can also reference query columns here.

The List Divider has been styled. In order to provide more contrast with the elements it has received a white font on a dark grey background. This is done by applying CSS in the apex_mobile.css file:

li.a-ListView-divider.ui-bar-inherit {
    background-color: rgba(0,0,0,.55);
    color: white;
}

The Universal Theme classes are used to reference the List Divider. 

The List View page now looks and functions according to the specifications:



In the next Episode we will refine the Form page.



Sunday, 7 July 2019

Creating a mobile app with APEX - Part 3: Adapting the layout

In this post the general layout of the application will be tweaked:

  1. Another Theme Style will be chosen
  2. The landing page for the application is changed
  3. The menu will be replaced by a top/bottom menu bar. The menu will be adapted to the pages
  4. The List View page is set as landing page
  5. Unused space will be reclaimed
  6. The page title will be shown in the top bar

Theme Style


We will select another Theme Style for the application:

  • Run the application from the App Builder
  • Select Theme Roller from the Developer Toolbar
  • Select Vita - Slate for the Style
  • Press the button Set as Current

Now we have other (darker) colors in our application. The dark grey top bar will enable us to use other colors as background in the application. The background color for the date picker region in the List View will be set in the post 'Refining List View'.

Landing page

The default landing page is Page 1. We created our Activity List on Page 10 and this should be the new landing page:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the tab Attributes:
    • Set Home URL to f?p=&APP_ID.:10:&SESSION.
Run the application and you are taken directly to page 10.

Menu

The default left menu is not so very useful for an smartphone app with limited pages. Therefore we will use a top/bottom menu bar:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the tab Navigation Menu:
    • Set Position to Top
    • Select Top Navigation Tabs for List Template
    • In Template Options set Mobile to Do not display labels

With these changes the Left Menu is replaced by a menu bar. This bar is shown just below the top bar on desktop. On mobile it is displayed at the bottom of the screen.
With the last option the Menu Bar will only show icons, thus preserving vertical space. 

Menu items

Now we will add menu items:
  • Go to Shared Components > Navigation Menu > Desktop Navigation Menu
  • Delete the first menu item Home
  • Open the option Activities:
    • Set Image/Class to fa-home
  • Create new List Entry:
    • Image/Class: fa-plus
    • List Entry Label: New Activity
    • Target > Page: 15
    • Target > Clear Cache: 15 Empty the page items for new record
    • Conditions > Condition Type: Current Page = Expression 1
    • Conditions > Expression 1: 10 Only show on page 10
  • Create new List Entry:
    • Image/Class: fa-save
    • List Entry Label: Save
    • Target > Target type: URL
    • Target > URL Target: javascript:$('.save_button').click();
    • Conditions > Condition Type: Current Page = Expression 1
    • Conditions > Expression 1: 15 Only show on page 15
These menu options will be shown in the menu bar. Some options will only be shown on one page.
Run the application and observe the behavior of the menu bar. The Save action will be enabled when the Form page is refined. The Save option will execute the action connected to the button with the class save_button.

JavaScript and CSS

In following steps some JavaScript and CSS code is added. This code is wrapped in files which are loaded into the Static Files. Download the JavaScript and CSS file using the links and upload them to the Application Static Files in the Shared Components. Then create references to these files in order for the code to be available on all pages:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the Tab JavaScript:
    • Set File URLs to #APP_IMAGES#apex_mobile.js
  • In the Tab Cascading Style Sheets:
    • Set File URLs to #APP_IMAGES#apex_mobile.css

Reclaiming unused space

The Universal Theme regularly uses white space. On the desktop this avoids a clogged interface. 
On mobile however screen space is valuable and should be used as effective as possible.
Therefore we will reclaim unused screen space as much as possible. One of the possibilities is to 
remove the padding of the inner content of the body: 

.t-Body-contentInner { padding: 0; }

With this CSS we remove the white border around the regions. 
The CSS is included in the CSS file apex_mobile.css. As we have loaded this file in the last paragraph
the change is already active. 
In the next posts will reclaim more unused space using the Region Template Options.

Page title in Top Bar

Another space user is the Breadcrumb which is used to indicate which page is shown. This information can also be shown in the Top Bar. 
For this we need to apply some JavaScript. We will place the code in a Page Load Dynamic Action on the Global Page. This way the code will be executed on each page:
  • Open page 0 (Global Page - Desktop)
  • Goto Dynamic Actions The tab with the Lightning Icon in the Left Pane
  • Left Click on Page Load and chose Create Dynamic Action
  • For this Dynamic Action change the title to Page Load - General
  • Change the True action New:
    • Action: Execute JavaScript Code
    • Code: TitleInTopbar();
The referenced code gets the page title from the page header and displays it in the Top Bar. 





Creating a mobile app with APEX - Part 2: The basic app

In the second part of this series we will create the basic app. This is the out of the box app which is already fully usable to insert, update and delete data.

Before creating the app we need to create the database objects. The object creation script should be run in a schema with at least the privileges to create tables, views, sequences, procedures and triggers. In Oracle APEX add a workspace assignment for this schema.

Creating the application

Now we can create the application:

  • log into the APEX workspace
  • open the App Builder
  • hit the Create button
  • Chose New Application
  • Enter a Name for the application
  • Change the Application ID if desired
  • Select the schema with the objects as value for Schema
  • Hit Create Application

Creating the pages

Create the List View and Form pages using the create page wizard:

  • Press Create Page
  • chose Forms
  • hit Report with List View on Table
  • Fill in the following data:
    • Report Page Number: 10
    • Report Page Name: Activities
    • Form Page Number: 15
    • Form Page Name: Activity
  • Press Next
  • Check Create a new navigation menu entry and hit Next
  • Chose the table ttm_activities_vw and press Next. We will replace this with a query later on.
  • Check Select Primary Key Column(s) and select all columns in the shuttle. 
    • Select act_id for Primary Key Column 1
    • Remove the column act_prj_name
  • Hit the Create button

We can run the application and see how it looks:



Now the pages have been created, but they do not function very well:
  • the list view only shows ID’s
  • the form page contains large fields with bad labels and a numeric project reference
Note that it is already possible to insert and update activities using the form page.

Improving the List View

To refine the list view open page 10:
  • Select the region List view
  • Change the following values:
  • Title: Activities
  • Source > SQL Query: 
select act.act_id
     , to_char(act_start_date,'fmDay, fmMonth fmddth yyyy ')    
                                     as  start_date
     , act_prj_name                  as  project_name
     , act_start_time
       || ' - ' ||act_end_time
       || ' '   ||act_description    as  details
from   ttm_activities_vw     act
order by trunc(act_start_date) desc, act_start_time

  • Open the Attributes of the Activities region. In Settings:
    • check Show List Divider
    • uncheck Enable Search Search will be implemented later
    • Text Column: PROJECT_NAME
    • Supplemental Information Column: DETAILS
    • List Divider Column: START_DATE
Run the application and notice how much better the content looks already. 



We have clear information about the activities and they are grouped and sorted in a useful way. 

Some remarks about the changes on the list view page: 

  • the base query of the List View region provides the elements to be shown. For the second line the start time, end time and description are combined into one string, supplying a lot of information about the activity
  • it shows how easy it is to define a list divider. Just check the Show List Divider box and supply the column
  • the Search option is also very easy to define. Just check Enable Search and provide the column to be searched.It is possible to define whether the search is exact and how it handles case. The search column can be an existing column or a new column combining to be searched. The search option is disabled because the List View will show one week of activities based on a date picker item. 

Refining the form page

Now we will improve the form page:

  • Open Page 15.
  • Select the region Form on TTM_ACTIVITIES_VW
  • Set Title to Activity.
  • Change values for the items:
    • P15_ACT_START_DATE:
      • Label: Date
    • P15_ACT_PRJ_ID:
      • Type: Select List
      • Label: Project
      • List of Values:
        • Type: SQL Query
        • SQL Query: select prj_name, prj_id from ttm_projects order by 1
    • P15_ACT_START_TIME
      • Type: Text Field
      • Label: Start time
    • P15_ACT_END_TIME
      • Type: Text Field
      • Label: End time
    • P15_ACT_DESCRIPTION
      • Label: End time
      • Appearance > Height: 2
    • P15_ACT_LOCATION
      • Type: Text Field with autocomplete
      • Label: Location
      • List of Values:
        • Type: SQL Query
        • SQL Query: select distinct act_location from ttm_activities order by 1

When the form page is run the improvements become clear:


The labels are improved and the page fits on the screen. The Project name is shown and can be chosen using a select list. Entering the Location is supported by the auto complete functionality. 

Some remarks about the changes on the form page: 
  • APEX uses the column names as labels. They usually need to be changed to some more useful text
  • The form wizard creates Textarea items of 4 lines for most varchar2 columns. This takes a lot of vertical space and is not needed in most cases. In this case only the description is expected to need 2 lines. The type of the other items is changed to Text Field.
  • To show the project name for selecting the project the item type is changed to Select List. For reasons of simplicity the LOV query is defined with the item instead of in the Shared Components.
  • To implement the specified auto complete functionality the type of the location item is changed to Text Field with autocomplete. Now the location can be chosen from previously entered locations filtered by the text already entered. Note that the query only returns one column.

About the next post

In the next episode we will look at the general layout of the pages. 
  • Our own Theme Style will be applied
  • The menu will be changed to a navigation bar
  • Screen space will be used more efficient
  • Page title will be placed in the top bar


Thursday, 4 July 2019

Creating a mobile app with APEX - Part 1: Introduction and design


Coming back from my 5 month sabbatical in South East Asia I am refreshed and ready to resume blogging ;-). 

The first task is have assigned to myself is to write a series of blogposts about mobile development with APEX 18.1 (and up). Since this version support for mobile applications is included in the Universal Theme which makes it even easier to develop small screen applications.

I have a number of personal productivity apps like registering expenses and keeping track of working hours. These apps are (of course) built using APEX and targeted for large screen/keyboard use. 

In a series of blogposts I will describe the process of building a client smartphone app for an existing time registration application. 

The report and data entry screen for the desktop app is shown below:







I want to have access this data also on my phone. I also want to enter a new activity or change existing activities.
The desktop pages are not suitable for use on a smartphone. They take up to much screen space or when zoomed the text is too small and the text boxes can not be handled. 

I want to have access this data also on my phone. I also want to enter a new activity or change existing activities.
The design of forms and especially reports needs to take into account the small size of the screen. I have the need to have an overview of activities and a form page to enter or edit an activity. 

Using the mockup tool Balsamiq the layout of the pages is created:


The Activities page will serve as the Home page. It shows a list of all the activities for one week. The activities are grouped by day, ordered by time. The days are ordered descending, meaning the most recent day is shown on top.  
The date picker at the top of the screen is default set to today. Another week can be accessed by changing the date by entering another date, using the date picker or the arrows to move to the previous or next week. 
Each activity is shown as a list item with project name and location as main text and time and description as details. 

The Activity page can be used to enter a new activity or update an existing one. 
The projects can be chosen from a list. 
The date can be entered using the date picker. 
The time fields can be entered using the format HH24:MI. 
The location will be implemented with Autocomplete functionality. 

Coming blog posts in this series


  1. Introduction and design
  2. The basic app
  3. Refining the layout
  4. Refining the List View
  5. Refining the Form
  6. Input and processing of time items
  7. How to prevent auto-zoom on iOS
  8. Autologin
  9. Create app icon on smartphone

Thursday, 13 December 2018

Applying a style to a line of a classic report

APEX is a powerful tool, but even powerful tools have their shortcomings. One of the functionalities I need regularly is the styling of (parts of) a row of a Classic report based on a value from the base query of the report.

In my example I have a summary line of averages. This line is generated by the base query of the report. I would like to indicate the summary line by using bold characters and a light grey background. The bold characters could be applied in the query but it would imply mixing data and styling and would result in less readable query. The background has to be applied on the td element that cannot be influenced using the declarative features.
So how can this be solved elegantly?

The solution I chose is to include a hidden input element in the query that holds the name of a class to be applied to the tr element. The hidden element is appended to a column of the query for which escaping of special characters should be disabled.

select ename
    || apex_item.hidden(0,'summary-line','data-name="row_class" ') as ename
...

It is important to have the attribute data-name row_class. The value is the class name to be applied in the row. The first argument in the apex_item.hidden call is not important, unless it is a tabular form.

In the After Refresh Dynamic Action on the report, this class is applied to the row using this line of JavaScript code:

$('input[data-name="row_class"]').each(function() { $(this).closest('tr').addClass($(this).val());});

Now you can define whatever styling you want for the row. For the example I mentioned:

tr.summary-line td {
   font-weight: bold;
   background-color: #D0D0D0!important;   
}

Note that defining the background color does not work for tr elements, but should be applied on the td elements. Unfortunately the use of !important is needed because the alternate row coloring of APEX has very specific selectors, which would make our CSS very complex. 

It is also possible to style individual columns, for example showing the salary cell in bold red when the salary is too low:

select ...
     , sal 
    || case when sal < 1000
            then apex_item.hidden(0,'sal-to-low','data-name="row_class" ')   
       end         as sal
...

Combined with the following CSS it yields the desired result:

tr.sal-to-low td[headers="SAL"] {
   color: red;
   font-weight: bold;

}

The JavaScript supports multiple row_class items per row so you can apply multiple classes to a row. 

Happy APEXing

Tuesday, 29 May 2018

Limitations of Oracle Exadata Express X20

For my current customer in the agricultural business I have created an application to plan the growths of Chrysanthemum flowers. The current users were used to work in Excel so I had to build in all kind of Excel like features, like navigation with arrow keys and exotic layouts (everything is possible in Excel). The application makes extensive use of Ajax calls to provide an interactive user interface.


The interface cannot be called a typical APEX Universal Theme interface.
Our development enviroment was a virtual Windows server with an Oracle XE database. This environment provided a reasonable performance.

The Exadata Express Environment

The application should be deployed on an Exadata Express cloud server. After some struggles and hickups with the provisioning the management console of the Exadata is easy to use. With the right privileges a user has access to both the APEX administration environment and all workspaces.

Access from SQL Developer

It took some time to figure out how to get access with SQL Developer. You need to download a ZIP file with TNS configuration files and a keystore. During the download process you need to provide a password to protect the keystore. The download process was quite picky about the passwords provided, and a lot of passwords were rejected while they adhered to the password rules given. It took some time to find an appropriate password, and I still do not know what made the difference (I did not want to spend more time on it). The zip file can be referred to when defining the SQL Developer connections.

Migrating the database

The initial database was migrated using a script generated with SQL Developer. After that the base data was migrated with drag-and-drop from the development schema to the cloud schema.
Of course the migration of the APEX schema was a piece of cake. Just import the application and run.

Referencing the JS and CSS files

The only thing that needed to be changed was the reference to the JS and CSS files. In the development environment those files are placed on the webserver and referenced through virtual directories, like /assets/js and /assets/css.
As there is no webserver or OS access on Exadata the files can only be loaded into the workspace static files. And the reference to those files has the form #WORKSPACE_IMAGES#. And the references to the virtual directories were all over the place.
The solution for this was to edit the application export (do not do this at home or just if your know what you are doing). The references to virtual directories were replaced by references to substitution variables &JS_DIR. and &CSS_DIR.. Import the application in the development environment and define the substitution variables JS_DIR and CSS_DIR with values /assets/js/ and /assets/css/ (do not forget the last slash).
After import of the changed application on the Exadata instance all that should be done is change the value of the substitution parameters to #WORKSPACE_IMAGES.  And of course loading the files.

Loading the JS and CSS files

Some 25 JS and CSS files should be loaded into the database. Normally a lot of pointing and clicking. But luckily APEX can load and extract zip files. So zip all the files and load the ZIP file into the static workspace files. APEX will automatically unzip the files when you set Unzip File to Yes:


Running the application => Error

Now everything was ready to run the application. So one person started to test the application, and after half an hour suddenly the application issued an error message: 


Also the APEX Builder was not reachable. After an hour or so the application suddenly became available again. This happened a few times until I tried to connect using SQL Developer and got the message:

ORA-00018: Maximum number of sessions exceed.

Using this clue it turned out the the Exadata X20 has a MAX_OPEN_SESSIONS parameter set at 30. For a normal APEX application you can host a good number of users because they will not use these sessions simultaniously. But this application used a lot of Ajax calls. 
The user reported to have used a specific page just before the error occurred. And on this page changing the value of a select list fired a dynamic action with 5 refreshes. If you change that select list a few times fast, you hit the limit of 30 sessions on your own! So this page was rebuilt to fire a submit on change so that only one new page was requested instead of numerous refreshes. Less elegant but much more efficient. 

Performance

What was also surprising was, that the application performed less fast on the Exadata than on out development server. It is a heavy application with tough queries but I would have expected the Exadata to outrun Oracle XE on a virtual server. But no, overall the development server was 20-30% faster than the Exadata. 

Conclusion

The conclusion is that the Exadata X20 seemed to be too good to be true, and indeed it did not turn out to be the solution for this problem. The limited of the number of sessions proved absolutely too small for this application. Also the performance was not adequate for this type of application. The next Exadata offers also have a limited number of sessions, so my customer moved to Oracle DBAAS. 

Happy APEXing