Kscope Submission Ideas

Have you been thinking about submitting an abstract for Kscope? The submission deadline for Kscope18 is fast approaching! Submissions close on January 5, 2018!

Some of you may still be struggling to think of some topics. I’ve asked Dietmar Aust (Kscope18 APEX Track Lead), and Helen Sanders (Kscope18 Database Track Lead) for help with some topic ideas that would get your creative juices flowing. Scroll down and browse through the topics that are sure to trigger some ideas.
Aside from these general ideas, it is always great to hear about real-world projects. How were they approached? What were the challenges and which lessons were learned? This will help others to use good judgment and avoid problems proactively.

Finally, I should add, the list below is by no means inclusive. There are many other topics you could cover.

Reading Suggestions

If you know what you want to talk about, then get some inspiration for crafting the perfect abstract form this excellent blog posts:
* http://www.odtug.com/p/bl/et/blogid=1&blogaid=497 
* http://spendolini.blogspot.com/2013/11/presentation-advice.html
* https://www.linkedin.com/pulse/20140630141618-4832037-how-to-get-your-conference-abstract-accepted
* http://www.pythian.com/blog/concrete-advice-for-abstract-writers/
* https://chadthompson.me/2012/12/how-to-submit-winning-presentations/



  • Code quality, test-driven development for PL/SQL
  • Infrastructure setup and performance
  • Good practices in using SQL & PL/SQL for your APEX apps, new features for developers in Oracle 12.2
  • Version control with APEX
  • Development tools for troubleshooting, test automation, documentation, performance optimization, deployment automation, etc.
  • Security; threats and how to prevent them
  • Security; how to detect that information was leaked and/or stolen?
  • Modularization and Reuse: How can we extend APEX using plugins and how can we reuse code most effectively?
  • Beautiful APEX; Using and tweaking the universal theme to create beautiful apps
  • APEX Interactive Grid; interesting use cases and leveraging the API

APEX New Release

  • What is new in APEX 5.2 and why does it make a difference?

APEX Integration

  • Consuming and exposing RESTful services
  • Integration with SAP, AWS, Azure and other systems
  • Using different programming languages and tools like Java or sqlcl to support your APEX app either during development and/or at runtime
  • Using Docker in with Oracle and APEX


  • How to manage my Javascript code in my APEX app? Where shall I put it, how to debug it and what does the lifecycle look like?
  • Integrating new Javascript frameworks by converting them into APEX Plugins
  • Debugging and performance optimization of Javascript code in my APEX app


Database Administration – Foundational

  • Database In Memory
  • Security
  • Optimization
  • Upgrading, New Releases, and Patching (including new features)
  • Multitenant and Virtualization
  • Disaster Recovery and Backup
  • Performance Monitoring, Diagnostics, and Tuning
  • Best Practices (indexing, using external tables, etc)
  • Enterprise Manager

Database Administration – Beyond the Basics

  • Python for the DBA
  • REST data services for the DBA
  • DBAs in the Cloud
  • Blockchain
  • Web Services
  • Open Source
  • Docker

Database Modeling

  • SQL Developer Data Modeler
  • Database Design
  • Documenting the Database

Database Development – Traditional, Cornerstone, Foundational

  • PL/SQL
  • SQL
  • SQL*Plus
  • Upgrades, new Release features for developers
  • Performance topics – developer related
  • Best Practices
  • Data loading – sqlldr
  • Analytic Functions, Pattern Matching
  • Regular Expressions
  • SQL Developer – what’s new, tips and tricks

Database Development – Beyond the Basics

  • SQlcl
  • Python for Developers
  • Methodologies, eg Agile, SCRUM
  • Version Control (Git, SVN, etc)
  • Beacon Technology and tracking
  • Blockchain
  • JSON
  • JAVA
  • REST services
  • Open Source
  • MySQL, PostgreSQL, NoSQL
  • PHP
  • node.js
  • Ruby on Rails
  • Developers in the Cloud

What is Multi-Cursor Editing?

This little question on Twitter prompted me to record this brief video.

