Martin Giffy D'Souza on Oracle APEX: 2009

Tuesday, December 1, 2009

APEX Orphaned Application Files

If you allow end users to upload files to your APEX application you may have a lot of "orphaned" files in apex_application_files and not even realize it.

Orphaned files are files that exist in APEX_APPLICATION_FILES that are not associated with an application. This can happen for several reasons, the most common are:

Files uploaded in Shared Components that aren't associated to an application

End users uploading files then purposely keep them in APEX_APPLICATION_FILES

End users uploading files and an error occurs

You can easily identify orphaned files using the APEX_APPLICATION_FILES view:

SELECT *
  FROM apex_application_files
 WHERE flow_id = 0 -- flow_id is the same as application_id

All 3 situations listed above will result in the file uploaded with flow_id = 0. The last 2 points, files uploaded from end users, can result in files that you may no longer need. I don't recommend that you keep uploaded files in the APEX_APPLICATION_FILES view. Instead you should move them immediately to a custom table.

The main problem comes from the third point. When a user uploads a file and a validation fails. In this situation the file is uploaded to APEX_APPLICATION_FILES and then the validation fails. Even though the validation failed, the file still resides in APEX_APPLICATION_FILES. The following screen shot demonstrates this issue.

To resolve this issue, I run the following application process which automatically "tags" uploaded files with a flow_id of -1. By doing so you can run a nightly process to delete any files that have a flow_id of -1.

Application Process: AP_TAG_APEX_FILES
Sequence: -100
Point: On Submit: After Page Submission - Before Computations and Validations

-- AP_TAG_APEX_FILES
BEGIN
   FOR x IN (SELECT v (item_name) item_name_value
               FROM apex_application_page_items
              WHERE application_id = :app_id
                AND page_id = :app_page_id
                AND display_as = 'File Browse...')
   LOOP
      UPDATE apex_application_files aaf
         SET aaf.flow_id = -1
       WHERE aaf.NAME = x.item_name_value;
   END LOOP;
END;

Monday, November 16, 2009

Using Decimals in APEX Page Numbers

When creating an APEX page you can use decimals in the page number. If you logically group your pages by page numbers and run out of page numbers this may be useful. For example if you create pages 1 through 10 then realize you'd like a page right beside page 5, you could create Page 5.1.

I don't personally recommend using decimals in page numbers as you can't use decimals in item names, so P5.1_X won't work. Of course you may find some other use for creating pages with decimals.

If you're concerned about logically grouping pages for your development try initially separating pages by increments of 100 or by using Page Groups. To configure Page Groups go to the main application development page and click on "Page Groups" on the right hand side. Once you've setup the group and assigned pages, go back to the main development page and under the "View" drop down select "Groups".

Tuesday, October 27, 2009

Standardizing Help Text in APEX

I had a requirement where the same item name was used in various areas of the application. To maintain consistancy, and to minimize development time, I decided to create a "help framework" to standardize on the help text for these items.

Note: I know APEX comes with a great tool to maintain consistancy for your page items called User Interface Defaults, however it would not work in this case.

The goal was for developers not to have to enter any help text for commonly named page items. Instead, when applicable, the help text would automatically be retreived from a common area so we'd only need to maintain it in one place. The design also had to handle custom help text (i.e. if we needed to override the default help text).

