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. 


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. 


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

Thursday, 17 May 2018

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:100pxheight: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:

        function() {
            var td    = $(this).closest('td');
            var value = $(this).val();            

Happy APEXing,

Wednesday, 14 February 2018

Easier maintenance of standard APEX buttons

One of the strong points of APEX is the use of templates. Combining templates with template options and attributes you can end up with HTML objects that are declarative and in most cases do exactly what you want. When you want to make general changes to these objects just change the template or the underlying CSS. This is a very good implementation of the DRY (Don't Repeat Yourself) principle.

However when generating pages with APEX you generate many identical buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW) for which the label is a hard coded property for each instance of the button (not so DRY). If you there is the need to change the labels ( maybe for a translation ), you will have to go through the entire application and change all the relevant instances of the buttons.
It gets even harder (or more nagging) when after finishing the application you are asked to apply another look-and-feel including a left-side icon.

So you have to change the button on the left to the button on the right.
For each button you need to:
- select the button
- apply the CSS class
- apply the icon CSS class
- open the template options (default icon position is right)
- change the icon position
- close the template options
A lot of work, and that times the number of buttons in your application. That is a lot of work and by its repetitive nature very error prone.
All these scattered definitions are not very DRY either.

A solution I use is to create specific button templates for the most used buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW). The button templates are based on the Text with icon button:

New button templates are created by copying the Text with icon template and adapting it to your needs.In my applications for example for the Save Button, this looks like:

The label is hard coded to Save and the left icon class fa fa-save refers to the save icon (the floppy disk). As we are using the left icon, the code for the right icon is omitted. The class t-Button--iconLeft is needed to display the left icon. And finally the class delphy-hot defines the specific look-and-feel for this application suite.

Now all that needs to be done is to apply these specific templates to the buttons. This took me about half an hour for a medium sized application.
Instead of the list of actions mentioned above you just need to:
- select the button
- change the button template
Look at the animated GIF below to see how fast the change can be applied.

Once you have got all the button templates referenced, you can easily change the look-and-feel of all the buttons just by changing the corresponding templates.

If you have several applications with the same look-and-feel you can copy the templates from one application to another. After copying you can adapt to the application, for example by translating the labels.

Happy APEXing ;-)

Wednesday, 24 January 2018

Dynamic Navigation Bar

In my current assignment I work on a large number of applications. These applications all share the same UI theme. Once logged in you can switch applications through an application menu.
The navigation bar in all these applications is (or should be) the same. Until today each application had his own copy of the Desktop Navigation Bar which is in fact an Oracle APEX list. 
As the number of applications is approaching double digits you can imagine I would like one code base for the navigation bar content. 

In the Shared Components > User Interface Attributes > User Interface Desktop you can find the details of the Navigation Bar:

Here you see that the list on which the Navigation bar is based can be changed. The template can also be chosen, but the current template is sufficient for this moment. 

Unfortunately we cannot reference lists in other applications, only copying is possible. 
But we can define a dynamic list to populate the Navigation Bar. This opens the door to defining the list as a query or view in the database, which can be shared between applications. 
I could not find the definition of the list elements needed for the Navigation Bar list, so I had to do some experimenting myself. It seems that the meaning of the columns in the query result set is determined by their position. 
The following query results in a correct entry:

select '1'            as  lvl
     , 'Logout'       as  label
     , 'apex_authentication.logout?p_app_id=&p_session_id='  as  target
     , null           as  attr1
     , 'fa-sign-out'  as  icon_css_class
     , null           as  attr3
     , null           as  attr4
     , ''             as  badge
     , ''             as  list_item_css_classes
from   dual
where  nvl(v('APP_USER'),'nobody') != 'nobody'

In this query the label is the text displayed in the entry. 
The target is the URL to be linked to. 
The icon_css_class can contain a reference to a Font Awesome/Apex icon. If omitted no icon is shown. 
For some columns I did not find any use, but as the meaning is positional these columns are needed anyway. 
The entries are implemented as an HTML UL element (unordered list). The list_item_css_classes can contain CSS classes for the list item that contains the entry. These classes can for example be used to apply a color to the entry text and icon. 

You can build a query by connecting a number of selects from dual of the above form. But this results in a long messy query which is hard to maintain. Luckily a APEX list can also be based on a PL/SQL function returning a query. So the query can be composed in PL/SQL which enables more clean coding. A package is defined with procedures to add an navigation bar entry and to return an APEX url. 
The code to generate the query in PL/SQL looks like this: 

  function navigation_bar_query return varchar2 is
    l_target      varchar2(1000) := null;

    -- logout entry
    l_target := 'apex_authentication.logout'
         ( p_label                   =>  'Logout'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-sign-out'

    -- user entry
         ( p_label                   =>  initcap(v('APP_USER'))
         , p_icon_css_classes        =>  'fa-user'

    -- applications entry
    l_target := apex_url
                    ( p_app_id       =>  aut_pck.get_aut_app_id
                    , p_app_page_id  =>  'APP_MENU'
         ( p_label                   =>  'Applications'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-desktop'

    -- DEVELOPMENT entry
    if in_development_environment then
           ( p_label                   =>  'DEVELOPMENT'
           , p_icon_css_classes        =>  'fa-exclamation-circle'
           , p_list_item_css_classes   =>  'yellow'
    end if;


All the messy code is encapsulated in the procedures and functions. 
Two standard entries are defined: the logout entry and the display of the username. Notice that the username is displayed using initcap. It is also possible to display the user's real name here when it is available in the database. 

Next a link to the applications menu is supplied. 

The last entry is used to notify a user that he is working in the development environment. This entry is only shown when working in the development environment which can usually be determined in SQL, for example by retrieving the database SID or service. In this way the application can be deployed to other environments unchanged. 

You can define a dynamic list using this package: 

This list can be chosen in the User Interface attributes to represent the Navigation Bar. 

The resulting Navigation Bar looks like this: 

You can download the full code of the package here.  

Happy APEXing :-)