I had the delight of participating on a “Fill The Glass” webinar (#FillTheGlass). Fill the Glass is a new style of, technology related, webinar series by Cary Millsap.

Cary Millsap is a developer, teacher, writer, and consultant who has been a part of the Oracle ecosystem since 1989. His new webinar series about Oracle and related technologies is going to be little different than the usual hello-slide-show-Q&A-bye routine. These shows are going to include conversation. Our aim is to bring the technology to life. Our guests will of course talk about the problems they’re solving and how they solve them, but our conversations will include discussions about their ideas, their motives, their tools, …even their careers. Cary likes to find the twist, a way of perceiving a situation that you might not have thought of. Join us to learn, improve your career, and have some fun.

In this episode of Fill the Glass, Cary and I discuss techniques for measuring software performance. Some of the topics include instrumenting your code, measuring workloads, SQL Plan Management (SPM) and code development ideas in general that you may be able to apply to your projects.
Although the application in the video was built with Oracle Application Express, the concepts are universal to programming. At the very least, very current and apropos for Oracle developers of any kind.

The recording is now available here:

Fill the Glass Episode 4 with Jorge Rimblas – Measuring software performance in the real world from Enkitec LP on Vimeo.

Related links of topics seen or mentioned during the webcast:

I’m really excited to share with you that the 2nd Edition of APRESS Expert Oracle Application Express (Amazon link) is now released. Some of the concepts in this book may apply to previous versions of APEX, but this book was created with APEX5 in mind.

Expert Oracle Application Express

This book is the result of hard work from 14 authors: Doug Gault, Tom Petrus, Denes Kubicek, Roel Hartman, Dan McGhan, Francis Mignault, Raj Mattamal, Martin D’Souza, Christoph Ruepprich, Dimitri Gielis, Nick Buytaert, Karen Cannell, John Scott, and myself.

I should also mention the expert help of the APRESS team and the technical reviewers: Patrick Cimolini, Vincent Morneau, and Alex Fatkulin.

By the way, all but one of those authors and technical reviewers are on Twitter. If you’re an Oracle professional and work with Application Express, I recommend you follow them.

The book is comprised of 14 chapter over 650 pages.

1. APEX Builder, Denes Kubicek
2. Oracle REST Data Services, John Scott
3. Oracle APEX 5.0 Charts Inside Out, Dimitri Gielis
4. Tabular Forms, Denes Kubicek
5. Team Development, Roel Hartman
6. Globalization, Francis Mignault
7. Debugging, Doug Gault
8. Dynamic Actions, Martin Giffy D’Souza
9. Lifecycle Management, Nick Buytaert
10. Plug-Ins, Dan McGhan
11. jQuery with APEX, Tom Petrus
12. Map Integration, Christoph Ruepprich
13. Themes and Templates, Jorge Rimblas
14. Report Printing, Karen Cannell

One more piece of information. All proceeds from this book go to the families of two exceptional people that left us way too soon: Carl Backstrom and Scott Spadafore. Both members to the APEX team and instrumental in laying the foundation that has gotten Application Express to where it is today. Although I never met them in person, their knowledge sharing and efforts in the community to help people like me better learn and use APEX helped me immensely.

APEX 5 has a nifty nice enhancement to the use of apex.widget.waitPopup. It now returns a “remove” function you can call to remove the wait indicator.

// variable for return function (add it to your "Function and Global Variable Declaration" field)
var $wP;

// Start the page overlay
$wP = apex.widget.waitPopup();
}, 10);

// do long running "stuff"

// remove overlay and indicator

The Details

I’ve used apex.widget.waitPopup many times. You simply call it like this:


In APEX 4.x, you would see the following animated GIF.

APEX 4.x waitPopup

APEX 4.x waitPopup

Often, you would call it before submitting the page if you had a long running step. Then when the page refreshes it would be gone. If instead you were going to remain on the page, you had to manually remove it.

// add page overlay and wait indicator

// long running "stuff"

// remove indicator in APEX 4.x

Notice the use of setTimeout as sometimes, inside of a Dynamic Action, the page rendering would be frozen and you wouldn’t see the wait indicator.

