Tuesday, 29 November 2016

Answer with SQL: How many weekdays a year?

I'm a big fan of generating data with dual, using a perk of the connect by syntax.

I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.

Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple select over this to aggregate my result
with years as (
   select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
   from dual
   connect by level <= 365
select count(*)
      ,sum(case when dy in ('sat','sun') then 1 end) weekends
from years
You can see this demonstrated at livesql.oracle.com
Never heard of it? I recommend you have a play. I've only dabbled myself, but if you don't have an environment to experiment with, this is free!

Thursday, 24 November 2016

Ordering & Formatting Date Columns in APEX

Quite often I'll find I'd like to display a date with the time in a column within an APEX report, but you end up wondering how to control the wrap.

This is one problem that probably has a half dozen solutions, but I think this is the cleanest. And blogging about it helps me remember next time.

Depending on your screen size, you might be faced with something that takes up too much space per row.

not a good wrap

One solution is to turn that column into a formatted character string
,to_char(created_date,'DD-MON-YYYY"<br>"HH24:MI:SS') created_date
Note the HTML break tag surrounded by double quotes, this will put the time on the next line.

Combine this with styling on the column to stop wrapping.
Column setting CSS Style: white-space:nowrap
And you'll get a neater output.

If column is varchar, won't order nice

But there's a problem. And it's a deal breaker if you want to allow the user to order the column.
Your output will be ordered Apr, Aug, Dec, Feb, Jan... sense a trend? Alphabetical, not chronological.
Applying formatting within SQL is something you generally want to avoid.

Instead, just use the funky date format mask and apply it in a declarative manner within the column attributes, along with the CSS Style. This will allow the report to honour the appropriate ordering on the date columns, whatever format mask you require.

Declarative column attributes, using DD-MON-YYYY "<br>"HH24:MI:SS

Either way you need to set "Escape special characters" to No. I think your individual date values will be safe from cross site scripting.

These settings, along with the other Column Formatting options are used frequently in my applications, particularly HTML Expression. Just remember, if you use style frequently, define a class in your page/application/theme CSS definition and use that instead.
.date_fmt {white-space:nowrap;}


Thursday, 17 November 2016

AUSOUG Connect 2016 Presentations

Another conference series over and many new lessons are still churning around somewhere in the back of my brain.

Our Australian conferences still seem a little subdued compared to years gone by, but the thicker community bonds held strong while the economy decides what to do.

The other problem we have is getting all those people out there we know are using Oracle technology out from their cubicles and at these fun events!

My Presentations

I did 2 and a half this year, and I have plenty more ideas for next year. Some of which got cut out of my 5.1 Charts session, which really didn't end up what I intended it to be. In part because APEX 5.1 is still in early adopter, though I hear that there will be no EA3 and it will head straight into apex.oracle.com as UAT. Just not sure when...

Mastering Dynamic Actions - demonstrating 3 useful patterns I use daily. Certainly my favourite topic at the moment.

APEX 5.1 Charts with OracleJET - more to come on this topic, and I think I'm going to find myself digging into JET over the summer.

The Perfect Trigger - this is the 'half', well, only 10 minutes. I shared a slot with Penny & Ray who talked about some of their favourite things.
Bonus slide at the end for the Melbourne crowd where I just mentioned some sites people might like but may have missed.

Conference notes

Here are some thoughts from my scratchy notes, included here to help reiterate my learning, and some of you might find something interesting.

Basheer Khan described "Extensibility" as a required feature of a good UX. APEX has that nailed, affirming some of what I was going to say about plugins in my OracleJET session.

Basheer' design philosphy for mobile was: glance, scan, commit. Which translated into workflows of only 3 levels deep. Glancing at choices, scanning results, commiting to action. I think we do a decent job of that already, and so does the documentation. I always feel only 3 clicks away from the information I need.

The final thing from Basheer I have to look up is: UX RDK.

This is not me. I'd never play for Melbourne...
Mark Lancaster gave me something that may help the penny drop with an issue regarding tabular forms, not that I use them often. I'm certainly looking forward to learning all about Interactive Grids.

Connor smashed out some good feels about 12.2 where it seems some good features that weren't quite finished get the treatment they need. Well, almost.

LISTAGG now has features to elegantly handle problems the concatenated string becomes large, but there is still no 'distinct' option.

Validate_conversion sounds like a very useful datatype validation device, but it might has well behave like LNNVL.
Column level collation sounded interesting, but I didn't make enough notes. Connor speaks pretty fast. Case insensitive columns will solve a few issues, but come with caveats.

I love the look of the deprecated pragma. PL/SQL warnings in general I would like to revisit.

External table alterations on the fly complete the picture for the true flexibility of external tables. And doing things on the fly seems to be a bit of a trend with 12.2

JSON generation is a biggy, not just features for reading. It's a shame the ability to read and write came out at the same time, but again, it completes the picture in regard to the JSON lifecycle.

So many other goodies to come in 12.2, including approx_count_distinct() which works in a very interesting manner to help make histogram analysis quicker on large data.

Analytical Views seem like a massive feature attempting to solve the problems with rollup and grouping, but I wonder if it will be a another spruce goose.

Melbourne was also a blast. Trent Schafer showed me how much I really don't know about APEX administration, and Ubuntu for that matter. I've really stalled in my use, and speculating whether I made the right decision. Let's see how I go with my exploration of Atom.

Lino's session on Stripe was a good demonstration on how web services make APEX applications become easily extensible. It also gave me another presentation idea.

And all this is a drop in the ocean to what I hear is coming out of some European conferences right now. Information overload. I feel like those people who know an awful lot about one thing and do it really well... an 'expert', right? One who knows a lot about a little.


Saturday, 5 November 2016

APEX 5.1 Chart Column Mapping

If you've played with the D3 chart plugins you can find in the Sample Charts Packaged Application, you may have noticed the ability to nominate a column from your query as the series name.

APEX 5.1 Column Mapping

APEX 5.1 provides this built into the framework. Combined with some rather granular attribute control at series and axis levels, I think it will be harder to find tweaks that haven't been made declarative.

And if you do, there is a section in the chart attributes for JavaScript code. This also means if you do need to start hacking your chart, you're just adding to the existing attributes.

OracleJET Custom JavaScript

Compare this with the XML API for AnyChart 6.x where once you start using XML, say goodbye to most of your declarative attribute settings.

The ability to nominate a series column is optional, bringing more flexibilty to the SQL that no longer needs to conform to positional columns. I'm not sure yet if this will impact how often I use pivot/unpivot, but I'm sure it will help.

This post was brought to you by the result of last minute presentation screenshot prep and a prompt from this tweet, so thank Peter.

Tuesday, 1 November 2016

Build Options on Report Columns

Let's say you're experimenting with a report (IR or classic) column and you would only like it to appear in you development environment.

What options do you have for hiding that column in other environments?

  1. Don't migrate the change - use your source control processes to only move it when it's ready. This isn't always practical, nor the intent, particularly since you can programatically set the status.

  2. Add server condition on database name - we can use an expression such as
    sys_context('userenv','db_name') = 'dev'This isn't bad, I have a common function I call for this sort of thing. But it's quite granular.

  3. Build Options - under-utilised silent catalyst that can help us turn a group features off/on at the flick of a switch.
In Oracle APEX 5.1, Build Options are an attribute of each column, in addition to just about every other component in the builder.

APEX 5.1 Column Attributes

Prior to APEX 5.1, column definitions were one of few components that don't have a declarative option. However, we still have the magic of dictionary views.

Pre 5.1 Build Option on Column
So we can query the application's build options and determine if the relevant record is switched on.
select null
from apex_application_build_options
where build_option_name like 'My build option'
and build_option_status = 'Include'
and application_id = :APP_ID
Again, you could put this in a re-usable function/view, not that you would need it often.


Friday, 28 October 2016

APEX Survey Results: Instrumentation Thoughts

This question in my 2015 survey is related to question 7 regarding instrumentation.

Q8: Do you have any thoughts you'd like to share on instrumentation?

I left this open for people to add a free-text response. Here are some worth highlighting, though it was hard to choose since so many people had something to add.

a) How would it help me?

