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