Friday, 8 July 2016

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, syntax pun = intended.

What feature should I know, to be productive & efficient?
1. analytics
2. scalar subqueries
2. with clause

What fundamental principles should I understand?
(Stew Ashton)
1. DDL: data design is key to good SQL
3. Bind variables

What frequent mechanics/behaviour should I be consistently aware of?
1. null behaviour
2. date arithmetic
3. data type comparison issues, for lack of better desc

All the while remembering architecture is so important.
(Dani Schnider)
1. SQL is a set-based language
2. Clean data model is important
3. Constraints are not just for fun

and so is performance
(Draft A Center? Or Kyle Hailey posting from wrong account?)
1, Bitmap Indexes
2. Reading Explain Plans
3. SQL Tracing

Why there are often exceptions to rules:
(That Jeff Smith)
4. getting results back doesn't mean your code is right.
5. learning never stops

For the creative out there.
(David Fitzjarrell)
6. There is usually more than one way to write a query.
7. Sometimes you need to think 'different'

And there's always the "comedian".
(Connor McDonald)
select, from, where :-)

That's quite the collection!

Wednesday, 6 July 2016

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.

A number of editors were suggested in Other, including two takers for Notepad. All I can say is wow, and I hope for the sake of others that tab characters are not being saved in your files.

I've been happily using Textpad almost since I started coding. I'm interested in making the jump to a non OS specific editor like Sublime, or perhaps Atom, but I'm not there yet. Though after watching the Kscope16 deep dive / montage (as a livestream), I want to try soon!

Monday, 4 July 2016


Saddened but not surprised to see COALESCE lagging behind NVL.

Why? Because I think coalesce is a good idea and the modern equivalent of NVL.

I follow @SQLDaily for useful tips. Oracle SQL evangelist Chris Saxon runs the feed.

Sunday, 26 June 2016

Have fun #KScope16

Full and utter credit goes to the gang at CommitStrip, you should check it out.

I thought a few choice replacements would help Joel Kallman and team rip into what I'm sure will be another stellar set of Sunday Symposium in Chicago.

Credit to CommitStrip, text modified from original

We'll be listening around the world, keep your tweet on.

Tuesday, 21 June 2016

Hide nulls in Value Attribute Pairs report

If you have one record where you want to display multiple columns of information, the 'Value Attribute Pairs - column' report template is pretty nifty.

Some of the packaged applications use this within the breadcrumb bar, above a region display selector, and it looks really tidy.

Nulls shown with tilde

Note, I've modified region attribute setting 'Show null values as' to a tilde (~).

But what if I wanted to hide those null values for Mgr & Comm, similar to the 'show nulls' option within single row view of Interactive Reports?