Some useful links:

Sublime Text for Oracle Apex Developers


Freeze Column & Headers Plugin

I recently had this requirement for freezing the column and header on a Classic Report, just the way you can with Excel. The report had too many columns and once you scroll horizontally or vertically you would loose track of the information you’re seeing.

It turns out freezing the header is pretty straight forward and it’s even out of the box functionality on the Interactive Reports, but not for Classic Reports.
If you only need to freeze the headers, just head over to Marko Gorički’s blog “How to make any table header sticky” and be done with it.

However, freezing columns is a completely different technique from freezing a header, or it requires special markup. Ideally, I didn’t want to create a Custom Template (even though, you know, I’m a big fan).

After some discussions and good advice from my buddy Vincent Morneau, I began scouring the interwebs for a suitable solution.

This JSFiddle https://jsfiddle.net/RMarsh/bzuasLcz/3/ seemed very promising and easy to adapt to Oracle APEX Universal Theme markup.

It seemed to me that I had to adjust selectors. So table became table.t-Report-report, thead became table.t-Report-report thead, and so on. You get the idea.

Then, after a working prototype, I saw the opportunity to bundle everything in a nifty easy to use APEX Plugin. You can find the plugin in apex.world and Github https://github.com/rimblas/apex-freeze-col-headers. Maybe you will find it useful.

View Demo

Using it is dead simple:

  1. Create an “After Refresh” Dynamic Action on the Classic Report you want to “freeze”.
  2. For the True Action select “Insum Freeze Headers & Column”
  3. Make sure “Fire on Initialization” is Yes.
  4. Don’t specify an affected element; the plugin uses this.triggeringElement

Warn Before Closing APEX Modal Dialogs

As you may already know, APEX 5.1 implements native Warn Before Exit functionality. Basically, if the user makes changes on a page and tries to navigate away before they save their changes, they get a warning. Modal Pages also get this feature, with one caveat; the close “X” on the window does not warn.
As I understand, this is was the intended behavior. However, for some use cases, I disagree. For this reason, and after a client’s request, I came up with this solution.
On the Modal Page attributes, we specify a “beforeClose” function. Like so:

Then, on the calling page (or globally) you want the following code:

Line 2 and 3 are the most important to this whole solution. Modal Dialogs run inside an iframe. As such, the calling page needs to take a peek inside the dialog and see if something was changed.
Line 2 gets a hold of the “apex” namespace for the dialog. Line 3 can then check is anything has changed within it.

Finally, line 10, prevents the close even from happening and maintains the dialog opened if they user opts to stay.

I should mention, I tried to use apex.message.confirm instead of the more rudimentary confirm, however because we’re already inside a modal, calling apex.message.confirm would invariably end up closing the modal I wanted to keep open. Perhaps, there’s a way to accomplish this, if you have any ideas, please share in the comments.

Dynamic Modal Dialog Titles in APEX5

Modal Dialogs in APEX5 use the Modal Page Title attribute as the Title of the dialog.

Standard Dialog Title

Standard Dialog Title

Often, I wish this valuable area of the page could convey more information. So, I thought I could use a Substitution String in the page title, but this doesn’t work that well because the value cannot be changed dynamically. To truly make it dynamic you need to use JavaScript.

Thanks to the awesome blog post from John Snyders I was able to take this example:

That enhances the dialog to open and close with a sliding effect. Into this:

