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.

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.

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

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:

Secure document.

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.

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;

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.

Displaying Percentage Bar in APEX Reports

APEX can create "Percentage Bars" within a report. They're probably a lot of 3rd party tools you can use for fancy percentage bars, however if you want a basic display to the user here's a quick way to do it. Click here for a demo.



1- Create your report
In this report we're using the employees percentage of salary within their department

SELECT e.ename,
       e.job,
       d.dname,
       e.sal,
       ROUND (e.sal / SUM (e.sal) OVER (PARTITION BY e.deptno) * 100, 0) pct_dep_sal, 
       ROUND (e.sal / SUM (e.sal) OVER (PARTITION BY e.deptno) * 100, 0) bar
  FROM emp e,
       dept d
 WHERE e.deptno = d.deptno

2- Add Percentage Bar for the "bar" column
- In the Reports Attributes section, click on the "Bar" column attributes
- Under Number/Date formatting enter the following: PCT_GRAPH:330099:CC0000:100

PCT_GRAPH:<Hex background color>:<Hex foreground color>:<Bar width in pixels>