When building many APEX applications, it is good to think about some best practices concerning as you go along.
APEX is so easy to get started with, that many developers just start an application, as a prototype, and gradually this application is adapted by the end users, and “glides” into a production state. Thinking about at few simple guidelines during the development phase, can really help make the maintenance/enhancement phase a lot easier.
I have divided the best practices into 3 areas: Installation, Pre-development and Development. Many of the practices are learnings from APEX books, seminars, and experience from many APEX projects.
The APEX installation is fairly simple, however here are a few tips:
- Install APEX in its own tablespace (I always install APEX objects in a tablespace called “APEX”). This way the APEX objects can be backup up separately if needed, and it is easier to control the growth.
- The same goes for tablespaces for your APEX projects. I create 1 tablespace per APEX workspace (before reating the workspace). The database schema for the workspace should be created like “CREATE USER APP1 DEFAULT TABLESPACE APP1″. After creating the database user, create the workspace (based on the existing user).
- Concerning choice of web server, the new APEX listener is really great for serving the dynamic APEX content from the database. However running the listener in “stand alone” mode should only be used when developing -not in production. I have seen that rendering static files in the standalone mode is not optimal.
- Concerning your own (custom) static files, it is best to keep these in a separate directory on the server. Remember that when APEX is updated, the APEX image catalog will be replaced with files from the new installation.
When starting APEX development, the following advice is good to remember:
- Create seperate user accounts for the developers. At some point it could be necessary to see who changed what.
- Setup “User Interface Defaults”. This setup is performed on table/column level, and is only necessary to do once. Setup as much as you can : Prompts, Help text, lengths, item types etc. These values are used when creating new components (regions, fields..) using the APEX wizards.
- Determine the strategy for re-use. If the application components (List of values, templates…) can be re-used in other applications, you should consider created a “master” application containing re-usable components. The new application should then “subscribe” to the components in the master application.
- Determine which theme to use. If all applications used in the company should have the same look and feel, consider setting up a company specific theme. Select one of the standard APEX themes, and modify this to your company needs.
- Design the data and process model. The SQL Developer tool from Oracle includes the “Datamodeller” which can be a great help in creating these models. For larger applications, consider integrating the model with the APEX application. This can be achieved by exporting the model from SQL developer (into a “Reporting schema”).
- Decide the security model for your application. A number of Authorization schemes should be created, and can then be used when creating new tabs, pages and other APEX components.
- Decide the strategy for deployment between environments (dev/test/prod). Basically the APEX application (and database objects) can be deployed manually (using the standard DDL files, program files and APEX .sql file), or “packed” into a single .sql using “Supporting Objects”. The build in supporting objects is most useful, when the same application should be deployed on multiple environments (ex. when selling the application to multiple customers). If manuel deployment is chosen, consider creating a install.sql file which created database objects, and deploys PL/SQL program modules etc.
- Set up version control. This is always a point asked by new users, since APEX version control differs from many other development environments. Know this: APEX has build in tracking (and reports) to show which developer changed what at wha time. The standard file artifact from APEX is a single .sql file containing the complete application. This singe file can be saved in a standard version control system (CVS, SVN…) however is can not really be used to show the application changes (its just a bunch of PL/SQL calls which builds the application). Consider using the APEXReportSplitter if there is a need to store and track each APEX component in a file based versioning system.
During the development phase, these tips can make life easier:
- Place all PL/SQL logic in database packages. This way it is much easier to maintain the code, and use IDE’s which are best at coding in PL/SQL. Another benefit is that the stored PL/SQL program units can be re-used by other programs. It means keeping the presentation logic seperate from controlling and domain logic (MVC pattern). Note that SQL Developer has a tool for migrating PL/SQL code in APEX to packages.
- Plugins can be downloaded from several sites, and used in the APEX application. This is a great way to improve the look and feel of the application without spending a lot of development time. The sites are APEX-plugin.com and the Oracle plugin repository.
- When find yourself creating the same components several times, consider building your own plugins. Company wide plugins are highly efficient for keeping the same look and feel, and then the code has en single place of maintenance. When creating these enterprise plugins, the PL/SQL code behing the plugin can be placed in database packages (in a common database schema). So even if the plugin instance is loaded into many applications, the code behind the plugin can be maintained in one place (per database).
- Page Zero: Another way to re-use with the application, is to place components on Page Zero. You can use the “Condition” to specify which page(s) the component should be displayed on.
- One of the greatest advantages in APEX is the Interactive Reports (IR). Use this whenever you can, because it works great for the in-experienced user, and at the same time gives great reporting capability to the more advanced end users. However take care concerning the query behind the report. Especially function calls in the column list can really drain the CPU.
- Aliases: Use aliases for applications and for the pages which are linked directly by users. The application and page id can change, so a link containing aliases are better.
More on best practices (in Danish) here.
Happy APEX Developing, and let me know what should be added to the list
Martin B. Nielsen