Tuesday, 4 July 2017

Restricting shuttle choices even faster

Some months ago I published an Oracle Apex plugin to restrict the left side shuttle choices. You can read the original post here.
On the demo page the On Change event was used to refresh the left side list. My friend Alex Nuijten made the remark this was not very intuitive. The user needs to navigate out of the selection item to see the change.
So I changed the demo page to provide instant feedback. The selection is now refreshed after each keystroke. As this process is completely executed on the client the response is very fast. Only for very long list the user might notice a slight delay.
No changes to the plugin were needed. It can still be downloaded at apex.world


Here is how is was done:

Selection item
Name : P400_SELECTION
Type: Text Field

Dynamic Action
Name: Select on keystroke
Event: Key Release
Selection Type : Item
Item(s): P400_SELECTION

True Action
Name: Perform Selection
Action: Restrict Shuttle Values [plug-in]
Search Item: P400_SELECTION
Selection Type: Item(s)
Item(s): P400_SHUTTLE
Fire on initialization: No

Happy Apexing


Wednesday, 28 June 2017

Avoiding multiple logins from external Apex links Part 2

A few weeks ago I wrote a post how to avoid multiple logins when using a link in an email. This solution however does not work in a batch process for two reasons:
  • the URL needs to be fully specified, so protocol, server, port and directory are also needed. These data are not available outside the Apex context
  • the function apex_util.prepare_url can only be called from within an Apex session
My first idea was to prepare the URL for every possible mail while being in an Apex session and store it in the database. The stored URL can be used when generating the email.
This would be technically  complex, because a new column would have to be created and the content of this column would have to be kept up to date all the time.

Another possibility would be to create a fake Apex session within the batch process. Then the rest of the code need not be changed. Martin Giffy d'Souza provides a way to create an Apex session in this blogpost. Although this is an 5 year old post I was able to create an Apex session and generate a valid link.

The last solution was chosen because of the simplicity and the absence of need for code change.

Happy Apexing!

Wednesday, 21 June 2017

Debugging causes Oracle Apex page with plugin to crash


Today I was presented with mysterious behavior from an Apex page. I had developed a dashboard in one application and transferred it to another application connected to the same Oracle schema.
When I ran the page it gave the error:
Error in PLSQL code raised during plug-in processing.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The error back trace did not give much info:

ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 561
ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 985
ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 355
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 165
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1855
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1894
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 935
ORA-06512: at "APEX_050100.WWV_FLOW_PLUGIN", line 1296
What was the matter?
I added an exception handler to the render function of the plugin, but no errors were reported from there.
I added a debug statement at the beginning of the render function and there was no output from it in the debug log.
I studied the case with my colleague Alex Nuijten and we could not find a solution. Until he had the bright idea to run the page not in debug mode. And the plugin rendered without errors! Thanks, Alex.

