Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:


And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing…

Source Article from http://dickdral.blogspot.com/2017/09/ora-00933-when-editing-apex-report.html

When speaking to users they often ask me whether the editing of data can be like Excel. Using tabular forms is a long way from using Excel. It is hard to operate the tabular form just with the keyboard and copying of values needs to be done in a number of keystrokes.

With this in my mind I created a JavaScript function to perform a (single) copyDown action. The value of an item is copied to the item below and the focus is set to that item. When this can be executed using a key(combination) the user is able to do a fast copy down action.
After creating this function I realized it also can be used to move up and down in the tabular form using the arrow keys. And I decided to create a Plug-In around it.

The Plug-In can be called from a Page Load dynamic action. No settings are needed.
The actions will execute on any text, date, and select item in a tabular form. Checkboxes are not yet supported.
The available function keys are:

  • [Up] move up to previous row
  • [Down] move down to next row
  • [Ctrl] + [Down] copy content of current cell to cell below and set focus to this cell
  • [Ctrl] + [Up] copy content of current cell to cell above and set focus to this cell
  • [Ctrl] + [Shift] + [C] copy content of current cell to cell below and set focus to this cell ( needed for Select items because they have another function for [Down] )

I could have included the [Right] and [Left] arrow keys but then the editing of the cell content would be affected. These keys cannot be used anymore to navigate within the content. So the keys [Tab] and [Shift][Tab] can be used to go to the next and previous item in the row.

I think many of you can make your users even happier with their Apex application when implementing this Plug-In in your application.

As always this plugin can be downloaded from apex.world.

Source Article from http://dickdral.blogspot.com/2017/09/adding-bit-of-excel-to-tabular-forms.html

At the beginning of 2017 I published a plugin to copy the content of Apex items to the clipboard. I use it on a regular basis in my applications but always for visible elements.
Yesterday I got a comment that the plugin does not handle hidden Apex items correctly. So I set out to create a new version of the plug-in.

In the new version of the plug-in the hidden Apex items are handled correctly and also added support for JQuery selectors.

The new version 1.1 now supports Apex items of type Hidden. This means you can build a string in  hidden item and provide the user with a button to copy the content to the clipboard. Just create a button, add a Click dynamic action to it and supply as action Copy to clipboard [Plug-In]:

In this example the hidden item is called P800_HIDDEN_1.

For the developers that feel at home with JQuery there is the option to reference to DOM elements using JQuery selectors. On the example page you will see a button Introduction to clipboard (JQuery selector) in the header of the Introduction. This button fires the following dynamic action:

The Introduction region has got a static ID INTRO and if you open the inspector you will see that the introduction text is contained in a div element with class t-Region-body. So if you click on the button the text will be copied to the clipboard.
NB Only the first element from the JQuery selection will be used

You can download the new version on http://apex.world

Happy Apexing

Source Article from http://dickdral.blogspot.com/2017/09/copy-hidden-items-to-clipboard-plugin.html

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

Source Article from http://dickdral.blogspot.com/2017/07/restricting-shuttle-choices-even-faster.html

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!

Source Article from http://dickdral.blogspot.com/2017/06/avoiding-multiple-logins-from-external_28.html

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.

Source Article from http://dickdral.blogspot.com/2017/06/avoiding-multiple-logins-from-external.html

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