To do this I used Tool Tip Help (see: http://apex-smb.blogspot.com/2009/09/tooltip-help-in-apex-alternative-to.html) and made the following modifications:

- Create a Default Help Page
Page Name: Default Help Page

Create a region called "Default Help Items" and add all the items you want default help text for. For example, I created P10 and added the following items: P10_EMPNO, P10_ENAME. In each of these items I added the default help text for similarly named items.

- Modify the Application Process AP_GET_HELP_TEXT

BEGIN
  FOR x IN (SELECT    '<span class="itemToolTip" foritem="'
                   || item_name
                   || '">'
                   || item_help_text
                   || '</span>' help_html
              FROM (SELECT a.item_name,
                           NVL (a.item_help_text, dflt_help_text.item_help_text)
                                                                           item_help_text
                      FROM apex_application_page_items a,
                           (SELECT 'P' || :app_page_id || '_' || LTRIM (item_name, 'P10_')
                                                                                item_name,
                                   item_help_text
                              FROM apex_application_page_items
                             WHERE application_id = :app_id
                               AND page_id = 10 -- Enter Default Help Page number here (and modify select statement above)
                               AND item_help_text IS NOT NULL) dflt_help_text
                     WHERE a.application_id = :app_id
                       AND a.page_id = :app_page_id
                       AND a.item_name = dflt_help_text.item_name(+)
                       AND NVL (a.item_help_text, 'null') != '@NO_HELP@')
             WHERE item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

Now when I run the application, any item that is called PXX_EMPNO or PXX_ENAME will try to use the help text from P10 for the corresponding items. Developers can easily override the default help text by filling in specific help text for an item or by entering in "@NO_HELP@"

Monday, October 26, 2009

Saving Saved Interactive Reports when Updating Application

Their is now a supported method to preserve saved interactive reports. Please read the following post which explains how to do this in APEX 4.0: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html

When updating existing APEX applications that contain Interactive Reports (IR) you may, not knowningly, delete users saved IRs. The only supported way to prevent this from happening is to ensure that your Application ID is the same when you move it from Dev, to Test, to Production. David Peake wrote a full explanation of this issue here: http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html. I suggest you read his post before continuing.

What if you develop a single application that needed to be deployed to multiple clients/instances? I.e. you develop your application in DEV (100) and deploy to PROD (200), PROD (300), and PROD (400). Currently there's no supported way of doing this while maintaining your saved interactive reports.

The following script can be run after updating your production applications to ensure that your saved IRs don't get lost. Please note that this is not supported by Oracle and can put your application in an unsupported state. If you are not an advanced APEX developer I do not suggest using this as it may result in unexpected results.

Besides preserving saved IRs, users who are currently on the system will retain their current IR configurations, otherwise they will be lost. For example a user is working on an IR and applys some filters to it, you then update the application which will cause the interactive_report_id to change. The next time the user refreshes the page they won't see their filters any more (i.e. they'll have the default IR again). Running this script will prevent this from happening.

Note: this must be run as SYSTEM or a user with SYSTEM level access

BEGIN
  FOR x IN (SELECT a.ID, a.name, a.session_id,
                   b.interactive_report_id
              FROM apex_030200.wwv_flow_worksheet_rpts a,  -- This could also be flows...
                   apex_application_page_ir b
             WHERE a.flow_id = :app_id
               AND a.page_id = b.page_id -- Linking is done via the page so please be aware of any IR page changes
               AND b.application_id = a.flow_id
               AND a.worksheet_id != b.interactive_report_id
               and a.status = 'PRIVATE'
               )
  LOOP
    UPDATE apex_030200.wwv_flow_worksheet_rpts
       SET worksheet_id = x.interactive_report_id
     WHERE ID = x.ID;
      
  END LOOP;
END;

Thursday, October 22, 2009

Designing APEX Applications: UI Mockups

When doing any sort of development it's important to use the right tool for the right job. With that in mind, I thought I'd discuss mockups for APEX applications, or better yet any GUI.

One of my biggest pet peeves is when I get a functional or technical spec with modified screen shots of existing applications for the UI mockups. I'm not a graphic artist or a UI expert but I do know that taking a screen shot of a similar application, then spending several hours moving things around in Paint isn't very good. Using MS Paint, or similar applications, would be like developing your application using a typewriter (I've included a picture for those that have never used one :-).

I know some people have developed basic APEX applications and used screen shots for their UI mockups. I think APEX is a great tool for creating demo applications but it shouldn't be used for mockups. People reviewing the design may get carried away with the specifics since it looks like a real application.

What should you use to create application mockups? Personally I like a good old-fashion hand drawn diagram (I've been known to ask colleagues to give me their mockups on scrap paper). Though they are quick they don't work well in documents and they aren't great when doing several design iterations. Wireframe diagrams are an excellent alternative as they're exactly like hand drawn diagrams with pre-defined shapes.

Why use wireframe diagrams?

Quick to develop (the example above took me 2 minutes to create)

Easy to modify

Focus on flow and useability, not specifics

Cheap/Easy to do multiple design iterations

The list could go on, but I'll stop there. I'd like to emphasize on the fact that people will focus on specifics rather than the flow and usability of the application when looking at screen shots. For example: if you use have a screen shot with a drop down list of 100 countries and you only include 2 or 3 in the diagram, readers may wonder where the other 100 or so countries are etc. They aren't focused on the fact that it's a drop down list with countries, rather there's something wrong with the list. With a wireframe diagram it will be extremly clear that it's a drop down list of countries with no real focus on the values inside the select list.

I've you're looking for an application to do wireframe diagrams I'd recommend Balsamiq. I've been really impressed with it so far as it allows me to do UI mockups really quickly (I have no affiliation with Balsamiq, so please don't take this as an advertisement). Of course they're many other tools out there that will do this so I encourage you to find one that is suitable for your organization.

Thursday, October 15, 2009

Oracle and APEX Jobs

A lot of people were affected by the recession and may have lost their jobs. I know they're several companies looking for talented workers. To help out I've decided to start a small Jobs section on my blog (see right hand side). If you're interested in submitting a job, please leave a comment on this post with the following information:

- Job Title
- City the job is in
- Link to position

To keep with the theme of this blog please only submit Oracle (SQL,PL/SQL) and APEX related jobs.

On a side note I may create a small application where people can post jobs specific to APEX.

Thursday, October 8, 2009

apex.stackexchange.com

stackoverflow is a programming Q & A site that Jeff Atwood and Joel Spolsky created a while ago. It is now a very popular programming Q & A site and takes a different approach to asking questions then conventional forums.

They recently launched a site which allows individuals to host their own version of the application that focuses on a specific topic. I decided to register apex.stackexchange.com for Oracle Application Express (APEX) questions.

I realize that the APEX Forum is the main source of questions and answers regarding APEX, but I thought I'd see if anyone was interested in a different approach to handle questions.

If you're interested in trying it out, please go to: http://apex.stackexchange.com. You don't need to register to ask a question, but if you want to you can register using an OpenID account.

Friday, September 25, 2009

APEX Developer Competition 2009 - Top 12

Yesterday I found out that the application I submitted for the Oracle Application Express Developer Competition 2009 finished in the top 12! You can view all the winners here: http://www.oracle.com/technology/products/database/application_express/html/comp_winners.html

You can read about my application here: http://apex-smb.blogspot.com/2009/09/apex-rules-guidelines.html. There's a link where you can download a copy of it as well.

I put together my application rather quickly in a weekend so it does have some bugs. I'll try to update it soon for those that are using / interested in using it in their organization.

Thank you to all the judges and congrats to everyone that participated!

Monday, September 21, 2009

Enhanced APEX Session Timeouts

APEX has built in logic to set the lifetime of a session. To configure this option go to Shared Components / Edit Security Attributes / Maximum Session Idle Time in Seconds and set the time in seconds

This essentially terminates the user's session in the database and the next time they submit the page they'll be redirected to the login screen. The user will only know that they are logged out once they submit the page. If you have an Interactive Report (IR), or use Partial Page Refresh (PPR) the users won't know they're logged out. Instead it will look as though the report is still trying to load.

Another situation that may happen is that the user is filling out a long form on your page, their session timesout, then they click "submit". They'll be redirected to the login page and they'll lose all the information that they entered.

What if a user wants to extend their session? i.e. they haven't done anything to the page but would like a warning message before we automatically log them out? Or they are entering a log form and don't want to be logged out? I got this idea from the Air Canada web site when I was booking tickets. I really liked the fact that they let me know that they were to end my session, and gave me the option to extend my session.

The following solution will allow you to use APEX's session timeout and resolve the issues listed above. You can view the demo here: http://apex.oracle.com/pls/otn/f?p=20195:2600

Please note that since the demo page is set to public you can refresh after the session is supposed to have timedout and it will still work. If you set the Idle Session in APEX, this will work for pages that require authentication

Here's a high level overview of what this solution does:

Start a timer (pingApexSession) that will constantly "ping" (therefore refresh) your APEX session every X seconds.

Start a timer (idleTimer) to detect movement on the page.

If the idleTimer times out, give the user the option to extend session

If the user does not extend their session, terminate their session

I haven't put this code into a production application yet. As I mention below, I plan to make a jQuery plugin for this, so if you please send me any feedback that would be useful fur the plugin.

This solution uses jQuery and the following plugins:

Simple Modal

jQuery idleTimer

jApex

jQuery Countdown

- Create Application Process: AP_NULL
- Process Point: On Demand
- Name: AP_NULL
- Type: PL/SQL Anonymous Block
- Process Text: NULL;

- Create Application Process: AP_LOGOUT
- Process Point: On Demand
- Name: AP_LOGOUT
- Type: PL/SQL Anonymous Block
- Process Text:

BEGIN
  apex_custom_auth.LOGOUT (p_this_app                   => :app_id,
                           p_next_app_page_sess         => :app_id || ':1');
END;

- Create Region: "Extend Session" on Page 0
- Title: Extend Session
- Type: HTML Text
- Static ID: P0_REG_EXTEND_SESSION
- Region Attributes: style="display:none"
- Region Source: Your session will timeout in: <span id="timeoutCountdownDisplay" style="font-weight:bold"></span>
You can put whatever message you want. Just make sure the span tags exist for the countdown timer

- Create Button: "Extend Session" on Page 0
- Button Name: EXTEND_SESSION
- Text Label: Extend Session
- Display in Region: Extend Session
- Target is a: URL
- URL Target: javascript:gTimeout.timers.killSession.liveFn();

- Create Region: "Session Timedout" on Page 0
- Title: Session Ended
- Type: HTML Text
- Static ID: P0_REG_SESSION_ENDED
- Region Attributes: style="display:none"
- Region Source: Your session has ended. Please login.

- Create Button: "Login" on Page 0
- Button Name: LOGIN
- Text Label: Login
- Display in Region: Session Ended
- Target is a: Page in this Application
- Page: 1

- Create Region: "JavaScript - Session Timeout" on Page 0
- Title: JavaScript - Session Timeout
- Type: HTML Text
- Template: No Template
- Region Source:

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.simplemodal-1.3.min.js" type="text/javascript"></script>
<script src="#APP_IMAGES#idle-timer-0.7.080609.js" type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.jApex.0.9.2.js" type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.countdown-1.5.3.js" type="text/javascript"></script>
 
<script type="text/javascript">
  //*** Insert script from below ***/
  //Removed for display purposes
</script>

You'll need to upload the JS files beforehand. Please see the list above to obtain the files

Here's the script to put into the region above. I separated them for display purposes.

I probably should have created this as a jQuery plugin. I may convert it later on

var gTimeout = {
  //debug
  debug: false, //Set to True to turn on debugging
  debugFn: function(pMsg) {
    if (gTimeout.debug){
      console.log(pMsg); 
    }
  }, //debug
  modalRegions: {
    //Region that contains the "Extend Session" information
    extendSession: {
      id: 'P0_REG_EXTEND_SESSION',
      backgroundColor: '#CCC',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.openFn');
        // Start display timeout counter
        $('#timeoutCountdownDisplay').countdown('destroy');
        $('#timeoutCountdownDisplay').countdown({
          until: '+' + (gTimeout.timers.killSession.time / 1000),
          compact: true, 
          format: 'M:S'
        });
        // Load modal box to give user option to extend session
        $('#' + gTimeout.modalRegions.extendSession.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }, //openFn
      closeFn: function(){
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.closeFn');
        $.modal.close();
        return;
      }//closeFn
    },
    //Region that will be displayed if the user does not extend thier session
    sessionEnded: {
      id: 'P0_REG_SESSION_ENDED',
      backgroundColor: 'black',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.sessionEnded.openFn');
        // Close Extend Sessios modal window
        gTimeout.modalRegions.extendSession.closeFn();
        // Open Logout modal window
        $('#' + gTimeout.modalRegions.sessionEnded.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }// openFn
    }//sessionEnded
  },//modalRegions
  timers: {
    //Ping APEX Session timer will update the database session timer 
    pingApexSession: {
      id: -1,
      time: 5000, //Time to keep database session alive. This should be really close to the APEX idle time
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.pingApexSession.fn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.unloadFn:');
        clearTimeout(this.id); 
        this.id = -1;
        return;
      },//unloadFn
      fn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.fn: Extending APEX Session');
        jQuery.jApex.ajax({
          appProcess: 'AP_NULL',
          success: function(){},
          async: true
        });
        gTimeout.timers.pingApexSession.loadFn();
        return;
      }//fn
    },//pingApexSessions
    //Kill current session. This is called when the user gets the option to extend their session
    killSession: {
      id: -1,
      time: 5000, // Time to kill the APEX session once launched. Should only be run when extend session popup box is loaded
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.killSession.killFn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.unloadFn: ');
        clearTimeout(this.id); 
        this.id = -1;
        gTimeout.modalRegions.extendSession.closeFn(); // Close extendSession Modal 
        return;
      },
      killFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.killFn: Killing APEX Session');
        // Open Logout modal window
        gTimeout.modalRegions.sessionEnded.openFn();
        // Stop ping Apex session
        gTimeout.timers.pingApexSession.unloadFn(); 
        // Logout APEX session
        jQuery.jApex.ajax({
          appProcess: 'AP_LOGOUT',
          success: function(){},
          async: true
        });
        return;
      },
      // Prevents the session from being killed. We should be in a about to kill state now
      liveFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.liveFn: ');
        //Check that we're about to be killed
        if (this.id == -1){
          alert('Session is not marked to be killed');
          return;
        }
         
        // Stop the kill timer
        this.unloadFn();
        return;
      }//liveFn
    },//killSession
    // Timer for user movement time
    idle:{
      time: 5000, // Time to load the "Extend Session" popup box
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.loadFn:');
        $.idleTimer(this.time);
        $(document).bind("idle.idleTimer", function(){gTimeout.timers.idle.idleFn();});
        // Trigger countdown timer
        return;
      },
      idleFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.idleFn:');
        // Load modal box to give user option to extend session
        gTimeout.modalRegions.extendSession.openFn();
        // Only load if we're not in a kill state
        if (gTimeout.timers.killSession.id == -1){
          gTimeout.timers.killSession.loadFn();
        }
        return;
      } //idle Fn     
    } // idle
  },//timers
  loadFn: function() {
    gTimeout.timers.pingApexSession.loadFn(); // Keep database sessions alive
    gTimeout.timers.idle.loadFn(); // Turn on user idle timer
    return;
  }//loadFn
   
};//gTimeout
 
