As my demo site grows I am curious about my visitors. Based on the Apex activity log I have created a report which shows the origin of the visitors:

The location is determined from the IP address. This is done in a Dynamic Action that calls a web service at ipapi.co to retrieve the geographical location from the IP address and then stores the result into an Oracle table for subsequent usage.

NB The apex_activity_log is limited in size and the older entries are regularly deleted. Therefore the content of the log is daily copied to a another table. This table is used for the report and the map.

I like to see that the visitors come from all over the world, not only from Europe, North America and Asia, but also from Middle and South America and Africa.
It is good to see that Apex is used all over the globe!

But the list does not give me an overview of the geographical locations. So I looked for a way to visualize this data and came across the GeoHeatMap plugin by Jeffrey Kemp. It is very easy to implement and just requires a query retrieving latitude, longitude and weight.

The result is a very nice looking map:

You can get a clear view of where the visitors come from.

In the normal mode the light green color is hardly visible against the predominantly green color of the maps. By setting the plugin attribute Map Style to mostly gray, found at  https://snazzymaps.com/style/4183/mostly-grayscale, you get the quiet background with sufficient contrast.

You can see the result at:

http://www.speech2form.com/ords/f?p=141:HEATMAP

It is possible to see the geographical distribution per page by changing the Page select list. There is clear difference in distribution for the various pages of the application.

Happy apexing

Source Article from http://dickdral.blogspot.com/2017/02/using-heatmap-plugin-to-monitor-site.html

Starting in Apex I used bind variables to reference the input items.


select name from table where id = :P100_ID

Then I move the query to a package and I had to rewrite the bind variables to calls of the Apex v function.

select name from table where id = v(‘P100_ID’)

For some queries I preferred to pass the parameters as PL/SQL procedure parameters so I had to change the query to:

select name from table where id = p_id


And for defining a ref cursor I prefer to use substitution parameters:

select name from table where id = [id]


Now this is just a short query with just one parameter.
But it is a lot of work for queries with  20+ lines with 5+ parameters.

And then it takes time to convert the parameters. And I make mistakes, I forget to change a parameter or I make a typo. Most of the time I find the error quite fast but sometimes it is not so obvious.

Enough reasons for me to automate this process. And make the solution publicly available:

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

On the page you can paste your query. If possible the source parameter type is recognized.
You can indicate to what type of parameters are the target of the conversion.
Further you need to specify the delimiters for the substitution variables or the prefixes for PL/SQL and Apex ( for bind variables and v-function).

Then hit Generate, clip the code and use it.
For me most of the time I can use the code without modification.

Happy apexing…

Source Article from http://dickdral.blogspot.com/2017/01/utility-for-rewriting-query-parameters.html

Building applications with Apex I am coding in PL/SQL a lot. I find myself typing in similar patterns many times. And I do not like it. Being an IT man I look for ways to automate it.

 One of the things I regularly have to create is getters and setters for package variables. I start out with a package and define a package variable. Then after a while it turns out I need this value in a SQL statement. So I need a getter. It is more elegant to use getters and setters anyway.

But my point is I always end up typing more or less the same code and it slows me down. So I created another generator. You can find it at:

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

It is very simple and fast to use. Just type (or paste 😉 ) the name of the variable, chose the datatype and hit the generate button. Then hit Copy to Clipboard and paste the result in your PL/SQL package specification and body code respectively.

Extra parameters:
– Internal variable name: use it if the internal variable name differs from the one you want to expose in the interface. The case of this name is not affected by the Case switch.
– Case of keywords: you can have your code with uppercase keywords

Happy Apexing

Source Article from http://dickdral.blogspot.com/2017/01/generating-setters-and-getters-for.html

Apex Plug-in

This control is now available as an Apex dynamic action plug-in.
You can find it at the plug-in section of apex.world.

The settings of the plug-in allow you to:
– define the output time format ( application attribute )
– define the default time window base ( component attribute), see explanation below

Using a default time window

