• Entry Rules and Eligibility
• Application Development Guidelines
• Criteria for Judging Submissions
• Prizes / Awards
• Registration

Entry Rules and Eligibility

• Entry is open to any members of the Oracle Technology Network (OTN), except for employees of Oracle and its subsidiaries and the judge's companies.
• You must be registered on the Oracle Technology Network (OTN) in order to enter the competition. (Register here)
• You can only submit one APEX application.
• Oracle Application Express application criteria -
  • Must be written in Oracle Application Express 3.2.x.
  • Be provided as a single Packaged Application including necessary supporting objects.
  • The primary language must be English, including supporting documentation. Optionally, you may also include other translated language versions of the application.
  • Can incorporate third-party libraries (such as jQuery) providing the third-party licensing agreement(s) are specified in the read-me file provided with the application.
  • Can include references to "public" Web Services providing the third-party licensing agreement(s) are specified in the read-me file provided with the application.
  • May be designed for deployment on either the Internet or Intranets or both.
• Submissions must be entered into the APEX Competition Application by 17:00 Eastern US Daylight Time (EDT = GMT - 5:00 hours) on 4 September, 2009.
  {APEX Competition Application available after accepting the APEX Competition Legal Agreement and registering}
• Submissions are final, and late or duplicate submissions will not be accepted, unless additional submissions are requested by Oracle.
• The judges decision is final.

Application Development Guidelines

1 - Overview

2 - Database Design Considerations

2.1 - Object Naming

• Database objects for an Application should be prefixed with an appropriate "identifier". For instance, each object belonging to the application "Content Management" could be prefixed with "CM_". This groups all the objects for a particular application together and helps to avoid contention for object names.
• Primary Key Constraints should be named <TABLE_NAME>_PK.
• Unique Key Constraints should be named <TABLE_NAME>_UK.
• Foreign Key Constraints should be named <BASE_TABLE_NAME>_<REFERENCED_TABLE_NAME>_FK.
• Check Constraints should be named <BASE_TABLE_NAME>_<COLUMN_BEING_CONSTRAINED>_CC.
• Triggers should be named <BASE_TABLE_NAME>_BI, <BASE_TABLE_NAME>_BU, or <BASE_TABLE_NAME>_BIU (where BI indicates Before Insert, BU indicated Before Update and BIU indicated Before Insert or Update). Other types of triggers should be similarly named.

2.2 - Constraints

• Each table should have a primary key constraint enabled.
• Applications are easier to generate and data is easier to maintain if you use system generated primary keys. That said, each table that has a system-generated primary key should also have a unique key implemented (with both a unique constraint and mandatory column). This key is simply the 'real' primary key of the table - a column or combination of columns that makes each record unique.
• Whenever you have a column that references values from another table, you should have a Foreign Key implemented. This enforces that relationship at the database level and includes that in the definition of the table.
• All Foreign Key columns should be indexed. These are the columns that are typically used in joins so including a non-unique index for each foreign key column will increase your performance.
• When defining Foreign Keys, remember to use 'ON DELETE CASCADE' or 'ON DELETE SET NULL' whenever appropriate.
• Use Check Constraints wherever necessary. Even when you will have a List of Values limiting the contents of a particular column, it is good practice to also implement that in a check constraint. This enforces that rule at the database level for and protects your data when it might be modified via an interface that is lacking your list of values.
• Use Unique Keys for Look Up tables
  
  ALTER TABLE <TABLE_NAME>
  ADD CONSTRAINT <TABLE_NAME>_UK
  UNIQUE (<COLUMN_NAME>)

2.3 - Triggers

