Sunday, 10 September 2017

ORA-00933 when editing a Apex report

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


Sunday, 3 September 2017

Adding a bit of Excel to tabular forms

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.

You can see and experience the Plug-In at this demo page: http://www.speech2form.com/ords/f?p=141:copydown

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

One last question: does anyone know how to access the state of checkboxes in an Universal Theme tabular form?

Happy Apexing



Friday, 1 September 2017

Copy hidden items to clipboard plugin

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

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

Update 22-07-2017: A new version 1.2 has been released with a bug fix.This bug prevented the plug-in form functioning. 

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