Have a read of this post to get an idea of how the debug output can help solve problems.

b) It's not optional

I agree, it should be baked into your code.

c) An application is not just building, but also managing after deployment. To reduce the cost of the second fase (sic) instrumentation is a must

Another perspective on what instrumentation is all about.

d) Nobody instruments enough ....

Well, I think you could probably have too many logs.

e) Not enough planning goes into instrumentation in general.

Planning is an interesting point. While we could plaster our code with debug logs, this isn't necessarily going to help debugging your code, particularly when you need to further sort the wheat from the chaff.

Once you've decided on a framework, start thinking about standard patterns such as noting start/end of procedures; incoming/outgoing parameters; the varied level of detail you may need; how you can turn it on/off; how errors are highlighted and treated.

Any other factors you think are worth mentioning in regard to planning?

f) Please make Debugging more smoother and documented. An idea can be to create a packaged application to showcase debugging.

Something for the APEX development team to consider.

g) Instrumentation should point out the exact place where error is occured (sic). It should not be overhead for the APEX application and there should be a switch to turn it on/off.

Back in APEX 3.x, debugging was displayed where relevant, within the rendered page itself. It looked awkward and was hard to decipher. Any code is overhead, but the risk/benefit equation outweighs the cost. APEX debugging can be turned on/off, and some logging libraries provide options for granular logging.

