Calling PL/SQL from JavaScript in APEX

When I first wrote Chapter 9 of my book, Choosing Processing Options, I knew I still had a lot of research and experimentation to do, particularly regarding the async parameter. Below is a summary of the outcome of this work and drove a full revision of my original chapter.

Last week I received a printed copy of my book and noticed that unfortunately the first submission of chapter 9 made it into print. Apress are looking into this for me, but please consider the concepts in this post refactored into what you read. It all relates to the following message you may have noticed in your browser console.

The following post is slightly modified from an email conversation considering workflow from a browser based system. There was a more detailed discussion in a forum post that I'd need to dig up.

Imagine a button on a screen to add a record that may already exist. How do you interact with the user?

--***
On 8 October 2015 at 09:05, Scott Wesley wrote:

Hi gang,

I've played with this, thought about it, and I think we're already on a good wicket with another pattern.

Option A)

On click btn ->
PL/SQL: try insert, set result to page item (eg: P0_SIGNAL)
Dynamic action on P0_SIGNAL ->
Notification: Display message based on item.

We need to shake it up a little to provide different colour message - success/failure etc.

Tom Petrus suggested a more elegant alternative (with one less step and/or didn't involve a page item?), but it was in transit time at kscope and it was a hard topic to explore without code. In revising this post, I think perhaps he set the message to some JavaScript container, which in turn is sent to the database using APP_AJAX_X01.

Option B)

On click -> test javascript condition, which actually calls a synchronous function to test existence in database
True action - PL/SQL to insert
False action - alert user

This is what we were attempting this afternoon, and sounded good in my head but there are a few things wrong.

1. We're calling the db twice, when we just need to do it once. We're in an optimistic world now: try it, work it out once it's done.
2. The web isn't built for it, and the specifications are making it harder for us to do so. A common sentiment on from MDN.

Below are the examples we attempted for the JavaScript to return true or false, hence triggering the appropriate true/false dynamic actions. Function b() represents better practice. CB_AJAX is an AJAX callback (PL/SQL) defined on the page and returns a number as a text string.

This works, is completely synchronous, but will only work in browsers for a finite time thanks to the highlighted row (async:false).

function a() {
console.log('a');
apex.server.process
  ("CB_AJAX"
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"
   ,async:false
   ,success:function(pData) {
       console.log('Result a:'+pData);
       console.log('success:'+ (result.length));
       result = pData;
     }
   }
);
return result.length == 2;
}

The next example uses 'deferred objects', which is jQuery's updated method for handling "success", but we can't access the callback values outside that scope, so we can't return true or false. The 'callback' is the done() section.

"javascript return result from callback" is an obscenely googled phrase, with a fair response of: "This is impossible as you cannot use an asynchronous call inside a synchronous method."
If that hurts your head a little, welcome to JavaScript mechanics.

function b() {
console.log('b');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).done(function(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
});
// anything here will run before plsql finished
}

Eddie suggested using "promises", which sounded promising. ha.
But they are just the native implementation of deferred objects. And extremely new, unsupported by even IE11.
I also think this quote is telling: "This is extremely useful for async success/failure, because you're less interested in the exact time something became available, and more interested in reacting to the outcome."

function c() {
  console.log('c');
  return new Promise(function(resolve, reject) {
    var jqxhr = apex.server.process
 ("CB_AJAX" // this is just => htp.p('X');
 ,{pageItems : '#P9_EMPNO'}
 ,{dataType:"text"}
).done(resolve);
  });
}

c().then(function(result) {
  // code depending on result
  console.log('.then');
  console.log(result);
  return result.length == 2;
}).catch(function() {
  // an error occurred
  console.log('error');
});

Then I tried letting functions accept callbacks, which really defines all logical sense I learnt in programming 101. If this doesn't screw your brain, google "doSynchronousLoop.js". I'm sure it's 21st century spaghetti code, with the added dimension of time.

function myCallback(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
}
function d(callback) { // d
console.log('d');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).done(callback);
// anything here will run before plsql finished
}
d(myCallback);

So I think the conclusion to be drawn out of all this is that Tom Kyte is right, and always has been. Run code, if it raises an exception, let out propagate, politely let the user know; otherwise success and move on.

Anything else and you are going against the grain.

APEX provides the ability to do this declaratively with dynamic actions, stick with option A, and we'll improve the mechanism in future to incorporate a library to set/read P0_SIGNAL with a JSON string that will get converted into signal type/message and invoke Alertify as appropriate, as opposed to setting it with a single value as currently done in <redacted>.

Edit: If you've ready this post, you should also about async wait by by Vincent.

References

• http://stackoverflow.com/questions/6847697/how-to-return-value-from-an-asynchronous-callback-function
• http://www.html5rocks.com/en/tutorials/es6/promises/
• http://caniuse.com/#search=promises

Tutorial: Include action button in report

In reality, this 'add' button could represent any action you would like in a report that would execute PL/SQL upon press of a row level button.

In this example I click on a button in a report to add the row to collection, without submitting the page.

Prepare page

Add Static id to your report region:
p2_my_report

Add hidden page item: P2_ADD
Set protected = No if you need to submit the page for other processes.

Create before header process to initialise collection
APEX_COLLECTION.TRUNCATE_COLLECTION(p_collection_name => 'EMPS');

Add link column

Define a new column in your report that will serve as the link button, it only returns null. For classic reports you could create a virtual column for the purposes of a link.

My report on emp uses this SQL
select e.*, null add_btn from emp e

Modify the new column and set the column type to 'Link'.

Set URL to: javascript:void(0);
Link text: Add
Link Attributes: data-id="#EMPNO#" class="add t-Button t-Button--warning t-Button--simple t-Button--stretch"

The data tag creates an attribute that we can interrogate easily with jQuery, returning the ID of the record.
The classes represent the same classes that would be applied when choosing relevant template options. You can explore these with the UT button builder.
The 'add' class is added for our dynamic action.