To remove the overlay and the wait GIF image you would have to remove them from the DOM with the jQuery .remove();

However, now on APEX 5, apex.widget.waitPopup returns a remove() function you can call to handle this for you. I find this a much nicer and elegant approach.

This is how the new waitPopup looks in APEX5.

APEX 5 waitPopup

APEX 5 waitPopup

I recently got to use a new cool feature in Oracle 12c R1, the WITH clause with inline PL/SQL (read more at oracle-base). Then Scott Wesley blogged about it and I made a comment on twitter. But don’t stop reading just yet just because you’re not using 12c.

Then, Nick Buytaert blogged about the new “Lazy Loading” feature on his APEX Select2 plugin (using apex_util.json_from_sql) and I made some comments on his blog post. By this point I figure it was time to do a proper blog post linking all these things together.

The WITH clause with a PL/SQL Function feature is important, heck I think it’s fantastic. However, just because you’re not on 12c yet you should dismiss the technique outlined here. So read on. First, lets define a scenario to use the functionality.  Lets say you are using the Select2 plugin and have an On-Demand process that will generate a JSON string with your matching values.  You would need something that looks like this:

  "row": [
      "D": "KING",
      "R": 7839
      "D": "BLAKE",
      "R": 7698

This is easily generated by apex_util.json_from_sql. The SQL to generate this result would like something like this:

select ename d, empno r from emp

The Select2 plugin will pass a search term to the On-Demand process and we need to use it in our SQL statement. The search term will be passed to our On-Demand Process in apex_application.g_x01. Unfortunately, we cannot (yet) use apex_application.g_x01 directly in our SQL like this (because it’s a global package variable outside of our scope):

// This is invalid!!
select ename d, empno r
  from emp
 where instr (upper(ename), upper(apex_application.g_x01)) > 0

// WARNING: This will NOT work!
l_sql := 'select ename d, empno r
  from emp
 where instr (upper(ename), upper(apex_application.g_x01)) > 0';

But SQL could use a PL/SQL function, so we need to wrap apex_application.g_x01 with a PL/SQL function so that we can pass it to apex_util.json_from_sql. In 12c this can now be done right in the SQL statement! Plus it comes with a performance benefit.

  l_sql varchar2(4000);
  l_sql := 'with function param return varchar2 is 
      begin return apex_application.g_x01; end;
    select ename d, empno r
      from emp
     where instr(upper(ename), upper(param())) > 0

But if you’re not in 12c yet, you would need to create a function (preferably inside a package) and then use in your SQL. Or, as Christian Neumueller noted in the comments, if you want to save a step just use the very handy V function with v('APP_AJAX_X01') to retrieve the value of apex_application.g_x01.

  l_sql varchar2(4000);
  l_sql := '
    select ename d, empno r
      from emp
     where instr(upper(ename), upper(v(''APP_AJAX_X01''))) > 0

But, please, please, whatever you do, do not concatenate the value as a string right into the SQL as that opens the door to SQL Injection. I’m not even going to write that code option here as I wouldn’t want anyone to copy paste it. :)

Borrowing a test harness from Tim Hall of oracle-base fame. Here’s a quick test of the performance difference between the new WITH function clause, a regular PL/SQL function and the V function.

SQL> create table t1 as select object_name from all_objects;

Table created.

SQL> create function param2
  return varchar2
  return apex_application.g_x01;