$(document).ready(function(){
  // Set Parameters
  gTimeout.timers.pingApexSession.time = 5 * 1000; // Refresh APEX session every 5 seconds. This should be really close to your apex session timeout values
  gTimeout.timers.idle.time = 10 * 1000; // 10 seconds of inactivity will trigger this window
  gTimeout.timers.killSession.time = 10 * 1000; // Once the warning message pops up, user has 10 seconds to extend their session
  // Configure Modal windows (not required)
  gTimeout.modalRegions.extendSession.backgroundColor = '#CCC';
   
  gTimeout.loadFn();
});

Thursday, September 17, 2009

Redirects for APEX Tabs instead of page submits

With APEX Tabs you may not want the page to be submitted each time the user clicks on a tab. If you look at the link for the tabs they look like: javascript:doSubmit('xxxx'); Where "xxx" is the name of the tab. "doSubmit" will submit the page and could trigger page computations, validations, and processes. If all you want to do is use the tabs as a form of navigation (i.e. when you click on a tab it redirects to another page) then this could cause some problems.

To avoid triggering Page Processing on tabs you can modify all the conditions on your Page Process (this could be very long) or change the link. They're multiple ways to change the links, here's a simple one using jQuery.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2500. Note, in the demo I don't trigger the javascript to change the link automatically so you can see what they look like before and after changing the links.