The analog displays a 12 hour period, while the day consists of 24 hours. This is tackled by the AM/PM indicator. It is however not very user friendly if you have to press the PM button each time you want to enter a time after noon. Therefore the concept of a time window is introduced. In many cases your application will accept times in a certain ranges. For working hours registration this might be between 7:00 and 19:00.
It is possible to interpret the chosen times to that specific period. So hour=8 will yield 8:00 and hour=5 will result in 17:00. In this example the time window is from 7:00 to 19:00 and the time window base is 7.

The time window base can be set as component level attribute. The user can overrule this setting by using the AM or PM buttons. So if the user enter hours=5 and presses the button AM, the resulting time will be 5:00.


I would really like to hear whether you think this control useful. Or maybe you have  suggestions on improving it.

Source Article from http://dickdral.blogspot.com/2017/01/plug-in-for-time-input-for-touch-devices.html

It is a hobby of mine to generate code. I use Apex as a shell to enter the parameters and display the result. Usually the result is a text area in which the code is contained. After generating the text I can select the code and copy the result to the clipboard. But I want it to be a bit more comfortable.

So this is why I developed this plug-in. It is a dynamic action plug-in that you can use in any DA, but usually it will be a DA fired from a button. It is used in the page to generate substitution code:

Implementing this functionality is as easy as importing the plug-in and call it in the dynamic action behind the click on the button. Select the item of which the content should be copied and you are off to go:

You can see this plug-in in action on the demo page for this plug-in.

You can find the plug-in on apex.world.

Happy Apexing!

Source Article from http://dickdral.blogspot.com/2017/01/plug-in-for-copying-to-clipboard.html

In November I posted about splitting an Apex Report into multiple columns. This solution involved some JavaScript code to be included and called from the Apex page.

One of the comments I got was a question whether the items could also be sorted horizontally. With a small number of rows the current code can result in less columns than specified. I decided to implement this feature:

I also realized that creating a plug-in would make it much easier to implement this functionality. No need to add JS code, call this code at some point and figure out the right value for the parameters…

So here is my first (public) plug-in, Report2columns.
The easiest way to get the plug-in is om the Plug-ins page of  apex.world:

To use it:

  • import the plug-in 
  • create an After Refresh trigger for your report 
  • chose the action report2columns [Plug-In] 
  • enter the number of columns and the sorting direction

That’s all there is to it.

 Happy Apexing

Source Article from http://dickdral.blogspot.com/2017/01/plug-in-for-splitting-reports-into.html

Apex 5.1 ships with a large set of icons. These are contained in a font called Font Apex.
This font contains 1000+ icons. Part of the icons are Font Awesome V4.7 based, others are specifically designed for Apex. The Font Awesome based icons are less bold, as you can see in the menu icons below. Original Font Awesome to the left, Font Apex to the right.

Scaleability

Because the icons are less bold they are more suitable for enlargement. Font Awesome icons look a bit bulky after enlargement, the largest one is 3em:

while Font Apex stays elegant :

Applying Font Apex

You can activate Font Apex in the properties of the theme ( Shared Components > Theme > Specific Theme > Icons ):

The HTML to use Font Apex is exactly the same as with Font Awesome. Font Apex also has the classes .fa and .fa-nameBecause Font Apex is a superset of Font Awesome v4.7 you can toggle between the two font as long as you do not use Font Apex icons.

List of icons

Source Article from http://dickdral.blogspot.com/2016/12/new-icons-in-apex-51.html

To send mail from your application you can use the package APEX_MAIL. I had created a package to send daily status mails and everything worked fine when called from within the application. So I thought I was almost ready… Then I tried to call the package from SQL Developer :

Error in Send daily mail: ORA-20001: This procedure must be invoked from within an application session.

A Google search quickly revealed the solution. The Apex workspace ID should be set.
Alas, this did not work ( anymore? ).

I did not want to rewrite the logic so I had to figure out a way to call the package from within the Apex context.
It can be done using a public Apex page sending the mail. This page is called from PL/SQL using UTL_HTTP. The use of the page is restricted by using a secret parameter.
Read on to see how it works in detail.

Database