Create dynamic action

Create a new dynamic action that will respond to button press, using on click of a jQuery selector.

Use the following selector to identify add button clicks on your report.
#p2_my_report .add

Add an action to execute the following JavaScript. It sets the page item with the value of the ID set in the data tag.
$s('P2_ADD', $(this.triggeringElement).data('id'));

Don't forget to set this to not Fire on Page Load. this.triggeringElement represents the button pressed, which is generated as the following HTML.

<a href="javascript:void(0);" 
   data-id="7900" 
   class="add t-Button t-Button--warning t-Button--simple t-Button--stretch">Add</a>

Other JavaScript options

If other information was required, you could define more data tags, or traverse the DOM to find other values in the row. For IR you would need to first define static ID for the column as SAL. Classic reports automatically use the column alias.
$(this.triggeringElement).closest('tr').find('td[headers=SAL]').text()

If you were defining a remove function, then a second statement could be added to immediately hide the row from view without needing to refresh the report by locating the surrounding tr tag.
$(this.triggeringElement).closest('tr').hide();
Though this may make pagination feel a little strange, as the number of rows displayed won't add up.

Or you could add a declarative true action to hide the triggering element, so the button can't be double clicked by a frustrated user.

Partial Page Refresh

May 2017 - I've added this section in response to a reader comment. I can't believe I neglected this property in the first place.

You'll find a problem if the report is refreshed due to a range of actions such re-sorting, applying filter, or perhaps invoked as a refresh action in yet another dynamic action - the on click dynamic action on our action button no longer works!

This is easily adjusted using the Event Scope property for the on click dynamic action, using the 'Dynamic' option (formerly 'Live').

Set Dynamic Action Event Scope to Dynamic

This maps to a jQuery setting that, as the item help describes:

Binds the event handler to the triggering element(s) for the lifetime of the current page, irrespective of any triggering elements being recreated via Partial Page Refresh (PPR).

The default option is static perhaps as the lowest common denominator, favouring speed. Most reports would have this adjusted to Dynamic.

The second property allows you to define the surrounding container of what's being refreshed, ie, the region. It's my understanding that you would include the following value.
#p2_my_report

And this would reduce the search area required to find our particular buttons, but I thought that was the purpose of supplying this as a surrounding ID/repeating class combination.

Execute the PL/SQL

You could then define a second action that executes PL/SQL, including P2_ADD in 'Page Items to Submit', so you can then refer to :P2_ADD as a bind variable in the PL/SQL. Note, you should always explicitly convert any value from session state that is not a string.

Alternatively, you could define an onChange event on P2_ADD which does the same thing. This would allow different UI on the page to invoke the same action.

The onChange dynamic action should only execute when the item is not null, and an action after the PL/SQL should clear the item. This allows the same value to be selected successively, otherwise the value wouldn't 'change' the second time around.
In the screenshot & example below I also refresh the region containing the collection.

Outcome

So that describes a pattern I use frequently, and some variation of which is asked on the forums all the time. I plan to extend this example to include the collection report as a modal dialog with the ability to add & remove.

Run the demo to see it in action.

If you want to explore this further, you might like my book on jQuery with APEX. </shameless-plug>

Book: Pro jQuery in Oracle APEX

The word on the street is my book is now available.

Update: The original copy mistakenly had a draft of  Chapter 9 included. If you had an early copy, please be sure to read the revised chapter online.

If you like using dynamic actions in APEX and want to learn how to use jQuery selectors effectively, this book is for you.

If you want more flexibility with your dynamic actions, and understand how handy this.triggeringElement can be, give it a read.

If you want some more ideas on how to improve the way your users interactive with your data, check it out.

Practical techniques to enhance your APEX user interface, written for APEX 5.0.

Complete acknowledgements in the book, but thank you to Alex Fatkulin for being the technical reviewer. Any mistakes you might find are on me, some chapters got some major revisions after Alex's eyes cast through them. In part thanks to the APEX 5.0 release, in part to me continuing to learn better ways to write jQuery.

Roel has already spotted a mistake, thanks mate ;p
I missed the second X when referring to built in substitution string APP_AJAX_X01.

I feel like I'm standing on the shoulders of some giants who have paved the way before me. Without the help of the broader community on the OTN forums, StackOverflow, Slack, Twitter, conferences and people sitting right next to me, such an accomplishment would not be possible. Three in particular have been vital in my learning of jQuery, thanks Tom Petrus, Jari Laine, and Jeff Eberhard.

I've reviewed a bunch of books (and things) in the past, too, if you're looking for more book selections. I bought a few in the Cyber Monday sales, including the revised, multi-author gold nugget that is Expert Oracle APEX, plus a couple on node.js.

I welcome you all to review mine on Amazon or your blog once you've had a read. Hit me on Twitter or Slack if you have any questions or commentary. I welcome your input. It was quite the experience writing this and I have all the hesitations I'm sure many before me have faced regarding it's reception. It was a rather lone project, but one undertaken with pride and enthusiasm.

I'll write a few posts over the next few weeks that might whet your appetite for some jQuery action. And no, I wouldn't rule out writing another one day!

#letswreckthistogether

Kscope ODTUG Interview

The ODTUG booth at Kscope15 had a permanent spot for interviews, and they can all be found on the ODTUG channel.

Martin interviewed me, and here it is, with embarrassing front still and all.



If you want some more Australian accent, and the sound of a local magpie, listen to Connor's little video regarding his move to Oracle. Though he's done quite a few more now since I drafted this post.

I have an idea or two for some videos from myself, finally. All just a matter of time.

Customising APEX workspace login

A few years ago Peter Raganitsch showed us how to customise the workspace login page in APEX 4.x.

