Wednesday, 21 September 2016

Show report tooltip as notification

Last week I described a method to make the tooltip on information more accessible to the end user.

Here is how you could make the same information available to touchscreen users.

1) First step, as before, is to define the HTML Expression of the column to include the title tag.
In this case I also stored my row identifier as an extra data- attribute.
<span title="#RECENT_NOTE#" data-key="#ROW_KEY#">#MY_COLUMN#</span>

2a) Create a dynamic action on click of the column cell.
jQuery Selector:
td[headers="my_column"]

2b) Set the value of a hidden page item.
This could be done a few ways, but here is how you can do it with Set Value, based on a JavaScript expression.
Set Value action example

What's pertinent is this.triggeringElement, which identifies the element being clicked. In this case it's the table cell, so .children() identifies the span from the HTML expression; .data() grabs the extra attribute; and .attr() grabs the title tooltip.
$(this.triggeringElement).children('span').data('key') + ': ' + $(this.triggeringElement).attr('title')

This expression extracts the a relevant key value (to identify the record) and concatenates it with the tooltip.

My P50_LAST_NOTE is hidden and unprotected.

2c) A Notification plugin could be used to display the value set in the hidden item, as a second action in the dynamic action. Other notification plugins are also available.
This 'gritter' style notification from APEX uses the hash syntax to get the client value,
#P50_LAST_NOTE#
As opposed to the value from session state while the page was rendered.
&P50_LAST_NOTE.
This information is documented in the relevant attribute help.

Now when you tap (or click) on the cell, the tooltip is displayed in the notification.

Wednesday, 14 September 2016

Extend column tooltip to table cell

Quite sometime ago while still on 3.x I described a simple way to add tooltips in a report using standard HTML in a HTML Expression.
<span title="#RECENT_NOTE#">#MY_COLUMN#</span>

However, it will only appear when hovering over the span content, not anywhere within the table cell.

To do so we can add a dynamic action to execute some JavaScript after refresh of the relevant region.
$('td[headers=my_column] span').each( // for every data cell in the column
  function() { 
     // copy the title attribute to the parent cell
$(this).parent().attr('title',  $(this).attr('title'));
  });
This JavaScript copies the title attribute from the inner span to the surrounding td tag, so you will see the tooltip when the cursor is anywhere within the cell. I find this relevant when there is a bit of spacing/padding in the report.

In this follow-up post I describe how you could display this tooltip on tap of the cell, useful when used within touch devices.

Friday, 9 September 2016

APEX Survey Results: Workspace Activity Log

This question in my 2015 survey relates to built-in instrumentation.

Q7. Do you utilise apex_workspace_activity_log (for monitoring/reporting application usage)?

Yes (90)  47%
No (102) 53%

This log table reports details of all page rendering and AJAX process calls. I really like this information, though I would like to be able to add to the output things such as
sys_context('userenv', 'server_host')

In addition to the Monitoring pages you'll find in the APEX administration section, I've built a few pages that report on this information in my own particular way. This allows me to monitor behaviour and identify bottlenecks. More information to come on this since it's fuelling a presentation I'm writing on charting in APEX 5.1, so stay tuned.


Monday, 5 September 2016

Users still double click buttons

Despite web pages having been around for quite some time, and users have been given years of affordance, people still double click on links & buttons in web pages.

This can cause a few issues in an APEX environment as the underlying process may get executed twice, and today I came up with what I thought was a simple, declarative solution.


In my case I had a button that submitted a modal page. I added a dynamic action that simply hid the button on click. I just had to ensure the action didn't execute on page load, and the button still submitted the page.

I thought I'd look around to see what others have done to mitigate this issue, and it seems I was on par, but with less code:

Wonderfully verbose description of what I described above
Funky JavaScript solution from 4.x
JavaScript solution in 3.x
Similar JavaScript solution
Purely session state protection

The only thing that makes me uncomfortable about my suggestion is the button has two behaviours, so Roeland's description may be considered more elegant, ie - a submit and hide action.

Friday, 2 September 2016

APEX Survey Results: Instrumentation / Debugging

This question from my 2015 survey may pique curiosity among some APEX developers.

Q6: How do you instrument your code?



Hopefully those in the 22% who do not add instrumentation just didn't understand the question.

Instrumentation is another term for adding debugging information to your code. There is plenty of commentary on this topic and done right it can make tracking down problems a breeze.

It can be as simple as knowing when a procedure was called, and with what parameters. Take the following procedure definition with instrumentation (debug logs) added.
procedure xyz(p_in number) is
begin
  debug('start xyz');
  debug('p_in:'||p_in);

  -- do stuff

  debug('end xyz');
end;
 I think you're essentially coding blindfolded without it. You could only infer this procedure was executed because of whatever it does, and if something goes wrong how do you know what was passed in? Where did it get up to? Did it execute at all?

Imagine using the database without the ALL% dictionary views. They're essentially instrumentation to your database. Tom Kyte has some particularly good talks on the topic, and a few chapters in his various books.

It's very much a PL/SQL thing, but since APEX pages are generated via PL/SQL, there is a natural solution with the supplied apex_debug package. Any process can add information to the APEX debug log, which is only collected while debug is on.