Function created.

  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  TYPE t_tab IS TABLE OF VARCHAR2(4000);
  l_tab t_tab;
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'with function param return varchar2 is 
            begin return apex_application.g_x01; end;
          select object_name
            from t1
           where instr(upper(object_name), upper(param())) > 0';
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  CLOSE l_cursor;
  DBMS_OUTPUT.put_line('WITH_FUNCTION   : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'select object_name
            from t1
           where instr(upper(object_name), upper(param2())) > 0';

  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  CLOSE l_cursor;
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'select object_name
            from t1
           where instr(upper(object_name), upper(v(''APP_AJAX_X01''))) > 0';

  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  CLOSE l_cursor;
  DBMS_OUTPUT.put_line('V       FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

WITH_FUNCTION   : Time=5 hsecs CPU Time=6 hsecs
REGULAR FUNCTION: Time=33 hsecs CPU Time=31 hsecs
V       FUNCTION: Time=118 hsecs CPU Time=118 hsecs

PL/SQL procedure successfully completed.

Of course, your millage may vary, but I find the results very interesting. Always do your own testing and validations.

Do you struggle finding a suitable color for a hover state or a selected item? If yes, read on.

Check out this simple example. You’ll see the color represented in the usual RGB hex values and in HSL. The HSL hover color only changes from the original color in one position from 32% to 40%. Without knowing anything about HSL yet, doesn’t that seem a lot easier to understand than the hex counterpart? Let me tell you how easy it was to find that “hover” color.

Original color Hover color
#0074A2 #0092CC
hsl(197, 100%, 32%) hsl(197, 100%, 40%)

The Background

This blog post is another drill down follow-up of a feature I talked about in my KScope14 presentation “Go-Go Gadget: Learn About Inspectors”.  I first learned about this brilliant technique form Paul Irish.

HSL stands for Hue, Saturation and Lightness and it’s an approach to representing color. We often use an RGB format (Red-Green-Blue) either expressed as hex values (#FF0000 for red, #00FF00 for green, #777777 for grey, etc) or with the following notation rgb(255,0,0) or rgb(0,0,0), etc…

With HSL, the Hue represents the color value and it can go from zero to 360. The Saturation and the Lightness are expressed as a percent where zero% is the absence of saturation or light and 100% is the maximum value.

Why Should I Care?

Because HSL is perfect for finding a nice color for our hover state or selected elements!  When you have a color in HSL, changing the saturation or the lightness alone will give us a very similar color that is either darker or lighter than the color we started with. I don’t even need to worry about the hue component. Let me show you how it works.

The Technique

I’m going to concentrate on Firebug for Firefox and Chrome because they have the easiest way of handling this.  Lets start with Firebug. If you’re inspecting the element with the color you want to start with, you twirl open the menu for the Style tab and select “Color as HSL”.


Firebug “Color as HSL”

Immediately, all colors within Firebug will be displayed in their corresponding HSL format via this notation: hsl(nnn, nn%. nn%);  Next, you would usually click on the last value, lightness, and use by up and down arrows to find a nice complementary color. Once I find it, I would switch the color display format back to HEX so that I can use the newly corresponding RGB color to use in my CSS.

Chrome is my favorite for this technique because within the Style Inspector Panel you can simply Shift-Click on the color swatch and rotate through different color formats. It’s so easy! So I would select the element. Shift-Click on the swatch (usually two times). Find a pleasing alternate color. Switch back to hex via another Shift-Click and copy paste the new HEX value into my CSS.  Here’s a quick gif animation showing what I mean. When you see the percent values changes I’m simply holding my up or down arrow down. Notice how the “Publish” button changes colors!

Finding an HSL color with Chrome

Finding an HSL color with Chrome


The purpose of this post is to document the process I used to convert the data from a Microsoft SharePoint site to an Oracle database with an APEX front end.  However, there won’t be much about APEX in this post, sorry. This one is all about the data conversion.

Sure, SharePoint does allow you to easily export its data to flat file or Excel, however this approach doesn’t scale too well when you need to repeat and test your conversion scripts several times. I was dealing with 20 source lists (think tables) that needed to be extracted, transformed and mapped into their final Oracle tables. It was clear that we needed repeatability in the conversion process and thus connecting directly to the Microsoft SQL Server schema and moving the data over to Oracle was a better approach.

At a high level, the approach had these steps:

  1. Use Oracle SQL Developer to copy the (“as-is”) data from SQL Server to Oracle.
  2. Create all the objects in the Oracle database schema.
  3. Run the scripts that will massage, transform and move the data.
  4. Rinse and repeat (Until testing and validations were successful).

Using Oracle SQL Developer

SQL Server connect tab

SQL Server connect tab

Without SQL Developer this whole process would have been unnecessarily complicated. SQL Developer has the very nifty ability to connect to other non-Oracle databases and data sources via it’s support of Third Party JDBC Drivers. When the necessary JDBC Driver is present, SQL Developer will present the corresponding tab on the connection dialog. Jeff Smith explains the feature best in his Connecting to Access, DB2, MySQL, SQL Server, Sybase, & Teradata with SQL Developer blog post. We connected directly to the SQL Server database using the jTDS SQL Server JDBC driver that we found thanks to this other post.

Now, I’ll warn you, at the client site no one had ever connected to this SQL Server database without a fancy-autodiscovery-autoconfigured GUI tool. It took many back and forth emails and support calls to determine the correct connection settings we needed. Now, I’m by no means a SQL Server expert, so I could be using the incorrect terminology here, but here’s what worked for me. We did use the name of the machine for the host name. The key as you can see from the connect image was in the port field. We used port slash site.  I don’t really know if that word after the port is the site, schema, instance or what. Like I said, it took several tries and combinations.  However, once the connection worked everything went pretty smooth. This is what we were able to see in the SQL Developer Connections Panel.

SQL Server objects

SQL Server objects

The SharePoint Data

The SharePoint data we needed was stored in only 5 tables:

  • AllLists
  • AllUserData
  • AllUserDataJunctions
  • AllDocs
  • AllDocStreams

User data in SharePoint is usually stored in lists. Everything is a list and AllLists has the name and IDs for those lists, think the “Header” record. AllUserData is the bulk of all the data. All the list data is stored here, think “Lines” that join to a header. AllUserData has many columns with generic names like nvarchar1, nvarchar2, nvarchar3, int1, int2, bit1, bit2, ntext1, ntext2, etc.. you get the idea. The AllUserDataJunctions is basically a lookups and joins table. It contains the relationship between two lists.  The AllDocs and AllDocStreams worked together to store the documents we needed. AllDocs contained the document information, the header if you will and AllDocStreams contained the BLOB with the document. Other than sorting out the relationship between the AllLists, AllUserData, AllDocs and AllDocStreams I was pleasantly surprised that we were able to extract the binaries (BLOBs) documents we needed.

Moving the Data

Copy to Oracle

Copy to Oracle

Next, from the Connections panel, in SQLDev, we right click on the object we want to copy to our Oracle database and select “Copy to Oracle…”.  A dialog will pop-up where we need to select the destination connection we want to copy the data to. The connection must already exist. You’ll see a checkbox to “Include Data” and a radio button group labeled “Options if the table exists” with the following three options: Indicate Error, Append, or Replace.

I would mostly use the “Append” option because I was controlling cleaning the tables, but it’s your choice.
Now, the first time it runs, this step will create the table and fail with this error:

Table AllLists Failed. Message: ORA-01400: cannot insert NULL into ("MYSCHEMA"."ALLLISTS"."TP_DELETETRANSACTIONID")

But it doesn’t completely fail, it does create the table. We just need to make the TP_DELETETRANSACTION_ID column nullable:

alter table AllUserData modify TP_DELETETRANSACTIONID RAW(32) null;

Notice that the datatype is RAW(32), maybe this is why SQLDev maps the column incorrectly, who knows. But after fixing this we can try the copy again. The AllList table is fairly small and we quickly see:


Then we move to AllUserData. It also fails on the TP_DELETETRANSACTIONID column so we fix it with:

alter table AllUserData modify TP_DELETETRANSACTIONID RAW(32) null;

Repeat the “Copy to Oracle”. While this is running we see this dialog:


In my case, it took around 10 minutes to complete.

We also need AllUserDataJunctions. We repeat the “Copy To Oracle”. It will also fail. We fix the table and try again.

We repeat for AllDocs. Except, in this case, the column to fix is DELETETRANSACTIONID. So we run this command:

alter table AllDocs modify DELETETRANSACTIONID RAW(32) null;

Next we can copy AllDocStreams. There is no column to fix here. This is the table that contains the actual document BLOB column and all the versions of the documents. It may take a while to run.

Finally, I created some indexes to speed things up before running the conversion scripts.

create index AllUserData_i01 on AllUserData(tp_ListId);
create index AllUserData_i02 on AllUserData(tp_Id);
create index alluserdatajunctions_i01 on alluserdatajunctions(tp_fieldid);

We’re done! All the data is now in Oracle and ready for conversion.

I’m not saying the process is completely automated, far from it, but it’s a lot faster, repeatable and reliable than extracting 20 different lists and then loading them into the database.

The Data Mapping

At this point, since all the data we need is now in our familiar Oracle tables, this becomes a data mapping/massaging exercise.  If you look at the AllLists table, it will be clear what lists you have available and it should be trivial to map them to what you see on the SharePoint Site. The TP_TITLE column has the name of the list.

Mapping the columns on AllUserData is a little more involved. However, depending on your data, this is not necessarily complicated, it’s just busy work. I created views for all the lists I needed in order to make it a lot easier to work with. Here’s a view for the Country lists which basically contained the country_id, country name and country_code.

create or replace view wss_country 
select d.tp_id       country_id
     , d.nvarchar1   country
     , d.nvarchar3   country_code
     , d.tp_author   created_by
     , d.tp_created  create_date
     , d.tp_editor   updated_by
     , d.tp_modified update_date
  from alllists l
     , alluserdata d
 where d.tp_listid = l.tp_id
   and l.tp_title = 'Country'
   and d.tp_iscurrent = 1;

It was simply a matter of looking at the SharePoint list and the data in AllUserData to find and map nvarchar1 and nvarchar3. The same exercise was repeated for 19 other lists.

While mapping the more complex lists I learned some useful things:

  • The floatN (float1, float2, etc) and intN columns contain numeric values and floats don’t necessarily have decimals, it would depend on your data.
  • The ntextN fields contain multi-line data, but of course they fit perfectly into a varchar2.
  • The bitN (bit1, bit2, etc..) columns are used for checkboxes in SharePoint and they simply had a 1 when set or a zero or null when not.

Dealing with AllUserDataJunctions

This table was a little more involved. Here’s the example of Suffixes assigned to “people”. First we needed a view on the possible Suffixes. This is easy, it’s the same scenario as above for countries.

create or replace view wss_suffixes
select d.tp_id     suffix_id
     , d.nvarchar1 suffix
  from alllists l
     , alluserdata d
 where d.tp_listid = l.tp_id
   and l.tp_title = 'Suffix'
   and d.tp_iscurrent = 1;

Then we need to figure out the suffixes assigned to people. Using a previously created wss_people view, here’s the view that would combine return all the suffixes assigned to people.

create or replace force view wss_people_suffixes_xref 
select p.people_id
     , d.tp_id     suffix_id
     , d.nvarchar1 suffix
     , d.tp_author
     , d.tp_created
     , d.tp_editor
     , d.tp_modified
  from alluserdata d,
     (select a.tp_id
           , wssp.people_id
        from alluserdatajunctions a
           , wss_people wssp
       where a.tp_fieldid = '50F1CA52-3D21-45A1-A23D-C6A556A74144'
         and a.tp_isCurrentversion = 1
         and a.tp_docid = wssp.tp_docid) p
 where tp_listid = 'F5A9D1ED-9136-4604-8C52-A7448CD08A42'
   and d.tp_iscurrent = 1
   and d.tp_id = p.tp_id;

The tp_fieldid required a little of poking around and searching for unique enough values to find it. The tp_listid is just a list id. We were able to use the exact same construct for a few other cross references that used the AllUserDataJunctions table.

Dealing with AllDocs and AllDocStreams

When I think about SharePoint, I usually imagine a document repository. However, that was not the case for this site. This was growing to something a lot more complex (hence the need for the conversion). As such, documents in this system were all assigned to the people entity. It did not have a generic document repository (another reason it was a good candidate for conversion). I would imagine that if you were extracting a regular document repository the documents would be part of a special “Documents” list. However, at this time I’m unable to confirm this.

Here’s the view we used to extract documents.

create or replace view wss_documents
select d.tp_id       document_id
     , d.tp_listid
     , d.nvarchar3   extension
     , d.nvarchar7   title
     , d.nvarchar13  people_id
     , d.nvarchar13  person_name
     , d.int1        folder_id
     , d.tp_author   created_by
     , d.tp_created  create_date
     , d.tp_editor   updated_by
     , d.tp_modified update_date
  from alllists l
     , alluserdata d
 where d.tp_listid = l.tp_id
   and l.tp_title = 'Document'
   and d.tp_iscurrent = 1;

Notice nvarchar13 containing the people_id that links a particular document to a person. Then, here is the code to move the documents and the BLOB.

insert into myapp_documents
( id
, people_id
, folder_id
, title
, filename
, mime_type
, blob_content
, created_by
, created_on
, updated_by
, updated_on
select wd.document_id
     , wd.people_id
     , nvl(wd.folder_id, 1) folder_id
     , wd.title
     , d.leafname filename
     , decode(lower(d.extension),
                 , 'pdf','application/pdf'
                 , 'doc','application/msword'
                 , 'pdf','application/pdf'
                 , 'aspx','application/x-asap'
                 , 'css','text/css'
                 , 'docx','application/msword'
                 , 'dotx','application/msword'
                 , 'gif','image/gif'
                 , 'htm','text/html'
                 , 'jpg','image/jpeg'
                 , 'png','image/png'
                 , 'ppt','application/vnd.ms-powerpoint'
                 , 'rtf','application/msword'
                 , 'vsd','application/visio'
                 , 'xls','application/vnd.ms-excel'
                 , 'xlsx','application/vnd.ms-excel'
                 , 'xml','text/xml'
                 , 'application/octet-stream'
             ) mime_type
     , s.content
     , wss_get_username(wd.created_by)
     , wd.create_date
     , wss_get_username(wd.updated_by)
     , wd.update_date
  from alldocstreams s
     , alldocs d
     , wss_documents wd
 where d.doclibrowid = wd.document_id
   and d.listid = wd.tp_listid
   and d.internalversion = s.internalversion
   and d.id = s.id

Notice two important things:

  1. The document binary/BLOB is in AllDocStreams.content
  2. We had to derive the MIME types from the document extension.
  3. wss_get_username is a simple function that maps IDs in SP to the users in the new system.

Disclaimer and Final Words

Everything you see here was done with SQL Developer v3.x, I have no reason to believe it would be any different with SQL Developer 4.x.  I’m sorry to admit I have no clue which flavor or version of SharePoint I was accessing. My best guess is that this was the 2010 version. I also know that SharePoint has a lot of other features that fortunately we didn’t have to worry about. For example, we didn’t have to deal with Web-parts, social networking or wiki-pages.

If you’re facing a similar situation, I hope this helps you and gets you going in the right direction. I would love to hear about corrections or additions you may have to the information here or even better, tell me if this helped you out.

I better get this out of the way. I love Balsamiq Mockups! There, I said it. This post will probably sound like an advertisement, but it’s not. It’s unsolicited and I paid for my own version of Balsamiq. However, for the money (measly $79 for the desktop version) it’s the best tool I know for the job. What is the job you ask? Well, to create wireframes or mockups for your applications. I do know a lot of people that are Balsamiq users already, if you’re one of them, you’re probably not going to learn that much here.

I use Balsamiq all the time for my APEX projects. It’s simple, quick, and easy. It allows me to communicate in a graphical format how I expect an application will look. If the system I’m building is not all that well defined, I always tell my clients “even if my first draft (of the mockups) is wrong, at least we’ll have something to discuss and that will be progress”.

Here’s an example of a mockup page created with Balsamiq and the APEX application equivalent.





Of course, they are not identical, but the end result was awful close if we concentrate on layout and functionality. Balsamiq is meant to have a low-fidelity (Lo-Fi) look. This means that it is not meant to be pixel perfect or represent a real app. It even has a hand drawn look to it. It allows people to focus on features, functionality and layout. I especially like that people will not get distracted by the font you used or the color of the buttons. That all becomes secondary. Also, I have found that you, as a developer and architect of the system, and the users get aligned with the objectives of the system a lot better.

When it comes time to sharing the mockups, Balsamiq allows you to export each page as and individual PNG image. However, where it really shines is exporting a PDF that the users can navigate as if they were using some sort of skeleton version of the application. If you have a button link to another page, the PDF will link the two together. In fact, most UI elements in Balsamiq can link to other pages.  Here’s a small animation showing what I mean. As you click around you navigate the PDF. Notice how you can navigate the tabs of your application.

Navegable Mockup

Navegable PDF Mockup

Balsamiq comes with many UI and layout controls out of the box: labels, text fields, datepicker fields, text areas, radio buttons, checkboxes, select/dropdowns, lists, many icons, field sets (which I use as regions), Data Grids (perfect for Classic or IR reports), Tabs, and many, many more. Just about everything you’ll need to create very convincing mockups of your applications. Once they are built, it’s easier for me to split a project among different developers. We all get to work from a clearly defined blueprint.


I would be remiss if I didn’t mention some of the downsides and limitations. First of all, with the mockups, you are representing the UI of the application, you’re not representing business logic. For example, if a button massages and transforms lots of data, Balsamiq will not help you in describing this data transformation (unless you use it to create a workflow diagram, which you can). It’s also hard to represent how some sections will hide and show based on some user action. To accomplish something like that you need to create a copy of the page and make the necessary edits. This brings me to another downside.  You often duplicate a page and make edits to represent a new state or have a new version (this is very common when representing modal windows). Sometimes you even duplicate this new edit/version and make even more edits. The problem comes when you need to go back to the original to make some change. Then your duplicates, which are now new pages based on the original, will not reflect these new changes. Balsamiq does offer what it calls “Symbols” to help will this. For example, unless I’m 100% sure my tabs won’t change, I will place my browser window element, tabs and navigation bars on it’s own symbol. The symbol is then used on all the pages I create. If the symbol changes, the other pages will immediately reflect this change. It’s sort of using file “includes”.

If you have several roles or rights in your application, you’re probably going to want to create a mockup for the most privileged user. That way you show all the possible elements and available functionality. Sometimes, however, you may need to create a brand new mockup to represent some other role/right, but I find it that this is rarely the case.

Bottom Line

Some people will probably think that building with APEX is already pretty fast and that you can do your mockups directly with APEX.  I find that works fine for simple applications or when the end result is pretty clear to all involved. But, if I’m building something more complex than a breadbox, mockups are ideal.

Creating wireframes allows me to better plan my design. It makes development and communication with the end users and stakeholders a lot easier.  People rarely get disappointed when they see the real application, quite the opposite, the more you include them in the wireframe building process the more ownership they take. It makes my life a lot easier. Even if you don’t use Balsamiq, I would recommend you use something to wireframe your applications. I’m sure you’ll be happy you did.

Here’s my KScope14 presentation: Go-Go Gadget: Learn About Inspectors

I recommend you use the space bar to navigate forward. You can also use arrows.

If you’re interested here’s the companion demo app.

If you prefer, here’s the PDF download also: Go-Go Gadget

The Background

This blog post is drill down follow-up of a feature I talked about in my KScope14 presentation “Go-Go Gadget: Learn About Inspectors”. In that presentation I explained how using the $0 feature in most modern DevTools and Inspectors can be really useful while prototyping Dynamic Actions (DA) in APEX that can be triggered by many elements on a page.

Often, DA act on some user input or user action. For example, when an item value changes, a mouse click, a key press, etc… The triggering element is the element or elements that causes the DA to fire. When the DA is defined on an Item or Button, it’s very clear which element has just caused the DA to run. We only have one element on the page to deal with. However, when the DA uses a jQuery Selector for the selection type it is not uncommon to have multiple DOM objects on the page that could be our triggering element.

DA Button Click

DA Button Click

DA jQuery Selector Click

DA jQuery Selector Click

For the jQuery Selector example above, if we were to run the selector $("a.orderView") from the console we would see a DOM element for each match. If the report had 20 rows on the screen, you would get 20 elements back.


1. Run jQuery selector $(“a.orderView”) 2. Results from jQuery 3. Elements found on page

The DA architecture provides us with an attribute called triggeringElement. Your JavaScript code running in the context of the DA, can simply use this.triggeringElement to reference the actual DOM object of the element that fired the action.

The Example

Lets write a DA that highlights each customer row as the user clicks on them. The highlight will be achieved by adding a class of “current” to all the TD elements of the row that contains the flashlight that was clicked.

Chances are you’re going to need to give things a few tries before you narrow down the code you need. I say to you that all this work can be done from console using $0

Getting to the Point

Open you favorite inspector (I’ll use Chrome for these examples), inspect one of the a.orderView anchors.

Inspect orderView anchor

Then go to the console and type $0

dollar zero

Running $0 in the console

That’s right! $0 is a reference to the last highlighted or selected element in the inspector. We can now prototype our JavaScript code right from the Console using $0 in place of this.triggeringElement. When we’re done, we swap $0 for this.triggeringElement and place the code in the DA.

Working Through The Example

There are many ways to achieve what we want here. We want to add a class to all the TD elements on the row where our element was clicked. I propose we walk the (DOM) tree up to the TR element, then select all the TD and add the “current” class.

So try these in the console:

var $el = $($0);


Line 1 creates $el as a jQuery object to our element.
Line 3 finds the closest TR element. Then we find all children “td” elements for that “tr” and we add the “current” class to all of them.

console prototype

I’ll admit, I didn’t get that short and sweet line of code on my first attempt. However, the beauty is that I could work through it, live on the page, right from the console by using $0.

Then, the final thing to do is to replace $0 for this.triggeringElement and create a True “Execute JavaScript Code” Action to our DA:

var $el = $(this.triggeringElement);


We’re not quite done. At this time, the rows highlight, but they never “turn off”. We’ll add one more simple line that will remove all the “current” classes from the TD elements. The final code, will look like this:

var $el = $(this.triggeringElement);



Prototype your JavaScripe code in the console using $0 and then replace $0 with this.triggeringElement.
Here’s a working demo.


oraclenerd SuperAPEX!

oraclenerd SuperAPEX!

UPDATE Sept 5, 2014: Visit apexmeetups.com to find the most up to date list of meetup groups!

If you’re interested in APEX and you live near Minneapolis/St.Paul, Minnesota, we want to meet you!
Click the RSVP button to attend our first inaugural meetup! RSVP
Here’s the direct link to the first meeting. It is set for 2pm on Thursday June 12, 2014.

Or visit the group and join – it’s FREE: http://www.meetup.com/orclapex-MSP/


The origin of these orclapex-LOCAL groups is the brain child of Dan McGhan and Peter Raganitsch. Read all about it directly from Dan. See what others are doing in their area: Dough Gault in DFW, Scott Spendolini in Virginia.

Oh, here’s some behind the scenes information. The “oraclenerd SuperAPEX” image that has been adopted by several of these local groups is my own selfie.

I herby grant rights for the image to be used in web, email or print to promote any orclapex-LOCAL group. Here’s a link to the small image used by many of the groups.

If you want to get an oraclenerd or a’peks shirt here’s where to find them.

This is the shot for making sure the image is calibrated and color corrected.

This is the shot for making sure the image was calibrated and color corrected. Yes, it did take many attempts to get the final image just right.

List of Groups *

orclapex-NYC (New York City, NY, USA)
orclapex-DFW (Dallas/Forth Worth, TX, USA)
orclapex-MSP (Minneapolis/St.Paul, MN, USA)
orclapex-NOVA (Ashburn, VA, USA)
orclapex-MTL (Montréal, QC, Canada)
orclapex-DUS (Düsseldorf, Germany)
orclapex-MUC (Munich, Germany)
orclapex-Stuttgart (Stuttgart, Germany)
orclapex-Vienna (Vienna, Austria)
* List as of May 30, 2014

Let me know if I missed your group or made a mistake with a name or link.