- Create an Application Process
Note: You may want to only apply this to certain pages depending on the use case

Name: AP_UNSUBMIT_TABS
Process Point: On Load: After Header

BEGIN
  FOR x IN (SELECT tab_name,
                   tab_page,
                      '<span class="apexTabURLs" tabname="'
                   || tab_name
                   || '" tabnewurl="'
                   || apex_util.prepare_url (   'f?p='
                                             || :app_id
                                             || ':'
                                             || tab_page
                                             || ':'
                                             || :app_session
                                             || ':'
                                             || tab_name
                                             || '::'
                                             || tab_page
                                             || '::')
                   || '"></span>' tabinfo
              FROM apex_application_tabs t
             WHERE application_id = :app_id)
  LOOP
    HTP.p (x.tabinfo);
  END LOOP;
END;

- Create a HTML region
Note: You'll need to install the jQuery JS file in Shared Components / Static Files

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
  $('span.apexTabURLs').each(function(i){
    var vTabName = $(this).attr('tabname');
    var vNewUrl = $(this).attr('tabnewurl'); 
    $('#t20Tabs a[href="javascript:doSubmit(\'' + vTabName + '\');"]').attr('href',vNewUrl);
  });
  // Remove the extra span tags
  $('span.apexTabURLs').remove();
</script>  

Tuesday, September 15, 2009

Tooltip Help in APEX: An alternative to popup help

The default help functionality for APEX is ok but can cause some problems with users browsers since it uses popup windows to display the help. If you do some digging around regarding web development best practices you'll find a lot of articles discussing why you should avoid popup windows. Instead of using the default help popup windows I prefer to use tooltips to display the help. Besides avoiding the popup window, tooltips generate a good user experience by displaying item help very quickly.

This demo uses a jQuery tooltip plugin: http://bassistance.de/jquery-plugins/jquery-plugin-tooltip/. Please visit the Bassistance website to find out how to configure the look and feel of the tool tips.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2400

- Create or Update Label Template
Note: You don't need to create a new template. If you want to, just update the existing templates
- Copy the "Optional Label with Help" and rename to "Optional Label with ToolTip".
Note: You can do this for required labels as well

- Change the "Before Label" From:
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="javascript:popupFieldHelp('#CURRENT_ITEM_ID#','&SESSION.')" tabindex="999">

To:
I removed the href reference and replaced with #
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="#" tabindex="999">

- Create a HTML region
This can be done on P0 to load for each page
Don't forget to upload the jQuery and tooltip JS files into Shared Components / Static Files

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.tooltip.pack-1.3.js" type="text/javascript"></script>
 
<script type="text/javascript">
 
$(document).ready(function(){
  $('span.itemToolTip').each(function(i){
    $('label[for="' +  $(this).attr('foritem') + '"]').attr('title',$(this).html()).tooltip({ 
        track: true, 
        delay: 0, 
        showURL: false, 
        showBody: " - ", 
        fade: 250 
      });
  });
  // Remove Original ToolTip Help Text
  $('span.itemToolTip').remove();
});
</script>
 
<style>
#tooltip {
 position: absolute;
 z-index: 3000;
 border: 1px solid #111;
 background-color: #eee;
 padding: 5px;
 opacity: 0.85;
}
#tooltip h3, #tooltip div { margin: 0; }
</style>

- Create an Application Process
Name: AP_GET_HELP_TEXT
Process Point: On Load: After Header