There is no reason why you shouldn't instrument any PL/SQL, and have the granular control to turn on/off at will. Libraries are available to make this job easier.

You may have heard Logger talked about at conferences. It's a community developed library, well, mostly driven by some key players. It's had a few homes (remember google code?) but is now found on GitHub:
github.com/OraOpenSource/Logger

PL/SQL Logger can be found here, if you want to compare solutions or look for ideas, read other people's PL/SQL.
https://sourceforge.net/p/plsqllogger/wiki/Home/

Despite recommending it a few times, I've never actually used Logger on-site (sorry Martin). For quite some time I've had a basic infrastructure of my own that has worked fine, using the same premise. That being said, it's on my list to convert/upgrade when the timing is right. Partially because I've followed it's progress, know who wrote it, and inspected the code. I look forward to benefits some extra features will bring.

The next few questions also relate to instrumentation, so stay tuned.

Thursday, 25 August 2016

How to debug stuff in Oracle APEX

Recently a fine young gentleman, who shall remain nameless (let's call him Jerry), asked for some assistance he was getting with an error in Oracle APEX.

He had done all the right things in regard to debugging the problem, but didn't know enough about APEX to know which settings to investigate.

I think developers new to APEX need the occasional post like this to give them an idea on how to start looking into a problem, so I hope you found this knowledge helpful in squishing bugs in future.

The problem.

An associate of Jerry's converted a classic report to an interative report. However, when using the search bar to add a filter, the error "missing expression" was shown instead.



We could be fairly certain it's the filter we added that caused the problem, but that's built by the APEX engine. What can we do but perhaps run the page in debug mode to look for clues.

developer toolbar


Jerry enabled debug mode in the developer bar, which refreshed the page, collecting information about the rendering processes within the page. Clicking on open debug, then drilling into the the recent debug entry, using ctrl-F to find the error and you'll see a result like this.

Click/tap to embiggen

The brackets are there, but there's nothing in the middle, hence "missing expression". Jerry told me about this and wondered how this could come to be?

The Hint

Jerry said they recently expanded the capacity to filter stuff on that report, for my benefit, he says... anyway, that's a lead we can follow through on.

It turns out each column in an Interactive Report is configurable at a fairly granular level, and it turns out all columns had the ability to filter disabled.

Isn't it so great we can manipulate 8 columns at once like this?


If a few columns were set as searchable in this fashion, the debug would report a statement that looked like this.
select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
)  r
where ((instr(upper("ENAME"),upper(:APXWS_SEARCH_STRING_1)) > 0
    or instr(upper("SAL"),upper(:APXWS_SEARCH_STRING_1)) > 0
))
) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

Note your own PL/SQL can also contribute to this debug log using the apex_debug package.

But, how?

Anyway, how could Jerry know to look there? Unfortunately the debug machine can't do all our work and tell us what to check. I guess knowing exactly where to look is when APEX developers get paid the foldy notes, in knowing what sort of settings to check out first.

Through experience I guess I had a hunch on where to look and how to get there. Adding to that, any errors you may receive with Interactive Report filters, try repeat the filter for just one column instead of 'row text contains'. I find issues are often isolated to one particular column.

I normally start with the related region if I'm not sure where to start, though these settings are mostly oriented to the region framework, regardless of the widget inside the region - report, chart, plugin etc.

People new to the Page Designer often forget the 'Attribute' node under the columns. This is where you find settings specific to the region type you've chosen.

IR search bar controls are fairly granular, and I remembered that IR columns also had some options, and seeing 'Filter' gave it away for me.

Will we always have a job?

Back to the concept that computers can't do it all. I think us software developers have got it good, because we're the ones who need to design the AI to solve problems without humans. We'll be one of the last jobs to go, right?

Thursday, 28 July 2016

Application Item Prefix Usage

I was constructing a fresh app for a presentation idea and I came to think about the prefix people use for Application Items in Oracle APEX.

I've seen a few variations used, and upon asking the question on Twitter (as a poll limited to 4 options), some came back with a few other suggestions.

The final tally based on the replies (we'll see how pasting from Excel goes...)

F_ 15 Inspired by APEX team, no doubt. +1 for me
APP_ 10 Fair call, but could match built-in
G_ 6 Global, clever. One person combines this with A_ for application scope.
{project} 5 To match the prefix on your tables, right?
AI_ 3 Can't deny the provenance
{none} 3 This is dangerous, potential clash with built-ins
A_ 1 Based on item scope.

I remember some chatter recently about a community sourced standards document for APEX being kicked off on StackOverflow. Perhaps mini surveys like this can help shape projects like that, or maybe it fits into the oraopensource activities.

Either way, this sort of discussion is surely healthy for the community, eking out any important considerations in sometimes seemingly trivial matters, or "bikeshedding" is a term I heard recently.

I can't say I've tried creating application items (or substitution strings for that matter) that are the same as a pre-defined substition string, but surely it would be detrimental.

You never know, even the Oracle APEX development team may decide a thing or two from community responses. I certainly know what my next poll topic will be, now the Fire on Page Load default No campaign is over ;p

ps - more 2015 survey results to come. The next question was on instrumentation, so a few juicy points to make there.