I think it's even easier from APEX 5.0 (through to at least 18.2), though it looks pretty slick already.

Here is the solution I shared on Slack a few weeks ago, also on Github.

<script type="text/javascript">
$( document ).ready(function() {
  $('.a-Login-title').text("Scott's workspace").css('color','darkgreen');

 // In order of reference
 // Oracle header
 // Down arrow
 // Second page fluff
 $('.a-Login-message, .a-Header--login, .a-Login-slideNav, .a-Login-slide--secondary').css('display','none');
 // Orange message bar
 $('.a-Login-message').text('Reminder: use your windows credentials');

 // Hide reset password
 $('.a-Login-links').text('')

 // Change logo, list in /i/apex_ui/css/Core.css
 $('.a-Login-logo').addClass('gi-icon-admin-dashboards').removeClass('gi-icon-apex-logo-icon');

});
</script>

Paste this in the same location in the INTERNAL workspace, under Manage Instance -> Login message.

And voila!

Customised Workspace Login

Perhaps use this to indicate environment details.

A setting was introduced in 18.x to hide some of this content, but I'm not sure it works properly.

INTERNAL workspace instance setting

I believe some others have been tackling easy options to replace the icon.

George Hrab @TEDx Rethinking Doubt

I realise the Internet is a big place, so if you haven't heard of the TED talks then I recommend you head over and check it out.

They're limited length talks about amazing things people are doing, discovering, and innovating around the world. Ideas worth spreading, is the catch-phrase.

Many talks are about topics that are really beyond our personal purview, but today I listened to one that I think everybody should listen to, especially teenagers. The speaker, George Hrab, addresses something that everyone can apply, every day.



Thanks to one of my heroes, Phil Plait, for pointing this one out. Though considering I periodically listen to George's podcast, I would have heard his golden voice at TEDx at some point. He's also a drummer in a funk band and has released some cool science based music.

Introducing Slack to the Oracle APEX community

If you're like me, the first time you ever heard of Slack was when you checked out apex.world launch last month.

Update April 2016 - apex.world Slack Community Guidelines are now available on GitHub
https://gist.github.com/tschf/149881e7dbc60311b5cd

Since then I've signed up, had a play, interacted with people and with the support of apex.world I thought about what it could do for our community. Slack's mission is “make your working life simpler, more pleasant and more productive.” To me that sounds like something we should think about getting involved with.

What is Slack?

Slack is a relatively new site but has grown extremely fast, compiling the some of the best bits of twenty years of Internet interaction in the one offering. It's basically a highly integrated chat app built by the guy that brought us Flickr. The best article I've seen describes how it's killing e-mail, and I can really see how. It's worth the read and will help you understand why we're a little excited.

Firstly let's consider the tools we currently use:

Email

Email is a funny thing. I still email the colleagues that sit nearby. Don't get me wrong, nothing goes past a good face-to-face conversation, but we also work with code, visualisation, and our headphones on. We want to be disturbed on our own terms, so e-mail can be a convenient tool. However, an email conversation is quickly polluted with signatures, loss of context and which ones do you keep?

Forums

The OTN community is a great place to ask questions, float ideas and see what other developers are struggling with. I find it optimised for clear questions and concise responses. Anything requiring more interaction can be painful and slow as forum lag creeps in.

Twitter

Need to float an idea? Need to consume information based on a particular tag like #orclapex? Twitter's a great tool in our community and you'll find plenty of the experts and gurus use it. Tim Hall induced an interesting discussion this week on its value. Journalists and sociologists worldwide love it's instantaneous nature and insight into the human psyche; information is extremely current but transient.

One downside is that sometimes it feels rude joining an existing conversation, despite the fact you're essentially writing on a public wall. Others also like to talk about things other than Oracle and related technologies, who knew? For me it's science and pseudoscience, a side hobby. Some like this insight into the personal nature of people, but it's not for everyone.

Following conversations between others can be hard unless you're directly involved, and trying to convey technical information with 140 characters can be tough. Unless of course you transfer your discussion to somewhere like Slack, where other perspectives are naturally welcome.

ICQ

Remember this? It was fantastic for instant chat, we used to use it at university to communicate with students in other study rooms. Apparently it's till around, and I think Slack is a more advanced, more mature version of a chat room - built for engineers.

Slack

Imagine the benefits that instant messaging could give your team, one with a useful UI, one that's searchable, where you can set up a few different channels, include various forms of media, and one that integrates with a whole bunch of other frameworks. That's Slack.

What does apex.world do?

For those that aren't interested in registering with forms of social media, apex.world does a great job of aggregating information.

apex.world screenshot

The first column lists all the conversations coming out of Slack, with a drop down list for the different channels. By registering with apex.world you can get invited to the orclapex.slack.com domain.

The column on the right aggregates all messages coming from Twitter with any of the tags shown in the region heading, which are typically the cream of the crop for data mining. This differs from lists that people can make in Twitter that show any tweets by particular people, which sometimes aren't about Oracle or technology.

The middle column lists any link the apex.world team deem newsworthy. The first few in the screenshot relate to jobs, but the types of links are quite heterogeneous. The next in the feed was highlighting a particular tweet from Joel Kallman essentially confirming Oracle JET as the target charting engine in APEX 5.1.

Visiting apex.world means you don't need to be a member of Twitter nor Slack, nor do you even have to register at apex.world, but I'd like to convince you otherwise. I've previously posted about the advantages of Twitter as a work tool, and I'd like to familiarise readers with why Slack could help APEX community members.

Finally, the apex.world site is bigger than just this landing page and registering brings other benefits such as the job market.

Using Slack

Slack sidebar

I'm still a relatively new user to Slack, but Juergen tells me that makes me the prime candidate for spruiking my findings. It's certainly a new tool for the APEX community, and I'm sure there are uses we're yet to discover. Consider this a time of cultivation of a product that's already proved its worth - apex.world would not be possible without Slack due to the vast geographic separation of the development team.