BEGIN
  FOR x IN (SELECT    '<span class="itemToolTip" foritem="'
                   || item_name
                   || '">'
                   || item_help_text
                   || '</span>' help_html
              FROM apex_application_page_items
             WHERE application_id = :app_id
               AND page_id = :app_page_id
               AND item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

- Change Item Labels
Change Item labels to "Optional Label with ToolTip". Only do this if you created a new template

Monday, September 14, 2009

Updateable Interactive Report for APEX

A colleague had a requirement where he had over 10,000 rows of data which had to be updateable. Using Interactive Reports (IR) was the preferred approach as it would allow users to filter the data to modify the rows they wanted to before submitting the page. Tabular forms wouldn't work since the page would be to large. This is the solution that I proposed to make "Updateable IRs".

The following solution will work with IRs and standard reports with pagination. If the users applies filters or paginates to another set of data, the changes they make will remain in the collection. This is using a similar technique that I wrote about for APEX Report with checkboxes (advanced). To summarize this process:

Store the current query data into a collection as well as the md5 checksum.
Build an IR against the collection and use APEX_ITEMs to display input fields.
When a user changes a field, we submit that change to the collection.
Once the user is done with their changes you'll need to process the collection as required. In the last step in this example I have a query that will help identify changed rows.

You can do a lot with this approach but if you don't have an urgent need I'd suggest holding off until APEX 4.0. They're some security issues that would need to be addressed before launching this code in a public application. I didn't include the security updates in this example since I did not want to lose scope of the base functionality. Updating the code to make it secure shouldn't be too difficult.

Here's the link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2300

- Create IR Report Region
Note: You can use this for regular reports with pagination as well

SELECT e.empno,
    apex_item.text (2,
                    ac.c002,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="2"'
                   ) ename,
    apex_item.text (3,
                    ac.c003,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="3"'
                   ) job,
    apex_item.select_list_from_query
       (4,
        ac.c004,
           'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
        || e.empno,
        'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
        'YES',
        NULL,
        '- Manager -'
       ) mgr,
    apex_item.text (5,
                    ac.c005,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="5"'
                   ) hiredate,
    apex_item.text (6,
                    ac.c006,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="6"'
                   ) comm,
    apex_item.select_list_from_query
                       (7,
                        ac.c007,
                        'SELECT d.dname d, d.deptno r FROM dept d',
                           'class="updateableIR" seqid="'
                        || ac.seq_id
                        || '" attrNum="7"',
                        'YES',
                        NULL,
                        '- Department -'
                       ) deptno
FROM apex_collections ac, emp e
WHERE ac.collection_name = :p2300_collection_name AND ac.c001 = e.empno

- Create Region Items
- Hidden & Protected: P2300_COLLECTION_NAME

- Create Page Computation
Note: This is just to set the collection name. You can call it whatever you want

Item: P2300_COLLECTION_NAME
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION

- Create a Page Process (PL/SQL)

Name: Load Collection
Process Point: On Lead - Before Header

-- This creates the collection if it isn't created yet.
DECLARE
v_collection_name             apex_collections.collection_name%TYPE
                                                 := :p2300_collection_name;
v_reset_flag                  VARCHAR2 (1) := 'N';
BEGIN
-- Create collection if it does not exist or reset collection required
IF    apex_collection.collection_exists
                                   (p_collection_name            => v_collection_name) =
                                                                     FALSE
  OR v_reset_flag = 'Y'
THEN
 apex_collection.create_collection_from_query
   (p_collection_name            => :p2300_collection_name,
    p_query                      => q'! SELECT empno,
    ename,
    job,
    mgr,
    TO_DATE (hiredate, 'DD-MON-YYYY'),
    comm,
    deptno
FROM emp !',
    p_generate_md5               => 'YES'
   );        -- Generated md5 is important to help identify changed columns
END IF;
END;

- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth's jApex plugin. You'll have to upload the .js files as static files in Shared Components.

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.jApex.0.9.2.js" type="text/javascript"></script>
 
<script type="text/javascript">
/** 
 * Updates a cell in a collection
 * @author Martin Giffy D'Souza. http://apex-smb.blogspot.com
 * @param pThis input item that was changed
 *  Must contain the following attributes:
 *  - seqid Seq ID from collection
 *  - attrnum Attribute number (column number)
 * @param pOptions Options
 *  - appProcess: Application process to call
 *  - collectionName: Collection to update
 *  - successFn: Function to execute once completed
 */
function updateCollectionCell(pThis, pOptions){
  var vDefaults = {
    appProcess: 'AP_UPDATE_COLLECTION_CELL',
    collectionName: '',
    successFn: function(){}
  };
  pOptions = jQuery.extend({}, vDefaults, pOptions);  
   
  // Check that collection name is present
  if (pOptions.collectionName == ''){
    alert('Missing Collection Name');
    return;
  }
   
  jQuery.jApex.ajax({
    appProcess: pOptions.appProcess,
    success: pOptions.successFn,
    x01: pOptions.collectionName,
    x02: $(pThis).attr('seqid'), //Seq ID
    x03: $(pThis).attr('attrnum'), //Attribute Number (i.e. column number)
    x04: $(pThis).val() // New Value
  });
   
  return;
}// updateCollectionCell
 
//Set all updateableIR columns onChange events
$('.updateableIR').live('change',function(){
  updateCollectionCell(this, {
    collectionName: $v('P2300_COLLECTION_NAME'),
    successFn: function(){}
    });
});
</script>

- Create Application Process:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand

-- AP_UPDATE_COLLECTION_CELL
BEGIN
apex_collection.update_member_attribute
                             (p_collection_name            => apex_application.g_x01,
                              p_seq                        => apex_application.g_x02,
                              p_attr_number                => apex_application.g_x03,
                              p_attr_value                 => apex_application.g_x04
                             );
END;

- Query to see which rows were changed

SELECT ac.collection_name, ac.seq_id, ac.c001, ac.c002, ac.c003, ac.c004,
    ac.c005, ac.c006, ac.c007, ac.md5_original,
    CASE
      WHEN apex_collection.get_member_md5
                                       (:p2300_collection_name,
                                        ac.seq_id) =
                                                ac.md5_original
        THEN 'NO'
      ELSE 'YES'
    END row_change
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name

Wednesday, September 2, 2009

APEX Rules & Guidelines

After several late nights of nerding I've finally submitted/completed my APEX application for the APEX Developer Competition 2009.

~~You can demo and download the application here: http://apex.oracle.com/pls/otn/f?p=46865~~
Note: Demo is no longer up and will not be supported.

This purpose of this application is to allow development teams to create an online Rules & Guidelines development document. The template for this application is based on Patrick Cimolini's (Cayman Islands Government) presentation at ODTUG.

The concept of the R&G template is:

Rule: Follow 100% of the time
Guideline: Follow 90% of the time

Mandatory Headings:

Rule/Guideline: Active voice sentence.
Why:Important for Team Buy-In

Optional Headings:

Result: Illustration of desired outcome
How: How the result is achieved.
Notes: State the non-obvious
See Also: Point to related Rules and Guidelines

You can use this template for both APEX development and your PL/SQL standards. I've included the installation files in the demo application so feel free to install at your organization. If you are using it please post any comments/suggestions on this post and I'll try to write some updates.

Thursday, July 30, 2009

APEX: $x_disableItem

I had an issue where I disabled a radio button (after selecting a value) using the APEX JavaScript function ($x_disableItem) but my selected value wasn't being saved.

After some testing I noticed that if I selected a value, disabled the radio, then submitted the page item's value would be null (empty string).

You can view an example of this here: http://apex.oracle.com/pls/otn/f?p=20195:2200

They're several ways to fix this problem. The easiest would be to "undisable" (i.e. re-enable) the item before the page is submitted. I know "undisable" is not a word but to re-enable a page item you need to call the disable function: $x_disableItem('PX',false);

Tuesday, July 28, 2009

APEX: How to Pass Multiselect List Values in URL

When passing multiselect list values, or any multi LOV, in the URL you may have some unexpected behaviors. Here's an example: http://apex.oracle.com/pls/otn/f?p=20195:2100

If you take a look at the example you'll notice that the URL doesn't contain all the values that you may have submitted. For example I selected KING (7839), BLAKE (7698), and CLARK (7782). I would expect the URL to contain these values when I pass them via the URL. Instead the URL looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2100:1674288126968745::NO::P2100_EMPNO_LIST:7839:7698

Notice how only 2 values are passed in? That's because the delimiter used in LOVs is the same that is used in the URL. What can be even more confusing is that I selected 3 values but when I pass them in the URL only 1 is "accepted". This is because the last value in the URL is the "PrinterFriendly" parameter (please see: http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/concept.htm#BEIJCIAG)

To fix the issue for all your mutli LOVs you can use a similar technique that I used to resolve the %null% issue. An example of the fix can be found here: http://apex.oracle.com/pls/otn/f?p=20195:2110. If you take a look at the example and select several employees the URL now looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2110:1674288126968745::NO::P2110_EMPNO_LIST:7839*7698*7782

Notice how the delimiters are *s for the empnos?

1- Create Application Process to replace colon delimiter with *
Note: You aren't limited to using * as your delimiter

Name: AP_REMOVE_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process
Point: On Submit: After Page Submission - Before Computations and Validations

BEGIN
  FOR x IN (SELECT item_name,
                   REPLACE (v (item_name), ':', '*') new_item_value
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (display_as) IN ('checkbox', 'shuttle', 'select list', 'multiselect list') -- Limiting to these types. Can remove if you want to handle all types
                       AND ROWNUM > 0) x
             WHERE INSTR (v (x.item_name), ':') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Note: This will replace the colon delimiter with a *. This may change some of your validations, page processes etc.

2- Create Application Process to replace * with colon delimiter on page load

Name: AP_RESET_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process)
Point: On Load: Before Header (page template header)

