Monday, 6 February 2017

Generate log statements in seconds

One of the things that cost me a lot of time is the writing of log statements.
I do it a lot when I am working on complex PL/SQL or JavaScript routines. Just to understand what's happening. Mostly these log statements have the form:

logger.log('varname1='||varname1||', varname2='||varname2, etc... );

and most of the time the variable names are even longer :-(. 
So it is a lot of typing, especially for something that is deleted after a few hours. As I am into automating my job I decided to automate it. Here you can see it in action: 

The selection and generation is all written in JavaScript. You can see how fast it works ( it is recorded in real time ). The code is pasted in the textarea, variables are selected by clicking and then the log statement(s) can be generated. 

You can find the page here:

The page supports PL/SQL and JavaScript. For PL/SQL the identifier naming rules are quite strict, so these are implemented. At the moment the same rules are also applied to JavaScript. 
NB As JavaScript rules are very free, some variables might not be recognized, for example when they contain diacritics.

For PL/SQL you can select logger or apex_debug, for JavaScript console.log statements are generated. 
There is an option to generate mulitple calls or combine all variables in one call. 

I have tested this with PL/SQL and JavaScript and I could run the generated code as is, no changes were needed. 

Happy Apexing and JavasScripting

Wednesday, 1 February 2017

Using Heatmap Plugin to monitor site visits

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 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, you get the quiet background with sufficient contrast.

You can see the result at:

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

Sunday, 29 January 2017

Utility for Rewriting query parameters

Last month I have worked a lot with charts in Apex. As it was a BI application on research data the queries were long and contained many input parameters.
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:

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...

Friday, 20 January 2017

Generating setters and getters for package variables

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:

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

Friday, 13 January 2017

Plug-in for time input for touch devices

In februari 2015 I published a blogpost about a new form of time entry on smart phones. Now I have wrapped this control into an Apex plug-in. 

The essence of the control is that you draw the hands of the clock on your smartphone instead of  entering or choosing the time in digital format.
In my experience it feels much more intuitive. It is also very fast way for entering time values. 

The next link will take you to a page where you can try this control.

The control is designed for touch devices. In a desktop browser you can click on the numbers in the analog clock to select the hours or minutes.

If you want to try the control on your phone or tablet you can have an email with a link to the demo page sent to you. Enter your email address below and press the 'Send link by mail'. Your mail client will open and you can send the mail.

Send to email address     Send link by mail
( Your email address is not stored )

How to use the control

Below is an explanation of the control:

  • Touch the clock image next to the time input field and the time input control will appear.
  • In the center is the analog clock in which the hands can be drawn. 
  • Above it you see the digital time with buttons for changing to AM or PM. 
  • Below it are buttons to Accept of Cancel. 
  • You can draw a hand by moving your finger from (approx.) the center of the clock outward toward one of the numbers. 
  • The accuracy of the clock is 5 minutes
  • The active hand and the active part of the digital display are indicated by a red color
To enter 10:15 you start at the center of the clock and swipe towards the 10, then release.
The hour in the digital time display above the clock will display 10 and the minutes will have turned red (=active). Then start in the center of the clock again moving toward the 3 this time. When you release you will notice that the digital clock displays 10:15.
The time is initially interpreted as a value between 7:00 and 19:00.
To change to PM touch the PM button.
If you are satisfied you can press OK to save the time. Cancel returns to the form without saving the time.

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

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.

Happy developing,

Tuesday, 10 January 2017

Plug-in for copying to clipboard

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

Happy Apexing!

Sunday, 8 January 2017

Plug-in for resizing modal dialogs

Some weeks ago I wrote about automatic resizing of modal dialog to fit the content. Since then my new hobby is Apex plug-in development, so I decided to create a plug-in for this. The plug-in works on Apex 5.0 and 5.1.

New resizing functionality for modal dialogs in Apex 5.1

In reaction to my previous post Shakeeb Rahman tweeted that the resizing was on of his favorite features of UT in Apex 5.1. After getting Apex 5.1 this was of course one of the first things for me to investigate. At first nothing happened, then I realised that the compatibility probably should be set to 5.1 in the Application properties:

And then the modal was indeed resized on load. But not always as I expected, as you see on the demo page On this page you can de-activate the plug-in. The application is set to Compatibility 5.1, so you can observe the new behavior. 
The report scales all right, but the dialog with the form items does not. It may be a timing issue, in that the display of the items happens after the setting of the height. 
So on Apex 5.1 the plug-in extends the new resizing functionality. 

The plug-in

Anyway, the Resize Dialog plug-in gives you control over the moment and the way of resizing. 
It is a Dynamic Action plug-in that can be called at Page Load or as in the example below on After Refresh for a report. The plug-in works on Apex 5.0 and Apex 5.1. 

Apart from resizing the plug-in also:
- guards the dialog from running of the page
- enables to define margins around the modal dialog
- enables vertical centering of the dialog

The margin size and the enabling of centering can be set with custom attributes. 

The plug-in can be downloaded on

Happy Apexing