Domain Header

The big "orclapex" in the top section provides a drop down with site level links of interest, including Preferences.
For instance, I've chosen a skin I like, separated private channels in the list and set emoji to text only. I also enabled desktop notifications for certain events.

Channels

The these predefined channels were devised by the apex.world team as to break up the orclapex domain into categories that hold their own weight when it comes to discussions.

If you have a question or thought regarding dynamic actions, there's a spot for you. If you need feedback on using plugins, ask a question over there. When you sign up you can opt in to channels that interest you, I've done subscribed to them all.

The channel displays bold when new content is present, as per the private apex-world channel in the screenshot. The UI does a great job at showing new information to suit your preference.

No doubt others will arise over time, perhaps more transitory channels for use in ODTUG webinars?

Private Channels

It's also possible to define private channels for your own projects, cities or languages... or who knows what in future. There are two in this screenshot, signified with the closed padlock.

We welcome non-native speaking communities to define private channels for their native tongue. First ask in the #_general channel if one has already been created in your language and you will be sent an invite since anyone who's a member can invite anyone else in slack domain.. If one doesn't already exist, we suggest create a language channel using the international format

• apex_nl
• apex_de
• apex_ru

Or city based, eg:

• apex_perth
• apex_nyc
• apex_birmingham

We hope this will reduce channel clashes and help consistency.

Direct Messages

Direct messaging is available, just like most other networking sites, but please consider standard etiquettes. Not everyone likes private messaging.

Back to a utilitarian perspective, you can define a group of people for a private conversation. New message appear bolded like the channels, with the addition of a message count.

Membership is growing quickly, so it's possible to put your friends, colleagues and favourite channels in a starred section, back up the top of the sidebar.

As a Gmail user, I find this perspective rather familiar.

Slackbot

The Slackbot helps you start out and has potential for future utility just like Siri, as you type commands into the message box prefixed with the / slash.

Command shortcuts

There are actions it can do now such as set reminders, but keen an eye out for this little AI. You can also use it as a place to test out messaging features.

Communicating

I've found the message bar rather impressive. For a start it handles me pasting an image from the clipboard, enabling me to add some meta information before posting. In addition to this common need, it's possible to upload documents, pictures, videos and does it with a smooth UI.

This screen grab shows some examples that all programmers should be interested in. Inline code, the result of talking to the AI, multi line code and a snippet example I used after being gently prompted as I started to write code. There is a more direct option under the plus button.

Markup examples

The markup options are suggested on the bottom right. If you want to include something larger than a few lines of code, check out the snippet popup. Is this tool for built programmers or what?

Add snippet

You can use the @ symbol to mention people within a message so they receive addition notification. 

This can also be useful in a larger discussion if you need to keep information in the group but focus your contribution to someone in particular, just like Twitter but with more elbow room.

https://community.oracle.com/thread/3823539

And hereby lies an example of how Slack can be effective, highlighting a nugget amongst the chaff in the #javascript channel.

Search

The search facility is a clever looking bit of UX, and for a community our size it should filter through a decent look through the past. Ten thousand messages to be exact, enough to be current.

Slack search 

Back to the UX, as you can see from the screenshot the search for "workspace" was effective. Clicking on any of these cards expands the neighbouring conversation to help with context.

For a more generic guide on what you can do in Slack and how, try
http://thenextweb.com/insider/2015/08/11/the-ultimate-guide-to-doing-anything-in-slack/

There are some awesome shortcut commands for the keyboard warriors out there. For instance, after submitting message you can press the up arrow to quickly edit a mistake. Ctrl / will display the current shortcuts.
http://www.howtogeek.com/219328/become-a-slack-power-user-with-these-useful-tips/

As good as I find the browser experience, apparently there's also dedicated applications for most device types.
https://slack.com/downloads
I've tried the mobile app and it's clean and easy.

Behaviour

The tools I've mentioned serve their niche well. For instance the OTN forums are great for technical questions, but I'm not sure Slack is the best medium for this. Slack is more of a technical discussion board than a technical answer board.

Something might arise in Slack, but as soon as it starts looking like a forum post with connection details being passed, it probably belongs in the forum. Though people on the forum might get places faster in a private discussion on Slack, timezones permitting. In this time of exploration we'll probably find more examples of the Forums and Slack complimenting each other.

Twitter can also be good to throw something to the crowd in a sentence or two, hoping to elicit a response directing to more detailed information. If the conversation gets deep, transfer it to Slack. This might encourage others to contribute to a conversation that can get lost in the quicksand that is Twitter.

I find Slack a place where a conversation can be had about current events, or asking "has anyone done this potentially esoteric thing". Or something that used to be an email discussion where some listened, others contributed, and we all got frustrated at such an unconsolidated feed.

Keep an eye out for a concise set of guidelines from the apex.world team shortly, so we all try eke the best out of a useful tool.

Looking forward

Slack is a modern tool with a lot of potential. The #orclapex community is still exploring it but I think it's effectiveness is already shining through. People have asked various questions about current events and received feedback from around the world. It's similar to Twitter, but more focussed and digestible.

As I wrote this post I asked if anyone else had experienced what I thought was a strange behaviour I encountered today. Within minutes Patrick Wolf suggested I turn Escape Special Characters off my column, something I'm sure I thought I tried but sure enough, I was on my way.

As OOW15 was going on, people who weren't at the event wanted to get involved in the converation, seeking further information announcements like as Oracle JET. Sure, people were also doing the same thing on Twitter and the OTN forum, but the tone of the discussion on Slack was more accessible. I found it encouraged more participation. Less like we're on a giant spinning planet keeping us all geographically distant, well, me anyway.