BEGIN
  FOR x IN
    (SELECT item_name,
            REPLACE (v (item_name), '*', ':') new_item_value
       FROM (SELECT item_name
               FROM apex_application_page_items aapi
              WHERE aapi.application_id = :app_id
                AND aapi.page_id = :app_page_id
                AND LOWER (display_as) IN
                      ('checkbox', 'shuttle', 'select list', 'multiselect list')   -- Limiting to these types. Can remove if you want
                AND ROWNUM > 0) x
      WHERE INSTR (v (x.item_name), '*') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Friday, July 24, 2009

Oracle: How to update all sequences

If you ever do data refreshes from production to development or test environments you may run into an issue where your sequences are not up to date. It seems that Oracle exports the sequences first, then the data. If your sequence numbers change during the entire export process you may get errors when using them in your refreshed schema.

To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:

-- Update all sequences
DECLARE
  v_increase_by                     NUMBER;
  v_bkp_increment_by            NUMBER;
  v_str                         VARCHAR2 (1000);
  v_count                       NUMBER;
BEGIN
  v_increase_by                  := 1000000;
 
  FOR rec IN (SELECT *
              FROM   user_sequences) LOOP
    -- Backup current incrementation number
    v_bkp_increment_by         := rec.increment_by;
    -- Alter the sequence to increase by a defined amount
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by;
 
    EXECUTE IMMEDIATE v_str;
 
    -- Increase by that amount
    v_str                      := 'select ' || rec.sequence_name || '.nextval from dual';
 
    EXECUTE IMMEDIATE v_str
    INTO              v_count;
 
    -- Reset the increment factor
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by;
 
    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;
/

Wednesday, July 22, 2009

Oracle: Advanced Error Messages

This is not an APEX specific post, however it can be useful for error handling.

A colleague showed me a great way to get more useful debug information. Normally I used SQLERRM and SQLCODE in an exception to display or store error messages. Using DBMS_UTILITY you can get more detailed Oracle error messages. Here's an example:

-- I put this in a package for demo purposes
CREATE OR REPLACE PACKAGE pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  );
END pkg_err_test;
 
CREATE OR REPLACE PACKAGE BODY pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  )
  AS
    v_ename emp.ename%TYPE;
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = p_empno;
 
    DBMS_OUTPUT.put_line ('Employee name is: ' || v_ename);
  EXCEPTION
    WHEN OTHERS THEN
      -- Basic Error Message
      DBMS_OUTPUT.put_line ('Old Error Message: ' || SUBSTR (SQLERRM, 1, 255));
      DBMS_OUTPUT.put_line ('Old Err Code: ' || SQLCODE);
      -- Advanced Error Messages
      DBMS_OUTPUT.put_line ('-- New Error Messages --');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   -- Error Message
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   -- Where it occurred
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);   -- Call Stack
  END sp_err_test;
END pkg_err_test;
 
-- Run the error test with an invalid employee number so an exception will be raised
EXEC pkg_err_test.sp_err_test(p_empno => 123);

DBMS Output:

Old Error Message: ORA-01403: no data found
Old Err Code: 100
-- New Error Messages --
ORA-01403: no data found

ORA-06512: at "GIFFY.PKG_ERR_TEST", line 9

----- PL/SQL Call Stack -----
object line object
handle number name
362D7814 24 package body GIFFY.PKG_ERR_TEST
362D70E0 1 anonymous block

The error message is displayed as well as where the error occurred and the call stack. In large systems this can be very helpful. You should be aware that when called from a package, it does not list the procedure or function (as seen in this example) where the error occurred so you may need to hard code the function or procedure name in your error message.

Tuesday, July 21, 2009

How to resolve %null% issue in APEX LOVs

Patrick Wolf mentioned this at ODTUG Kaleidoscope this year.

After you implement your first LOV in an APEX application you'll quickly learn about the %null% problem. APEX substitutes an empty string for Null return value as %null%.

They're several workarounds, like using "-1" as the NULL value. Or modifying your query using "'%' || 'null%'". For example:

SELECT ename,
       empno
  FROM emp
 WHERE empno = DECODE (:p_empno, '%' || 'null%', empno, NULL, empno, :p_empno)

Instead of using workarounds you can convert %null% to NULL (empty string) by creating the following application process:

Application Process: AP_SET_LOV_NULLS
Process Point: On Submit - Before Computations and Validations

BEGIN
  FOR x IN (SELECT *
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (aapi.lov_display_null) = 'yes'
                       AND aapi.lov_definition IS NOT NULL
                       AND aapi.lov_null_value IS NULL
                       AND ROWNUM > 0) x
             WHERE LOWER (v (x.item_name)) = '%' || 'null%') LOOP
    apex_util.set_session_state (x.item_name, NULL);
  END LOOP;
END;

Monday, July 20, 2009

JavaScript: ? = If Else

This is not directly related to APEX however it will help if you use a lot of JavaScript

When I first started to develop web based applications and used JavaScript I came across some JS code with a ? in it. I didn't know what it was for and Googling "JavaScript ?" didn't help either. Here's a quick summary on how it works

Boolean ? true action : false action

So this:

var x = 2;
if (x > 1)
  window.alert('True');
else
  window.alert('False');

Becomes this:

var x = 2;
x>1 ? window.alert('True') : window.alert('False') ;

Thursday, July 16, 2009

APEX_APPLICATION.DO_SUBSTITUTIONS

I've run into several instances where I needed to store HTML in a table. The problem is sometimes the HTML references APEX Items. For example if your HTML needs to reference a picture, odds are you'll need to reference &APP_IMAGES. (or some image location item). In the past I've done manual REPLACE calls for known items, but it was fairly restrictive.

APEX has a great function (not yet documented to my knowledge) called APEX_APPLICATION.DO_SUBSTITUTIONS. If you pass in a string, it will substitute any APEX values. Here's an example app: http://apex.oracle.com/pls/otn/f?p=20195:2000

To create demo:

1- Create table and insert values

CREATE TABLE tp2000(line_content CLOB NOT NULL);
 
INSERT INTO tp2000
     VALUES ('Google Canada Picture: <img src="' || CHR (38) || 'APP_IMAGES.google_logo.gif">');
 
INSERT INTO tp2000
     VALUES ('My Current Session: ' || CHR (38) || 'APP_SESSION.');

2- Create Report Region (with substitutions)

SELECT apex_application.do_substitutions (line_content) content_with_subs
  FROM tp2000

3- Create Report Region (without substitutions)

SELECT line_content content_without_subs
  FROM tp2000

Wednesday, July 15, 2009

APEX: How to Develop in 2 Browser Tabs

I saw this during a presentation by Anton Nielsen last year. If you've developed with APEX for a while then you've probably wanted to have 2 developer tabs open on your browser at the same time. You'll quickly find out that this doesn't work to well.

There's an easy way around it. Lets say you're developing on an instance running on your laptop. The URL you normally go to looks something like: http://localhost:8080/apex/

