Modifying APEX Workspace login page

Many APEX developers would love to customise the first thing they see when logging in each day - the workspace login page.

Peter Raganitsch has done some awesome things for the APEX community, and one of them was to provide a script that can spruce up this page.

Trouble is, the APEX development team keep changing the look and feel of the environment, stuffing up training manuals world-wide ;-)
Edit - Peter informs me he has updated his version, I can't remember why I couldn't see it at the time.

By default, the APEX 4.1 login looked like this

APEX 4.1 Workspace Login

 And 4.2 looks like this

APEX 4.2 Workspace Login

Last time I checked, Peter's version hasn't been modified to suit the latest version so I thought I'd tackle this myself and add some improvements of my own - partially to enable some font color changes between environments.

Or just allowing me to put SAGE green in this example, complete with stretched logo. Lucky I thought too late to put a green pig instead.

Customised for SAGE

Here is some sample code you need to add in the APEX Administration (INTERNAL), under Manage Instance -> Login message.

<script src="/i/themes/theme_sage/custom/ApexLib_Loginpage.js" type="text/javascript"></script>
<script type="text/javascript">
apexlib.loginpage.customizeLoginPage
( "SAGE Computing Services"
, "1.5em"
, "darkgreen"
, "Isn't this brilliant?"
, "/i/sage.gif"
, "http://sagecomputing.com.au"
, false
, false
, false
, false
, false
, false);
</script>

And here is the ApexLib_Loginpage.js script with my modifications

//==============================================================================
//  PROJECT: ApexLib - The APEX Library (http://apexlib.soureforge.net/ )
//
//  DESCRIPTION:
//
//    This JavaScript library contains helpful functions used to
//    customize the APEX Workspace Login Page.
//
//  SUPPORTED APEX VERSIONS:
//
//    4.2
//
//  REFERENCED OTHER LIBRARIES:
//
//  AUTHOR(S):
//
//    Original: Peter Raganitsch (http://www.oracle-and-apex.com/ )
//
//    Update for APEX 4.2: Scott Wesley (http://grassroots-oracle.com/ )
//
//  SVN HEADER:
//
//    $Id: $
//
//==============================================================================

//==============================================================================
// Initialize the namespace for ApexLib.
//==============================================================================
if (!window.apexlib)    window.apexlib    = {};
if (!apexlib.loginpage) apexlib.loginpage = {};

//==============================================================================
// Global variables used by most of the functions.
//==============================================================================
apexlib.loginpage.sVersion = "1.0";

