Oracle APEX Best Practices — Save 50%
Accentuate Oracle APEX development with proven best practices with this book and ebook
In this article by Alex Nuijten, Iloon Ellen-Wolff, and Learco Brizzi, authors of Oracle APEX Best Practices, we will discuss different aspects of setting up an Application Express (APEX) environment. Among others, we'll take a look at installing APEX, performing preparational tasks before actually building applications, and transforming the data model into initial screens.
(For more resources related to this topic, see here.)
Let's take a look at the history and background of APEX.
History and background
Before APEX there was WebDB, which was based on the same techniques. WebDB became part of Oracle Portal and disappeared in silence. The difference between APEX and WebDB is that WebDB generates packages that generate the HTML pages, while APEX generates the HTML pages at runtime from the repository. Despite this approach APEX is amazingly fast.
Because the database is doing all the hard work, the architecture is fairly simple. We only have to add a web server. We can choose one of the following web servers:
- Oracle HTTP Server (OHS)
- Embedded PL/SQL Gateway (EPG)
- APEX Listener
APEX became available to the public in 2004 and then it was part of version 10g of the database. At that time it was called HTMLDB and the first version was 1.5. Before HTMLDB, it was called Oracle Flows , Oracle Platform, and Project Marvel. Throughout the years many versions have come out and at the time of writing the current version is 4.1.1. These many versions prove that Oracle has continuously invested in the development and support of APEX. This is important for the developers and companies who have to make a decision about which techniques to use in the future. According to Oracle, as written in their statement of direction, new versions of APEX will be released at least annually. The following screenshot shows the home screen of the current version of APEX:
Home screen of APEX
For the last few years, there is an increasing interest in the use of APEX from developers. The popularity came mainly from developers who found themselves comfortable with PL/SQL and wanted to easily enter the world of web-based applications. Oracle gave ADF a higher priority, because APEX was a no cost option of the database and with ADF (and all the related techniques and frameworks from Java), additional licenses could be sold.
Especially now Oracle has pointed out APEX as one of the important tools for building applications in their Oracle Database Cloud Service, this interest will only grow. APEX shared a lot of the characteristics of cloud computing, even before cloud computing became popular. These characteristics include:
- Roles and authorization
- Browser-based development and runtime
- RESTful web services (REST stands for Representational State Transfer)
- Simple and fast to join
APEX has outstanding community support, witnessed by the number of posts and threads on the Oracle forum. This forum is the most popular after the database and PL/SQL.
Oracle itself has some websites, based on APEX. Among others there are the following:
Oracle uses quite a few internal APEX applications.
Oracle also provides a hosted version of APEX at http://apex.oracle.com. Users can sign up for free for a workspace to evaluate and experiment with the latest version of APEX. This environment is for evaluations and demonstrations only, there are no guarantees! Apex.oracle.com is a very popular service—more than 16,000 workspaces are active. To give an idea of the performance of APEX, the server used for this service used to be a Dell Poweredge 1950 with two Dual Core Xeon processors with 16 GB.
In this section, we will discuss some additional considerations to take care of while installing APEX. The best source for the installation process is the Installation Guide of APEX.
Runtime or full development environment
On a production database, the runtime environment of APEX should be installed. This installation lacks the Application Builder and the SQL Workshop. Users can run applications, but the applications cannot be modified. The runtime environment of APEX can be administered using SQL*Plus and SQL Developer. The (web interface) options for importing an application, which are only available in a full development environment, can be used manually with the APEX_INSTANCE_ADMIN API. Using a runtime environment for production is recommended for security purposes, so that we can be certain that installed applications cannot be modified by anyone.
On a development environment the full development environment can be installed with all the features available to the developers.
Besides the environment of APEX itself, the applications can also be installed in a similar way. When importing or exporting an application the Run Application Only or Run and Build Application options can be selected.
Changing an application to Run Application Only can be done in the Application Builder by choosing Edit Application Properties. Changing the Build Status to Run and Build Application can only be done as the admin user of the workspace internal. In the APEX Administration Services, choose Manage Workspaces and then select Manage Applications | Build Status.
Another setting related to the Runtime Only option could be used in the APEX Administration Services at instance level. Select Manage Instance and then select Security. Setting the property Disable Workspace Login to yes, acts as setting a Runtime Only environment, while still allowing instance administrators to log in to the APEX Administration Services.
Following the install guide for the full development environment, at a certain moment, we have to run the following command, when logged in as SYS with the SYSDBA role, on the command line:
@apexins tablespace_apex tablespace_files tablespace_temp
The command is explained as follows:
- tablespace_apex is the name of the tablespace that contains all the objects for the APEX application user.
- tablespace_files is the name of the tablespace that contains all the objects for the APEX files user.
- tablespace_temp is the name of the temporary tablespace of the database.
- images will be the virtual directory for APEX images. Oracle recommends using /i/ to support the future APEX upgrades.
For the runtime environment, the command is as follows:
@apxrtins tablespace_apex tablespace_files tablespace_temp
In the documentation, SYSAUX is given as an example for both tablespace_apex and tablespace_files. There are several reasons for not using SYSAUX for these tablespaces, but to use our own instead:
- SYSAUX is an important tablespace of the database itself
- We have more control over sizing and growth
- It is easier for a DBA to manage tablespace placement
- Contention in the SYSAUX tablespace is less occurring
- It's easier to clean-up older versions of APEX
- And last but not least, it's only an example
Converting runtime environment into a full development environment and vice versa
It's always possible to switch from a runtime to a production environment and vice versa. If you want to convert a runtime to a full development environment log in as SYS with the SYSDBA role and on the command line type @apxdvins.sql. For converting a full development to a runtime environment, type @apxdevrm—but export websheet applications first.
Another way to restrict user access can be accomplished by logging in to the APEX Administration Services, where we can (among others) manage the APEX instance settings and all the workspaces. We can do that in two ways:
http://server:port/apex/apex_admin: Log in with the administrator credentials
http://server:port/apex/: Log in to the workspace internal, with the administrator credentials
After logging in, perform the following steps:
- Go to Manage Instance.
- Select Security.
- Select the appropriate settings for Disable Administrator Login and Disable Workspace Login. These settings can also be set manually with the APEX_INSTANCE_ADMIN API.
Choosing a web server
When using a web-based development and runtime environment, we have to use a web server.
Architecture of APEX
The choice of a web server and the underlying architecture of the system has a direct impact on performance and scalability. Oracle provides us with three choices:
- Oracle HTTP Server (OHS)
- Embedded PL/SQL Gateway (EPG)
- APEX Listener
Simply put, the web server maps the URL in a web browser to a procedure in the database. Everything the procedure prints with sys.htp package, is sent to the browser of the user. This is the concept used by tools such as WebDB and APEX.
The OHS is the oldest of the three. It's based on the Apache HTTP Server and uses a custom Apache Module named as mod_plsql:
Oracle HTTP Server
In release 10g of the database, OHS was installed with the database on the same machine. Upward to the release 11g, this is not the case anymore. If you want to install the OHS, you have to install the web tier part of WebLogic. If you install it on the same machine as the database, it's free of extra licence costs. This installation takes up a lot of space and is rather complex, compared with the other two. On the other hand, it's very fl exible and it has a proven track record. Configuration is done with the text files.
The EPG is part of XML DB and lives inside the database. Because everything is in the database, we have to use the dbms_xdb and dbms_epg PL/SQL packages to configure the EPG. Another implication is that all images and other files are stored inside the database, which can be accessed with PL/SQL or FTP, for example:
Embedded PL/SQL gateway
The architecture is very simple. It's not possible to install the EPG on a different machine than the database. From a security point of view, this is not the recommended architecture for real-life Internet applications and in most cases the EPG is used in development, test, or other internal environments with few users.
APEX Listener is the newest of the three, it's still in development and with every new release more features are added to it. In the latest version, RESTful APIs can be created by configuring resource templates. APEX Listener is a Java application with a very small footprint. APEX Listener can be installed in a standalone mode, which is ideal for development and testing purposes. For production environments, the APEX Listener can be deployed by using a J2EE compliant Application Server such as Glassfish, WebLogic, or Oracle Containers for J2EE:
Configuration of the APEX Listener is done in a browser. With some extra configuration, uploading of Excel into APEX collections can be achieved. In future release, other functionalities, such as OAuth 2.0 and ICAP virus scanner integration, have been announced.
Configuration options of the APEX Listener
Like OHS, an architectural choice can be made if we want to install APEX Listener on the same machine as the database. For large public applications, it's better to use a separate web server.
Many documents and articles have been written about choosing the right web server. If you read between the lines, you'll see that Oracle more or less recommends the use of APEX Listener. Given the functionality, enhanced security, file caching, fl exibility of deployment possibilities, and feature announcements makes it the best choice.
Creating a second administrator
When installing APEX, by default the workspace Internal with the administrator user Admin is created. Some users know more than the average end user. Also, developers have more knowledge than the average user. Imagine that such users try to log in to either the APEX Administration Services or the normal login page with the workspace Internal and administrator Admin, and consequently use the wrong password. As a consequence, the Admin account would be locked after a number of login attempts. This is a very annoying situation, especially when it happens often. Big companies and APEX Hosting companies with many workspaces and a lot of anonymous users or developers may suffer from this. Fortunately there is an easy solution, creating a second administrator account.
Login attempt in workspace Internal as Admin
If the account is already locked, we have to unlock it first. This can be easily done by running the apxchpwd.sql script, which can be found in the main Apex directory of the unzipped installation file of APEX:
- Start SQL*Plus and connect as sys with the sysdba role
- Run the script by entering @apxchpwd.sql.
- Follow the instructions and enter a new password.
Now we are ready to create a second administrator account. This can be done in two ways, using the web interface or the command line.
APEX web interface
Follow these steps to create a new administrator, using the browser.
First, we need to log in to the APEX Administrator Services at http://server:port/apex/. Log in to the workspace Internal, with the administrator credentials
After logging in, perform the following steps:
- Go to Manage Workspaces.
- Select Existing Workspaces.
- You can also select the edit icon of the workspace Internal to inspect the settings. You cannot change them. Select Cancel to return to the previous screen.
- Select the workspace Internal by clicking on the name.
- Select Manage Users. Here you can see the user Admin.
- You can also select the user Admin to change the password. Other settings cannot be changed. Select Cancel or Apply Changes to return to the previous screen.
- Select Create User. Make sure that Internal is selected in the Workspace field and APEX_xxxxxx is selected in Default Schema, and that the new user is an administrator. xxxxxx has to match your APEX scheme version in the database, for instance, APEX_040100.
Click on Create to finish.
Settings for the new administrator
When we still have access, we can use the web interface of APEX. If not we can use the command line:
- Start SQL*Plus and connect as SYS with the SYSDBA role.
Unlock the APEX_xxxxxx account by issuing the following command:
alter user APEX_xxxxxx account unlock;
- Connect to the APEX_xxxxxx account. If you don't remember your password, you can just reset it, without impacting the APEX instance.
Execute the following (use your own username, e-mail, and password):
BEGIN wwv_flow_api.set_security_group_id (p_security_group_id=>10); wwv_flow_fnd_user_api.create_fnd_user( p_user_name => 'second_admin', p_email_address => 'email@example.com', p_web_password => 'second_admin_password') ; END; / COMMIT /
The new administrator is created. Connect again as SYS with the SYSDBA role and lock the account again with the following command:
alter user APEX_xxxxxx account lock;
- Now you can log in to the Internal workspace with your newly created account and you'll be asked to change your password.
When an administrator of a developer workspace loses his/her password or has a locked account, you can bring that account back to life by following these steps:
- Log in to the APEX Administrator Services
- Go to Manage Workspace.
- Select Existing Workspaces.
- Select the workspace.
- Select Manage Users.
- Select the user, change the password, and unlock the user.
A developer or an APEX end user account can be managed by the administrator of the workspace from the workspace itself. Follow these steps to do so:
- Log in to the workspace.
- Go to Administration.
- Select the user, change the password, and unlock the user.
|Accentuate Oracle APEX development with proven best practices with this book and ebook|
eBook Price: $29.99
Book Price: $49.99
We have set up the APEX environment, now let's focus on the database. For means of consistency and maintainability, it's a good practice to use standards and guidelines. In this section, we will describe some standards and guidelines for data modeling, database objects, and PL/SQL usage.
When the requirements are clear, we can create a data model. A data model provides the structure, definition, and format of the data.
We will translate the requirements into tables, columns, and relations. It will be the single point of truth of our system. Because our whole system is built upon this model, it's very important to spend sufficient time on it. A data model is also a great means to communicate with your customers and among developers about the system and design of the database. When a database is well designed, it can be easily maintained for future development.There are a number of Computer Aided Software Engineering (CASE) tools that you can use to create a data model. Besides data modeling, some of these CASE tools can also be used to maintain all the PL/SQL packages, functions, procedures, and trigger code that we use in our applications. Oracle itself provides Oracle Designer and Data Modeler from SQL Developer. The following diagram shows Data Modeler. One of the advantages of using such a tool is the ability to generate and reverse engineer the database creation scripts in an intuitive graphical manner.
SQL Developer Data Modeler
Relations between the tables are always in a one-to-many relationship; for example, a user can perform one or more searches. We can use colors to differentiate between tables that have a lot of mutations and tables that don't have any. Those tables can be candidates for creating lists of values.
A great example of standards and guidelines is Oracle's well-documented CDM RuleFrame. Database modeling standards and guidelines can be as follows:
- Table names are written in plural.
- Check constraints will be used for short domains on columns. If they are long or not know yet, we use a lookup table.
- The primary key is always named as id. This is useful when we want to write reusable generic code.
- For each table we define a short three- or four-letter alias.
- Foreign key column names are constructed as follows:
- The alias of the join table name is postfixed with id.
- For every foreign key we define an index.
We use database triggers to populate the primary keys and use one sequence that will be used to populate all the primary keys. For the triggers, a script such as the following can be used for all tables:
CREATE OR REPLACE TRIGGER doc_bir BEFORE INSERT ON documents FOR EACH ROW BEGIN :new_id := NVL(:new_id,all_seq.NEXTVAL); END; /
An alternative to triggers and sequence is the use of sys_guid(). On the Internet, a lot of information about the pros and cons for both approaches is available. Define all the id columns as the RAW(16) columns and use sys_guid() as a default value for the id column. For example:
CREATE TABLE t ( id RAW(16) DEFAULT sys_guid() PRIMARY KEY , column_a VARCHAR2(10) , column_b VARCHAR2(10) ... ) /
Creating the database objects
The first thing we have to do is create the database schema, which will hold the database objects. We can use the SQL Workshop of APEX for creating the database objects, but its use is very limited compared to the specialized CASE tools.
The following objects can be created in the application schema:
- Primary keys
- Unique constraints
- Foreign keys
- Indexes on the foreign keys
- Other indexes
- Scripts for insert and update triggers on the tables to generate an ID
- Other objects (packages, materialized views, and so on)
Beside the tools for creating a model, we need some tools during the further development process, tools for accessing the database easily, and tools for web development. Without going into detail, we will just name a few tools that you can use.
Examples of database tools are:
- SQL Developer
- PL/SQL Developer
- Web Developer
- Internet Explorer Developer Tools
- Built-in tools in the browser
- Versioning tools
- Performance measurement tools
- GUI design tools
We use the following guidelines regarding PL/SQL:
- Keep PL/SQL in APEX to an absolute minimum
- Try to store all your PL/SQL in packages in the database
- Replace PL/SQL blocks in APEX with simple calls to the functions and procedures in those packages.
This approach has the following advantages:
- Easier to debug
- Higher maintainability, due to more structure
- Better reusability possible
- Don't deploy an application each time there is a change in PL/SQL
- Easier to tune
Creating a workspace
We have installed APEX and the database objects, now we have to create a workspace that will hold our applications. A workspace is linked to one or more schemas in the database. Workspaces can contain zero or more applications. An application in a workspace can access all the objects of the workspace schemas. The following diagram explains the relation between workspaces and applications:
Relationship between workspaces and applications
If security issues are expected, it is possible to create a dedicated empty schema for the workspace and grant access to only the database objects that are needed for that schema. Applications in that workspace can only access the objects that have been granted to the schema.
When we want to create a workspace we have to log in to the APEX Administration Services. In APEX there are two special workspaces. Internal as mentioned before is, as the name implies, the internal workspace used by APEX itself. There is also another workspace called com.oracle.apex.repository, which is used for themes.
Once logged in to the Internal workspace, we see the screen shown in the following screenshot:
Creating a workspace
We have two possible ways to create a workspace: directly by using the button or indirectly through the Manage Workspaces option, where we have to click on Create Workspace. After filling in the details in the wizard, the workspace will be created.
Creating administrators, developers, and users
When creating a workspace, we also need to create an administrator for the workspace. If the workspace is in place, we could log in to the workspace as the administrator and add other administrators, developers, and end users.
User Interface Defaults
After creating the workspace and users, it's the moment to use the User Interface Defaults possibilities in APEX—do not start building applications immediately. This option isn't used very much in practice, but it's a very useful utility. With User Interface Defaults, we populate initial values and control the appearance and behavior of items when using them in reports and items (in forms). User Interface Defaults can accelerate your development and result in less repetition of tasks in APEX. You can compare its possibilities with table and column properties in Oracle Designer.
User Interface Defaults don't work retroactively; they only apply to newly built objects. User Interface Defaults can provide consistency across multiple pages for all the applications in our workspace. It's also possible to export and import the User Interface Defaults, to use them in another workspace.
APEX provides two types of dictionaries for this purpose:
- Attribute Dictionary
- Table Dictionary
The Attribute Dictionary consists of a simple set of attributes. The definitions are matched only by column name, and a particular definition can be shared amongst several columns by creating synonyms.
In the Table Dictionary, the defaults are defined by table and column combinations. The Table Dictionary is more specific than the Attribute Dictionary, because more properties can be defined in this one. When processed during the use of a creation wizard for a region or item, an entry in the Table Dictionary takes priority over an entry in the Attribute Dictionary. On table level, we can define some region defaults. It's also possible to migrate the Table Dictionary to the more generic Attribute Dictionary. When migrating, you lose some default properties that don't exist in the Attribute Dictionary (for instance the list of values information).
Another functionality of the Table Dictionary is the use of Column Groups. Related columns within a table can be grouped together. In forms, these groups appear as separate regions and in the single row view of interactive reports.
Creating User Interface Defaults
User Interface Defaults can be defined at the workspace level. Follow these steps to get there:
- From the Home screen, select SQL Workshop.
- Select Utilities.
Then, select User Interface Defaults.
User Interface Defaults Dashboard
On this screen, you'll see the Dashboard tab of the User Interface Defaults. You can also see the tabs for Table Dictionary and Attribute Dictionary.
The simplest way to start using User Interface Defaults is to synchronize the database tables with the Table Dictionary. New tables and columns from the database are added to the Data Dictionary that doesn't exist yet, as well as table and columns that don't exist in the database anymore as they are removed from the Table Dictionary.
After selecting a table entry, we see the following screen:
On this screen, you can select a column to define the default properties or select Edit Table Defaults to define the region titles for this table. You will also see a list of tasks that you can perform. After selecting a column, we see the following screen. This is where the actual work will be done. You can see, for instance, the different sections for Form Defaults and Report and Tabular Form Defaults.
Defaults for the Table Dictionary
It's good practice to use the plural name of the table for the region name of reports. For the name of the region of a form, we can use the singular name of the table.
A disadvantage of the defined list of values information is the fact that they are defined at workspace level and not at application level, so they cannot be shared like the defined list of values in the shared components.
After filling in all the defaults for all the tables and columns in the Table Dictionary, we can define the Attribute Dictionary by migrating some Table Dictionary entries or defining new ones. In the following screen, you can see the properties that you can define in the Attribute Dictionary:
Defaults for the Attribute Dictionary
Besides the property screen for the Attribute Dictionary, we can also use a grid edit to define the defaults easily.
Page Zero acts as a template page for the application. All components of Page Zero will be rendered on all the pages in the application. Compared to a normal page, it consists of only the page rendering part. The following components can be used on Page Zero:
- Regions, for example, forms, reports, lists, and HTML
- Dynamic actions
In combination with the use of conditions for the components that determine on which specific pages the components will be rendered, is a fl exible place for reusable components. Think, for example, of a menu made up of a list region, centralized help functionality, personal information, task list, and so on
Page Zero has no processing part. The pages themselves—where the components of Page Zero will be rendered when running the application—will take care of this.
Structure of multiple applications
When creating applications, the less repeating work is done, the better. Within an application you can use shared components for this purpose. If our application (or better said, system) consists of more application modules than one big application, we need another approach. Between multiple applications in the same workspace, we can use the less known subscribe feature of APEX.
By using the subscribe and publish mechanism of APEX, we can create a structure of multiple applications that can serve as a good starting point. Some benefits:
- Better maintainable look and feel across multiple applications
- The use of shared components across applications
- Modular structure
- Reusable centralized functionality, such as authorization, authentication, auditing, and logging
Subscribe and publish
The subscribe and publish mechanism is very straightforward. In the following screenshot, we can see the Subscription part of a template definition:
Subscription part of a template
Subscribing and publishing can be compared to pull and push. When defining a template, for instance, it's possible to reference a master template from another application. This is shown in the following screenshot:
Subscribed to a master template
As we can see, there's a button for refreshing the template. After clicking on the button, the definition of the master template is loaded and copied over the current template. This is the pulling method. On the template overview screen, there's also a column Subscribed that shows which templates subscribe to a master template. Multiple template refreshing can be accomplished by clicking on the Subscription tab on the Templates overview screen.
The pushing method is done via the master template and it is shown in the following screenshot:
Subscribed from a master template
When we click on the Publish Template button, APEX published this master template to all subscribed templates from other applications. Multiple template publishing can be accomplished by clicking on the Publish tab on the Templates overview screen.
The shared components that can be subject for the subscribe and publish mechanism of APEX are as follows:
- Templates (themes)
- List of values
- Navigation bar entries
- Authentication schemes
- Authorization schemes
- Subscribed objects are updatable, but refreshing them will overwrite these changes.
- Subscribing and publishing only works within a single workspace. It's not possible to reference, for instance, a template from an application in another workspace.
|Accentuate Oracle APEX development with proven best practices with this book and ebook|
eBook Price: $29.99
Book Price: $49.99
Creating a framework
A well thought out structure of applications can serve as a framework for all our future development. To create such a framework, we need a number of applications, depending on the complexity of the system and our specific needs. The applications to build are a master application, template application, and optionally a login application and/or a system application. The template application serves as a basis for the actual applications to build (application 1 to n), as we can see in the following diagram:
Master and template application
Create a master application that contains publishable shared components such as authentication schemes, authorization schemes, lists of values, and templates. Changes can be pushed from the master application to all subscribing applications. Other applications will reference the standard templates by subscribing to the master application.
To make newly created applications subscribe to the master application, we need to create a template application. Create the template application as a copy of the master application and change the subscription of all the shared components to reference the master application. To change the subscription of multiple templates, you can replace templates in this application with templates from another application. This can only be done for currently used templates. Another option is to delete templates and recreate them as a copy (and subscribe) from the master application. The remaining templates and shared components can be modified manually.
The template application is also the place where Page Zero is added.
In more complex situations, the master can have more than one theme or more variants of shared components. We can then create more than one template—which serve as a starting point for the applications.
To create a new application, we simply need to make a copy of the template application. Because the new application is a copy, all the subscriptions to the master application are also copied. For the shared components, it's always possible to unsubscribe. To unsubscribe from multiple templates, the Unsubscribe Templates option from the Task menu can be selected.
When more than one application in the workspace share the same authentication, we can extend the framework with an application dedicated to the authentication process. This application will handle the login and can serve as a starting menu for the other applications.
We can create this login application as a copy of the template application. The authentication of this application is different than the other applications. Here we create the actual authentication schema, which we want to use in all our applications.
When switching between applications without having to log in again, we need to share the session state between the applications. To do so we have to give the session cookie the same name. We do that in the authentication schema of the master application.
We also need to get redirected to this application when a user tries to log in directly to one of the applications. We can accomplish this by redirecting to this application by setting the Session Not Valid property of the authentication schema in the master application to redirect to the login application. In APEX 4.1.1, you are automatically redirected to the calling application after successful login.
Optionally, we can also set the logout URL to point to a specific page in the login application.
In the following screenshot, we can see the relevant authentication scheme settings for the master application:
Authentication scheme settings
Other examples of pages that can be included in this application are as follows:
- User registration page for self-service applications: It can also have the functionality to send a confirmation e-mail with a link in it, which leads to an activation page.
- Application start page.
- Personal account settings page.
- Password forgotten page to reset an old password: An email with the new password is sent to the user. The e-mail can also contain a link to a landing page to change it immediately.
- Change the password page to let passwords expire after a given period of time.
- Help pages for the application(s).
Besides a login application, we can also create a system application for the more technically oriented system administrator or DBA. This application will offer control and maintenance functionality for all other applications and/or the whole system.
Possible functionality includes:
- Controlling e-mail queues.
- Performance reports of the pages and applications: This is also possible in APEX itself, but it could be useful to be able to access it in a convenient way within the application.
- Reading the APEX Feedback.
- Dealing with user approval of a self-service application.
- Inspecting (error) logs.
- Setting system parameters such as mail host address, mail host port, colors, and so on.
- Inspecting jobs.
- A structure for maintaining user and access roles: On this structure we can build some functionality that can be incorporated into the authorization scheme of the master application.
- Maintaining system parameters.
Depending on the situation, a part of this functionality can be part of the normal application administrator tasks of the system as well.
To deploy these applications, we just need to export them and then do an import in the target environment, just as a single application. If it applies to the situation, also deploy the login and system application. It's not necessary to deploy the master or template application.
If you develop many projects for different companies with different needs, we need to create a framework for every new customer/system. What we need is in fact a template workspace as the starting point for creating the actual workspace, with a master template and login/system applications in it with the minimal used common functionality.
When we start a new project, we need to create a new workspace and place all the applications in it with export and import. If we want to use the same application IDs, we have to set up such a template workspace in a separate database.
Now all the definitions are in place and preparation work is done, we can finally start building our applications. Go to the Application Builder in your workspace and create an application, either by making a copy of the template application or by clicking on the Create button.
List of values
One of the first things that we need to tackle is the creation of list of values. There are two kinds of list of values—static and dynamic. A static list of values consists of a limited number of possible values. A dynamic list of values is defined by a query that returns the possible values. For dynamic list of values, it's also possible to make them even more dynamic, by just typing in a function that returns (dynamically) a query.
Most of the list of values will be dynamic, but we can easily think of a few static list of values that will be used in almost every system and thus are a good candidate for the template application. Two examples of them are the Yes/No and the Male/Female list of values.
If for some reason we create a list of values definition for an item by typing in a query, instead of referencing a pre-created shared component list of values, we can easily create a real reusable list of values for it, by clicking on the Convert LOV task. This wizard will create a reusable component list of values and replace the hand-made query with a reference to the new list of values.
There are two sources to identify our first set of application-specific list of values. The first one is for short domains, used in check constraints of a column in a table. These values can be used in static list of values. The second source is to identify base tables and create dynamic list of values of them.
When creating list of values, always use the aliases d and r or display_value and return_value for the two columns in the query, just to be clear which values are displayed and returned.
After creating our first set of list of values, we need to add more list of values as we build our system.
Mapping the model to pages
The next step is to map our data model to APEX pages. With mapping we mean that for every table in the data model we must define pages (with respect to the desired functionality) to manipulate or query the data. There could be exceptions, such as parameter or logging tables, although pages for those tables could also be very useful, though not necessary for a properly functioning application.
We have some guidelines regarding pages:
- If we are just selecting and if we want the user to enhance and adjust the resulting (report) page, we should use interactive reports, otherwise we should use normal reports.
- Don't confuse the user with too many objects on a page. On the other hand, we don't want to create too many pages for simple tasks.
- Basic tables should be maintained on a single page.
- Forms can always be put on a separate screen and if necessary, can be called from a link in a report.
- Be sure to use User Interface Defaults for consistency.
- Use region columns where appropriate.
- Use nested region where appropriate.
Some other points to take into account are as follows:
- At the moment, it's not possible to put more than one tabular form or more than one interactive report on a single page.
- Drawback of having everything on a single page is the number of buttons with the same name, and so on. Rename some of them, but be consistent with that renaming throughout the application. Also, some kind of current record indicator is needed. This can be accomplished by manipulating the report template.
Basically we use the following regions for building or composing a page:
- SQL report
- Interactive report
- Form on a table or view
- Tabular form
- Form on table with report
- Master detail form
When we build a page, we look at the data model and along with the requirements we try to combine one or more of these regions. Also we link those regions to each other where appropriate. While building the pages we also create the processes, validations, computations, extra items, dynamic actions, and so on, which we need to achieve the desired functionality.
We begin with base tables that we have to maintain. These base tables are often used in LOVs. A way to recognize a base table is to look at the number of foreign keys. If there are no foreign keys in the table, it's a good candidate for a base table. Another characteristic of base tables is that the data is more or less static. It's also good practice to group these pages together on a separate tab with a name such as Basic Data or System.
Base table with one list of values
Depending on the number of columns in the base table we have two choices regarding the layout:
- If there are a few columns in the base table, we can use a tabular form if the total width of all the columns is not too wide when placed side by side. We don't want the user to scroll horizontally.
If we have too many columns, we can use a form on the table with a report to layout the columns neatly in the form. We could also use this approach when we have a few columns. If we do not want the user to switch between too many pages, we can generate both the form and report on one single page. We can accomplish that by filling in the same page number for the form and report in the wizard. After that we may want to place the report above the form.
Form on a table with report
By looking at the model, we can identify possible candidate tables for a master detail table.
Master detail tables with one LOV
In the Master Detail wizard, we have a lot of decisions to make. Always use a master report for navigation and don't use master row navigation, because it's a little bit confusing when navigating. We can choose to edit the detail as a tabular form on the same page.
Master detail page, detail as tabular form
Another option is to generate a report as a detail region with a form on a separate page or the same page. As with base table pages, it depends on the number of columns in the detail table.
Master detail page, detail on separate page as a form
If we do not want the user to switch between too many pages, we fill in the same page number for the components that we want to appear on the same page. After that we need to place the report above the form. Beware of using breadcrumbs when we put all the regions on one page. In that case, we will get the ORA-00001: unique constraint (APEX_040000.WWV_FLOW_UNIQUE_MENU_OPT) violated error.
Depending on whether the master is already a base table with its own page we can maintain that table here. If we don't want to maintain it here, we can hide that generated region, so the form will never be shown. We don't have to delete it, so that we can always use it later, if necessary. After the wizard, we have to rearrange some regions to get the right page.
Another variant is master detail. In that case, we have to link the regions together manually.
An intersection table can be recognized by the fact that they also have—besides their own ID—two IDs from the foreign keys. It's also possible that the intersection table contains other foreign keys or columns.
We can implement an intersection table as a master detail page with an LOV, but APEX also offers two alternatives to implement an intersection table—a shuttle and a multiselect list. We can implement one of the driving tables as a base table and use the other table as a lookup table. In the following screenshot we see an example of a shuttle:
In the following screenshot, we can see the use of a multiselect list:
Unfortunately, APEX doesn't offer standard processes for populating and maintaining shuttles or multiselect lists. The following function and procedure can be used as a generic solution for these processes. You should put them in a package and write exception handlers to log and deal with the errors that can occur. The function get_selectlist can be used to populate the item. We call this function in the On Load — After Header process and after the Fetch Row process, which is generated by the wizard (if present):
FUNCTION GET_SELECTLIST (P_INTERSECTION_TABLE IN VARCHAR2 ,P_LOOKUP_FK_NAME IN VARCHAR2 ,P_MASTER_FK_NAME IN VARCHAR2 ,P_MASTER_FK_VALUE IN VARCHAR2 ) RETURN VARCHAR2 IS -- Get the selectlist value as a list e.g. 1:2:4 . -- Create the process to fire After Header and after -- the wizard generated Fetch Row process. l_selected APEX_APPLICATION_GLOBAL.VC_ARR2; l_sql_statement VARCHAR2(1000); l_dummy_number NUMBER; BEGIN -- Check if master foreign key value is a number l_dummy_number := TO_NUMBER(p_master_fk_value); IF p_lookup_fk_name IS NOT NULL AND p_intersection_table IS NOT NULL AND p_master_fk_name IS NOT NULL AND p_master_fk_value IS NOT NULL THEN l_sql_statement := 'SELECT ' || p_lookup_fk_name || ' ' || 'FROM ' || p_intersection_table || ' ' || 'WHERE ' || p_master_fk_name || '=' || p_master_fk_value ; EXECUTE IMMEDIATE l_sql_statement BULK COLLECT INTO l_selected; END IF; -- Assign the colon separated list to l_selected RETURN APEX_UTIL.TABLE_TO_STRING(l_selected); EXCEPTION WHEN OTHERS THEN NULL; -- logging can be done here! END;
If the intersection item P250_shuttle is called and the driving table ID is stored in P250_id, the call to this function could look as follows:
: P250_shuttle := get_selectlist ( p_intersection_table => 'dep_pages' , p_lookup_fk_name => 'pag_id' , p_master_fk_name => 'dep_id' , P_master_fk_value => :P250_id);
Beware of SQL injection and keep P250_id hidden and protected
The procedure set_selectlist can be used to store the changes made in the shuttle or multiselect list. We call the function On Submit — After Validations and Computations and after that we call the DML processes generated by the wizard. Be aware of a reset process. If such a process is present, we have to call our procedure before the reset process. Otherwise, we lose all our changes and nothing is saved.
PROCEDURE SET_SELECTLIST (P_LIST IN VARCHAR2 ,P_INTERSECTION_TABLE IN VARCHAR2 ,P_LOOKUP_FK_NAME IN VARCHAR2 ,P_MASTER_FK_NAME IN VARCHAR2 ,P_MASTER_FK_VALUE IN VARCHAR2 ) IS -- Insert the selectlist value (as a list e.g. 1:2:4) into -- the intersection table. This process fires After Submit -- and after the wizard generated process that handles -- inserts, updates and deletes on the master table. l_selected APEX_APPLICATION_GLOBAL.VC_ARR2; l_sql_statement VARCHAR2(1000); l_id NUMBER; l_dummy_number NUMBER; BEGIN -- Check if master foreign key value is a number l_dummy_number := TO_NUMBER(p_master_fk_value); IF p_lookup_fk_name IS NOT NULL AND p_intersection_table IS NOT NULL AND p_master_fk_name IS NOT NULL AND p_master_fk_value IS NOT NULL THEN -- Convert the colon separated string of values -- into a PL/SQL array l_selected := HTMLDB_UTIL.STRING_TO_TABLE(p_list); -- Clean up the intersection table first -- Delete necessary records only l_sql_statement := 'DELETE FROM ' || p_intersection_table || ' ' || 'WHERE ' || p_master_fk_name || '=' || p_master_fk_value || ' ' || 'AND instr('':'|| p_list ||':'','':''||TO_CHAR('|| p_lookup_fk_name||')||'':'' )=0' EXECUTE IMMEDIATE l_sql_statement; -- Loop over the array to insert lookup_ids and -- master_id into the intersection table FOR i IN 1..l_selected.count LOOP -- Check if the record already exists l_sql_statement := 'SELECT ' || l_selected(i) ||' ' || 'FROM ' || p_intersection_table || ' ' || 'WHERE ' || p_master_fk_name || '=' || p_master_fk_value || ' ' || 'AND ' || p_lookup_fk_name ||'='|| l_selected(i); BEGIN -- when the record exists do nothing EXECUTE IMMEDIATE l_sql_statement INTO l_id; EXCEPTION WHEN OTHERS THEN -- In case there is no record, insert it l_sql_statement := 'INSERT INTO ' || p_intersection_table || ' ' || '(' || p_master_fk_name || ',' || p_lookup_fk_name || ') ' || 'VALUES (' || p_master_fk_value || ',' || l_selected(i) || ')'; -- no parent key exception BEGIN EXECUTE IMMEDIATE l_sql_statement; EXCEPTION WHEN OTHERS THEN NULL; -- logging can be done here! END; END; END LOOP; END IF; EXCEPTION WHEN OTHERS THEN NULL; -- logging can be done here! END;
If the intersection item P250_shuttle is called and the driving table ID is stored in P250_id, the call to this procedure could look as follows:
set_selectlist ( p_list => :P250_shuttle , p_intersection_table => 'dep_pages' , p_lookup_fk_name => 'pag_id' , p_master_fk_name => 'dep_id' , p_master_fk_value => :P250_ID );
If we have only one simple read-only table or query for a page, we can use a SQL report or an interactive report. With the latter, the user has a lot of possibilities, including the presentation and filtering of the data.
There are always certain pages that don't fall in the aforementioned categories, with special functionality—for example, parameter sections or charts with management information. These pages must be built up with separate regions and after that those must be attached together. Another example is a wizard for the end user that can be used to accomplish rather complex input tasks with validations between the subscreens.
In this article, we got an overview of the installation and preparation tasks before actually building the application. We discussed User Interface Defaults and the application structure. We saw techniques to transform the data model into initial screen designs. We also discussed a lot of best practices and guidelines during the building phase.
Resources for Article :
- Preparing Your Forms Conversion Using Oracle Application Express (APEX) [Article]
- Building a Ext JS Theme into Oracle APEX [Article]
- Oracle APEX Plug-ins [Article]
About the Author :
Alex Nuijten works as a database developer and Expertise Lead (database development) for AMIS Services BV in Nieuwegein, The Netherlands. Besides his consultancy work, he conducts training classes, mainly in SQL and PL/SQL. Alex has been a speaker at numerous international conferences, such as ODTUG, Oracle Open World, UKOUG, IOUG, OGh, and OBUG. He is also a frequent contributor at the Oracle Technology Network forum for SQL and PL/SQL. He has written many articles in Oracle-related magazines, and at regular intervals he writes about Oracle database development on the AMIS Technology Blog (technology.amis.nl), as well as on his own blog, Notes on Oracle (nuijten.blogspot.com). In August 2010, Alex was awarded the Oracle ACE Director membership.
Iloon Ellen-Wolff started working with Oracle 21 years ago, employed by several software houses in the Netherlands. Her experience goes back to Oracle Forms version 2 and Report Writer.
Almost 14 years ago, she started working for Oracle Support Services for the developer competency (Oracle Forms, Oracle Reports, SQL Developer, and Application Express), assisting customers by solving their software-related problems.
During those years, she applied the knowledge she had gained in many ways such as coaching new engineers, team leading, teaching the Application Express courses, and seminars for Oracle University.
Aside from being a very senior team member with excellent troubleshooting and customer skills, in Oracle Support, she acts as Advanced Resolution Engineer.
One of her responsibilities in Oracle Support is Global Technical Lead Application Express. She works closely with Sustaining Engineering, Product Development, and Product Management of the Application Express team.
Starting last year, she is now involved in the Oracle Cloud project. She is member of the platform as a service readiness team. Involved in testing Application Express in the Cloud and being a trainer in this area, she enjoys the challenge to prepare her engineers for the Cloud and ultimately to support Oracle customers in the very near future!
Besides that, she is a frequent speaker for different Oracle User Groups about Application Express, SQL Developer, and Oracle Public Cloud.
Learco Brizzi received his MSc in Informatics in 1993 and then he started working with the early versions of Oracle Forms (3.0), Reports (1.1), and Designer (1.2.1). When WebDB was launched, he created his first steps towards building Internet applications. When APEX (HTMLDB) hit the market, he saw the potential of this tool and built a complete music download store with it in 2004, including integration with e-mail, reports, and payment service providers. This was one of the very first serious applications ever built with APEX. Nowadays, Learco is dedicated to the APEX and PL/SQL development. He is a very enthusiastic technician, trainer of advanced APEX courses, presenter at conferences, organizer of seminars, committee member of the OGh (Oracle user group in Holland) and member of the editorial-committee for the Oracle magazine OGh Visie. Together with a companion Learco started his own company, Itium, in 1999, which specializes in Oracle technology. In May 2010, Learco was awarded the Oracle ACE membership. You can contact Learco at firstname.lastname@example.org.