Monday, 18 June 2018

Free Oracle Learning Tools

This post is one of a series on what I learned while not at Kscope18.

Would you like to learn something from the Oracle technology stack?
Here's a slide probably in a bunch of Oracle employee decks.


I think this collection represents the commitment Oracle is making to the developer community, in part thanks to the developer advocates team. Upon seeing these listed together, and considering the amount of work done to keep raising the bar, I've learned Oracle really are serious about engaging with the greater community.

  1. quicksql.oracle.com - one of the packaged apps given it's own URL. Construct your DDL/DML really fast, if you're happy to learn more markdown syntax.
  2. livesql.oracle.com - it's ridiculously easy to start playing with the Oracle product at this site. Recently I wanted to check some behaviour with a JSON query in the database version above what we had available, and I was able to confirm a database upgrade would solve the problem we had.
  3. devgym.oracle.com - I spend 5-10 minutes a week playing 3 quizzes, targeting PL/SQL, SQL, and database design. Sometimes I learn something new, something re-enforce something I'm familiar with, something I'd forgotten, or something interesting the database can do but I might never use.
  4. apex.oracle.com - surely we've all been here, playing with the most up-to-date release of the best feature of the a great database.
  5. asktom.oracle.com - I learned all my best habits from here, and highly recommend anyone ask a question. Especially anyone learning (hint - that's all of us). It's also a great place to learn how to ask a question.

These are all included in the APEX shortcuts page, along with another learning tool that could fill that 6th spot (besides 18c XE) - the forum. Here I do what I used to do at AskTom - read anything comes through; start listening to topics I need/want to learn about; and here I also get to contribute where/when I can add some of my knowledge & experience. Or if I have a bookmark to a solution someone previously documented.

At the forum you'll find a collection of volunteers willing to help you with your APEX/Oracle questions. Remember, "the #orclapex community is like no other".

ps - we could probably also add the Oracle Learning Library.

Thursday, 14 June 2018

Community Recognition at Kscope18

This post is one of a series on what I learned while not at Kscope18.

Some well deserved people have been recognised by the Oracle ACE program. One in particular I noticed was Daniel, creator of many practical APEX plugins.


Other mentions include, but not limited to, Adrian Png, Maxime Tremblay, Kai Donato, Moritz Klein, Becky Wagner, Eugene Fedorenko, Opal Alapat.

People like these really help keep our community strong, as Monty says,
"The #orclapex community is like no other." 

And Juergen Schuster, createor of apex.world, received special recognition from the APEX community.


It was a pleasure to meet this man, and I really need to catch up on his APEX podcast.

To everyone, a very Australian good onya!

The faces of Oracle APEX

This post is one of a series on what I learned while not at Kscope18.

The faces of Oracle APEX.
Well, some of them, at least. There are more listed in this picture, but it's harder to see. The one of Shakeeb comes across a lot better on his Twitter profile.

I've been lucky enough to meet many of these people, but here they are. If you see them in person, feel free to thank them.

Across the top, with my impression of their major facet of contribution:
Marc Sewtz - various integrations
David Peake - Aussie ex-pat bringing some Forms touch to APEX
Anthony Rainer - IR/IG/Accessibility, and various things JavaScript

Alon the bottom:
Joel Kallman - PM and all round nice guy
Carsten Czarki - tamer of web services using SQL
Hilary Farrell - hard to get a face to that name online, but you may have thanked her Charts demo app.
Shakeeb Rahman - all things UT and design.
Patrick Wolf - I believe we can thank him for Page Designer. Probably had a big hand in plugins, too.

Seriously, what a product!

#letswreckthistogether

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.


Which means this
date '2018-06-10'

Is the same as
to_char('10-Jun-2018','DD-MON-YYYY')

And you'll never want to type the latter again.

I learned this little chestnut as a trainer of SQL, but what I didn't pick up, or have since forgotten, is this

timestamp '2018-06-10 14:33:41'

Thanks again, Connor, for adding to this thread.

ANSI dates
Here's some more info on ANSI dates. It's not lazy, Tanel, it's efficient ;p

Learning is a lifetime pursuit.

Oracle XE 18c and #OracleRAD

This post is one of a series on what I learned while not at Kscope18.

Oracle XE (Express Edition) 18c - which is more than just a free, limited db.
Combined with the notion of #OracleRAD, you've got a mongo killer ;p

#oraclerad

Quite a few people had a shot of the XE 18c feature list.


Impressive as that is, when you start putting it into context of the sort of things that can now be achieved with the technology available today, it's mind boggling.
And Connor knows just how to amaze us the potential of this technology, making amazing concepts sound just within reach.


But I think the best way to think about it is how this mini-stack adds up.


We've got a free Database; with a middle tier that can handle all our web services needs (REST), while still using SQL; and APEX in the middle, giving us access to pretty much every capability the web offers today, where the productive IDE lives within the browser. Simply, wow.

I'd like to end this post with this tweet I saw just before Kscope18 began


This XE 18c will be magnitudes more advanced than XE 11g, and I think I'm understanding the true potential of this. I can't wait to find a stream of Mike Hichwa expressing his passion for this release.

Things I learned while not at Kscope18

Yep, as much as I wanted to be at Kscope18 this year, my abstracts were not accepted.
And it's a long way.
And I'm still waiting for this.

So this is a small collection of stuff I learnt only from Twitter - just by keeping an eye on the #kscope18 hashtag. Anyone can do his, even if you're not on twitter. Try that link and see.

#oracleRAD

Seriously, Twitter is an effective tool in keeping up with the Oracle APEX product. Plenty of ACEs on Twitter, all dishing out interesting infomation. If you don't like consuming it direct, apex.world has a great filtered feed.

Some of these short posts may look like those horrible "articles" which are just a collection of tweets with a little commentary, where they still call it journalism.

These next few posts are different to that, honest ;p


And I'd love for anyone who was actually at the event to extend/correct information I've interpreted.
It's still going on, too. We're in the relative future in Australia, so I'm still seeing tweets come through as I write this. And I've been concentrating on #orclapex.

If you still want to just see some sessions, some are already streamable, and I understand many more will be available at some point for members on the ODTUG website, just like previous years.

It seems last year I had similar inspiration, and I saw this tweet a few days before Kscope began.

Tuesday, 15 May 2018

Transposing data using UNPIVOT

A couple of years ago I posted a method to remove nulls from a report using the Value Attribute Paris - Column template.

Here's an example of how we might utilise the region, within the breadcrumb region position.

Note - some values may have been adjusted from this screenshot for their protection.

Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or on load of the page

Cool idea, can be improved.

And when the JavaScript was placed on one line, it seems so innocuous. It works, so what? What's the harm?
$('dd.t-AVPList-value').each(function(){if ($(this).text().indexOf('~')>0) $(this).hide().prev().hide()})

The trouble with my original method is that it's executing jQuery after the page is rendered. This represents extra work that could be eliminated. The same justification was present in Oracle Forms, as Post-Query triggers were not preferred.

And of course the applies to DML being applied to the database. Sure, you may need some conditional processing and apply a zillion single updates, or you could write some elegant SQL to do it within one update.

The other problem is that the Universal Theme responds to this change with some content movement that can frustrate the user - the body bubbles up to meet the removed content.

New and Improved Solution

Side-note: how can it be new and improved?

In this case I've been talking about columns from a relative simple query, as simple as selecting from scott.emp.

If we can transpose these results, like a magic wand you can do in Excel, then we could just use the Value Attribute Pairs - Row version of the template, and not worry about any jQuery that manipulates the page after it's rendered.

We tranpose columns by surrounding the existing query with a simple UNPIVOT.

select * from (
    select to_char(empno) empno
     ,ename
     ,job
     ,to_char(sal) sal
     ,to_char(comm) comm
    from scott.emp
    where empno = 7788
    --where empno = 7654
) 
unpivot 
(val
 for name in (
    (empno) as 'Emp No'
   ,(ename) as 'Name'
   ,(job) as 'Job'
   ,(sal) as 'Sal'
   ,(comm) as 'Commission'
 )
);

NAME       VAL                                     
---------- --------
Emp No     7788                                    
Name       SCOTT                                   
Job        ANALYST                                
Sal        3000   

4 rows selected.

... query executed with other empno

NAME       VAL                                     
---------- ---------
Emp No     7654                                    
Name       MARTIN                                  
Job        SALESMAN                                
Sal        1250                                    
Commission 1400

5 rows selected.

Notice the result using an emp with a commission shows more rows. The page will only render the data supplied so there no dynamic action required.

Default ordering seems to honour the order of elements in the FOR expression.
Datatypes of columns must match up, hence the to_char around the numeric columns.

Update - I saw Mike on the forums suggest that "transpose" implies a matrix, and offered a combined unpivot/pivot.

tl;dr steps

  • surround the existing query with unpivot
  • add any datatype conversions necessary to make columns the same
  • change report region from pairs with column to pairs with row
  • remove declarative column ordering
  • remove the dynamic action that finds the tildes

If you want to modify how something presents itself on an APEX page, there are other options to explore before jQuery
  • Template options
  • Conditional SQL, manifesting as HTML expression in a column
  • CSS solutions trump JavaScript.
Simplify, man.