• When you have a system-generated Primary Key, you should populate it via a trigger. This ensures that the same value will be set if the data is created via another interface or directly on the table (using something other than your application such and the SQL Workshop or SQL Developer).
• When you do use a trigger to populate a Primary Key column, use the Global Unique Identifier (GUID), however, check to see if the primary key value has already been set with code like that below.
  
  IF INSERTING AND :NEW.ID IS NULL THEN
     SELECT TO_NUMBER(SYS_GUID(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
     INTO :NEW.ID
     FROM DUAL;
  END IF;
  
  By wrapping your set command in this if statement, you are allowing data to be loaded into your application that already has a primary key value. This helps with loading of sample data.
• We suggest using the Global Unique Identifier instead of a sequence. This ensures that primary key columns are populated uniquely while reducing the number of objects that need to be managed. It also helps to ensure uniqueness in a multi-node environment.

2.4 - PL/SQL

• PL/SQL procedures and functions should always be logically grouped into a package whenever possible. Only in rare circumstances should stand-alone procedures and function be implemented. Using packages has many advantages, including modularity, easier application design and maintenance, promotes information hiding, and can result in better performance.
• As with other objects, all packages should be prefixed with your application prefix. The remainder of the name should be descriptive of the purpose of the public methods exposed in the package.
• For variable naming, all global variables should be prefixed with 'g_' to indicate they are global. All local variables should be prefixed with 'l_' to indicate they are local. All parameters should be prefixed with 'p_' to indicate they are parameters.

2.5 - Other

• Each table should have the following columns to maintain audit information:
  
  

  Column Name	Type
  CREATED_BY	VARCHAR2(255)
  CREATED_ON	DATE
  LAST_UPDATED_BY	VARCHAR2(255)
  LAST_UPDATED_ON	DATE

  
  
• To ensure that the audit information is consistently added, ensure that they are populated using Triggers:
  
  IF INSERTING THEN
     IF :NEW.ID IS NULL THEN
        SELECT TO_NUMBER(SYS_GUID(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
        INTO :NEW.ID
        FROM DUAL;
     END IF;
     :NEW.CREATED_ON := SYSDATE;
     :NEW.CREATED_BY := nvl(v('APP_USER'),USER);
  END IF;
  
  IF UPDATING THEN
     :NEW.UPDATED_ON := SYSDATE;
     :NEW.UPDATED_BY := nvl(v('APP_USER'),USER);
  END IF;
  


• If you require full history of the updates made, you will need to accomplish that with a more robust data model.
• To ensure that the Underlying data model is flexible to future changes or expansion, each table should have least 5 redundant or 'Flex' Columns. For instance:
  



Column Name	Type
FLEX_01	VARCHAR2(4000)
FLEX_02	VARCHAR2(4000)
FLEX_03	VARCHAR2(4000)
FLEX_04	VARCHAR2(4000)
FLEX_05	VARCHAR2(4000)

3 - Application Development Guidelines

3.1 - Naming

• Whenever possible, Menu items, Breadcrumbs, Region Names, and Page Titles should be named consistently. If you access a page by clicking on a Menu Item called "Annual Report", the Page Title, Region Title and breadcrumb of the page displayed should also be "Annual Report". You might not realize it but the Page Title is displayed as the main title in your browser when the page is displayed.
• Reports should ideally be named in the plural. They display multiple rows.
• Forms should be named in the singular. They display only one row of data at a time. The exception to this is a Tabular Form.
• Labels for Display only Items should be end with a colon. This helps to separate the label from the data. Labels for enterable items should only end in a colon if the item type is a radio group.
• Avoid hard-coding text in a Template. Instead, values should be referenced using Substitution Strings or Shortcuts. These can then be referenced as #SHORTCUT_1# in your template.

3.2 - Report Formatting

• Numeric values should be displayed as Right Justified.
• Use appropriate format masks on numbers.
• Consider changing upper case data to initcaps for reports. Uppercase text takes up much more horizontal space and draws more attention from a user than it should.
• Reports based on table should include the ‘flex’ columns but should not be displayed. This will make it easier for a developer to expose them in the future.
• Use a consistent Pagination Scheme for all reports within an Application. You can deviate from this whenever you have a special report that might perform poorly using your given scheme. Example of this are when your selected pagination is rows x to y of z. If you have a report that could return a million records, you would not want to incur the performance hit of calculating the z.

3.3 - Forms

• Any items displayed should use the 'Form Field Optional' or 'Form Field Required' label templates provided in Application Express. This visually indicates to your users which items are mandatory.
• Any page that captures data for processing should have suitable validations. You should catch the user at the point of data entry and check that their data is not null or numeric or meets any other criteria that you need. Validations should always be defined for any items with a 'Form Field Required' template and all NOT NULL columns in the underlying table (unless their value is set by a trigger). Remember to use consistent wording when creating your error messages.
• All items that are displayed should include help text. You may want to wait until the end of development cycle to create your help so that you can keep it consistent across your application.
• One great way to provide accurate and consistent help text is to define comments on your table columns and then default User Interface Defaults. When this is done, the column comments are read in and will then be used to default help text for any form item created against that column.
• Delete actions should include a confirmation step. This protects users from accidentally clicking a delete button and permanently removing data. This can be accomplished by including this javascript as the URL target for your delete button:
  
  javascript:confirmDelete('&DELETE_MSG.','DELETE');
  
  DELETE_MSG is an Application Substitution string. You could also hardcode text instead.
• It is a good practice to have a success or failure messages after each action (e.g. Save, Delete etc.). Ensure that your branches are set to 'include process success' so that your messages will be displayed to the user even when leaving the page.
• Multi-Step processes should store data in the relevant tables only on completion of the final step. For instance, creating a new user should store the profile only once the user confirms his details and submits.
• If your Application is required to store/maintain temporary data, you should use 'Collections' (a feature of the Database). This relieves you from using your own temporary table, which you would need to maintain.
• Forms based on table should include the 'flex' columns but should not be displayed. This will make it easier for a developer to expose them in the future.
• On each page containing a form, include the auditing columns for the base table but put them in a separate region with a region template of hide and show. This region should be made conditional and display audit data only if the Primary Key is Not Null.
• The Audit Columns should be made display only, with labels as bold text labels and end each label with a colon. This helps to differentiate the label from the data

3.4 - Other

• Group all "Maintenance and Administration" Pages under the 'Administration' Tab, with each function listed under a form and a report.
• If you have a group of applications that can be glued together to form a Suite of Applications, ensure that they have a consistent look and feel. Begin by selecting a common Theme for your applications.
• Decode your data using Lists of Values. This means that if you are displaying a column that has a Y indicating Yes and an N indicating No, create static List of Values and associate with that report column to 'decode' the value. This saves you from including a decode or case statement in your select, allows new values to be easily entered in the future and allows for translation of the data if necessary.
• Use a consistent date format throughout your entire application. Use Application Substitution string named APP_DATE_FORMAT. Set the default format to DD-MON-YYYY and reference it (as &APP_DATE_FORMAT.) for each item and report column that displays dates. When you package your application, include this as a substitution string for the installation so that it can be changed on install.
• Similarly for Date and time formats use the Application Substitution string named APP_DATETIME_FORMAT. Set the default format to DD-MON-YYYY HH24:MI.
• Passing of character data values in a URL is strongly discouraged. Navigation between pages is often accomplished via URLs and passing of data values to set session sate on the target page. Numeric values should be passed and the corresponding textual values retrieved via On-Load Page Processes on the target pages.
• For Applications using passwords to authenticate users, always store encrypted passwords. Also, these should not be visible to the Application Administrator at any time. Only the user should be able to change their password.

4 - Packaging Your Application

4.1 - Messages

• There are several messages that can be displayed to your user. They include Welcome, License, Application Substitutions, Build Options, Validations, Confirmation, Post Installation and Deinstallation.
• Most messages are simply displayed to the user along choices that they have (like Application Substitutions and Build Options). These types of messages should be used to explain the selections and their ramifications.
• If a License Message is included, the installing user will need to accept the terms before continuing.

4.2 - Prerequisites

• Free Space: This should always be defined. You can calculate the space that your initial set of objects (and sample data) needs by running a Workspace Utilization Report before and after you install your application. By determining the number of bytes that the installation used and dividing that by 1,024 you will have the amount of Free Space, in KB, that a user will need to install your application.
• System Privileges: Review the types of objects that your installation scripts will create and check all those appropriate. This way if you need to create a type but the target user does not have that privilege, they will get an elegant error message before any installation script is executed.
• Object Names: You should list all the objects that you will create. This way those names will be checked for before the installation script is executed. You want to protect your user from installing your application that has a PROJECTS table into a schema that already has a PROJECTS table. Although they would see an installation error, if they deinstalled the application, you could accidentally drop their preexisting table. This Object Name check protects against that.

4.3 - Substitutions

• If your application contains Substitution Strings at the application-level, you can expose those here for the installing user to change. Examples of this would be if you have a substitution string used for the product name. You could allow the installing user to provide a new name.

4.4 - Build Options

• If your application contains Build Options, you can expose those here for the installing user to set on or off. You might have some optional functionality that they do not want and this both lets them know and also mitigates the need for them to go to build options after installation to turn one off. If you include these, remember to define a message that explains them.

4.5 - Validations

• Validations are similar to prerequisites but can be anything. You may need to check that a certain database option is installed or verify that the database is 9iR2 or greater. A validation is where you would do that.

4.6 - Scripts

• Be careful with the sequence of your scripts (e.g. create tables before triggers) and the sequence of objects within your scripts. An incorrect sequence often results in install failures due to database object dependencies. If you have many foreign keys, you may need to disable them upon create and then have a separate script to enable them.
• Include a script containing comments for each table and column. This improves the readability of your database definition and assists others when they need to add a new report to your application.
• If you are packaging your application for review or demonstration purposes, include sample data. This would be accomplished with an additional installation script that creates sample data. If you do include sample data, consider making a script available to your users that will cleanly remove the sample data.

4.7 - Deinstall

• The deinstallation script needs to remove every object that you create with your installation scripts. The end result of deinstallation should leave no trace of the installed application.

4.8 - Other

• If publishing your packaged application, ensure that the .sql and .zip file names are consistent with the Application name and its version.
• Including a readme.txt file in the installation .zip file is highly recommended. It should contain information such as:
     - A description of the application
     - The purpose of the included files
  
  • How to import and install the application
  • How to use the script to remove the sample data
  • How to deinstall the application
     - Any default username/password that would be required to run the application.
     - Any other information the installing user might need to know.

Criteria for Judging Submissions

• User Interface (e.g. overall appearance, consistency of presentation).
• Usability (e.g. ease of use, navigation).
• Feature Set (e.g. functionality provided, business applicability).
• Production Quality (e.g. no major bugs).
• Documentation (e.g. installation instructions, coding documentation [inline or separate], user guide [optional]).

• Matt Chivers - Oracle
• Dimitri Gielis - APEX Evangelists
• Justin Kestelyn - Oracle
• Thomas Marshall - Auburn University
• Anton Nielsen - C2 Consulting
• John Scott - APEX Evangelists
• Scott Spendolini- Sumner Technologies

Prizes / Awards

• First Place - An Unconference Session and One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
  
• Second Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
• Third Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
• Fourth through Twelfth Place - One copy of the book Pro Oracle Application Express, Apress Publishing, written by John Scott & Scott Spendolini.

Registration