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.

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?
(me)
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
2. ACID
3. Bind variables

What frequent mechanics/behaviour should I be consistently aware of?
(me)
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

COALESCE vs NVL poll

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.
http://www.commitstrip.com/en/2016/06/20/coder-bingo/

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)
    $(this).hide().prev().hide();
});
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 (apexea.oracle.com) 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,
         trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
         ,'predicted'
  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.