Create an 'after refresh' dynamic action on the region. This is the default when doing so via the Page Designer.
// for each value cell found on the page, determine if contents = ~, then hide row if true
$('dd.t-AVPList-value').each(function() {
  if ($(this).text().indexOf('~') > 0)
Then refresh your page. Done.

Nulls hidden
And if you have another event that refreshes the region, the JavaScript will re-apply and hide any nulls (represented as a tilde - something to find and hide).

The dd.t-AVPList-value is a selector for the Universal Theme. It uses a different class in other themes, so you would have to investigate using Inspect Element browser tool to check.

Right-Click -> Inspect Element

Thinking with my jQuery hat.

Wednesday, 15 June 2016

Charting Predictions, al a AskTom

The 'grand algorithm' favoured this particular tweet from Connor McDonald in my 'highlights'.
I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read the relevant documentation.

Trouble is, the final result isn't the sort of thing you want to print out on your dot matrix printer to show the big cheese, right? You want to graph that sucker.

So I create a page in Oracle APEX 5.1 ( and created a line chart.

I split the results into two series using Connor's query within an inline view, in part because that was the fastest way I could think to get the graph plotted
select dte
 ,sz as actual
from (
with reg as (
    select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
           regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
           max(dte) nxt
  from t
  select t.*, 'actual' tag
  from   t
  union all
  select nxt+r dte,
  from reg,
       ( select rownum r
         from   dual
         connect by level <= 30 )
  order by 1
where tag ='actual'
The result was compelling.

A picture paints a thousand words

I also like the fact the declarative series name honoured, rather than a double quoted "Column Alias".

It will be interesting to see how this plots more varied data, considering the data variance issues Connor described.

I added a to_char(dte, 'DD Mon YYYY') to get a prettier tooltip, but interestingly, it changed the angle of the line.

TO_CHAR() added

Then I spotted this field here, I could use the query as supplied by Connor without having to think about pivoting the result to get it into the right format the engine requires.

A new declarative option

However the result didn't come out quite right. I think that's more an issue with the communication between APEX and OracleJET that anything else, ie: a bug.

Not quite right

It appears the declarative charting options have been engineered in 5.1 to reduce the amount of hoop jumping with the result set. Charting in general appears more declarative, easier to navigate and work out what's going on.

Nice work APEX team.

Tuesday, 7 June 2016

Synchronous Dynamic Actions in APEX 5.1

If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.

Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.

Consider this scenario of dynamic actions on change of P42_ITEM:

Synchronous vs Asynchronous server calls
First JavaScript takes value of P42_ITEM, concatenates a letter and places result in another field. Let's say I entered "X":
$s('P42_NEW2', $v('P42_ITEM')+'a');
The value in P42_NEW2 would be "Xa"

The PL/SQL code concatenates another letter, passing current value of P42_NEW in and out of session state via page items to submit/return:
:P42_NEW2 := :P42_NEW2||'b';
The value will now be "Xab"

The second JavaScript concatenates another letter:
$s('P42_NEW2', $v('P42_NEW2')+'c');

On page load the value of P42_NEW2 would be "ac", since both JavaScript actions default to 'Fire on page load'.

If 'Wait for Result' on the PL/SQL action is 'yes' (default), then the communication will be synchronous and the JavaScript will only execute once the PL/SQL is finished. The final value would be "Xabc"

If the PL/SQL action 'Wait for Result' is set to 'No', then the subsequent JavaScript will execute immediately, not, um, waiting for a result. The value of P42_NEW2 would briefly be "Xa", then just "c" until the PL/SQL returns with "Xab".

Asynchronous DA result

Think about that. This impacts how you structure dynamic actions, pose questions to the user, and steer application workflow.

In a tweet by John Snyders, who has posted some amazingly detailed posts on the inner workings of APEX, he suggested we can soon forget there was any other way.

I understood this to infer that synchronous actions would disappear. That screenshot is from 5.1 EA1, so it seems they're hanging around for now. It could be considered a backwards compatability thing, though it's still the default option. I'd say many applications would not work as expected should this behaviour disappear.

Synchronous activity on a website is usually non-preferrable for good UX, I understand that's inciting the change in the nature of the inherent browser behaviour (ie: the warning above). As far as I understand it, the way the APEX team has mitigated this warning is by modifying the JavaScript call to the PL/SQL callback as follows:
  ("CB_AJAX" // name of AJAX callback
  ,{x01       : $v('P42_ITEM')}
  ,{async: false // deprecated synchronous option
   ,success: function(pData) {
     // deprecated wait for result
     $s('P42_NEW2', pData + 'c');
).done(function(pData) {
  // 5.1 wait for result
  // ...
Well, this is the jQuery method for deferred callbacks. In future this will be done natively as promises. I've previously detailed some sample code on these variations.

So the end result to the developer appears to be very little, though I'm sure there were plenty of tweaks in the engine to make this happen. Though it does protect applications from when browsers do decide to no longer support the async parameter.

Edit: The end result for the user, as per John's comment, is the browser will not be locked up whilst waiting for the result. This behaviour probably will result in some behaviour differences in some applications, time will tell. Be on the lookout for it.

Actions are no longer synchronous, but you can still have JavaScript execute only after the PL/SQL finished.