Go into your hosts file (C:\WINDOWS\system32\drivers\etc\hosts for Windows users). You should see an entry like this:

127.0.0.1 localhost

Add: 127.0.0.1 giffy01 on a new line (where "giffy01" is any arbitrary name).

Your hosts file should now look like:

127.0.0.1 localhost
127.0.0.1 giffy01

In your favorite web browser,

open the following URLs in 2 different tabs:

http://localhost:8080/apex/
http://giffy01:8080/apex/

You can now have 2 development tabs open at the same time.

Tuesday, July 14, 2009

APEX: How to Dynamically Render Regions

Suppose you needed to enable and disable report regions on each page based on a parameter. You could add a condition to each region. If the conditions were all the same, the smart thing to do would be to create a function and have your condition reference the function.

What if your application had 100 pages? Would you remember to apply the condition to each report region in the 100 pages?

This is not a problem that most developers run into, however when you are building large applications something similar may come up. If you can find a way to dynamically control items, regions, processes, etc this can save on development time.

At the ODTUG Kaleidoscope conference Dennis Vanill gave a presentation on how to use Page 0 items to enable and disable APEX objects dynamically. Using this logic, here's an example on how to dynamically disable a region.

Note: Use this when appropriate. For basic conditions stick with using "regular" conditions

A demo is available here: http://apex.oracle.com/pls/otn/f?p=20195:1900

1- Create a page with some report regions

-- Interactive Report:
SELECT *
FROM emp
 
-- Regular Report
SELECT ename, sal
FROM emp

2- Create Page Process: On Load - Before Header

DECLARE
BEGIN
  IF NVL (:p1900_hide_reports_flag, 'N') = 'Y' THEN
    FOR x IN (SELECT region_id
                FROM apex_application_page_regions
               WHERE application_id = :app_id
                 AND page_id = :app_page_id
                 AND source_type IN ('Report', 'Interactive Report')) LOOP
      FOR i IN 1 .. apex_application.g_plug_id.COUNT LOOP
        IF apex_application.g_plug_id (i) = x.region_id THEN
          apex_application.g_plug_display_condition_type (i) := 'NEVER';
        END IF;
      END LOOP;
    END LOOP;
  END IF;
END;

2- (For Demo purposes only)
I added the following on Page 0 to display in the example application. This shows that no conditions were applied to a region

SELECT region_id,
       region_name,
       source_type,
       condition_type,
       condition_expression1,
       condition_expression2,
       build_option,
       authorization_scheme
  FROM apex_application_page_regions
 WHERE application_id = :app_id
   AND page_id = :app_page_id

You can use the same logic to control computations, items, etc. Take a look at apex_application (desc apex_application) for more options.

Thursday, July 2, 2009

APEX: Saving item values for each user

Someone asked me today if APEX could remember input values for specific page items. For example if you have a page with report parameters could APEX remember the report parameters that the user last used the next time they logged in?

Note: Please read comments below as APEX does support this out of the box on an individual item basis. This solution is to make the option configurable for large applications.

APEX doesn't support this out of the box, however it does have some great features which can enable you to do this. You can use cookies for this but I wanted to make the solution work no matter where the user was accessing the application from.

To make things a bit more difficult, I don't want to remember all item values on a page so I must be able to control which items are "remembered" and which items aren't. I can do this by using a naming convention in my items, however I don't want to rename all my page items (I already have a lot of them). Instead I decided to create a table which will list all the items a user can remember.

You can try the demo here (follow the instructions on the page).

CREATE TABLE tapex_remember_page_item(
application_id NUMBER NOT NULL,
page_id NUMBER NOT NULL, 
item_name VARCHAR2(255) NOT NULL);
 
-- You don't need to add a UK, however it may be a good idea. 
ALTER TABLE tapex_remember_page_item ADD(
  CONSTRAINT tapex_remember_page_item_uk1
  UNIQUE (application_id, page_id, item_name));
   
-- Since I name all my APEX items in uppercase, just do this as an extra precaution
CREATE OR REPLACE TRIGGER trg_tapex_remember_pg_itm_buir
  BEFORE UPDATE OR INSERT
  ON tapex_remember_page_item
  FOR EACH ROW
BEGIN
  :NEW.item_name := UPPER (:NEW.item_name);
END;
/
 
INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_DEPTNO');
             
             
INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_MIN_SAL');            

For this example we'll store the values as APEX Preferences, however you could easily create your own preferences table to manage your data. I think they're several advantages to managing the preferences in your own table, however if you have a small application with a limited number of users then I'd recommend using the APEX_UTIL preference options

Create 2 Application Processes:

AP_GET_PAGE_ITEM_PREFS
On Load: Before Header (page template header)

DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_session_state (p_name      => x.item_name,
                                 p_value     => apex_util.get_preference (p_preference     => x.item_name,
                                                                          p_user           => :app_user
                                                                         )
                                );
  END LOOP;
END;

AP_SET_PAGE_ITEM_PREFS
On Submit: After Page Submission - After Computations and Validations

DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_preference (p_preference => x.item_name, p_value => v (x.item_name), p_user => :app_user);
  END LOOP;
END;

For those of you that are curious APEX Preferences are stored in : apex_030200.wwv_flow_preferences$ where apex_030200 is the schema name for APEX (could also be called flows_xxxxxx)

Sunday, June 28, 2009

ODTUG Kaleidoscope 2009 Summary

I just got back from ODTUG Kaleidoscope 2009. I met a lot of great people and saw many excellent presentations. My biggest regret was not being able to see everyone's presentations! Here are some highlights from the conference:

Sunday APEX Symposium: Saw some excellent presentations on how APEX was used in other organizations.

- Joel Kallman (Oracle) had a great opening presentation going over the history and future of APEX

- Olivier Dupont (iAdvise): Excellent use of APEX at the airport, even without a mouse or browser access!

- My presentation: Mike Kinahan did a great job filling in for Frank Hoogendoorn on short notice, however my camera man wasn't as good (see picture below). If anyone has some pictures of the presentation can you please let me know?

- Dennis Vanill (PAETEC Software): Using page 0 to cut down on common task for many pages.

- Jan Navratil (CampusIT): Demonstrated that APEX can be used in mission critical applications. Use translation as a configuration technique for each client's definitions.

- Paul Davidson (Cornerstone Brands): APEX used in large scale call center and how he cut down call time.

- Shravan Kumar (Apexor): created the term LAOS (Linux, Apache, Oracle, SQL Developer). Cut down long processes into 4 minutes using APEX!

- David Peake (Oracle): Anounced an APEX development contest for some free tickets to Open World.

Presentations:

- Tom Kyte(Oracle) as always had some great presentation and demo. He stressed (again) use the database for as much as you can since Oracle will do things quicker and faster for you.