Now everything fell into place. The debugging outputs the PL/SQL Code section of the plugin.
And this code was not fully shown until the last end statement. So the error was generated because the amount of PL/SQL code was too much to output when debugging :-( . The code was 21K long in total and up to about 16K was shown in the output.

This provides yet another reason to move the PL/SQL code of a plugin to a database package.

Happy Apexing

Monday, 12 June 2017

Avoiding multiple logins from external Apex links

Some Apex applications sends notifications emails to users when tasks are due. These emails contain a link to an Apex page. When the user clicks on the link he is taken to the page. When this page is not public - which usually is the case - he is directed to the login page of the application. Even when he is logged in for the same browser on another tab.
When users get a lot of these emails it is annoying they have to log in each time. Apex provides a way to avoid the login when a session exists in the same browser. This post describes how you can set it up using the Rejoin Sessions functionality.

In order to be able to rejoin an existing session a few settings have to be made. 

The instance setting Rejoin sessions needs to be set to Enabled for All sessions. Log in as Instance Administrator and navigate to Manage Instance > Security  


The application setting Rejoin sessions needs to be set to Enabled for All sessions. As a developer go to Shared Components > Security Attributes > Session Management :



After setting this parameter you may be prompted to set the parameter Embed in frames to Deny or Allow from same Origin

The link in the email usually contains parameters. Links with parameters should contain a checksum for session rejoining. So on the target page set Page Access Protection to Arguments must have checksum. Then provide the link URL with a checksum:

l_url := apex_util.prepare_url(l_url);

Use this URL in the notification email to your users. 
When the user clicks on the link and there is no session in the browser he is prompted to login. 
When the user clicks on the link and there is a session in the browser the target page is shown with the parameters from the URL. The user does not need to login a second time. 

This method does not imply a security risk because an existing session is reused. When the user is not logged in the login page will be presented. 

In this example the parameter names and values are exposed in the URL. To maximize the security these might be hidden by storing the actual URL in a table indexed with a hash value. The link in the email points to an intermediate page that uses the hash value to retrieve the actual URL and redirect to it. 

Happy Apexing












Friday, 28 April 2017

Displaying info cards using an Oracle Apex named report template

For the Apex Dashboard Competition of the DOAG in 2016 I created a Apex dashboard. You can read about it in this post
One of the elements of this dashboard was a group of four infocards.


These cards display the key values for a country on a certain subject. Apart from the text the cards differ in color and the icon used.
These cards are created using a named report template. This is a special kind of report template. Using a normal report template the query columns and rows map to the columns and rows in a HTML table. A named report template consists of a HTML template in which the query columns are referenced by name. For the infocards the query and template below are combined:

select 'Population'  title
     , population    data
     , 'Number of inhabitants'  text
     , 'fa-users'   as  icon_class
     , 'db2_red'     as  container_class
from   cnt

Row Template 1 within the Report Template:


The substitution strings in the template are replaced with the corresponding values from the query.
As you can see the column values can also be used to define CSS classes as in the enclosing DIV element. The value of the column container_class defines the name of the class to applied. This class defines the color of the card. The class icon_class defines the Font Awesome icon to be used.

The cards are styled with the following CSS:


The color of the card can be defined by selecting the db2_color class as container_class in the query. 

The icon is positioned absolute relative to the card with the class db2_icon_container. Space is created for the icon container by defining  left:80px; for the main DIV containing the text.

The rest is quite straight forward CSS styling.


You can create your own Named Report Template:
- navigate to Shared Components > Templates
- press Create
- chose Report
- create the template From Scratch
- enter the name for your template and check Named Column (row template)
- then enter the HTML for the template

Named Report Templates provides us developers with more freedom to style the output. The downside is that the templates are more specific and less widely applicable. 

Happy Apexing

Tuesday, 18 April 2017

Oracle Apex Plug-in for restricting shuttle choices

The Oracle Apex shuttle item is a neat way to select a number of values. It is however not so user friendly when the list of choices is very long. In this case it is useful to be able to limit the list of choices.
When you implement the limitation in the LOV-query of the item, the limitation will also be applied to the selected values. Usually this is not the desired behavior.
This behavior is avoided when the restricting of the left shuttle pane using JavaScript. I have created a Dynamic Action plug-in to do this. The choices in the left shuttle pane are evaluated case insensitive against the content of the search item. If the search item contains more than one string ( separated by spaces) all the strings should occur in the shuttle value. The search string 'INVOICE 2013' returns only values that contain 'INVOICE' (independent of case) and '2013'.



The plugin attributes are the shuttle item and the select item. The plugin needs to know the select item to disable submit when enter is pressed.
Furthermore the select all button is disabled once a selection is applied because this button copies everything from left to right irrespective of visibility. Once no selection is applied the button is activated again.

You can see the plug-in in action on:

http://www.speech2form.com/ords/f?p=OPFG:RESTRICT_SHUTTLE

As usually you can find the plugin on apex.world

Update 22-04-2017: A new version has been released with support for Safari and IE. Also the triggering item is not anymore restricted to the search item. 

Happy apexing

Sunday, 9 April 2017

Finding slow JavaScript

Working with Apex I regularly write Javascript. Most of the time these client-side code snippet are blazing fast, but in some cases they take a few seconds to execute.

In these cases I want to see which part of the code is slow. In this process I use two very simple JS functions which make life a lot easier for me:

var timing_start;

function start_timing( text) {
    timing_start = performance.now();
    if ( text) { console.log('Start timing:'+text); } 
}

function show_timing(text)
{ console.log('Timing:'+text+', milliseconds:'+ (performance.now()-timing_start) );  }

You can put this code on the page or include it in a general JS file.

 The use of these functions is really simple:

start_timing();
...
code to be timed 
...
show_timing('Data retrieved');

This will result in the following output in the browser's console:


Timing:Data retrieved, milliseconds:784.2350000000001

The show timing calls display the elapsed time since the last call to start_timing.

Happy JavaScripting