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

Source Article from http://dickdral.blogspot.com/2017/04/displaying-info-cards-using-oracle-apex.html

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.

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

Happy apexing

Source Article from http://dickdral.blogspot.com/2017/04/oracle-apex-plug-in-for-restricting.html

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

Source Article from http://dickdral.blogspot.com/2017/04/finding-slow-javascript.html

Recently I’ve been installing the OATOA (Oracle APEX on Tomcat with ORDS behind Apache) stack a number of times on both Oracle Linux 7 and CentOS7.

In the past this was no problem, Tomcat came up and a request to http://myserver:8080/ords/ gave me the expected APEX login screen.

However since Oracle Linux 7 and CentOS7, it seemed that Tomcat started before it could find the Oracle database. A simple restart of the tomcat service would do the trick. On my virtual machine (recently switched from VirtualBox to parallels btw) meant for doing presentations no problem at all.

systemctl restart tomcat

Now I’m playing around with “droplets” at DigitalOcean, just another name for virtual servers in my opinion, but they have great features at a reasonable price. Take a look: https://m.do.co/c/187c0416a2b3.

But my droplets showed the same behaviour as my parallels virtual machine. I first had to restart tomcat to get a working system.

You all should know that I’m not a linux guru (really I’m not), so broke my brains about this for a looooong time.

Yesterday I finally managed to get this working. The idea is to make the tomcat start-script wait until it can see “something” on port 1521 (the db listener) and only then continue to start.

Open the file /usr/libexec/tomcat/server

nano /usr/libexec/tomcat/server

now add some lines of code _before_ the line that starts with MAIN_CLASS like this:

#!/bin/bash
. /usr/libexec/tomcat/preamble
# .=.=.=.=.= START Make tomcat wait on oracle .=.=.=.=.=
i=1
while netstat -lnt | awk ‘$4 ~ /:1521$/ {exit 1}’0
do
  sleep 10
  let i+=1
  if [ “$i” -gt “5” ]
  then
    break    #Abandon the loop.
  fi
done
# give Oracle some slack to also start the database
sleep 10
# .=.=.=.=.= END Make tomcat wait on oracle .=.=.=.=.=

MAIN_CLASS=org.apache.catalina.startup.Bootstrap

add the obvious lines to your script.

What is happening?

The line “netstat -lnt | awk …” tries to find the string “:1521” in the result of a “netstat -lnt” command. This would indicate that something is listening on that port. In our case it will be the Oracle Listener.

If it does not find an open port 1521, it will sleep for 10 seconds, increment a counter (“i”) by 1 and do the loop again. The loop will loop a maximum of five times to prevent an endless loop.

After the loop completes we give the Oracle database another 10 seconds to get started.

That should do the trick. It does for me (I use the same code at digitalocean as well as my parallels virtual linux server.

I’m sure linux must have some system in place that could do the trick as well (make one service dependable on the other) but I haven’t found an easy one to use. If someone could help me out here, feel free to leave a comment.

Regards,

Richard

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:


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


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

Source Article from http://dickdral.blogspot.com/2017/02/generate-log-statements-in-seconds.html

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