There are downsides, Juergen read about a team that outgrew Slack, where the free plan allows search/browse of the last 10k messages. That sounds like a lot until you have thousands of users. The tool will still be a useful communication device, but one will hope the future 'Enterprise' pricing plan will help larger user sets since a per person charge for even a modest group would probably price communities like ODTUG out. The orclapex domain is currently at 321 confirmed members, so with modest participation rates I think we'll be ok for now. You never know, by that stage we might have a searchable archive on apex.world.

I'm sure the greater community will work out how to use which tool most effectively and for what content. Either way, I recommend keeping in touch! Let's make this big world a little smaller.

#letswreckthistogether

ODTUG APEX Gaming competition submission - Boggex

This post announces my humble attempt for the ODTUG APEX Gaming Competition 2015.

You can play the game yourself at
https://apex.oracle.com/pls/apex/f?p=70316
The about section allows you to download the current source I have exported.

I originally wasn't going to submit anything since I was busy writing a book this year, but I got inspired by something one day and thought I could at least submit something as long as I follow two major criteria:

1. Minimal time spent to create
2. Declarative as possible

Besides some allowances made to work nicely on a mobile, I think I fit both of those fairly well. It's a fairly basic framework over a fairly basic idea.

Besides the simple but addictive game play, there are two other tabs of interest:

'Tech' begins to details some of the technical aspects of the game. I'll be converting some of this detail into a presentation, hoping to illustrate some interesting concepts.
'Stats' shows some pretty graphs that show some statistics people might find interesting. This is also the precursor to a presentation idea.

Feel free to use the Feedback option to send me thoughts, ideas and constructive criticism.

Update
- iOS users - Fixed the fact you can't touch letters by adding touchpunch.js using 'touchstart click' as custom event for click letter. Wasn't a problem with the non-Apple devices I had on hand!

Otherwise, have fun! I can't wait to see the entries from those smarter and less time poor than I. On Twitter a few weeks ago I saw some submissions from other entrants, I think the organisers are thinking 'mission accomplished'.

Scott

Permission to board, apex.world

Let's see, what happened in the last 24 hours or so?

apex.world announced, everyone feeling the awesome.

I joined slack, I must say the curiosity rover self portrait helped sway my decision.

Got involved in some geeky discussions, bounced some ideas around.

Put my hand up to help with content.

Now involved in apex.world project.

I must say, after talking with Juergen he's affirmed my first impressions of this project. It's 100% community driven, and the project group want to maintain that integrity. I think this serves a great basis to cultivate some even grander ideas for the future.

APEX 5.1 revised SOD and apex.world

Two big announcements, one awesome product.

Statement of Direction

Today I think David Peake saw the wave generated by the announcement of apex.world and decided to also announce a revised Statement of Direction (SOD) for APEX 5.1, or he could try convince me it was a coincidence.

Not long after I started blogged I decided to follow these documents to try keep a feel of what's going on. It seems there have been a few adjustments since the initial iteration of 5.1 direction.

• Interactive Grid - I'm glad they've finally settled on a name, because I didn't think "multi-row edit region type" was really gonna slide. This will no doubt be the talk of 5.1, speaking of which I think someone's been waiting to do a presentation on this for years. I think with the quality of solutions now coming out of the APEX team this will surely impress. I envisage a widget where declarative settings and built-in dynamic actions replace a lot of jQuery, plus JSON magic for communication with the database.
• Master detail detail - this has been included in the statement of direction at least since 4.1. I can't say I'm in a rush for it, but I could be swayed. 
• New Charting Engine - this is probably the most interesting announcement of all. A new JavaScript based charting engine developed by Oracle which produces responsive and accessible HTML5 charts. That's a lot to digest. First, AnyChart is on the way out. The AnyChart version was not upgraded to HTML5 enabled library in APEX 5.0, and I remember a discussion at Kscope15 suggesting it was not the library for the future. Plenty of plug-ins are being produced in and out of Oracle using the D3 reporting engine, but posit they will build their own? Perhaps using as an existing framework as a basis? A feature that may have held the AnyChart engine back was the ability to export to PDF/Image, which may extra work with a HTML5 solution, but hopefully on the list. Maybe the biggest advantage of an in-house solution is the tighter coupling to the database, I can't wait to hear more.
  Update - some information from Patrick Wolf.