In the database we have a package mail_pck to send the mails. We add the following code to this package:

create or replace package body mail_pck is

  -- autorisation code 
  -- NB do not use special characters for URL's like '?', '&' etc.
  g_aut_code      varchar2(100) := 'secret_string';  
                                  
...

  procedure send_mails_using_apex_mail is
...

  -- return the autorisation code
  function get_aut_code  return varchar2 is
  begin
    return ( g_aut_code ); 
  end;

  -- check the autorisation code
  function check_aut_code ( p_code in varchar2) return boolean is
  begin
    return ( nvl(p_code,'x') = g_aut_code ); 
  end;

  -- calls Apex page from which mail is sent
  procedure batch_send_mails is
    l_url       varchar2(1000) := null;
    l_result    varchar2(4000) := null;
  begin
    l_url    := alg_pck.get_parameter('SERVER_URL')||'/ords/f?p='
                ||alg_pck.get_parameter('APP_ID')
                ||':9000:0::::P9000_CODE:'||get_aut_code;
    l_result := utl_http.request(l_url);
  end;

end mail_pck;

The schema for this package should be granted to execute utl_http.
An ACL should be created to access the Apex server. This ACL should be granted to the schema.

Apex

Create a page in Apex with
Page Number : 9000 ( for this example )
Authentication : Page is public

Create a page item
Name : P9000_CODE
Type: Hidden

Create a branch
This branch fires a redirect when the code is not correct
Name: To login page when code is not valid
Process Point: Before header
Type: Page or URL (Redirect )
Target page: LOGIN_DESKTOP
Condition Type: PL/SQL Expression
PL/SQL Expression: not mail_pck.check_aut_code(:P9000_CODE)


Create a PL/SQL region
This PL/SQL region performs the sending of the mails
Name: Send daily mail
Code:

begin
  mail_pck.send_mails_using_apex_mail;
  sys.htp.p('Mails sent.');
end;

Condition Type: PL/SQL Expression
PL/SQL Expression: mail_pck.check_aut_code(:P9000_CODE)


Happy Apexing!

Source Article from http://dickdral.blogspot.com/2016/12/using-apexmail-outside-of-apex.html

The modal dialogs are a very nice feature, easy to use and well integrated in the Apex framework. One thing that annoys me is the vertical sizing of the dialog window.
Normally I will create a page as a Modal dialog. When running it I notice that the window is way too high or not high enough. I go back to the builder and enter a number of pixels for the height and look what the result is. I repeat this process a few times until I am satisfied.

When the page is changed this may  change the height of the page, so… :-(.
This is typically one of these boring jobs I like to automate. So I examined the structure of the Apex modal dialog.
One thing that is counter intuitive is that the header of the modal dialog is part of the calling page. Only the red part is generated from the modal dialog page.

The height of the modal window is defined in the calling page. This is the reason why you have to refresh the calling page when you change the height of the modal dialog.

Inside the red square the height is determined by three div elements: dialog header, dialog body and dialog footer.
The JavaScript code below determines the total height of the dialog and sets the height of the determining element on the calling page to this value.

function resize_dialog()
{
  var header  = $('.t-Dialog-header');
  var height  = parseInt($(header).height());  
    
  var body    = $('.t-Dialog-body');
  var padding = parseInt($(body).css('padding-top')) + parseInt($(body).css('padding-bottom'));
  height += padding;
    
  var container = $(body).children().first();
  height   += parseInt($(container).height());  
    
  var footer    = $('.t-Dialog-footer');
  height   += parseInt($(footer).height());  
  console.log('total height = '+ height );

  parent_container = window.parent.$('.ui-dialog-content');
  $(parent_container).height(height);
}

This code can be called in the On page load section of the dialog page.

It is also possible to call the function in a Dynamic Action that changes the height of the dialog content. Two examples of this behavior can be seen in the example page here. You can change the number of rows or number of items and the dialog is resized automatically.

Happy Apexing!

Source Article from http://dickdral.blogspot.com/2016/12/automatic-resizing-of-modal-dialogs.html