- Michael Hichwa (Oracle): APEX 4.0 Demo. I'll write a separate post about this soon since it will take a lot of space (it's just that good)
Edit: 1-Jul-2009. Role Hartman wrote an excellent post for APEX 4.0. Please read it for more info: http://roelhartman.blogspot.com/2009/07/buzz-around-apex-40.html

- Dietmar Aust (Opal Consulting): Free PDF and XLS printing options for APEX. Check his blog for full details

- Scott Spendolini (Sumner Technologies): Managing Multiple APEX applications as one. The full sample application is on his web site here: http://sumnertechnologies.com/framework

- Patrick Wolf (Oracle): Had 2 great presentations. I missed his first one, but was able to attend his second presentation. Besides the content he's an excellent speaker so if you ever have a chance to see him present you should go! Patrick was kind enough to show Dennis and I some more APEX 4.0 features and answer a lot of my questions.

- Francis Mignault (Insum): Multi tenant SaaS APEX applications. I develop SaaS applications as well and it was really nice to see a different development approach.

- Patrick Cimolini (Cayman Island Government): Development Rules and Guidelines Document for APEX. If you're looking for a simple yet effective way to create a APEX standards document I strongly suggest you get a copy of his presentation (I'm going to see if he'll allow me to post a copy here).

- John Scott (Apex Evangelists): Unfortunately I wasn't able to see any of his presentations but I heard they were really good, I'll have to get a copy of his slides and white papers later on.

Tuesday, June 16, 2009

ODTUG Kaleidoscope - Twitter

I've broken down and created a Twitter account http://twitter.com/martindsouza. I wasn't planning on joining Twitter however it seems like it will be an excellent communication tool for the upcoming ODTUG Kaleidoscope conference.

On that note, our presentation's "Twitter Tag" (I'm new to Twitter so I don't know all the terminology) is: #ODTUG S299. Feel free to post something on Twitter or this blog during the conference.

Monday, June 15, 2009

How to Quickly Append VARCHAR2 to CLOB

This is not an APEX specific issue, however it could be useful for some of your PL/SQL code

I ran into an issue today where I had to append VARCHAR2s to a CLOB many times in a loop. I first tried appending a VARCHAR2 to a CLOB: CLOB := CLOB || VARCHAR2. I noticed that this was taking a long time to run. In order to speed up the process I tried the following techniques:
- Create a "temp" CLOB (TMP_CLOB := VARCHAR2) and then appended it the clob CLOB := CLOB || CLOB
- Use the CLOB := CLOB || TO_CLOB(VARCHAR2)
- Use DBMS_LOB.append (CLOB, VARCHAR2)

All three options resulted in significant speed increases, however using the "temp" CLOB method resulted in the quickest code. Here is the test that I ran along with the results:

DECLARE
  v_start TIMESTAMP;
  v_end TIMESTAMP;
  v_clob CLOB;
  v_tmp_clob CLOB;
  v_iterations PLS_INTEGER := 100000; -- Used 1,000, 10,000, and 100,000 for testing
BEGIN
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;
 
  FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
END;

The results were as follows:

1,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:00.578000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.063000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.047000000
DBMS_LOB.append method: +000000000 00:00:00.172000000

10,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:10.656000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.688000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.672000000
DBMS_LOB.append method: +000000000 00:00:00.687000000

100,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:42:17.453000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:17.953000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:08.140000000
DBMS_LOB.append method: +000000000 00:00:11.110000000

Friday, June 12, 2009

APEX Static Files Not For Secure Content

Static files and Images (under Shared Components) are a great way to use external files (such as images, css, js, documents, etc). However, they should not be used to store sensitive information as users don't need to be logged in to access these files.

Here's an example: In an APEX application I've uploaded a documented called "top_secret.doc" in the Static Files section. I only want logged in users to be able to download this file. After the user logs in their is a HTML region which contains a link to top_secret.doc. The region source is:

<a href="&APP_IMAGES.secure_doc.doc">Secure document</a>.

When the user logs in they now see a link on the first page called "Secure document" which references top_secret.doc.

At first glance this seems secure since the user must first login before downloading the document. The hyperlink looks something like this: http://localhost:8080/apex/wwv_flow_file_mgr.get_file?p_security_group_id=1037606673759910&p_flow_id=103&p_fname=top_secret.doc

If you notice there's no reference to the user's APEX session ID. Anybody can use this URL to download the file even if they don't have access to your application.

This is not a bug or an APEX security hole, but something that you should be aware of if you are thinking about storing sensitive information in the static files area.

Thursday, June 11, 2009

Quickly Modify APEX Interactive Report Options

If your APEX application has many Interactive Reports (IR) it can be tedious to configure IR features for each report, and their columns, such as filtering, highlighting etc.

Since APEX resides within the database there's a quick way to manage all of your IRs. Please note this is not supported by Oracle so please be aware of this.

First login to the database as SYS or SYSTEM.

Update Interactive Report options: You can modify more options by looking at the table definition for wwv_flow_worksheets

UPDATE apex_030200.wwv_flow_worksheets -- Where apex_030200 is your current APEX instance
   SET allow_report_saving = 'Y', -- Configure options as required
       show_finder_drop_down = 'N',
       show_display_row_count = 'Y',
       show_search_bar = 'N',
       show_search_textbox = 'Y',
       show_actions_menu = 'Y',
       show_select_columns = 'N',
       show_sort = 'N',
       show_filter = 'Y',
       show_control_break = 'Y',
       show_highlight = 'Y',
       show_computation = 'N',
       show_aggregate = 'N',
       show_chart = 'Y',
       show_flashback = 'N',
       show_reset = 'Y',
       show_download = 'Y',
       show_help = 'N'
 WHERE flow_id = :app_id
   AND page_id = :app_page_id -- Remove this predicate to push changes for all IRs

Update Interactive Report Columns:

UPDATE apex_030200.wwv_flow_worksheet_columns
   SET allow_sorting = 'Y',
       allow_filtering = 'N',
       allow_ctrl_breaks = 'Y',
       allow_aggregations = 'N',
       allow_computations = 'Y',
       allow_charting = 'Y'
 WHERE flow_id = :app_id
   AND page_id = :app_page_id;

Tuesday, June 9, 2009

Extend APEX Workspace Password

Every so often when you log into the APEX development interface, it will require you to change your account password. This isn't a bad idea, however if you develop in several workspaces, and have multiple accounts, it may be a bit cumbersome. You can extend the account password lifetime by doing the following:

- Login to the APEX Admin
- Home / Manage Service / Security
- Change the Account Password Lifetime (days) to whatever you need.


Oracle Application Express Plugins	Expert Oracle Application Express

Beginning Oracle Application Express 4	Expert Oracle Application Express 5

Pages