In this chapter, 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. We will also discuss some guidelines and best practices for these phases. You'll get a lot of new ideas for structuring and building your applications.
Before doing that we will take a brief look at the history and background of APEX.
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.
Oracle HTTP Server (OHS)
Embedded PL/SQL Gateway (EPG)
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:
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:
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 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.
To get a jumpstart with developing real-life APEX applications, we have written this book and provided you with some best practices. These practices don't have to be appropriate in all situations, see them as guidelines. This book is not intended as a point and click starters guide and assumes a basic understanding of APEX, PL/SQL, HTML, and CSS.
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. See the reference guide for details. 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. Also refer to Chapter 6, Deploy and Maintain.
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.
@apexins tablespace_apex tablespace_files tablespace_temp images
The command is explained as follows:
For the runtime environment, the command is as follows:
@apxrtins tablespace_apex tablespace_files tablespace_temp images
In the documentation,
SYSAUX is given as an example for both
tablespace_files. There are several reasons for not using
SYSAUX for these tablespaces, but to use our own instead:
SYSAUXis 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
SYSAUXtablespace is less occurring
It's easier to clean-up older versions of APEX
And last but not least, it's only an example
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. For more details see the installation guide.
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
Go to Manage Instance.
Select the appropriate settings for Disable Administrator Login and Disable Workspace Login. These settings can also be set manually with the
APEX_INSTANCE_ADMINAPI. See the reference guide for details.
Oracle HTTP Server (OHS)
Embedded PL/SQL Gateway (EPG)
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.
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 flexible and it has a proven track record. Configuration is done with the text files.
is part of XML DB and lives inside the database. Because everything is in the database, we have to use the
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:
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.
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, flexibility of deployment possibilities, and feature announcements makes it the best choice.
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.
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
Start SQL*Plus and connect as
Run the script by entering
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.
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
Internalto inspect the settings. You cannot change them. Select Cancel to return to the previous screen.
Select the workspace
Internalby clicking on the name.
Select Manage Users. Here you can see the user
You can also select the user
Adminto 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_xxxxxxis selected in Default Schema, and that the new user is an administrator.
xxxxxxhas to match your APEX scheme version in the database, for instance, APEX_040100.
Click on Create to finish.
Start SQL*Plus and connect as
APEX_xxxxxxaccount by issuing the following command:
alter user APEX_xxxxxx account unlock;
Connect to the
APEX_xxxxxxaccount. 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 protected]', p_web_password => 'second_admin_password') ; END; / COMMIT /
alter user APEX_xxxxxx account lock;
Now you can log in to the
Internalworkspace with your newly created account and you'll be asked to change your password.
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.
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.
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.
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 (discussed later in this chapter).
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
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
idcolumns as the
RAW(16)columns and use
sys_guid()as a default value for the
idcolumn. For example:
CREATE TABLE t ( id RAW(16) DEFAULT sys_guid() PRIMARY KEY , column_a VARCHAR2(10) , column_b VARCHAR2(10) … ) /
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:
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.
Internet Explorer Developer Tools
Built-in tools in the browser
Performance measurement tools
GUI design tools
Refer to Chapter 5, Debugging and Troubleshooting for the details on other tools.
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
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:
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:
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.
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.
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.
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.
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:
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
In combination with the use of conditions for the components that determine on which specific pages the components will be rendered, is a flexible 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.
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.
Better maintainable look and feel across multiple applications
The use of shared components across applications
Reusable centralized functionality, such as authorization, authentication, auditing, and logging
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:
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.
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.
List of values
Navigation bar entries
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.
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:
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.
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.
In the following screenshot, we can see the relevant authentication scheme settings for the master application:
Other examples of pages that can be included in this application are as follows:
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:
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.
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.
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. To identify them, see the Base Tables section discussed later in this chapter.
When creating list of values, always use the aliases
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. For more performance-related information on list of values, refer to Chapter 2, Leveraging the Database.
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:
Form on a table or view
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.
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.
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.
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.
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 (see next chapters in this book). The
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.
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 sub-screens.
With every new version of APEX, new features and capabilities are being added to the tool and it's constantly evolving. It's almost impossible to describe all the possibilities of APEX in this chapter, but with the information in this chapter you will be able to kick start and structure your development.
In this chapter, 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.
In the next chapter, we will be using some advanced database features to enhance our application.