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
https://livesql.oracle.com/apex/livesql/file/content_D6ZWKMK4O2IVMWGOAXIDEP61M.html
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!

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;}

#letswreckthistogether

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.
https://prezi.com/lko3dxsbzr3i/mastering-dynamic-actions/

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.

https://prezi.com/misgwk4trqhc/apex-51-charts-with-oraclejet/

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.
https://prezi.com/g0cew4clllzk/the-perfect-trigger/
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.

Scott.

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.

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.

Neat.