h) ... Also debugging apex_collections is somehow hard today ...

Constructs relating specifically to a particular session (such as collections) can be tricky to detail, but with careful planning you should be OK. It may be useful to output a collection count at key locations. You could have a little library that looped and logged key columns from the collection. And the Session link in the Developer Toolbar allows you to see collection contents naturally.

i) I wish I'd known about or used some of the instrumentation methods above long ago when I built my first Apex apps.

I think that comment right there should be a warning to all those who haven't started yet.

j) The CREATE ANY CONTEXT privilege requirement in Logger is a pain.

Sounds worth airing, but I'm pretty sure the benefits outweigh whatever pain that might be.

k) asdfasdfasfd

It seems even cats have opinions on the topic.

l) do it! use whatever tool fits your needs, but use something!

I'll finish with that one.

Does anyone recognise their comment? I didn't want to name anyone without asking.

Thursday, 13 October 2016

Connect2016 - Australian Conference Series

It's less than a month away from the Australian conference series and I'm probably about 2 thirds ready for my sessions.

Australia? Yup, the Perth gig will be on 7-8 Nov and the fun continues in Melbourne on 10-11 Nov 2016.

Check out www.ausoug.org.au/connect2016, then think about that junket. Jokes aside, events like these are the best place to chat to people about Oracle technology, with plenty of sessions to allow you to stay sane while considering what challenges others are facing. I know times are hard but the price is reasonable, and there is more to just the program.

Sessions I've got my eye on include:

"Evolving APEX Development Practices" - Mark Lancaster, title alone grabs me.

"User Experience (UX): Building Usable Applications – Why and How" - Basheer Khan, as an APEX developer I'm finding UX very interesting, when I can indulge.

"APEX, RESTful Services and STRIPE" - Lino Schildenfeld, I have plenty to learn about web services.
Lino is also doing "APEX Plugins - World of possibilities", and I'm keen to hear his perspective on this.

"Transition to Cloud - Should we or shouldn't we?"" - Howard Ong, I have a feeling I should listen to this.

"Next Generation Databases" - Guy Harrison, I know this one will be infotainment goodness.

And Connor McDonald will treat us with some 12.2 goodness, and my director has one on BI Visualisations, lessons direct from a recent project.

I have the following sessions:
"Mastering Dynamic Actions" - ready!
"APEX 5.1 Charts with OracleJET" - researched... mostly.

In Perth I'll be joining other Sage colleagues called "Our Kitchen Rules" and I'll have 10 minutes to cover a topic of my choosing - ready!

I have another couple of ideas ready for next year, too.

I'll be on both legs so it will be particularly awesome to see everyone on the east coast.