//==============================================================================
// used in the "Login Message" to customize the APEX Workspace Login Page
//==============================================================================
apexlib.loginpage._customizeLoginPage = function
  ( pLoginRegionTitle
  , pLoginRegionTitleFontSize
   ,pLoginFontColor
  , pLoginRegionText
  , pLoginRegionLogoImage
  , pLinkLogoToHref
  , pShowLoginMessageRegion
  , pShowWorkspaceRegion
  , pShowGettingStartedRegion
  , pShowCommunityRegion
  , pSocialNetworkingRegion
  , pShowOracleBanner
  )
{
  // if user passed a region title we remove the Oracle-Application-Express image
  // and display the given string instead (in the given font size which is 2.2em by default)
  if (pLoginRegionTitle)
  {
    //var vLoginTitle$ = jQuery("img[src$='apex-logo-white.gif']").parent();
    var vLoginTitle$ = jQuery("div.aLoginHeader h1");
    vLoginTitle$.text(pLoginRegionTitle);
    if (pLoginRegionTitleFontSize)
      vLoginTitle$.css("font-size",pLoginRegionTitleFontSize);
    else
      vLoginTitle$.css("font-size","2.2em");
    if (pLoginFontColor)
      vLoginTitle$.css("color",pLoginFontColor);
    else
      vLoginTitle$.css("color","black");
  }

  // identify the big APEX Logo image, this is used to navigate inside the login region
  var vLoginRegionLogoImage$ = jQuery("img[src$='apex-db-apps.png']");

  // shall we replace the default explanation text below the login-button?
  if (pLoginRegionText)
  {
    //vLoginRegionLogoImage$.parents(".rc-content-login").children("div").eq("3").children("div").html(pLoginRegionText);
    var loginText$ = jQuery("div.aLoginIcon p");
    loginText$.html(pLoginRegionText);
  }

  // does user want to display his company logo instead the APEX Logo?
  if (pLoginRegionLogoImage)
  {
    vLoginRegionLogoImage$.attr("src",pLoginRegionLogoImage).removeAttr("width").removeAttr("height");

    // should the logo be a link to a specified address?
    if (pLinkLogoToHref)
    {
      vLoginRegionLogoImage$.wrap("<a href='"+pLinkLogoToHref+"' target='_blank'></a>");
    }
  }

  // if user doesn't want to show the Login-Message-Region we hide it (display:none)
  if (pShowLoginMessageRegion === false)
  {
    //jQuery(".htmldbBodyMargin").children("div").eq(1).children("div").eq(1).css("display","none");  // APEX 4.1

    // APEX 4.2 remove yellow notice
    jQuery(".notice").css("display","none");
  }

  // if user doesn't want to show the Workspace Region we hide it (display:none)
  if (pShowWorkspaceRegion === false)
  {
    //jQuery("a[href^='f?p=4550:7:']").parents(".rounded-corner-region").css("display","none");  // APEX 4.1
    jQuery("a[href^='f?p=4550:7:']").parents(".borderlessRegion").css("display","none");         // APEX 4.2
  }

  // if user doesn't want to show the Getting Started Region we hide it (display:none)
  if (pShowGettingStartedRegion === false)
  {
    //jQuery("a[href^='f?p=4600:6:']").parents(".rounded-corner-region").css("display","none");
    jQuery("a[href^='f?p=4600:6:']").parents(".borderlessRegion").css("display","none");
  }

  // if user doesn't want to show the Community Region we hide it (display:none)
  if (pShowCommunityRegion === false)
  {
    //jQuery("a[href='http://forums.oracle.com/forums/forum.jspa?forumID=137']").parents(".rounded-corner-region").css("display","none");
    jQuery("a[href='http://forums.oracle.com/forums/forum.jspa?forumID=137']").parents(".borderlessRegion").css("display","none");
  }

  // if user doesn't want to show the Social Networking Region we hide it (display:none)
  if (pSocialNetworkingRegion === false)
  {
    //jQuery("a[href='http://forums.oracle.com/forums/forum.jspa?forumID=137']").parents(".rounded-corner-region").css("display","none");
    jQuery("a[href='http://twitter.com/oracleapexnews']").parents(".socialNetworking").css("display","none");
    //$('.aLoginSubBody socialNetworking').css("display","none");
  }

  // if user doesn't want to show the black Oracle image banner we hide it (display:none)
  if (pShowOracleBanner === false)
  {
    // remove Oracle APEX black banner
    jQuery(".aHeader").css("display","none");
  }

} // apexlib.tool._customizeLoginPage

//==============================================================================
// used in the "Login Message" to customize the APEX Workspace Login Page
//==============================================================================
apexlib.loginpage.customizeLoginPage = function
  ( pLoginRegionTitle          // string to be displayed in the region header
  , pLoginRegionTitleFontSize  // font-size for the region title, should include em or px
   ,pLoginFontColor            // Color of region title, eg: green for dev, darkorange for test
  , pLoginRegionText           // replaces the explanation text inside the login-region, can include HTML
  , pLoginRegionLogoImage      // URL pointing to an image with max size 240x200px
  , pLinkLogoToHref            // when user clicks the new Logo, this URL will be opened
  , pShowLoginMessageRegion    // true/false: show the Login Message Region (the yellow one)
  , pShowWorkspaceRegion       // true/false: show the Workspace Region
  , pShowGettingStartedRegion  // true/false: show the Getting Started Region
  , pShowCommunityRegion       // true/false: show the Community Region
  , pSocialNetworkingRegion    // true/false: show the Social Networking region
  , pShowOracleBanner          // true/false: show the black Oracle image top banner
  )
{
  // make sure, that the whole document (page) is loaded before changing it
  jQuery(document).ready(function(){apexlib.loginpage._customizeLoginPage(pLoginRegionTitle,pLoginRegionTitleFontSize,pLoginFontColor,pLoginRegionText,pLoginRegionLogoImage,pLinkLogoToHref,pShowLoginMessageRegion,pShowWorkspaceRegion,pShowGettingStartedRegion,pShowCommunityRegion,pSocialNetworkingRegion,pShowOracleBanner);});
}

Hopefully blogger doesn't butcher the syntax to make it difficult to copy. Let me know if you have problems, or download the file here.

And thanks again to Peter for the awesome groundwork for this option to add style to your APEX environment.

Scott

APEX Tree region use case - Privileges

While experimenting with the APEX tree region, I came up with a use case that demonstrated some of the related features, as well as providing some useful information about the roles defined in my database.

I will note that to do so my parsing schema required access to DBA_ROLE_PRIVS and DBA_TAB_PRIVS - something that was fine in my development environment, but would be rightly questioned in a production scenario, but I will demonstrate nonetheless.

Final layout

My page looks like the following screen grab, and below I break down the steps to get there.

The tree lists roles assigned to user set in the text item, and the accompanying report region displays object privileges for selected tree node / database role.

Tree region and partnering classic report.

Define tree region

First step would be to create a new page based on a tree region.

Accept the defaults, or enter attributes as desired - such as tree template.

Once you get to defining the table or view, I just selected anything because I'm going to override the query once I'm done anyway - so from there I left all attributes as default and created the page.

Edit the page and open the tree attributes to use the following query.

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status
  ,level
  ,granted_role||NULLIF((SELECT ' ('||COUNT(*)||')' FROM dba_tab_privs WHERE grantee = aa.granted_role),' (0)') title
  ,null icon
  ,granted_role value
  ,null tooltip
  ,'javascript:pageItemValue('||apex_escape.js_literal(granted_role)||')' As link
FROM dba_role_privs aa  
CONNECT BY grantee = PRIOR  granted_role
START WITH grantee = UPPER(:Pn_USER)

Updated to include apex_escape.js_literal

Create supporting items

Now the page is defined we can create some supporting items.

Create text item Pn_USER - this will accept a username to drive the tree query. You could also define this as an Autocomplete item, using the query for the LOV

SELECT username FROM all_users

Then create a hidden item Pn_SELECTED_NODE, source always replacing session state. This value will be set by the Link field in the query, which calls some JavaScript to be defined on your page.

Edit page properties

Edit page and set JavaScript function declaration as

function pageItemValue(somevalue)
{
  $s('Pn_SELECTED_NODE', somevalue);
}

This sets the value of the selected node in a hidden field, which we can listen for changes to refresh another region that displays the relevant object privileges that may be granted to that role.

Create classic report

This report will show the "Object Privileges" for the selected tree node - the selected role.

SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = :Pn_SELECTED_NODE

Once created, edit region grid layout to display as desired - I set "Start new row" to No.

Create dynamic action

When the user selects a node, we want the neighboring report to automatically refresh, so we create a dynamic action that listens for change to the selected node.
My dynamic action has the following properties:
Event: Change
Item: Pn_SELECTED_NODE
No condition
Action: Refresh region "Object Privileges"

Dynamic action screengrab

And that's it!

Scott

Review: Expert Oracle Application Express Plugins

In Oracle APEX there are probably a few broad classifications of technologist, and I think to create plug-ins as an APEX shared component is probably one of them.

While I have consumed many plug-ins, I haven't created any that aren't security related. And just quietly, the security plug-ins are a fair simple encapsulation of a known entity.

If you want to learn how to create plug-ins using the tools available, I would recommend reading Expert Oracle Application Express Plugins, by Martin Giffy D'Souza.

I started a technical review for this book, but had to pull out due to poor timing - super busy with too many other projects. Publishers apress kindly sent me a copy of the book regardless, which I read on train rides to work.

Chapter 1, Introduction

Fairly short but concise description of what is covered in the book. Again due to poor timing, Martin was unable to include the authentication & authorisation plug-ins - but on fundamentals alone you could build these no problems after reading half the book.
So it covers dynamic action, item, region & process plugins; and mentions to the reader you should be reasonably versed in PL/SQL, JavaScript, jQuery, CSS & HTML - which generally the demarcation from a typical APEX developer.

Chapter 2, Plug-in Fundamentals

Using some simple examples, it builds a pretty good picture on the required infrastructure for a plug-in.

Chapter 3, Item Plug-ins

A very thorough guide to building a date-picker item plug-in, probably the most popular & UI related plug-ins you'll encounter. I've had a few ideas & needs for item plug-ins, such as a Forms style combo-box that operates as a select list of an LOV as well as a free text input. If I had the time, this chapter would be my "right hand man" for building it.
As with much of the book, it's very syntax heavy - so be prepared for that. However, it's quality code - well structured and  is a good reference.
You are also introduced to the console wrapper in this chapter.

Chapter 4, Dynamic Action Plug-ins

First Martin does well to describe how dynamic actions work, then goes into an example of creating a modal dialog.

Chapter 5, Region Plug-ins

Again Martin sets the picture and talks about the importance of AJAX. He then pushes us in to the deep end by building a region to display titles from an RSS feed.

Chapter 6, Process Plug-ins

Process plug-ins are a great example of basic software engineering, where we can encapsulate complexity and increase reusability - the major tenant of plug-ins. Martin uses this to create the black box that is to send a message to a mobile phone. He doesn't forget to include the technical requirements for this using SMTP.

Chapter 7, Best Practices and community

The best thing about plug-ins is you don't need to write them, you can just consume them as part of the community. But of course, they introduce the concept of "cost of not writing the plug-in". Martin also covers security, instrumentation, commenting, templates, versioning, compression & error handling - lots of goodies ;-)

Chapter 8, Debugging & Tools

After meeting Martin in Melbourne last year, I found one of his APEX strengths is instrumentation, debugging & coding design. This chapter takes a much closer look at the JavaScript Console Wrapper, APEX debug, browser tools, jQuery UI Widget factory, the Apache HTTP server and finally the APEX APIs & data dictionary.

Summary

Certainly a very technical book on APEX plug-ins, but to become an effective plug-in developer you would expect nothing less. Even as a consumer of plug-ins, it wouldn't hurt to have a better understanding of how they work & fit together. Certainly, if you end up having to write one, this is is the best reference out there.

Related Links

Community Plug-ins
Oracle Plug-ins
Commercial Plug-ins

Scott

9 things about AUSOUG WA in 2013

The Western Australian AUSOUG page has recently been updated to detail some activities for 2013.

Some points I'd like to mention specifically:

1. February 28th - Kick off the user group year with Connor McDonald for a Thursday morning breakfast at Oracle's Kings Park office.
   As you can see, we've already scheduled a number of events - more to be announced!
   
2. Website - a long awaited upgrade to the national website is due this year. This will help us provide further services for members in a modern format using APEX. It will facilitate easier site maintenance for the  committee so we can concentrate efforts on, say, a more complete & polished mobile application for the conference.
   
3. Twitter - @AUSOUG_WA is where you'll find announcements, updates, and other things worth having on your Oracle twitter list.
   
4. Presentation skills - and more. SAGE will assist those interested again this year, contact us for more info.
   
5. Student memberships - We are targeting student groups this year to assist them with their future in IT. Ursula is your first point of call. Let us know if you have ideas for outreach.
   
6. Conference - finalising venue/dates now - send through any ideas you may have to make it the best one yet. We're looking to change things up a little this year.
   
7. Branch meetings - every month we hold an event with food & a speaker. These are great ways to meet & network with other in your community.
   
8. Membership - If you're not sure if you a member, ask us. Many Perth companies hold corporate membership so you may already be covered. If not, an affordable tax-deductible annual membership may be worth your while.
9. Have fun - I feel I'm lucky enough to enjoy what I do for a living. However you approach this year, be sure to have fun doing so.

writing.galaxyzoo.org/6uu2i2/

I look forward to an active and progressive 2013 for the WA user group. I encourage all readers to also take part and be pro-active within your Oracle community - wherever you live.

Scott Wesley
WA AUSOUG Committee Web Master & Conference Program Chair

Thursday thought - QR codes

Have you ever played with QR codes?

We used them at our SAGE booth at the Perth conference to link to our conference beta mobile application. I stumbled across one idea recently, possibly via Martin - my electronic signature

My nail in the head? #matrixreference

This gave me a sobering thought - it's another brick in the wall towards a world where we communicate with each other with a twitter-esque environment but with a tightly coupling of electronics & biology, and this is the stamp on our painting, so to speak, detailing whatever information will be required to identify us.

Too much?

Syntax differences between JavaScript & PL/SQL

I'm sure anyone who's worked with multiple programming languages - particularly at the same time - can attest to the occasional slip-up.

It's just unfortunate that they are just so hard to debug. Take these two statements

console.log('id:' || this.attr('id'));
console.log('id:' +  this.attr('id'));

Both are valid JavaScript statements & both work - in that the execute without error, but one returns the ID attribute value as debug output.

Thanks to Tom in the forums for helping me see what I missed
https://forums.oracle.com/forums/message.jspa?messageID=10654246#10654246
For those with a strong, habitual PL/SQL background such as myself, I think making this mistake at one point was pretty much inevitable.

Basically the first one accepts to expressions within an OR statement, while the second concatenates the two strings as desired.

It makes me wonder how many times strange things like this happen with human language. I'm sure there are some good analogies out there.

I certainly don't envy those people constructing multi-lingual applications - not that much demand here down under :-)

Scott