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

Create formatted Excel file from your APEX cloud application

This post describes generating an Excel file with formatting from an Oracle APEX webpage without the use of printing engines. That makes it perfectly suitable for any cloud solution because you only need Oracle APEX.

At the moment I am redeveloping an Excel application into an Oracle APEX application. One of the pages generates an order list to be sent to a supplier. The report is created in APEX using a PL/SQL region, so the HTML is generated in PL/SQL.
This is the result for one week:

An order list can contain several weeks.


The original Excel application generates order lists as Excel files. That was a piece of cake, because the page was an Excel file. These files were sent to suppliers and looked a lot like the image above. You see the use of background colors, larger cells, colored fonts, cell width.

A PDF was not acceptable, because the suppliers could not cut and paste the data easily.
So it had to be a 'real' Excel file. There are lots of solutions on the internet to generate Excel files. But most of them require a good deal of coding. And I had done my coding in PL/SQL already, and I did not want to duplicate that.

Generate Excel from APEX application

Standard it is not possible to generate an Excel file with formatting from an APEX application.
Yes, you can download a CSV file and import that file into Excel. But you will get a plain list of data without any colors, borders or bold characters. You will not get near to the requested output.

After some time searching the internet I stumbled upon a blogpost of Tobias Arnhold: "Easy table export to XLS (based on HTML)".
He describes how you can create an XLS file containing an HTML table. Opening this file from Excel will show the table including formatted headers.

So I took this direction: create an HTML table with the formatting needed and write it to an XLS file.
JavaScript will be needed to assemble the HTML table to be written.
Then this content will be written to a file as described in the post of Tobias:
- put the content into a hidden APEX item
- use JavaScript to change the content om the hidden APEX item
- submit the page (saving the value of the hidden item to session state)
- write the file in a before header process, getting the content from the hidden item

As base for the table I used the HTML table for the week orders show above.
And it worked partly. The result I got was a bit disappointing:

So the most of the data was there, but without formatting. Data in input items was not shown.
The width of some cells is too small so the content is wrapped. A good point was that Excel did recognize the row and col span of the top left cell.

HTML to Excel limitations

With trial and error I found:

1. you can add styles at the beginning of the file
2. Excel only recognizes the first style assigned to an object
3. You can define CSS on element types like td or th
4. Excel does not recognize input items
5. Excel does apply width and height as style attributes
6. Excel does not support named colors ( only black and white) so use the RGB hex notation to define colors

Formatting the Excel

1. Styles. When you embed the HTML table in a normal framework of html, head, and body elements, you can define your styles in the styles element in the head.

2. Multiple styles: you can only use a single style. Remove 'unused' styles, in my case t-Report-cell and t-Report-colHead used in the APEX universal theme. Having only one style per object means that you will probably have some duplicates in your style definitions.

4. Input items: input items need to be converted to plain text.

5. Dimensions: for sizing a cell apply the style, for example: style="width:100px; height:30px;"

Above points mean that the HTML captured from the page should be changed.

This can be done using JavaScript. In order to be able to use jQuery a copy of the table is created in a div in a hidden APEX region. The ID of the table is changed. Now the table and its elements can be processed.

With the above knowledge and some JS coding the result is:

JavaScript snippets

Remove all instances of a class:

function remove_class(table,p_class)
{
    $(table).find('.'+p_class).each( function() { $(this).removeClass(p_class);})

}

Convert input items into plain text:

    $(table).find('input').each(
        function() {
            var td    = $(this).closest('td');
            var value = $(this).val();            
            $(td).text(value);
        }
    );

Happy APEXing,