• Page Designer - I didn't really understand this sentence about the integration of the component view, nor the comment about attributes moved from rendering pane to property editor. I think I need a picture to paint these words, though Christian's post describing a UKOUG event help fill some gaps.
• Improved Wizards - Wizards are like propellers, continuous refinements over time make them even better and more efficient (but we'll always have steps/blades). Keep them coming. Though have you noticed how infrequently you're using wizards now? Add a shortcut key for Duplicate, please!
• Declarative RTL support - common question in forums, bring it on for those in the world that need it. I think Universal Theme has already made steps towards this, it just needs more refinement. Hopefully we'll see even more improvements and templates with the Universal Theme itself, I've already noticed some.
• Packaged apps - over time these updates seem to migrate to being baked into the product. They're also full of design ideas, and comparable to design solutions elsewhere in the market so consider that has confirmation of the Oracle finger on the community pulse, if you will.

apex.world

Unless you're living under a social media rock, Juergen and friends in association with ODTUG announced apex.world. To summarise, it was noticed at conferences that only the minority utilised social media tools like Twitter, even though they are a great resource of information, collaboration and light hearted humour. Other tools like Slack out there are appear to be used by engineering communities (among others) for collaboration, and I'm already hooked. 

Twitter is an open platform, you don't need to be a member to read most feeds. Integration options are out there for other sites. apex.world brings this information to one site, hopefully filterable in such a way that you get what you need.

On top of this, it opens the invitation to be the new home for APEX plugins, integrating very elegantly with github. A job market has also been included in the initial release.

Plenty of ideas are flowing in for expanding it as the one-stop-shop for other repositories. I suggested including a list of sample APEX applications, something I started once upon a time and planned to continue next year, but opportunity beckons for a more fitting home.

All built on Oracle tech of course, with plenty of community help integrating a number of services. Great work gang!

update - 5.0.2 now available

#letswreckthistogether

The Oracle Development Community is Buzzing

I don't know about you, but it sure seems like a fire was lit somewhere in Oracle and now there are all sorts of things going on in the development community.

We've had some discussions about this over the water cooler and I think there are many positives we can take out of this engagement, and I think a deeper message being sent to veterans that Oracle is still up for the game against the younger competitors.

The Oracle Developer Advocates Team

I think this is one of the best initiatives to come out of Oracle for some time. They've basically recruited some of the most passionate voices in the Oracle community to help the rest of us create faster, secure, more maintainable applications.

I first noticed this with Dan McGhan running jsao.io, a site I have not been able to keep up with considering other projects this year, but what a site it is. There is a long term coupling of JavaScript, Node and APEX and I can't think of a better person to spearhead the sharing of knowledge in the community.

I'm lucky enough to live in the same city as another advocate, Connor McDonald. This actually means I work within a stone's throw, but we don't nearly enough catch-up beers. I can't say enough about his presentation ability, but his on the job passion is second to none.

Steven is obviously Mr PL/SQL, and I met Todd briefly at Kscope15. Given this pedigree, I trust the others in the group bring superb qualities.

AskTom

Tom Kyte has been running this site for a long time, just before I started my Oracle career. I'm impressed he's managed it by himself for so long, but now the reins have been handed over, and the choice of Chris Saxon and Connor for replacement is spot on.
When first started this site sure helped me form better practices in SQL and PL/SQL. I don't use it so much anymore, but I agree with the message from up high in Oracle that this site had to continue, and become more active.

LiveSQL.oracle.com

If you've encountered jsfiddle.net, then you might have also seen sqlfiddle.com. Today on Twitter I saw an announcement for livesql.oracle.com, complete with supplied scripts and tutorials.

This is a brilliant idea, and it's I think it's awesome they're using APEX for delivery. You'll no doubt find many AskTom answers and demonstrations linking to this site, and I think it will be a great repository for developers

Conferences

I'm a long way from Rome, but with the aid of modern technology I'm able to keep my finger on the pulse, and it sure seems strong. It seems these days you kind find yourself a reasonably close conference targeting developers regardless of where you live. If you haven't been to one I highly recommend it, for the experience, the learning and the camaraderie.

If you're unable to attend a conference, ODTUG hosts many webinars, one of which is coming up next week that apparently all APEX developers should tune in for. Midnight challenge accepted.

I have more experiences to share from my time at Kscope15, still some hand written notes and mental thoughts I'd like to translate to the blog. It's still got me thinking about the community, and how amazing the ODTUG team is at serving it. Become a member and have your say in the board.

Oracle Open Source

If I were to guess the topic of the Top Secret APEX Community Event, I would say it has something to do with the gathering momentum of an Oracle open source project at oraopensource.com. I've heard a bunch of ideas and promises come from that direction, and so far the organisation of a bunch of stubs.

They have big, useful things in the works, time for pay dirt?

Oracle Database Developer Choice Awards

Voting closes at some point as the planet rotates today, but I hope you voted for a few people. It's another new initiative, maybe not done quite right but it's early days and there are many years to come.

If you're on social media it would be hard to miss ongoing commentary about some of the issues faced in regard to voting. I'm not going to go into detail, but let's remember it's early days in a new idea and mistakes will be made and learned from. It sure took a long time for the Nobel prizes to sort the crap out, just ask Rosalind Franklin.

Internet voting is hard. The organisers inherited an existing framework with some unforseen pitfalls. Perhaps next year a shiny APEX application will take its place. maybe with a modified voting system.

The spread of categories is certainly developer centric, another win for us. I was humbled when asked to be involved in the judging process for the APEX category. I figured if I'm a judge in something, surely that means I would warrant nomination for such an offer, or so my ego went.

However, this means congratulations should be forwarded to not only those nominated (and the other judges of course), but the many skilled developers out there who were not nominated simply due to community apathy, something that Tim Hall had a little rant about. There were also nominees that became ineligible by virtue of residing in a particular country. One nominee couldn't be added, ironically while organising a separate competition.

The same can be said for passionate, skilled developers out there who are not recognised as ACEs. I still have a post drafted diving into my thoughts on the program, but for the awards the APEX judges decided nominated ACEs have already received deserved recognition, and we as a community wanted to find some more diamonds in the rough. Some future ACEs no doubt.

As a judge I was ineligible to vote, but now voting is closed I thought I'd share some thoughts on each category.

SQL

This first category demonstrates an interesting issue regarding internet voting. With all due credit to Emrah, I think an active local community has come together to support their nominee, and he will come out of this a more noticeable member of the global community - mission accomplished.

I would like to thank Kim Berg Hansen for his very active presence online. I haven't participated in the PL/SQL Challenge site since Steven Feuerstein started wearing red shirts, but Kim's SQL quizzes are excellent and that takes commitment.

PL/SQL

Kudos to some nominees here crossing categories, but for me the standout in helping my understanding of some of the most useful but slightly more complicated features of the database is Adrian Billington. The www.oracle-developer.net website has articles that effectively explain some tough topics. These OakTable fellows sure are made of the right stuff.

ORDS

I haven't had enough time to really play with this product. A colleague has done most of the infrastructure work for our current client and I'm waiting for a chance to eke some more leverage out of this maturing product.

I asked a question on the forum about ORDS recently and was reminded how much I didn't know, and how much Dietmar is all over this product. In my mind a deserved inaugural winner.

APEX

Being so involved in this product now I could say plenty about each of the nominees, and illustrate how hard it must be to compare apples to oranges to mangoes. They've all contributed in relatively uniquely ways, and Morten in nominated once again.

Trent is probably the quietest achiever of the group, though I know his blog posts are quality over quantity and I've learned from his plugin dabblings.

If anyone has used the forum they've encountered Paul MacMillan as fac586. I think everyone must have learnt at least something from his replies.

Karen is the IR queen. I think our circles and geography differ somewhat, but that's why they asked for geographically diverse judges.

Morten has done amazing work in building a library of useful libraries. Hard to top and at this point he may have pipped Juergen, a new kid on the block but what a demo app for dynamic actions! And awesome work starting an APEX podcast with very interesting interviews.

Kiran Pawar has been a recent star in the forums, quick to provide good references to people looking for information. I have criticised some of his answers recently but I'm sure the same could be said for some of my responses, and certainly questions. I made some doozies recently.

Jari Laine is a JavaScript wizard that I've learned plenty of practical applications for jQuery. I could not have built such a versatile tablet application without integrating some work he's shared.

Database Design

Surely this is the most esoteric of the categories, but at times I really do miss some of the best designers I've encountered. I can't add much beyond congratulating fellow Perth technologist Mark Hoxey as a nominee. Someone obviously thought enough of his contribution to best practice to place a nomination, well done.

Final Thoughts

If you think support for Oracle technology is waning, think again. There's plenty I've missed, stuff I don't know, and time & spirit to nurture and cultivate some great ideas. With such a large amount to learn, I find solace in a community that facilitates such a variety of options to learn. I'm glad I have freedom to contribute my part.

Generating JSON from SQL cursor

It appears there will be a good 1001 uses for the APEX_JSON package in APEX 5, here is one of them.

Previously I had an AJAX PL/SQL callback that returned a relatively simple JSON string, using the LISTAGG technique described by Lucas here.

declare
  l_json varchar2(32767);
begin
  select listagg('{"id":"'||version||'","content":"'||version||'.x"}',',')  within group (order by null) json
  into l_json
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  sys.htp.prn('['||l_json||']');
end getGroups;

Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:

DECLARE
  c sys_refcursor;
BEGIN
  open c for 
    select version    as id
       ,version||'.x' as content
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  apex_json.write(c);
END;

The output differs only slightly
[{"id":"1","content":"1.x"},{"id":"2","content":"2.x"},{"id":"3","content":"3.x"},{"id":"4","content":"4.x"},{"id":"5","content":"5.x"}]
[ { "ID":"1" ,"CONTENT":"1.x" } ,{ "ID":"2" ,"CONTENT":"2.x" } ,{ "ID":"3" ,"CONTENT":"3.x" } ,{ "ID":"4" ,"CONTENT":"4.x" } ,{ "ID":"5" ,"CONTENT":"5.x" } ]

A few extra spaces in the apex_json version. If lower case required for JSON attributes then use double quotes around column aliases, ie: select version as "id"

Oracle 12c JSON APIs seem to be all about validating and deconstruction, while this API has a bunch of overloaded apex_json.write() modules, including support for CLOBs.

Craig Sykes demonstrated how this could be done dynamically with a simple page.

Review: The Martian

I think we have a new genre of films for those consumers yearning for science and exploration.

First Gravity took us on a cinematographic journey into low earth orbit.
Then Interstellar took us on an inception style ride.
If you liked these you may enjoy Europa Report, which has a 2001 feel to it.

Now we have The Martian, which only a few years ago was still in the imagination of write Andy Weir. The book has an amazing backstory, you should have a listen.

Thank you 20th Century Fox

This review from Kirstin Acuna nailed the comparison to Castaway (another of my favourite movies), but I'd like to comment on some of her conclusions.

Biggest obstacle: Science.

Let's think about the target demographic for a moment, shall we? Audiences have been yearning for this style of moview, and good, sensical dialogue should not be compromised for the lowest common denominator. Have a read of this parody [cached] regarding if sports was reported like science, then come back to reconsider.

Book vs Movie

I haven't read the book yet, but I would agree with Kirstin in that you should watch the movie first. I don't know how the book ends, but the cinema took me on an amazing journey, and I can't wait to go deeper in the book.
I didn't come out of movie bummed out with disappointment like Sphere, though perhaps in that case it shows movies just can't always translate to the screen.

Comparisons to Interstellar.

Movies will be compared to other movies all the time. You know the amazing thing about The Martian? No bad guy, no agenda, just the "love letter to science".

See it in 2D.

I beg to differ, see it in 3D. We don't need scenes constructed simply to satisfy an intense 3D experience. The Martian does an amazing job of taking you to the planet, go experience it in 3D to help with that journey. I also recommend sitting near the front where the screen fully immerses your peripheral vision, though I recommend taking a few things to cover the little arm rest lights we had in our cinema.

Conclusion

Watch this movie, I can't recommend it enough.
Thanks to my colleague for getting me pre-screen tickets - I think I will be seeing it again before too long to help my brain process it all!
Also, hang around for the credits. Not only does it start perfectly, the credits blend nice and neat.

APEX 5 Change Workspace Authentication

APEX has provided the ability to authenticate your application users against an LDAP server for quite some time.

APEX 5 now provides us the ability to change how we log into the development builder itself, and it's surprisingly easy.

Recently I modified our APEX 5 sandpit to authenticate against LDAP, so we can use our Windows passwords logging into the APEX Development Builder - one less password to manage.

Database ACL

I didn't need to apply an ACL to get this working in 11g APEX 4.2.1, but I did in 12c & 11g APEX 5 instances.

DECLARE
  l_acl       VARCHAR2(100) := 'ldapacl.xml';
  l_desc      VARCHAR2(100) := 'LDAP Authentication for SAGE';
  l_principal VARCHAR2(30)  := 'APEX_050000'; -- upper case
  l_host      VARCHAR2(100) := 'your-ldap-domain.com.au';
BEGIN
  -- Create the new ACL.
  -- Also, provide one starter privilege, granting the schema the privilege to connect.
  dbms_network_acl_admin.create_acl(l_acl, l_desc, l_principal, TRUE, 'connect');

  -- Now grant privilege to resolve DNS names.
  dbms_network_acl_admin.add_privilege(l_acl, l_principal, TRUE, 'resolve');

  -- Specify which hosts this ACL applies to.
  dbms_network_acl_admin.assign_acl(l_acl, l_host);

  COMMIT;
END;
/

You can check what you have already with

select * from dba_network_acl_privileges where acl like '%ldap%'

Instance Security

First log into the INTERNAL workspace and head to Instance Security settings. Under the authentication section you'll see a list of available schemes, all you need to do is change the 'current' scheme.

Instance Security Settings

Scheme Settings

The declarative settings are just like those for your application. Getting the LDAP string correct for your environment can be tricky.

At our site I had one that worked for some of us, but not others. Then I found since we're using a Microsoft LDAP we can use the simple format of domain\%LDAP_USER%

LDAP authentication attributes

Note the message on the right hand side.

NOTE: Even for external authentication schemes (e.g.HTTP Header Variable), you have to make sure that users exist as developers or administrators in the workspaces. Otherwise, Application Express will not be able to verify which workspace a user is allowed to work in.

Now the login process authenticates against LDAP, but it still uses APEX user settings to determine authorisation.
ie - what type of user are they? administrator, developer etc.

Internal Workspace Users

Since level of access is still deferred to APEX accounts, don't forget to define workspace users specifically for the INTERNAL workspace that match the LDAP username.

INTERNAL Workspace user list

It's unlikely you will have an ADMIN account in your LDAP server, which is a good thing.

Rolling Back

If you leave your INTERNAL session logged in you can use another browser (or private session) to test authentication and revert the current scheme if necessary.

Alternatively, as described by the warning when applying the scheme change, you restore the authentication from SQL Developer using an API.

You'll need administration privileges:
exec apex_instance_admin.set_parameter('APEX_BUILDER_AUTHENTICATION','APEX')

Workspace Logon Page

Don't forget to remind your developers what's going on, perhaps via a login message.

APEX 5 login message

The login message here is a running joke with our transient DBAs.

I've drafted a post where you can include JavaScript in the login message to make modifications to the login page, just like in prior versions.

APEX 5 Verify Theme Subscription

I was attempting to replicate the navigation bar example from the Opportunity Tracker application and I noticed my application was missing icons from the sub list dropping off the username.

Icons in submenu when visible

Here are the relevant User Interface settings for the Navigation Bar.

User Interface settings

It turns out in newly created application with Theme 42 the following is used as the sublist entry template
<li data-current="true" data-icon="#ICON_CSS_CLASSES#"><a href="#LINK#">#TEXT_ESC_SC#</a></li>

In my application that was created a few weeks ago uses
<li><a href="#LINK#">#TEXT_ESC_SC#</a></li>

So this is when I thought it might be worth giving the Verify Theme Refresh option a go.
Sure enough a bunch of templates, including the Navigation Bar, was "Out of Sync" instead of "Match"

Verify Theme Subscription

I followed the on-screen instructions as the documentation suggested, and waited with baited breath as to what to do next...

Nothing! A message came back saying my theme was synchronised and every template matched the theme master that lives in the Oracle domain somewhere.

I tried the Verify theme option previously but it does not seem to operate well in apex.oracle.com
ORA-00040: active time limit exceeded - call aborted
Not sure if this is a shared server thing or something is actually wrong.

At the end of the day this shows an advantage of running the Universal Theme - updates and corrections can come from Oracle at any time.

I guess we'll learn any disadvantages as time goes by, such as any JavaScript or CSS changes that affect your usage of the relevant template.

Also see UT migration guide.

Note: December 2017
Pre-release of 5.1 - Upgrading Universal Theme

APEX 5 Source Used attribute - tolerating less mistakes

If you've upgraded to APEX 5 and find yourself with an error complaining about the 'Source Used' attribute:
APEX.SESSION_STATE.DB_COLUMN_SOURCE_USED
then you should thank APEX 5 for finding (and no longer tolerating) a logic bug in your application.

APEX 5 - correcting your applications

Track down that field and set the Source Used attribute to 'Always, replacing any existing value in session state'.

On a side note, setting the application's Compatability Mode to 4.2 will also fix the problem, but in this case certainly not the appropriate solution. But this means by setting this attribute to 5.0 on your existing applications can help find these problems in the first place.

Inaugural Developer Choice Awards

Can you think of anyone worthy of being recognised in the following areas?

Developer Choice Awards Categories

Today is the last day for nominations, so visit the website and nominate the first person you think of - right now!

From mid next month you will have a chance to vote for the finalists.

It looks like Oracle are trying really hard to be more involved with the community, so I think good on them for this initiative.

APEX 5 Changing region type

APEX 5 change region type

If only to further emphasise some of the fundamental rebuilds APEX 5 must have undertaken, look at the ability to switch region types.

Prior to APEX 5.0, we could migrate a Classic report to an Interactive Report, but not the reverse. And don't even think about switching to another region type.

Just bear in mind the tweaks and adjustments you'd probably lose as you toggle around. I think it speaks more about how even more the APEX toolset fits in the RAD category, and how much neater the backend must be to make this happen.

This flexibility certainly helps a step in our course where we ask students to create a classic report. No matter how clear the instruction, there's always someone who creates an interactive report. This will make it easier to switch back!

Also note the "Legacy Calendar" type. This is what helps makes APEX backwards compatible, ensuring version upgrades go more smoothly by minimising the internal changes to applications. Though the same can't be said for plug-ins, where you'll probably where most of your upgrade pain.