When “dialogcreate” event fires, we override the dialog “title” option with the title we want.
A Dynamic Action, on Click of our link, will grab the text of the link (via this.triggeringElement.innerText and save it on P50_DIALOG_TITLE. Looks something like this:


The end result:

Dynamic Title

Dynamic Title


Now, I was wondering if I could simply set the attribute via the Dialog Attributes, and you can! Notice that the Dialog is a different page, but the attribute is from an item on the calling page. This means that if you call a dialog from many pages you may want to use a JavaScript variable with the same name.


Finally, there is one more use case to consider. What if you navigate within the dialog to a different record, for example with Next and Previous buttons? In this case, this code may come in handy:

OTN Appreciation Day : Pre-Built Developer VMs

I have something to confess, but it shouldn’t come as a surprise – I’m not a very good DBA.
… but I’m not supposed to be… I’m a developer! Sure, I can discuss configuration options and architecture, but don’t ask me to install anything.

This is why I need to say “THANK YOU!” to the Oracle Technology Network for their Pre-Built Developer VMs. By providing a Virtual Machine, that runs on VirtualBox (free), pre-configured with the latest and greatest technology, I can work, learn, and play freely. It’s an incredible benefit that I don’t take for granted.

Check out what others are thankful: Search #ThanksOTN.


Video: 006 – Substitution Strings, Bind Variables, and APEX Links

“Substitution Strings, Bind Variables, and APEX Links” oh my.

Have you ever build an APEX link manually and perhaps it looked something like this:

We should talk.

I used to make this mistake and I’ve seen it made too many times. It’s time to address it head on.

In this video, I will show you why there may be a much better way and why you should be careful. Of course, there are other ways of building links dynamically, like using APEX_UTIL.PREPARE_URL which could still be affected. Perhaps a better approach would be to use the “new” (in APEX 5.0) APEX_PAGE.GET_URL

In the video I comment on these links:

TL;DR: Don’t concatenate variables or columns in your SQL

Don’t do this in SQL:


You’ll avoid flooding your shared pool memory with unique SQL statements that cannot be re-used.

Video: 005 – Restore screen position after refresh

Picture a Report and Form pages used to edit records. The Report is an Interactive Report and the edit page is Modal. When the modal closes the IR refreshes, but also, the page jumps to the top after the refresh. This happens in APEX If your report is small this is never an issue, but with longer reports, this is pretty annoying to the user.

I wouldn’t be surprised if this gets fixed in a not so distant new version of APEX, but in the meantime, here’s a simple workaround. Oh and if you have any recommendations, improvements, questions, or suggestions I would love to hear about them.

Check out the demo.

I think my friend Gemma Wood first told me about this fix. I couldn’t find the actual code she shared with me, but the basic approach is pretty simple. Save the scroll position of the page before you open the modal page. Restore the scroll position after the refresh.

Here are the more detailed steps or, if you like, watch the video below:

  • Add a JavaScript variable to your page (use the “Function and Global Variable Declaration” field). We’ll use it to remember our scroll position.

  • Add a DA on click of the link used to invoke a modal page. It will save the current scroll position, so call it “Save Scroll Position”

  • Create an “After Refresh” Dynamic Action to restore the postion.

005 – Save screen position after refresh from Jorge Rimblas on Vimeo.

Video: Part 4 – Implementing Drag & Drop functionality

Do you want to add Drag & Drop functionality to your APEX applications?
That’s what this video is all about.

You’ll see me working on the list created in the previous episodes (Part 1, Part 2 and Part 3) and add the necessary JS to implement.

During the video, I follow this companion blog post (rimblas.com/blog/2016/08/implementing-drag-drop-in-your-apex-applications). Bookmark it and use it as a reference in the future.

In this 41 minute video you’ll find:

  • 02:08 to 03:22 — Add the jQuery UI library to the page.
  • 03:23 to 04:40 — Explaining how elements get re-arranged.
  • 04:40 to 08:02 — Make region/report sortable.
  • 08:02 to 16:04 — Identify the elements to sort and explaining how we actually capture the new order.
  • 16:05 to 24:17 — The AJAX call to save changes (plus some debugging), wrapping up.
  • 24:18 to 42:25 — Implementing the same thing on a regular Classic Report.

Part 4: Adding Drag & Drop functionality from Jorge Rimblas on Vimeo.

* Music licensed from Envato’s AudioJungle.

Implementing Drag & Drop in your APEX applications

I first learned how to implement Drag & Drop functionality from Doug Gault’s presentation at Kaleidoscope 2010 (yes before it was Kscope) “Replicating NetFlix Queue Drag-and-Drop Functionality with Oracle Application Express/jQuery” (Membership required). I mean, I wasn’t actually there, but the presentation has excellent step by step instructions. My goal is for this post to become a useful quick reference guide (as much for me as others).

With the proliferation of mobile apps and being fully into (probably the end of) Web 2.0, users come to expect this sort of feature more and more. When we implement it in our Oracle Application Express applications, it brings a nice “wow” factor. That said, I don’t think it’s for every scenario, and I would not overuse it. Also, keep in mind, on mobile devices, you may need some extra libraries to fill in for the lack of a mouse.

If you’ve seen some of my videos, Part 1 covers the creation of the template that is mentioned below. In video installment 004, I following these instructions. If you have any questions, hopefully, the video will cover them.

Demo app here.

Structure to Sort “Lines”

You’ll need a parent container that includes the sortable lines.
For a report think <TABLE> as the container and <TR> as the lines.
For a list think <UL as the container and <LI> as the lines.
The lines that are to be sorted require some ID that uniquely identifies them. You can use a real id tag (id="line123") attribute or a dataset attribute like data-id. The dataset approach is more robust as HTML id values cannot start with a number.

We’ll use a markup like the following:

We’ll define a “Named Column” report template to define the UL list. (To see how to do this watch Part 1)

“Before Rows”

“Row Template 1″

“After Rows”

We’ll use the following table to hold our data:

The DISPLAY_SEQ is the value we’ll use to sort and to re-arrange after a drag & drop action takes place.
The following SQL will define our Classic Report with the custom Named Column template. Give the report a Static ID, we’ll use todoRegion

Include the sortable jQuery UI library

This library is already part of your standard APEX distribution. Select the correct one for your version of APEX

APEX 4.2

APEX 5.0

Make the report Sortable

Notice this.affectedElements[0] which means you’ll use this within a Dynamic Action and specify your report region as the Affected Element.
containment (line 4) is optional, but sometimes it’s a helpful option to restrict how far the elements can be dragged. Try it without to see the effect.
At this point, the report lines can be dragged, but their new order won’t be saved because we have not defined updateDisplaySeq()

NOTICE Changes made on 9/12/16 :
I’ve made some small but important changes. Instead of using x01 the code above now uses f01. This also eliminates the need to issue a .toString() on line 2. The next big change is that now, in the PL/SQL side we can loop directly on apex_application.g_f01 and we don’t need to use apex_util.string_to_table. Thank you to Erik Diaz for pointing this out on the comments below.

The 'toArray' method will return an array with the ID of our lines. Then .toString() makes it easier to work with in our AJAX process.
By default toArray looks for the element ID, since we’re using data-id we need this option: {attribute: 'data-id'}
The code for UPDATE_ORDER will follow, but notice line 8. If we were using the old Notification Plugin from Oracle (that uses Gritter) we could generate a Notification to the user that the new order has been saved. You could consider using pNotify instead.
Finally, line 9 forces an APEX Refresh of the report to ensure all the latest data is in place and fresh.

AJAX Callback

This is the AJAX Callback reference in the code above as UPDATE_ORDER. Remember that the AJAX Callback name is case sensitive.
The following code receives f01 with our array in the new order we want.


If you’re using a standard Classic Report (instead of a Named Column template that you can control). You’ll need to add the ID handles to your <tr> rows.
The following code will find the ID column (it could be a link column). You’ll want to have this snippet in the attributes data-id=#ID#.
It will extract it from a dataset attribute and add it to the TR tags.
You will need this code defined as a function because you’ll call it after Refresh to re-insert the ID to the report.
pRegionID is the Static ID of the report. The first selector for $r may need to be adjusted depending on the theme.

fixHelper is very useful for a table report as it will set the width or the TR being dragged to the original width it had. Said another way, it will maintain the width of the row being dragged. Without it, the row may collapse to the width of its elements. You can see it’s being referenced in the helper parameter as part of the sortable constructor.

The selector inside updateDisplaySeq (called above) will need to be changed to work with a table. Something like this should work for you: