Random Insights into Oracle

Not happy Jan - follow up

2011-10-02T22:09:00.000-07:00

A while ago I posted Not Happy, Jan! about the changed behavior of list templates for hierarchical lists.

The changed behavior in APEX 4.1 meant my atypical hierarchical template which produced JSON instead of HTML lists no longer worked.

I've re-implemented the template to handle APEX 4.1. To see it in action, and how it works go to http://apex.oracle.com/pls/otn/f?p=PLAYPEN:2025

Dynamic Actions - Changed behavior in APEX 4.1

2011-09-27T05:05:00.000-07:00

APEX 4.1 Dynamic Actions have a changed behavior over 4.0 version when executing a Static Assignment or JavaScript Expression against multiple elements.

Reading that back makes it sound sufficiently obscure that it may not be an issue for you. Hopefully not, but let's run through the changed behavior, the reasons behind it, how to detect it and how to resolve it.

Firstly, some background.
As part of a demo on dynamic actions, I set the value of 3 items using a dynamic action, as shown below:

This worked perfectly fine until I upgraded to 4.1, where the first items' value was set, and the remaining item values are set to null.

Comparing the Dynamic Actions JavaScript for setValue (JavaScript Expression) code for 4.0 to 4.1 reveals why the change occurs.

apex_dynamic_actions_4_0:

setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lResult = eval(lJavaScriptExpression);
            if (lResult){
                this.affectedElements.each(function() {
                    $s(this, lResult);
                });
            }
        }
    },

apex_dynamic_actions_4_1:

setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lJSExpression = eval(lJavaScriptExpression);
            this.affectedElements.each(function(i){
                // Set the value to the first affected element, null the rest.
                lValueToSet = (i===0?lJSExpression:'');
                $s(this, lValueToSet, null, lSuppressChangeEvent);
            });
        }
    },

As the comment indicates, the changed behavior is by design.

Reasons behind the change
Anthony Rayner from the APEX development team was kind enough to explain the reasoning behind the change:

This is indeed changed behaviour as you describe, introduced in 4.1. I don't know if you've seen this already, but we added a section to the release notes on just this. I have copied this below for your convenience:

4.9 Dynamic Action Set Value with Multiple Affected Elements

In dynamic actions, prior to Application Express 4.1, it was possible to use the Set Value action to set multiple Affected Elements, but only to the same value, not different values. In Application Express 4.1, we introduced the following new capabilities to set multiple values from dynamic actions:

Action 'Set Value' > Set Type 'SQL Statement' - You can now define a SQL Query to return between 1 and 100 columns, which is used to set the 'Affected Elements' in the order defined (1st column is used to set 1st affected element, and so on). Previously, this only supported querying for 1 column, but could be used to set multiple affected elements to that same 1 column value.

Action 'Execute PL/SQL Code' - A new 'Page Items to Return' attribute that sets any page item values on the page to their updated values in session state.

With the introduction of some built-in options to retrieve multiple values, Application Express no longer supports the ability to retrieve 1 value that could then be used to set multiple page items. This was done for simplicity and usability. If you had defined a 'Set Value' dynamic action that set multiple Affected Elements to the same value, here is how you can fix this, according to the 'Set Value' dynamic action's 'Set Type':

Static Assignment - Define additional 'Actions' for each Affected Element, setting them all to the same value.

JavaScript Expression - Define additional 'Actions' for each Affected Element, setting them all to the same value.

SQL Statement - Alter your SQL Statement such that it selects the same column value, with different column aliases for each Affected Element.

PL/SQL Expression - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.

PL/SQL Function Body - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.

So really this change was prompted by the introduction of support for setting multiple different values from 1 'Set Value > Set Type (SQL Statement)'. This introduced a 'positional' notation of sorts for this set type, whereby affected elements map, by position to values returned from the SQL Statement. We felt with this introduction, that it would be simpler not to continue to support the setting of multiple affected elements to the same value, but rather that if this is desired, that one of the above techniques should be used instead, depending on set type. We felt this in the long run was simpler and more intuitive and that hopefully this would have minimal impact when upgrading from 4.0.

Hmm, that makes the reasoning behind the change a lot clearer. I really like the idea of being able to use a single dynamic action to update a bunch of fields at once.

For example, you could change the shipping method for goods delivery. Using a Dynamic Action with a "PL/SQL Code" action type you could update the shipping charges, subtotal amount, expected delivery date and terms & conditions all at once. Nice!

How to detect if this impacts you

Running the following query in 4.0 or 4.1 will identify any dynamic actions that are affected:

select a.workspace
      ,a.application_id
      ,a.application_name
      ,a.page_id
      ,a.page_name
      ,a.attribute_01
      ,a.dynamic_action_name
      ,a.action_name
      ,a.action_sequence
      ,a.dynamic_action_event_result
      ,a.affected_elements
  from APEX_APPLICATION_PAGE_DA_ACTS a
 where a.action_code = 'NATIVE_SET_VALUE'
   and a.attribute_01 in ('JAVASCRIPT_EXPRESSION','STATIC_ASSIGNMENT')
   and a.affected_elements_type = 'Item'
   and instr(a.affected_elements, ',') > 0;

Adjusting for the changed behavior
In my example I already have the value I want to assign, the most appropriate alternative here is to use a JavaScript Expression. Instead of defining a single Dynamic Action to affect multiple items, just create multiple actions each updating a single item as shown below.

APEX CSS Repository: How to include background images easily using Data URIs

2011-09-11T05:28:00.000-07:00

One of the challenges with loading CSS files into the APEX CSS Repository is dealing with references to background images.
In the screenshot from my demo site, I'm using a series of background images for the toolbar items.

Typically when you are hosting your own web-server, your web assets (images, CSS and JavaScript files) are loaded onto the web-server.
The CSS file will contain references to background images like:

.ico-add {
    background-image: url( ../images/icon/add.gif ) !important;
}
.ico-delete {
    background-image: url( ../images/icon/delete.gif ) !important;
}

The CSS rules reference the background-image by specifying a relative path to the image location.

If you loaded the CSS file into the APEX CSS Repository, you need to adjust the path to the image location to use an absolute path like:

.ico-add {
    background-image: url( /ux/playpen/resources/images/icon/add.gif ) !important;
}
.ico-delete {
    background-image: url( /ux/playpen/resources/images/icon/delete.gif ) !important;
}

Adding a path alias to your web-server config to resolve the path on the server completes the task:

<Context path="/ux"       docBase="C:\playpen\web\ux" reloadable="true" crossContext="true" />

It's more complicated on Hosted APEX websites
On a hosted APEX website like apex.oracle.com you don't have access to the web-server so you need to load the files into the APEX Repository. Let's also assume you don't have access to 3rd party web-server to host the files either :)

So now your images, JavaScript and CSS files are loaded into the Repository and the page templates have been updated to include the JavaScript and CSS files by using the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags:

<html>
<head>
    <title>#TITLE#</title>
    #HEAD#
    <link rel="stylesheet" href="#WORKSPACE_IMAGES#playpen.css" type="text/css">
</head>

Ideally, you would like to use the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags within the CSS file:

.ico-add {
    background-image: url( #WORKSPACE_IMAGES#add.gif ) !important;
}
.ico-delete {
    background-image: url( &WORKSPACE_IMAGES.delete.gif ) !important;
}

But APEX 4.1 and earlier versions do not substitute either the hash tag or substitution variable alternatives shown.

This leaves two less desirable options:

embed the stylesheet directly in the page template, which resolves the substitution tags but adds overhead to every page, or,
replace the #WORKSPACE_IMAGES# reference with it's resolved name in the CSS file
e.g. #WORKSPACE_IMAGES#add.gif becomes http://apex.oracle.com/pls/otn/wwv_flow_file_mgr.get_file?p_security_group_id=441224701954687600&p_fname=add.gif
This makes the application less transportable.

Data URIs to the Rescue

Data URIs provide a way for data to be included inline in a web page as though it was an external resource. The data URI format RFC 2397 is pretty simple:

data:[<MIME-type>][;charset=<encoding>][;base64],<data>

Encoding our previous example looks like:

.ico-add {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZ3AHe8YHO6XDSBLIe4cF+eUKDZlq/fppfVi6vUnLTZparUnW61WFaYSJbUi4/CfTmFMH2+Z73ktpbTiXeuY0CJNnnBZNTrz4e4cbXbqI7MhHzDa7jdr1OWRZXUimyqWkKNNrvcr3i0ZIm+eIrCec7pyVCZQXy6ZKbQloXNeZPNiKDZlWirU3DBY0KLOODy3LbdrESMOrLbqInIf42+e53YknG+X3rGa2WrVYHMdXS3XLfgr7PZpZfMhTSCLMXowJTTirfaqrTapqXbm2+1WLXfrKbPlm+1WX25a3WsYT2INGajVmuqWH6yaXyxZzeEL0OPOHqvZUeOPLPdqYO1bZnRi5jMinm9bsvmw37Hb6TOlGmlWH25an28Zn7BabTdqobJejyHMozLgV2cTY7Sg5LFgICzatzx2K/fpX+5bXS3W5jMh4rPfYbJfNfu0oS2bmqsW77itleeRXK5W5jLhpfHhXC4Wf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHcALAAAAAAQABAAAAengHeCg4SFhnduZU0Th4MXWVcWJBtoWocDIC4RQgVnPiMEhQNAZjQpGQ0dYzpHDIR0bSo/MnZ2KBpYRHGDUy8GYWxWtW8AAVRkUYJMcAdftc92dV0KLYJQUms40LV1EAhJgkhVEjYsN9x1cjwzD4MrXhU1ANwLOUFPhEpbMcV1RkNpdnjoUUhMCAxquJiYk2CJgEMcSjg4UUTEh4eN7sCgAMZJxo+DAgEAOw==) !important;
}
.ico-delete {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZwAPaEbPqdi7dKK+6Sj+BpavGdlvCMd/eTfvmSfvnLxPm5r9pkYfJ3Y99qavqjke2TjupcUMZVQsxbTPWmofi2qONuY8tZSbxQMviMdvSOgbxLMNFeU+l/eOl7cL5PNsFNM/3c2PNzXfupnPiUf/qah/q6rulbT/KtqvzHuupmXOqDff3b1PCRf89hU8RQNNpjX+R4cfivpOZaUu5mUvCFcNZgVPjDvvWjn/aroverocxSPPqTf/SinvN5Y++GfuljW/iGcPqrneNrYeh6deBlXPzUzfSgnuJwZrhKKuZWTOJ7dcBTOvaCaPCDe+6MgddiXN1qaPBkVu6KdfnTzPGcluZYTNxkY7pLLtBPPt5qZ8dXRfWvpb5TONddUfOno71RNeZWUO6EffeRfrhOLttpZPWsofGOevS0qttWS+ZZTtljXeddWe5qXuxfVOlZTf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHAALAAAAAAQABAAAAeigHCCg4SFhnBQVgtqh4MNDzZTCWdHG4cEJyAlDgEiKB0ShQReKyQsZiMIGBQVWoQqRQEHUgC1AD0xWINZW0EGbcDBDBkcS4JkCjs0b8zNbmwFHoIvOUBMzc4pVBeCT05iIW7i4yZNSleDaDhRM+NuVTI3LoQtQmVtEG5pSWA8NUiFLBCZ4OPHmjBGugg4FEHHkAEDYHxY2AgOly8axlTcOCgQADs=) !important;
}

While it looks much more complicated and bloated, Data URIs provide a performance benefit because they reduce the number of HTTP requests, allowing pages to load faster.

Most modern browsers support data URIs:

Firefox 2+
Opera 7.2+ - data URIs must not be longer than 4100 characters
Chrome (all versions)
Safari (all versions)
Internet Explorer 8+ - data URIs must be smaller than 32k

For IE6 and IE7 you need to use a separate browser dependent CSS file, but the rapid decline in usage for these legacy browsers will remove the issue over time.

How to encode Data URIs in your CSS file
I'm using CSSEmbed by Nicholas C. Zakas, a Java command line utility that reads in a CSS file, identifies the images referenced within, converts them to data URIs, and outputs the resulting style sheet.
The basic syntax is:

java -jar cssembed-x.y.z.jar -o <output filename> <input filename>

To learn more about the utility see Nicholas's blog on Automatic data URI embedding in CSS files.

Oracle Real World Performance Day

2011-08-19T17:34:00.000-07:00

I've just got back from attending the InSync11 Oracle Real World Performance Day held in Sydney, Australia.

Absolutely inspiring day, with 3 renowned Oracle performance experts in Tom Kyte, Andrew Holdsworth & Graham Wood sharing the stage the entire day, giving their insights from the developer, dba and system architect perspectives.

The format was very conversational, with a discussion about the principles behind a topic, examples the presenters have encountered and then demonstrations of how changing values or coding approaches impacts performance. There is nothing quite like seeing a live demonstration of row by row processing compared with set based processing, or how setting open_cursors too high can bring down performance.

Materials from the presentations are available:

The day started with a data warehouse story of an ETL performance issue, with the networking team, dba and BI teams in a room, each saying their piece of the puzzle was fine; the database was idling, network was performing fine and the ETL process hadn't changed, but overall performance had recently degraded...

Deja vu, at this point a DBA colleague from work and I looked across at each other - we had been it this exact position a few months earlier. The discussion from the experts around this was insightful; if only they had been present when we were tearing our hair out trying to solve the issue.

The day only got better from there on. Tom even mentioned the APEX 4.0 and Library Cache Latch Contention issue in Oracle DB 11.2.0.2.

If you get the opportunity to attend a Real World Performance Day as they tour the globe, I highly recommend it.

APEX 4.1: Not happy, Jan!

2011-08-01T17:46:00.000-07:00

"Not happy, Jan!" is a line from a popular TV advertisement that entered the Australian vernacular.
The advertisement showed a manager expressing her displeasure towards the fleeing figure of one of her staff, as she ran having neglected to book an ad in the upcoming year's Yellow Pages.

While I'm looking forward to using the new APEX 4.1 release and welcome the many new features available, my "Not happy, Jan!" moment came when the recently upgraded apex.oracle.com website broke my application navigation.

For a number of years, I've been using hierarchical list templates to output JSON rather than HTML. This has been very useful allowing me to generate data for a JavaScript tree component using the APEX builtin conditional list entries and authorization to control displaying tree nodes.

Changed functionality now means that the "Between Sub List Items" template attribute is not output unless the corresponding "Sub List Template" attribute contains a "</li>" tag.

Why is this necessary?
I guess its to do with changed functionality to support dynamic SQL lists or mobile themes, but couldn't the developer just leave the template field blank for some templates?

Its possible to code around the issue using plugin functionality, but for now my site is broken until I update it.

I think the APEX team are terrific and do a really great job, but perhaps they got this one wrong.

Packt Publishing discounts on Oracle books

2011-07-18T04:18:00.000-07:00

Just noticed that Packt Publishing are celebrating the publication of its 50th Oracle book.

They are offering some attractive discounts on all their Oracle books.

Buy any Oracle print book and get 20% off
Buy any Oracle eBook and get 30% off

There is also a competition to win a year's free subscription to their online library, so a good time to pick up a book or two.

Updated Code Bundle for "Oracle Application Express 4.0 with Ext JS"

2011-06-17T20:04:00.000-07:00

For those of you patiently waiting for the updated code bundle, its finally available.

The bundle includes the template application shown above.
All you need to do is install the application, including the supporting objects, and then upload the web assets and Ext JS library to your web server.

When you download the code, just extract the highlighted code bundle, the other folder is the original code bundle which can be discarded (I don't know why they included it).

One last change...

Add the following CSS rules to the playpen-book.css file:

.ux-table input[type="text"] {
    background-color: #FFFFFF;
    background-image: url(/ux/extjs/resources/images/default/form/text-bg.gif);
    background-repeat: repeat-x;
    border: 1px solid #B5B8C8;
    padding: 2px 3px 0;
    line-height: normal;
}

.ux-table input[type="text"] {
    height: 18px; 
    vertical-align: middle;
}

Gremlins, they're everywhere.

Book Released: Oracle Application Express 4.0 with Ext JS

2011-03-31T03:56:00.000-07:00

I'm very happy to announce that my book Oracle Application Express 4.0 with Ext JS is now officially published.

A printed copy turned up in the mail recently, and I'm very pleased with the final result.

The table of contents for the book as well as a sample chapter is available on the Packt website.

Sue Harper blogged about the three phases of book writing:

before the book - Seems like a good idea
during the book - Not a good idea, will this ever end? Who's idea was this anyway?
after the book - That was easy ... what's next?

After spending most evenings and weekends writing until midnight for the last 15 months, I've definitely done the first two phases, but haven't quite reached the last phase yet.

Still to do
The code bundle available to download on the Packt website includes code from each of the chapters.
I will be updating it in the next couple of weeks to:

include the Ext JS library, so the stand-alone examples work when the bundle is unzipped
add the APEX TEMPLATE application, CSS, JavaScript and image files used in the book
include a few extras that didn't make it into the book.

I'll write another blog post when the updated code bundle is available.

Then after that my demo site definitely needs an overhaul, it's looking sadly neglected...

Bookmarklets to extend APEX 4.0

2011-03-10T05:50:00.000-08:00

APEX allows you to add help text for your pages at a page level, and also an item level.
Your only given a plain textarea to add your text, as you can see in the following screenshot.
Hint APEX team: can we change this in the next version?

One way to replace the textarea with the APEX Rich Text Editor is to use a Bookmarklet, which allows you to embed JavaScript code in a browser bookmark.
Then simply click the bookmarklet to convert the textarea to a Rich Text Editor when needed:

Simply drag the following links onto your browser bar to save the bookmarklets:

The source code for the Page Level Help bookmarklet is:

javascript: (function(){
    function getScript(url, success){
        var script = document.createElement('script');
        script.src = url;
        var head = document.getElementsByTagName('head')[0];
        var done = false;
        /* Attach handlers for all browsers*/
        script.onload = script.onreadystatechange = function(){
            if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) {
                done = true;
                success();
            }
        };
        head.appendChild(script);
    }
    /* Code to execute after script loaded */
    function run(){
        apex.widget.ckeditor3("#F4000_P4301_HELP_TEXT", {
            "language": "en",
            "toolbar": "Basic",
            "toolbarStartupExpanded": true,
            "skin": "kama"
        });
    }
    /* Main */
    if (!window.CKEDITOR) {
        getScript('/i/libraries/ckeditor/3.2/ckeditor.js', function(){
            run();
        });
    }
    else {
        run();
    }
})();

All we are doing is adding the ckeditor.js file when it's not present, and then converting the specified page item to use the basic CKEditor.
For the advanced version replace "toolbar": "Basic" with "toolbar" : "Full".

You may need to adjust the item reference if your not using Application Express 4.0.2.00.07, or a translated version.

APEX_PUBLIC_USER password expires on 11g

2011-03-03T14:45:00.000-08:00

Just read Scott Wesley's post on APEX Listener issues which was being caused by Oracle 11g automatically expiring passwords.

The cleanest solution is to use an unlimited password lifetime profile, and assign to APEX_PUBLIC_USER.

This is done simply by:

CREATE PROFILE UNLIMITED_PASSWORD_LIFETIME LIMIT
  SESSIONS_PER_USER DEFAULT
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME DEFAULT
  IDLE_TIME DEFAULT
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS DEFAULT
  PASSWORD_LIFE_TIME UNLIMITED    /** this sets unlimited lifetime */
  PASSWORD_REUSE_TIME DEFAULT
  PASSWORD_REUSE_MAX DEFAULT
  PASSWORD_LOCK_TIME DEFAULT
  PASSWORD_GRACE_TIME DEFAULT
  PASSWORD_VERIFY_FUNCTION DEFAULT;


ALTER USER APEX_PUBLIC_USER
 PROFILE UNLIMITED_PASSWORD_LIFETIME
 ACCOUNT LOCK;

Web page optimization with Page Speed

2011-01-30T04:11:00.000-08:00

I've only recently become aware of Google's Page Speed Firefox add-on integrated with the Firebug extension.

Page Speed is Google's equivalent of Yahoo's YSlow, also a Firefox add-on that needs the Firebug extension to work.
Like YSlow it tests the performance of your webpages against a set of criteria, giving an overall score for a page as well as advice on how you can improve your page performance.

Not surprisingly many of the recommendations are similar to Yahoo's, but some are different, giving you an additional perspective on website performance.

As well as telling you how to improve page performance, there are tools built-in to assist you. Page Speed automatically optimizes JavaScript, CSS, and image files referenced from a page when you run the analysis, saving them to a local directory. Drilling into a rule, such as Minify CSS tells you the potential reduction in size as well as a link to the optimized version.

Page Speed also has mod_pagespeed, an open-source Apache module for Apache 2.2 that automatically optimizes web pages and resources on them. I haven't investigated this, but it would be interesting to experiment with.

I use both YSlow and Page Speed, each tool has something unique to offer. Page Speed is a useful addition to your toolbox for web development.

Plugin: Re-sizable columns and save preferences

2010-12-21T05:39:00.000-08:00

A very quick post, with an example of an APEX GridPanel Plugin used to convert a APEX Standard Report into an ExtJS GridPanel.

The Plugin implements the following functionality:

drag-and-drop to re-arrange columns
check-box menu to show or hide columns
resizable table to change width
resizable columns
save settings and restore defaults.

IE has a nasty habit of caching the page, so make sure you do a refresh to retrieve the saved layout.

Full details of how to implement are in chapter 9 of my book Oracle Application Express 4.0 with Ext JS

Illumination for Developers

2010-12-14T02:38:00.000-08:00

Just wanted to let people know about an awesome Firebug addon called Illumination for Developers created by Steven Roussey.

It makes Firebug know about ExtJS components and it's an absolute gem.

In this screenshot from Steven's blog you can see the Ext components on the left, and their properties etc on the right.

Currently it supports ExtJS, Sencha Touch, Dojo, SproutCore with plans for qooxdo, jQuery UI and YUI.
It's early days for it's development, and it will be a commercial product, but looks like a great addition for your toolbox.

BTW, I have no affiliation with the product and don't know Steven. I'm just impressed with the concept.

Book "Oracle Application Express 4.0 with Ext JS" released

2010-11-03T05:59:00.000-07:00

I'm very pleased to announce my book "Oracle Application Express 4.0 with Ext JS" has just been released as a RAW book by Packt Publishing.

RAW simply means the book is still being written, with 8 of 11 chapters completed so far. You're getting access to new chapters as they are being completed, in an unedited format.

A RAW book is an ebook, and this one is priced at 40% of the usual eBook price. Once you purchase the RAW book, you can immediately download the content of the book so far, and when new chapters become available, you will be notified, and can download the new version of the book. When the book is published, you will receive the full, finished eBook.

For details on the book visit the Packt website.

If you're not familiar with Ext JS, take a look at the Sencha website.

APEX combined with Ext JS allows you to create visually impressive web applications easily. Integrating the Ext interface into a APEX theme and powerful widgets as APEX Plug-ins allows you to give your users a much richer Web 2.0 desktop experience.

Writing the book has been an opportunity to revisit every aspect of integrating the two technologies, with the benefit of over 3 years experience combining the two.

My knowledge has deepened along the way, and I'm happy to be able to share what I've learnt with you.

Why do go to conferences?

2010-10-28T21:43:00.000-07:00

One of the really nice things about working with Oracle APEX is the sense of community you gain through the forums with people scattered all over the globe.

Attending OpenWorld this year was a great opportunity to met face to face with several members of the APEX development team and APEX enthusiasts after communicating with them via forums and emails. It's really surprising how much personality comes through in the written word.

One of the strengths of APEX is how accessible the APEX development team is to the community. When you visit the APEX stand in the exhibition hall, the people manning it are from the APEX development team - you just can't get better information than directly from the person who developed the functionality.
They really are interested in your feedback and making APEX better.

Leading up to OpenWorld I had contacted some of the team members asking for a small enhancement for Plugins.
A few emails later, they had agreed to include the functionality in a future release.
Catching up with Patrick Wolf at the APEX booth, we talked a bit more on the subject with a couple of different approaches, before landing on a final solution.

Anyway, I'm looking forward to the 4.02 patch which includes my enhancement request - bug# 10157646.

So that's why I go to conferences - to meet up with people I know online, talk shop, share ideas, and learn new stuff I can use at work.
How about you? Why do you go?

The next conference I'll be going to is the AUSOUG 2020 Conference in Perth, Australia on Nov 22-23.

Maybe I'll see you there.

Extend APEX 4.0 JS using trigger events

2010-08-15T01:31:00.000-07:00

APEX 4.0 has introduced JavaScript "trigger events" such as apexbeforerefresh, apexafterrefresh and apexbeforepagesubmit.
I'm using them to show a "loading" mask on regions when Partial Page Refresh (PPR) is enabled by clicking on the next rows button, or clicking on the heading to sort by a column.
You can see a live demo of Over-riding APEX JS and using APEX event triggers.

The JavaScript to add this effect is very brief, but there's a lot of concepts to understand.
To implement just copy into your application's JavaScript.

// Copy the APEX function to a new name, so we can override the original.
var apexInitReport = initReport;

// Extend the APEX function by over-riding with our own version and adding functionality
initReport = function(pRegionID, pInternalRegionID, pStyleMouseOver, pStyleChecked) {
   // we can call still call the original (renamed) version to use the existing functionality
   apexInitReport(pRegionID, pInternalRegionID, pStyleMouseOver, pStyleChecked);

   // APEX wraps PPR regions with a DIV with id report_xxx_catch, xxx being the region number
   var c = 'report_' + pInternalRegionID + '_catch';

   // proceed if the PPR region exists
   if (apex.jQuery('#'+c).length) {
      // find the parent DOM node for the PPR
      var p = apex.jQuery('#'+c).parent();

      // Add a bind event to the parent, since the wrapper is deleted on each refresh action.
      // This uses jQuery "live" binding, taking advantage of event bubbling up the DOM hierarchy.
      // ExtJS has more a advanced Event handler.
      apex.jQuery(p).bind('apexbeforerefresh', function(){
          // add a mask when any PPR actions happen 
          // NOTE: this is using the ExtJS library, jQuery users will need to do more work here.
          new Ext.LoadMask(c).show();
      });

   }
}

Over-riding the original initReport function means I don't have to add a call to it in my pages - APEX already does that for me.
Another advantage is that it is automatically applied to all my PPR reports, regardless of what report template I use.

Those of you with sharp eyes will note the "Go to row" item in the bottom of the screenshot.
I've added extra functionality to allow me to jump to any row I like.
I'll be discussing this and other ideas at OpenWorld this year, as well as in my upcoming book on APEX 4.0 with Ext JS.

APEX 4.0 joining the chorus for phasing out IE6

2010-02-27T18:42:00.000-08:00

Good to see APEX 4.0 has joined a growing chorus of vendors phasing out support of older browsers such as Internet Explorer 6.
Of course, it's up to you to take up the message in your apps.

Facebook requires its users to upgrade to Internet Explorer 8 before creating an account and YouTube has announced that it will stop supporting IE6.

Google recently announced the phasing out of support for IE6 for for Google Apps, claiming "We’re following other companies that have done the same, like Twitter, Facebook and Microsoft for Office Web Apps."

APEX 4.0 EA2: Improved debugging, my new favorite feature

2010-02-26T16:41:00.000-08:00

Just started looking at EA2, and like very much the new improved debugging.

First of all it's now in a popup window, which is a really good move.
With web pages relying more and more on javascript to render page components and rearrange page layouts, the old "inline" debugging was no longer effective.

I love the performance graph at the top, you really can't miss what areas are taking the longest time to execute.
Hovering over an item in the graph, shows the step number and name, so it's very quick to diagnose potential problems.

APEX is really coming of age!

Oracle APEX 4.0 + Ext JS = Happy Days

2010-01-21T05:11:00.000-08:00

2010 is going to be a great year!

I've taken Oracle APEX 4.0 for a spin over the last couple of weeks having a look at the new plugins functionality.
So far I've really only scratched the surface, but like it very much so far.

After integrating Ext 3.1 into a theme, I created a couple of plugins following Patrick Wolfs' post on how to create a plug-in. You can see the result above.

What's really nice about it, is now we can define attributes for the plugin, which can be assigned to the page item, as shown below for the Spinner.

So now I can choose my "spinner" item type and define the attributes without having to write a single line of javascript. Nice!

The Application Builder incorporates nice touches with the "Settings" region also.
For the spinner example, I have set the "Decimal Precision" fields visibility to be dependent on "Allow Decimals" value being true.
Selecting false, automatically hides the "Decimal Precision" field.

So if you haven't already done so, register a workspace at http://tryapexnow.com and give Oracle APEX 4.0 a go.
The APEX team are looking for feedback and suggestions on how to make the product even better, and they really do use your feedback.

My New Years Resolution is to write a book on "Oracle APEX 4.0 with Ext JS".
I've already arranged a publisher, and the first chapter is well underway - but more about that in another post.

With the new plugins functionality in APEX 4.0 and the highly customizable components in Ext JS, you'll be building amazing web apps faster than ever.

Like I said before, 2010 is going to be a great year!

Another Oracle APEX + Ext JS website

2009-12-14T03:09:00.000-08:00

Munky (Ben Burrell) has just got his demo site up and running after a short delay caused by falling off a cliff!

He's very active in the APEX forums, so I'm looking forward to some interesting content in the new year.

Other APEX + ExtJS sites to follow:

Matt Nolan: e-DBA blog and APEX Plugin Registry demo
Tobias Arnhold: APEX at Work blog and APEX at Work demo

If anyone else has APEX + Ext JS demo sites running, please let me know.

How to create a Loading Indicator...

2009-12-13T03:24:00.000-08:00

How to create a Loading Indicator...

CJ recently asked how to implement a loading indicator to hide the page layout changing regions into tab panels on my post Tabbed Regions in Oracle APEX using Ext JS.
While I use Ext JS to do it on my demo, here is how to do it using plain old javascript:

Create a div element which has width and height 100% at the beginning of your page.
Include an animated gif in a floating div to make it look like something is happening.
Include the rest of your page and javascript below the div.
Delete the div and image after a short delay using some javascript at the end of the page.

And here's the example html:

<html lang="en-au">
<head>
  <title>Page loading indicator</title>
  <!-- standard stylesheets -->

<style type="text/css">
/*Loading Indicator*/
#loading-mask{
  position:absolute;
  left:0;
  top:0;
  width:100%;
  height:100%;
  z-index:20000;
  background-color:white;
}

#loading{
  position:absolute;
  left:45%;
  top:40%;
  padding:2px;
  z-index:20001;
}

#loading .loading-indicator{
  background:white;
  color:#555;
  font:bold 13px tahoma,arial,helvetica;
  padding:10px;
  margin:0;
  text-align:center;
  height:auto;
}
</style>

  <script type="text/javascript">
      function removeElementById(el) {
          var node = document.getElementById(el);
          if (node) {
              node.parentNode.removeChild(node);
          }
      }
      function removeLoading() {
          removeElementById("loading");
          removeElementById("loading-mask");
      }
  </script>
</head>

<body>
  <div id="loading-mask" style=""></div>
  <div id="loading">
    <div class="loading-indicator"><img src="http://extjs.cachefly.net/ext-2.3.0/resources/images/default/shared/large-loading.gif" width="32" height="32" style="margin-right:8px;" align="absmiddle"/>Loading...</div>
  </div>

  <!-- include everything, including scripts after the loading indicator -->

  <p>Here is some content that will appear when the loading indicator has been removed.</p>

<script type="text/javascript">
    setTimeout("removeLoading()",250);
</script>
</body>
</html>

Quickest way to delete unused templates in Oracle APEX

2009-12-10T04:41:00.000-08:00

Best practice is to remove unused/unwanted templates from your application.
This encourages standardization in your application, which benefits both your users and developers.
It also reduces the size of your application.

But how to do it quickly?
APEX deliberately makes you delete them one at a time, a reasonable sanity check for novices.
But not very helpful if you know exactly what you want.

So here's how:

Export the theme
Use the splitter utility following instructions in apex/utilities/readme.txt
Comment out the unwanted templates as shown above
Import the theme from the sql command line.

And best of all, it's iterative, so you can repeat it as often as needed!

Note: It's not the quickest way - but it is the quickest supported way.

Oracle APEX: Silly Developer toolbar tricks

2009-11-18T06:01:00.000-08:00

At the AUSOUG National Conference in Melbourne on the weekend I presented on "Oracle APEX Themes and 4.0 Plug-ins".

The presentation was about creating a Theme in APEX from scratch, and just to make it interesting I did it using jQuery UI, and integrated the ThemeRoller into my theme.

Audience members were curious how the developer toolbar was moved into the header.

I blogged how to Move the Developer toolbar with ExtJS back in 2008, but since learnt an even easier way - CSS:

table[summary="Developer Toolbar"] {position:absolute;top:10px;left:25%;z-index:1000;}

This positions the toolbar 10 pixels from the top, 25% of the way in from the left margin.

One of the audience members suggested copying it, so you could have 1 copy at the top of the page, and one at the bottom.
So, ditch the CSS, and use the following jQuery code to copy the toolbar to a div.

This time add the following div to the top of your page template:

<div id="topDevToolbar"></div>

Then add the following javascript into your page:

$(document).ready(function(){
$('table[summary="Developer Toolbar"]').clone().appendTo("#topDevToolbar");
});

Now you have 2 toolbars!

JQuery UI confirmed in Oracle Apex 4.0

2009-09-22T03:20:00.000-07:00

Oracle indicated that jQuery would be included as part of Oracle Apex 4.0 last year at OpenWorld, and this is stated in the current roadmap.

The question is whether Oracle were planning to use jQuery UI or not still remained unanswered.
The recent Apex 4.0 video and powerpoint by Mike Hichwa and David Peake has a slide at the beginning making me think that they would be including the jQuery UI.

Today Tyler Muth confirmed that jQuery UI is definitely included as discussed in detail in this forum post.

So, does this leave ExtJS out in the cold?
Well not really, as I indicated earlier in the same forum post, because ExtJS has a plugable adaptor framework which can use jQuery as a base layer.
This allows you to use jQuery for the non-visual aspects and ExtJS on top of it, allowing you to use the best features of both.

The other rumour I heard, is a beta of Oracle Apex 4.0 is very close to going live, but that’s unconfirmed.

Apex Modal Windows with updates...

2009-09-19T03:29:00.000-07:00

LOL - and just when I thought I'd get away with being lazy...

Here's an example to keep Vikas happy

Apex Modal Windows are a snap using ExtJS

2009-09-18T05:55:00.001-07:00

A number of people have been posting about modal windows on the Oracle Apex forum of late.

Some of the solutions provided have used jQuery UI, or using window.showModalDialog.

I’m not a fan of window.showModalDialog as a quick Google search shows it is not supported by all browsers.
The jQuery UI version is functional, but the visual appearance doesn’t live up to the ExtJS standards in my opinion.

Anyway have a look at the Modal Popup Window example and see what you think.
To call the popup window I simply add the highlighted onClick text to my edit link as shown below.

You’d have to agree it doesn’t get much simpler than that.

Oracle APEX: User friendly exceptions and logging

2009-06-10T05:28:00.000-07:00

One of the bug-bears of Oracle Apex has been intercepting unplanned exceptions and displaying a user-friendly message. I've decided to tackle the issue using a little AJAX to capture the error and retrieve a "nice" user-friendly message. It also gives me the opportunity to log the error, as well as relevant session information. Check out Friendly Exception Messages with source code. The code uses Apex built-in javascript, so can be used on any application (no need for ExtJS).

Another Oracle Apex ExtJS demo site

2009-06-09T05:11:00.000-07:00

Tobias Arnhold has just released a Apex-ExtJS demo site APEX at Work as well as his blog.

It's well worth a look as Tobias is using a different ExtJS theme and is using more of the standard Apex builtins.

I look forward to seeing what he comes up with in the future.

Hopefully this may encourage a few others to demo their Apex-ExtJS solutions - I've seen screenshots from a number of other sites using ExtJS with Apex with really nice functionality.

ExtJS customized Viewport for Oracle APEX

2009-04-29T05:30:00.000-07:00

One of the issues you may experience with ExtJS, is that it moves html DOM elements around when it renders the page.

For example if your using tab panels or the Viewport. This can result in your form elements ending up outside of the form.
You can solve this by using allowDomMove:false as discussed in this Apex Forum thread or solve the problem once and for all by using a customized version of the Ext.Viewport.

I like the second option better, so here is the solution.
1. Replace references to Ext.Viewport with Ext.ux.FormViewport (see below) in your javascript.
2. Add the following style to your css #wwvFlowForm {width:100%; height:100%;}

See also Building a complete Oracle Apex page template using ExtJS

Enjoy Mark

/*
* Mark Lancaster Wednesday, 16 January 2008 12:00 PM
*

/**
* @class Ext.ux.FormViewport
* @extends Ext.Container
* A specialized container representing the viewable application area (the browser viewport).
* 

The Viewport renders itself to the wwvFlowForm element, and automatically sizes itself to the size of
* the browser viewport and manages window resizing. There may only be one Viewport created
* in a page. Inner layouts are available by virtue of the fact that all {@link Ext.Panel Panel}s
* added to the Viewport, either through its {@link #items}, or through the items, or the {@link #add}
* method of any of its child Panels may themselves have a layout.

* 

The Viewport does not provide scrolling, so child Panels within the Viewport should provide
* for scrolling if needed using the {@link #autoScroll} config.

* By default, when applyTo is not specified, the Viewport renders itself to the document body.
*/
Ext.ux.FormViewport = Ext.extend(Ext.Container, {
/**
* @cfg {Mixed} applyTo
* The id of the node, a DOM node or an existing Element that is already present in
* the document that specifies some panel-specific structural markup.By default, when applyTo is not
* specified, the Viewport renders itself to the document body.
*/
initComponent : function() {
   Ext.ux.FormViewport.superclass.initComponent.call(this);
   document.getElementsByTagName('html')[0].className += ' x-viewport';

   // applyTo specified element
   this.el = Ext.get('wwvFlowForm');
   if (!this.el) {
       this.el = Ext.getBody();
   } else {
       // disable scroll on body
       Ext.getBody().dom.scroll = 'no';
   }

   this.el.setHeight = Ext.emptyFn;
   this.el.setWidth = Ext.emptyFn;
   this.el.setSize = Ext.emptyFn;
   this.el.dom.scroll = 'no';
   this.allowDomMove = false;
   this.autoWidth = true;
   this.autoHeight = true;
   Ext.EventManager.onWindowResize(this.fireResize, this);
   this.renderTo = this.el;
},

fireResize : function(w, h){
   this.fireEvent('resize', this, w, h, w, h);
}
});
Ext.reg('FormViewport', Ext.ux.FormViewport);

Book Review: Learning Ext JS

2009-03-26T05:29:00.000-07:00

I recently took the opportunity when approaced by Packt Publishing to review "Learning Ext JS" by Shea Frederick, Colin Ramsay, and Steve Blades.
The book is produced as an "eBook", delivered in a PDF format with the book examples available in a zip file.

For readers not familiar with Ext JS, it is a cross-browser JavaScript library for building rich internet applications. It features highly attractive customizable UI widgets supported by a well designed, documented and extensible Component model.

The book is written with the experienced developer in mind. It's all about getting productive as quickly as possible. We've all been there, thrown into an unfamiliar technology and expected to perform miracles overnight. In these situations you can find yourself floundering, looking for some starting guidence while you try to find your feet.

The book is focused very specifically on Ext JS, so if you've never dealt with HTML or javascript before, I'd suggest a second reference to cover that as well.
Having said that, if your just working with Ext JS for your presentation interface, the book is complete enough to get you up and running.

After an obligitory introduction chapter, and some foundation concepts, your quickly working with Ext widgets. Very soon your creating Ext JS forms, applying textfields, datefields, combos, toolbars and validations. The very popular Grid an Tree components are also well covered, with Layouts appearing surprising late in the book.

I enjoyed the flow of the book, which is written in an easy reading light hearted style. The examples clearly explained the concepts, and raised a few chuckles at the same time. Examples that included server-side code assume a PHP backend, a fairly typical implementation for Ext JS.

I work with Oracle Application Express and PL/SQL, never the less the examples were easy to follow if you focused on the format of the server output rather than the implementation language.

Overall, if your starting out with Ext JS, or struggling understanding the Ext JS examples and scattered forum posts, this book is a good reference.
Chapter 7: Layouts is available as a sample chapter.

APEX Global AJAX variables - gotta love them

2009-03-20T06:17:00.000-07:00

I've just added a fully AJAX enabled editable tree on my demo site.
All modifications to the tree are transmitted to the database via an application process using AJAX.

Values are submitted using wwv_flow package variables:
g_widget_name
g_widget_mod
g_widget_action
g_widget_action_mod
g_widget_num_return
g_x01 .. g_x10

The application process simply calls a database package, which references the wwv_flow package variables and updates the data.

The key to AJAX functionality in Oracle Apex is based around these "Global input variables for AJAX utilities".

Using these variables, a little javascript to call a simple application process and then we handover to PL/SQL - back in every Oracle developers comfort zone.

This example has a fair bit going on, to see a simpler example check out:

My demo using extJS
Tyler Muths demo which uses jQuery
Carl Backstroms demo using native Apex javascript.

Oracle Apex templates - not just for HTML

2009-02-26T03:41:00.000-08:00

Have you ever considered using templates for generating something else besides HTML? You can use Apex templates to generate all sorts of things - XML, javascript and JSON to name a few. The tree panel shown gets all its data from a list template. See it in action, and the template details on my demo.

ExtJS Grid in Apex (with pagination)

2008-12-10T05:58:00.001-08:00

Check out the ExtJS Grid with Pagination on my demo site

So how does it work?

This is brief, because it's after midnight.

Use a Named Column (row template) for the Report to build the grid array data - see page source code for output.
The query needs to return the data in a JSON escaped format, so use a "SQL Query (PL/SQL function returning a SQL query)" with generic column names The function call looks like:

begin
return json_api.grid_query
  (p_region_id => 4367612871540792
  ,p_query =>'select * from geo_domains'
  );
end;

The query returned limits the rows and JSON escapes the data, much the same way as Apex does internally (hint: dbms_sql). Also - I set pagination to none, as its handled by my code.
Pressing next data set on the displayed page fires off a AJAX request (application process) which passes the region_id as well as start, limit and callback function values.
This executes a database package to re-run the query and return a paginated rowset. You can see the returned data format using Firebug.
The grid updates the data using builtin ExtJS functionality.

Next example will be an updatable grid - also near completion.

Nighty night - Mark

ExtJS Grids in Oracle Apex

2008-11-26T05:04:00.000-08:00

Here is a read-only ExtJS grid in Oracle Apex. I'm experimenting with grids and it's coming together quite well - more detail in the future. Try drag-and-drop, and resizing column headings, then use pagination - very nice.

Using ExtJS Calendar and CSS issues

2008-11-26T03:02:00.000-08:00

Mack McLendon emailed me with an issue he was having with the ExtJS calendar in Apex. He had successfully got the date functionality working, but as you can see from the screenshot he sent, the calendar is offset to the right. Mack wrote further, that disabling the standard Apex CSS and javascript fixed the problem, but at the expense of everything else on the page. Luckily I was able to answer this easily - having been through the same painful experience myself.

The Solution
The issue is caused by the following ul margin rule in apex_3_1.css

ul { margin:0 0 0 15px; padding:0;}

The solution is simple - make sure you reference the ext stylesheet after the apex stylesheet (#HEAD# tag) in your page template.

Ext includes some "reset" styles to ensure uniform appearance between different browsers, including the following rule:
ul {margin:0;padding:0;}

Because the ext stylesheet comes after the apex stylesheet, the apex style will be overridden, and the calendar appear correctly. Related info:

Oracle Apex: Boost performance with a CDN

2008-11-20T02:47:00.000-08:00

Ext has recently partnered with CacheFly, a global content network, to provide free CDN hosting for the Ext JS framework. See more details on the announcement.

A content delivery network (CDN) is a collection of web servers distributed across multiple locations to deliver content more efficiently to users.
The server selected for delivering content to a specific user is typically based on a measure of network proximity.

For example, the server with the fewest network hops or the server with the quickest response time is chosen. i.e. using a CDN to deliver static content, such as Ext javascript, css and images will result in your pages downloading significantly faster.

I've modified my demo site on apex.oracle.com to see the performance difference. Previously I was pulling the Ext content from extjs.com - using the CDN gave a significant performance boost, making the demo site load much faster and give it a much snappier feel.

To implement was a snap, just referencing the Ext content in my Oracle Apex page templates from the CacheFly site:

 <script type="text/javascript" src="http://extjs.cachefly.net/ext-2.2/ext-all.js"> </script>
<link rel="stylesheet" type="text/css" href="http://extjs.cachefly.net/ext-2.2/resources/css/ext-all.css">

And if that isn't sweet enough, using the Ext’s Build It! tool will automatically install your custom version of the Ext library on the CacheFly website.

So after implementing, I wanted to see how much better my YSlow score was now I was using a CDN. Bugger - no difference - it wasn't detecting the CDN, so a quick check on why, and how to fix. Something to think about...

Multiple Interactive Report Regions in one page?

2008-11-15T04:14:00.000-08:00

Here's how to get multiple IRR's in one page. Cheat - just use iframes. See this example, which includes an iframed IRR in a popup ExtJS window.

Oracle Apex IRR in ExtJS Popup Window

2008-11-09T02:45:00.000-08:00

Dimitri Gielis emailed recently asking if I'd embedded an Ineractive Report Region (IRR) in a ExtJS popup window.

Well no I hadn't - but it turned out to be straight forward in the end, as you can see in this demo.

He raised an interesting point that when he tried the IRR javascript failed (filtering, sorting etc).

I believe Dimitri had been referencing the apexir_WORKSHEET_REGION div when embedding the IRR in a window, whereas I added an extra div tag around the IRR and used that instead.

Such a small difference, such a big impact. The next screenshot shows some of the ExtJS windows html with my extra div tag wrapping the IRR.

Here is the html when referencing the apexir_WORKSHEET_REGION div.

So what's happened?

The apexir_rollover div (containing the filters etc) is no longer just below the apexir_WORKSHEET_REGION div. The relative positioning of these divs makes a difference.

Applying ExtJS functionality to existing html often results in html tags being moved around. Something to remember when you get unexpected behaviours.

Oracle Apex Spellchecker not working for you?

2008-11-07T04:42:00.001-08:00

If your a "textareas with spellchecker" look like this when you do a spellcheck, the solution is easily explained.

One of the parameters being passed is the language parameter, found in Shared Components > Edit Globalization Attributes.
In my case I have the language set to en-au (English - Australian). This is nice if you want your date-pickers to start the week on Mondays, but not so good for the spell-checker.

So how do we fix this?
It's just a case of populating the WWV_FLOW_DICTIONARY$ table for your language.
So for me the following script does it:
insert into wwv_flow_dictionary$ (language, owner, security_group_id, words, words_capitalized, words_soundex) select 'en-au', s.owner, s.security_group_id, s.words, s.words_capitalized, s.words_soundex from WWV_FLOW_DICTIONARY$ s where s.language = 'en-us'; commit;

Now if I was really being thorough, I'd go through all the words and fix the American spelling of words.
But after all, I am a developer and we're generally not known for our spelling :)

Thank you Carl - you will be missed

2008-10-30T04:05:00.000-07:00

It was very sad news to read Dimitri's blog yesterday that Carl Backstrom had a car accident on Sunday and passed away.

Carl and I had been in regular contact by email over the last 18 months. He was very generous and accessible with his time, a truly great guy. I count myself fortunate in meeting up with him in person at Open World this year.

He was very enthusiastic on the 4.0 release of Apex, and spoke of all the upcoming work around AJAX functionality and jQuery integration.
His enthusiasm was infectious. Only a week or so ago, I was emailing him of plans to create a jQuery demo playpen for next year.

Sometimes life really doesn't seem to have a sense of justice.

Carl was taken from us much much too young, but not before he left his mark on the world.

Vista Style Toolbar in Oracle Apex

2008-10-15T05:30:00.000-07:00

This post is short and sweet. If you want to know how to create a "vista style" toolbar in Apex see http://apex.oracle.com/pls/otn/f?p=200801:2012:0 It's easy to customize it, if you like the interactive behaviour, but not the look.

Oracle OpenWorld - done and dusted

2008-09-26T08:38:00.000-07:00

Oracle OpenWorld is over for another year, and it's time to review the key messages for Oracle Apex.

This was my first visit to OpenWorld, my previous experiences limited to downloading presentations.

So was it worth flying all the way from Australia to see it in person?
Absolutely - you get so much more from hearing and talking directly to the presenters.

Standouts for me: Tom Kyte's presentation on database schema design.
I'd downloaded much of the content of his presentation from other events, but had missed the power of the message. Hearing and seeing the demo really opened my eyes - I'll be reviewing my database design when I get back to work.
Tom will be presenting at the AUSOUG conference in October, so delegates to the Australian event are in for a treat.

Carl Backstrom's presentation on Web 2.0 functionality coming in Oracle Apex.
It was great to hear the enthusiasm about the upcoming functionality from Carl. If your not already starting to incorporate AJAX functionality into your applications, it's definitely time to get your head in that space.

I caught up with Carl on the Demo grounds, to see the Web-Sheets functionality, which takes interactive reports to a whole new level. It allows interactive AJAX based editing directly in the report, as well as allowing end users to define and share sheets.

Carl was also very excited that the Oracle legal team have given him the go-ahead to use jQuery in Apex. It means he can focus on higher level AJAX functionality, rather than getting down and dirty with the DOM. Apex 4.0 is very focused on delivering declarative functionality to developers, so they don't have to know AJAX.

And finally, meeting and networking with Apex users. With 33 presentations related to Apex, the enthusiasm for Apex can't be underestimated. Time after time, presenters shared their success stories on Apex, on how quick the development time was, how short the learning curve was, and the value add it gave to their organisations.

How to make the Oracle Apex calendar start with Monday

2008-08-25T04:04:00.000-07:00

BHavesh recently asked on my blog how to get a date picker which shows Monday as the first day.

The default apex date picker shows Sunday as first day. But I can see on your example, the default apex date picker has Monday as first day. Is there a way I can also make my default apex date picker to show monday as first day.

Good question BHavesh, I wasn't entirely sure how I did it either. It wasn't a conscious decision on my part, but a quick look with session debugging on revealed the answer, as you can see in the image below.

The NLS_TERRITORY is set to "AUSTRALIA". A quick look in the manual revealed

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.
This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.

So there's your answer - the territory setting establishes the week numbering. And where to set it in Oracle Apex? See below.

Oracle Apex - AJAX form created from Javascript

2008-08-15T04:28:00.000-07:00

One of the really nice features of using ExtJS is just how easy it is to create a form using javascript.

Combining it with the new Apex global variables which Carl Backstrom blogged about in May gives some very interesting opportunities.

It means it is now possible to have functionality sitting in a application javascript library ready for use on every page.
This is exactly what is happening in IRR reports, and the upcoming Websheets functionality.

Combine this with the ability to interrogate your application using the Apex dictionary views, and suddenly the world is your oyster.

Have a play with AJAX Form using Globals on my demo site.

I'm using the same concept for a AJAX editable tree, which I'll be talking about at Oracle OpenWorld this year.
Provided I finish building it... :)

Here's a screenshot of current progress. Mark

Selecting Apex page items by class using $x_ByClass

2008-07-24T17:17:00.000-07:00

I just noticed an $x_ByClass example on Carl Backstrom's demo site which selects page items using the class attribute.

So why is this important?

It provides you with a mechanism to easily tag form items, typically text input fields, so you can add AJAX functionality to them.
In Carl's demo he is simply changing the the border color of datepicker items, as a proof of concept. This is exactly the same technique I use with ExtJS to:

convert text items into javascript datepickers
create AJAX lookup fields
make textareas resizable (strictly speaking I select by itemtype)

All I have to do is assign a class to a field, and generic javascript code fires looking for the class and applying the functionality. What's really great is Oracle is building this javascript infrastructure directly into Apex.

It means that developers can easily hook in widgets and AJAX functionality, because the low level plumbing has been done for us.

Mark

Oracle Apex Builder CSS fix for Firefox3

2008-06-19T05:27:00.000-07:00

Does Firefox3 do this to your Oracle Apex Builder?

The fix is to disable a CSS attribute for the "fieldset.lov" class, as seen here using Firebug

To permanently fix the CSS, modify the apex_3_1.css file. Search for "fieldset.lov" and delete the "height:1em;" attribute.

I haven't tested to see if this has other undesirable side affects, but so far so good.

Mark

Tabbed Regions in Oracle Apex using Extjs

2008-06-13T04:18:00.000-07:00

David from Scotland emailed asking how I implemented tabbed regions on my demo site.

It's actually very easy using Extjs.

Step 1 - Create a region template which uses static ids
<div id="#REGION_STATIC_ID#" class="tab-content">
<div class="x-fieldset-bwrap" style="padding:10px">
<div align="right" class="x-fieldset-tbar">#CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#</div>
<div class="x-fieldset-body"> #BODY# </div>
</div>
</div>

Step 2 - Create your report/form regions as usual, but assigning them a static id.
I tend to use tab1, tab2, ..., tabn - but that's a personal choice.

Step 3 - Add an opening div tag before the first tab region and a closing div tag after the last region. <div id="pageTabs"> </div>
You can either create extra regions of type "html text" with no template, or simply add the extra tags in your region header and region footer. I use extra regions, so it's really obvious whats going on when I look at again in 6 months time. When the page is generated the html will look like this:
<div id="pageTabs">
    <div id="tab1" class="tab-content">  </div>
    <div id="tab2" class="tab-content">  </div>
    
    <div id="tabn" class="tab-content">  </div>
</div>

Step 4 - Add the javascript magic Here I'm showing 2 tabs, and usually include the script with the closing div tag.

<script type="text/javascript">
Ext.onReady(function(){
       var tabs = new Ext.TabPanel({
           cls: 'prism',
           applyTo: 'pageTabs',
           plain: true,
           width: 795,
           //height: 450,
           autoHeight:true,
           enableTabScroll:true,
           autoScroll:true,
           activeTab: 0,
           deferredRender: false,
           border: true,
           defaults: {layout:'fit', autoScroll:true},
           items:[
               {contentEl:'tab1', title:'Create/Edit Customer'},
               {contentEl:'tab2', title:'Existing Customers'}
           ]
           });
});
</script>

The cls: 'prism' is a customisation I use to have a blue background for the tab panel.
I'll leave that for you to work out how I did that.

As usual, the documentation on tab panels goes into far more details. Mark

Horizontal Radiogroups in Oracle Apex

2008-05-30T05:02:00.000-07:00

I had a requirement to set out a radiogroup horizontally the other day. Amazingly I'd almost never used radiogroups before in Apex, so didn't know how before searching the forums. The answer is quite simple: just specify the number of columns in the "List of Values" section against the item, as shown below. So that's fine for a static LOV where you know how many columns to specify. What about dynamic LOV's? Have a guess, and then go to my demo for the answer. Mark

Carl Backstrom has created a monster

2008-05-01T06:02:00.000-07:00

Carl Backstrom has created a monster with his forum post on "Enhancement Request Thread : Post 3.1"

The quality of the requests coming through really is testament to the growing maturity of Oracle Apex.
It is obvious that many of the respondents have the depth of understanding of the product that is only developed over time.

Interactive report regions (IRR), was a huge step forward in the 3.1 release.
It really enhanced and simplified implementing query-by-example functionality.
What's more by using AJAX functionality to only refresh the region, rather than the whole page, the user experiences better performance and a more interactive experience.

A particular feature to note is the column value filtering, which performs a lookup based on:
- column values (select distinct)
- pre-defined lov
- datatype specific, e.g. a variety of date based alternatives

So what next for future releases post 3.1?

To my mind the focus should now be on enhancing the data entry side of Oracle Apex.
With the reporting side so much improved, data entry is looking a little tired.

Two quick wins here:
- javascript dates instead of popups
- AJAX lookups.

Implementing javascript dates is already in the statement of direction, so enough said.

AJAX lookups are not currently mentioned, but I'm sure that internally its very much on the radar.
Why am I so confident of this?
Well, mostly because so much of it is already implemented in interactive report regions.

Benefits:
- allows lookups to be performed on large datasets
- allows "auto-complete" functionality i.e. reducing list of values as you type
- better user experience than popup search forms

To see a working example, look at my demo site http://apex.oracle.com/pls/otn/f?p=200801:2007:0

My implementation is based on a holiday table, which includes a customer_id.

The AJAX functionality calls the following procedure:

PROCEDURE customer_lookup
(p_callback varchar2
,p_query varchar2 default null
,p_limit number default 20
,p_start number default 0
,p_customer_id number default null );

The procedure works as described in the following pseudo-code:
if p_customer_id is not null then
-- lookup customer details and return in JSON format
else
-- lookup customers where name like p_query||'%'
-- return n records in JSON format (configurable by p_limit), starting at record p_start
end;

For Apex to implement this:
1. New item type (perhaps AJAX lookup?)

2. Developers would need to be able to define a 3 column lov query against the item
(Nice to have, but 2 column lov OK also)

select value, short_display_value, long_display_value from some_table

3. Mudge existing IRR lookup functionality for query and display on page.

Worthwhile? Whats your opinion?

Mark

Commenting system javascript on my web-site

2008-03-19T17:01:00.000-07:00

I've received a few requests to view the commenting system javascript from my demo web-site.

The javascript is already there for all to see, either by downloading the page or viewing the source directly.

I would definitely recommend using Firefox and Firebug when viewing as it allows you to view attached script files as well as the page source.

The screenshot shows the Script tab in Firebug, listing the script files attached to the current page. In the background you can see the source for the active file.

Most of the javascript for my demo site in contained in a single "application" file playpen.js. The code has been compressed, but not obfuscated. This means you can load it into your favorite text editor and read the full source after you've done some formatting. As far as understanding the code I would highly encourage you to look at Ext 2.0 Samples and Ext Documentation. Like anything worthwhile you need to invest time and energy to gain understanding.

The comment form is based on one of the examples for dynamic forms.

Here is a super-brief explaination, I'm a little time challenged at the moment :)

I use an application process to insert the data into the database.

The javascript to do this is on my demo site - just download the source and then format it to see what's going on.

When inserting it into the database I have to unescape the comment so it will render later. I use dbms_xmlgen.convert(:new.app_comment,dbms_xmlgen.entity_decode) to do this. There may be a better way I haven't thought of. I also strip out some html elements such as script, object, embed etc to prevent malicious attacks.

The javascript then does a PPR to display the comment immediately.

If this doesn't make sense to you, time to do some reading and learning :)

Mark

Implementing Ext Dates in Oracle Apex

2008-03-06T04:24:00.000-08:00

"Anonymous" asked me for more explanation on how I implemented the Ext date-picker in Oracle Apex, as seen in my demo.

Look, I don't mind answering, but please put a name on your feedback - anonymous is just rude.

Here's the plain English explanation:

We are converting a simple html input item into a Ext date-picker, which just happens to be associated with a date field in the database.

To make it easy to identify which input items to convert we assign a class of "date-picker" to the item. The code to do the conversion is shown in my demo.

The only other thing to worry about is making sure the date format of the input item matches the database date format.

Everything else you want to know can be learnt by reading the documentation.

Here are the steps:

1. Set the database date format in Apex
Lets use a mask of DD-Mon-YYYY as there's no confusion internationally.
In Apex 3.1 do this directly in Home>Application Builder>Application 200801>Shared Components>Edit Globalization Attributes.
In earlier versions, you need to create application processes for "On Load Before Header" and "On Submit After Page Submission - Before Computations and Validations" with the following code
execute immediate 'alter session set nls_date_format=''DD-Mon-YYYY''';

2. Create the date item
In Apex create a form on a table containing dates.
Make sure the item type of the date field to display as "Text Field" and not "Date Picker".
Finally add class="date-picker" in the Form Elements Attributes.

3. Add the javascript
Include the Ext javascript files in your page template, cut and paste the javascript code from the demo into your page or page template.

If you run that it all works beautifully.
You'll notice the code allows for alternative data entry format masks e.g "dd/mm/yyyy" and partial formats e.g. "dd/mm".
Thats because the alternate formats are easier for keyboard operators.
See ExtJS documentation on date formats available.

4. Optional step
You may have noticed on my demo I use "DD-MON-YYYY", not "DD-Mon-YYYY" as shown in this blog.
To achieve this I set the database format easily enough, but then had to override the default Ext date names with the following line of javascript in my application javascript file:
Date.monthNames =["JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"];

This is the Ext way on internationalizing dates, so it's not a hack.
What it does is force the javascript date format "d-M-Y" to return the month in uppercase.

Anyway hope this helps.

Mark

Moving the Developer toolbar with ExtJS

2008-02-19T15:24:00.000-08:00

Received and email on my blog, asking how to move the developer toolbar into the "south" region of a typical ExtJS layout. This is one of those annoyances developers see, but the end users don't. For a screenshot see my post from last year here. The solution is very short: In your page template include a div tag with an id, this is where you want the toolbar to end up. e.g. <div id="DeveloperToolbar"></div> Add the following code into your application javascript:


Ext.app.moveDeveloperToolbar = function() {
  var dest = Ext.get('DeveloperToolbar');
  if (dest) {
      var els=Ext.select("table[summary='Developer Toolbar']",false);
      els.each(function(el){
          el.replace(dest);
      })
  }
}


Ext.onReady(function() {
    /** any other stuff you want to include */   
    Ext.app.moveDeveloperToolbar();
});

The code checks the div tag exists, and then searches for a table with a summary property of "Developer Toolbar". For each instance it finds (there is only ever one), it moves it to the destination position. ExtJS has a powerful DomQuery component, well worth learning. See the DomQuery tutorial for further examples. Mark

ExtJS Ajax Search field

2008-02-13T06:09:00.001-08:00

Here is a screenshot of an AJAX search field running in Apex. Putting an example up on the Oracle Apex demo site may not be possible, as I call a PL/SQL package directly. It's based on the ExtJS live search sample. We were running it with Ext version 1.1, but had issues with paging. By default it uses "start" and "limit" as parameters - both reserved words with PL/SQL. If you search the Ext forums, you'll find a solution for Ext 2.0 - just search for mark.lancaster.

Ext JS extension like Forms LOV

2008-02-13T05:47:00.000-08:00

Just noticed on the ExtJS forums that someone has built an extension that behaves like an Oracle Forms LOV. Here is the post and a demo. Old forms programmers never die :)

Building a complete Oracle Apex page template using ExtJS

2008-02-12T04:41:00.000-08:00

Following on from my earlier post on integrating ExtJS we will now build a complete page template.
This template will contain:

a "north" region containing a logo, title, navigation bar and show current user
a "west" region containing 2 sub-regions navigation and settings
a "center" region containing dynamic page content
a "south" region for footer elements, e.g. copyright notices, links etc.

When prototyping its often easier to build using static html pages, and then load it into Oracle Apex.

Create a sub-directory in the /ext-2.0.1/examples/ directory. I've called mine "playpen", but it really doesn't matter.

Save the following text as a html file into the directory, e.g. "apex layout.html".

<html lang="&BROWSER_LANGUAGE.">
<head>
 <title>#TITLE#</title>
 <!-- standard stylesheets -->
 <link rel="stylesheet" type="text/css" href="../../resources/css/ext-all.css" />

 <!-- this style forces form to fit screen height -->
 <style type="text/css">
   #wwvFlowForm {width:100%; height:100%;}
 </style>

 <!-- Ext scripts -->
 <script type="text/javascript" src="../../adapter/ext/ext-base.js"></script>
 <script type="text/javascript" src="../../ext-all.js"></script>

 <!-- application configuration -->
 <!-- eventually move "application" script to an external file -->
 <script type="text/javascript">
   Ext.onReady(function(){

      Ext.state.Manager.setProvider(new Ext.state.CookieProvider());

      var myForm = new Ext.Panel({
        applyTo:'wwvFlowForm',
        layout:'border',
           items:[
               new Ext.BoxComponent({ // raw
                   region:'north',
                   el: 'north',
                   height:45
               }),
               new Ext.BoxComponent({ // raw
                   region:'south',
                   el: 'south',
                   height:32
               }),
              {
               region:'west',
               id:'west-panel',
               title:'West',
               split:true,
               width: 200,
               minSize: 175,
               maxSize: 400,
               collapsible: true,
               layout:'accordion',
               layoutConfig:{
                   animate:true
               },
               items: [{
                   contentEl: 'region_position_02',
                   title:'Navigation',
                   autoScroll:true,
                   border:false
               },{
                   contentEl: 'region_position_03',
                   title:'Settings',
                   autoScroll:true,
                   border:false
               }]
           },{
               region:'center',
               contentEl:'pageContent',
               layout:'column',
               autoScroll:true
           }]
       });

   myForm.doLayout();
   });
 </script>
</head>
<body>
<form id="wwvFlowForm">

<!-- -------------------------------------BODY SECTION start------------------------------------- -->
  <div id="north">
    <table cellpadding="0" cellspacing="0" border="0" width="100%" height="45px">
      <tr>
        <td valign="top">#LOGO#</td>
        <td align="center"><span style="{  }">Integrating ExtJS javascript library with Oracle Application Express</span></td>
        <td width="300px" align="right" border="0" style="padding-right:5px">
          <div style="height:20px">&USER.</div>
          <div id="navigation_bar">#NAVIGATION_BAR#</div>
        </td>
      </tr>
    </table>
  </div>

  <div id="west"></div>

  <div id="region_position_02">#REGION_POSITION_02#</div>
  <div id="region_position_03">#REGION_POSITION_03#</div>

  <div id="pageContent">
    <div id="region_position_01">#REGION_POSITION_01#</div>
    <div id="messages" align="center">#GLOBAL_NOTIFICATION##NOTIFICATION_MESSAGE##SUCCESS_MESSAGE#</div>
    <div id="region_box_body">#BOX_BODY#</div>
    <div id="region_position_04">#REGION_POSITION_04#</div>
    <div id="region_position_05">#REGION_POSITION_05#</div>
    <div id="region_position_06">#REGION_POSITION_06#</div>
    <div id="region_position_07">#REGION_POSITION_07#</div>
    <div id="customize">#CUSTOMIZE#</div>
    <div id="region_position_08">#REGION_POSITION_08#</div>
  </div>
<!-- -------------------------------------BODY SECTION end------------------------------------- -->

</form>

  <div id="south">Some footer content here</div>
</body>
</html>

Open it in your browser and you should see something like this:

Note the "West" panel expands and collapses and is resizable, and the navigation panel is also functional. Resize the width of the West panel, then refresh the page.

Did you notice it kept the new width?

This is all managed by a single line of code:

Ext.state.Manager.setProvider(new Ext.state.CookieProvider());

The panels that make up the page are stateful and remember position, provided a cookie has been registered. Users typically don't even notice unless you point it out to them.

Spend a bit of time looking at the script used to build the layout, consult the ExtJS doco etc.

OK, to make this an Oracle Apex template do the following:

Do a search and replace:

</form> becomes #FORM_CLOSE#

Adjust the path of the javascript and css file links, so if you followed my previous post:
../../becomes /i/themes/ext-2.0.1/

Copy and paste the content into a page template using header, body and footer regions.
The html already contains comments identifying where to cut.

Whip up a quick page to test it out, and barring any mistakes it should work just like the static version.

Enjoy.

Integrating Ext javascript library into Oracle Application Express

2008-02-05T05:52:00.000-08:00

So hopefully by now you’ve been to the website http://extjs.com and had a look at samples http://extjs.com/deploy/dev/examples and are ready to get your hands dirty.

The first step is to download the latest version from http://extjs.com/download and load it onto your http server 
(version 2.0.1 at the time of writing).
For Oracle Apex this can be either:
- Embedded PL/SQL gateway
- Oracle HTTP Server and mod_plsql.

For this article I have loading the complete set of unzipped files to a new folder in the 
apex/images/themes directory:
- http server - ORACLE_BASE/ORACLE_HOME/Apache/Apache/images/themes/ext-2.0.1/
- embedded PL/SQL gateway - /i/themes/ext-2.0.1/

Note: FTP access when using the embedded PL/SQL gateway is enabled as follows:

Open SQL*Plus and connect as SYSTEM


SQL> exec dbms_xdb.setftpport('21');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.  


If you have any issues do an internet search for "dbms_xdb.setftpport", there are numerous posts on the topic.

Once the files are uploaded you can verify using the following urls:
http://hostname:port/i/themes/ext-2.0.1/docs/index.html
http://hostname:port/i/themes/ext-2.0.1/examples/index.html

 
Using Ext with Oracle Apex
The first step to using Ext with Oracle Apex is to reference the javascript libraries and CSS stylesheets in
your page templates.

Start Apex Builder and navigate to a page template:

Home> Application Builder> Application 101> Shared Components> Templates> Edit Page Template

A minimal header definition would look like this:

<html lang="&BROWSER_LANGUAGE.">
<head>
  <title>#TITLE#</title>

  #HEAD#
  <!-- standard stylesheets -->
  <link rel="stylesheet" type="text/css" href="/i/themes/ext-2.0.1/resources/css/ext-all.css" />
</head>
<body #ONLOAD#>
  <!-- Ext scripts -->
  <script type="text/javascript" src="/i/themes/ext-2.0.1/adapter/ext/ext-base.js"></script>
  <script type="text/javascript" src="/i/themes/ext-2.0.1/ext-all.js"></script>

  <!-- application configuration -->
  <!-- add your custom scripts references here -->
  
  #FORM_OPEN#


This gives us access to the entire Ext library functionality. 
Depending on how much Ext functionality your web-site eventually uses you may want to consider only 
including a subset of the library. I’ll write more on this in a later article.

If your using a standard theme that comes with Oracle Apex, you will probably want to keep existing 
javascripts and CSS stylesheets in your definition initially.
 
Let’s build something
The first example I want to demonstrate is resizable text areas.
A demo is available at http://apex.oracle.com/pls/otn/f?p=200801:2001:0


 

Add the following code into your page template, or directly into a page containing one or more text areas.


<script type="text/javascript">
// convert html textareas into resizable textareas
function makeResizable(){
  var els=Ext.select("textarea",true);
  els.each(function(el){
    var dwrapped = new Ext.Resizable(el, {
        wrap:true,
        pinned:true,
        //handles:'s',
        width:el.getWidth(),
        height:el.getHeight(),
        minWidth:el.getWidth(),
        minHeight: el.getHeight()
    });
  })
}
    
Ext.onReady(function() {
  makeResizable();
});
</script>


What this script does search the html document object model (dom) for html textarea elements. 
Html textarea elements are converted to an Ext resizable component, with grab handles pinned (visible). 
The minimum height and width are constrained to the original dimensions.

Uncomment the //handles:'s' line to vertically resize only.

You can read more about the api on your website if you used my instructions at 
http://hostname:port/i/themes/ext-2.0.1/docs/output/Ext.Resizable.html,
or on the Ext website at http://extjs.com/deploy/dev/docs/index.html.

Demo website: ExtJS with Oracle Apex

2008-01-29T03:45:00.000-08:00

I like new year resolutions - it gives me an opportunity to set some personal targets for the year.

This year I've decided to put together a website demoing ExtJS and Oracle Apex.
My initial goal was to have it up and running by the end of January.

The downside of new years resolutions is trying to keep them, but for once I've managed.
My website is up for all to see at http://apex.oracle.com/pls/otn/f?p=200801

Admittedly it's not as complete as I'd hoped, but it's a good start.
Over the next few months I'll be blogging about how I've implemented features and anything else that strikes me as worth writing about.

For now, here's a screenshot, and an invitation to poke around the website.

Mark

Apex Enhancement Request Included

2007-12-11T16:51:00.000-08:00

Got a real buzz today when I saw my feedback on Apex 3.1 beta made it into the product as an enhancement.

Here's the original feedback:

Hi Guys

Got the following error:


ORA-20001: get_report error ORA-20001: get_dbms_sql_cursor error
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
l_query=
select rowid as apxws_row_pk, TABLE_NAME, COLUMN_NAME , count(*) over () apxws_row_cnt

from ( select * from (select table_name, column_name from user_tab_columns) r ) r

where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by rowid


Obviously it's currently looking for rowid, which seems an unnecessary restriction.

You may want rowid for full CRUD functionality, but often you only want read functionality.

And anyway couldn't you get the developer to nominate a single column as the primary key in the report attributes?

Why not replace your current query wrapper with something like this:

select apxws_row_pk, TABLE_NAME, COLUMN_NAME , count(*) over () apxws_row_cnt
from ( select rownum apxws_row_pk, r.* from (select table_name, column_name from user_tab_columns) r ) r
where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by apxws_row_pk

Apart from that, all the additional functionality of interactive reports looks great.

I'll be upgrading and converting by reports as soon as possible.

Regards

Mark



Response

Mark,

Your suggestion has been logged as an enhancement request.

Regards,

David

Apex: Table and column name limits

2007-07-12T20:15:00.000-07:00

I've been reviewing table and column name limits for Oracle Apex to come up with some internal standards.

Oracle supports 30 characters for object names, be it table, column, constraint, trigger, ... whatever.

Historically Oracle Designer used to add suffixes of 4 characters, and some of the built-in packages do also, e.g. for materialized view logs.

So generally 26 characters or less for object names is a good idea.

Oracle Apex prepends form items with a page reference e.g. P100_column_name.
Since it's easy to envisage an application having over a 100 pages, a limit of 25 characters should be imposed.

Importantly, you need to be aware that for PK columns, Apex imposes a limit of 22 characters for tabular forms.
See http://iadvise.blogspot.com/2006/12/apex-caveat-pk-column-name-length-it.html for details.

So in summing up the limits:

table names 26 characters or less
views, packages etc. can use 30 but try and stick to 26 characters or less
column names generally 25 characters or less
primary key column names 22 characters or less.

Naming conventions - well thats up to you!

Issue with APEX htmldbToolbar

2007-06-18T23:25:00.000-07:00

I'd love the APEX team to wrap the htmldbToolbar in a DIV tag, this would be more convienient for repositioning it when in development mode. Our application sizes its vertical height to 100%, which results in a scroll bar when we are developing. Also we have to create a DIV tag, rather than referencing the document.body. It's a small change, and would be greatly appreciated.

See the image below for an example of what I mean.

PDF output from Apex

2007-03-26T03:45:00.000-07:00

Carl Backstrom has a very interesting blog on APEX + Cocoon = PDF.

Upgrading Oracle XE to Apex 3.0

2007-03-23T19:40:00.000-07:00

Upgrading Oracle XE to Apex 3.0 has a couple of tricks to it, so here's how.

Download the software, and unzip it.
Edit the ..\apex\coreins.sql script
Run the installation/upgrade as usual
Upload the images using the correct version of apex_epg_config.sql.
Upgrade the owa packages in the ..\apex\owa directory.

Edit the ..\apex\coreins.sql script

On line 573 you need to set the XE variable to 1 for XE installation:

Rem
Rem Set XE variable to '1' for XE installation, '0' for non-XE installation
Rem
define XE = '1'

Run the installation/upgrade as usual

For a local install: e.g. sqlplus "sys/syspass as sysdba" @apexins password SYSAUX SYSAUX TEMP /i/

Upload the images

XE uses the embedded PL/SQL gateway so you need to run the apex_epg_config.sql script to upload images into the XDB repository. Note: documentation references apex_epg_config11.sql - this is for Oracle 11g only!!! Login to sqlplus as sys, and run the script passing the path to the apex directory you unzipped e.g. sql> @apex_epg_config.sql c:\temp

Update the owa packages

See the ..\apex\owa\readme.txt for details.

That's all there is to it. The most important thing to remember is don't just read the documentation, read and understand the scripts as well. It's easy to forget to document something, not that I've ever done that ... ;)