Understanding Oracle APEX 20 Application Development
Think Like an Application Express Developer
3rd ed.
To my parents, for their many years of unwavering love and support.
Application Express (otherwise known as APEX) is a web application tightly coupled to an Oracle database. It has several uses: you can use its SQL Workshop tool to query and modify the database, you can use its App Builder tool to create your own web applications that interact with the database, and you can run the web applications created by you and others.
The app builder is especially interesting because it provides a simple, nontraditional way to build web pages. You do not specify code for the page directly; instead, you choose from a set of built-in templates. There is a template for the overall page and templates for each kind of component that you want to put on the page (such as reports, buttons, etc.). Each template has a set of properties, whose values determine where each component is located on the page, what it looks like, and how it behaves. You create a page simply by choosing templates for the components you want and assigning values to their properties.
The APEX app builder saves the property values for each component in its own database. When a browser requests one of your application’s pages, the APEX server extracts the property values relevant to that page from its database, constructs the HTML code corresponding to those values, and returns that code to the browser. This process is called rendering the page, and APEX is called an HTML generator.
The advantage of using an HTML generator such as APEX is that you can build web pages without any knowledge of HTML (or CSS, JavaScript, or PHP). Moreover, because APEX is tightly coupled to an Oracle database, it automatically handles the intricacies of database interaction. APEX makes it possible to easily create good-looking, highly functional, and database-aware pages with only a rudimentary knowledge of SQL.
Why This Book?
Designing a page with APEX seems straightforward—all you have to do is choose the components you want and then assign the appropriate values to their properties. Choosing components is straightforward, but assigning property values is not. A page and its components have many properties, and you have to know the purpose of those properties to know what values to assign. These properties range from the essential (such as the source query of a report) to the obscure (such as the static ID of a report). Some properties (such as the HTML expression of a report column) are hooks that allow you to insert customized HTML or JavaScript code into the generated web page.
The purpose of this book is to gently lead you through this cornucopia of properties. To that end, the book develops a demo web application that illustrates various APEX techniques for building typical web page functionality. The pages of this application start out simply and gradually increase their level of sophistication. With each page, I introduce a few new properties, discuss their purpose, and illustrate their usefulness. By the end of the book, you will have been so immersed in the world of APEX properties that you should feel confident enough to tackle any website project of your own. And if your project requires even more sophistication than appears here, you should be comfortable enough to use properties that are not covered, perhaps by looking at the documentation, examining the numerous prepackaged applications provided by Oracle, checking a web forum, or even figuring it out on your own.
Another way to build web pages in APEX is to rely on wizards. APEX provides wizards to generate common components, such as report pages and data entry forms. Each wizard asks you a series of questions (such as “What is the name of the page?” “What table do you want to display?” “Should the page have an entry in the navigation menu?”) and then uses your responses to generate appropriate components having appropriate properties. The advantage, of course, is that you don’t need to know anything about properties. The disadvantage is that wizards tend to produce “one size fits all” pages, in terms of both their appearance and their functionality.
Wizards can take you only so far. If you want any kind of control over the look, feel, and behavior of your page, you need to get involved with its properties. This book provides the guidance you need.
Demo Application
As this book explains each part of the APEX app builder, it guides you through the development of a small application, named Employee Demo. I encourage you to build your own version of the application as you follow along. You can run my version of the application by going to the URL apex.oracle.com/pls/apex/f?p=91392:1. You can also download the source code for the application from the Apress website and import it into your own workspace.
Unlike demo applications in many books, this application does not “do” anything particularly interesting. Instead, each page is constructed to illustrate one or more techniques. Some of the pages have similar functionality, to illustrate the trade-offs between different implementation techniques.
The key of DEPT is DeptNo, and the key of EMP is EmpNo. Each table has a built-in sequence for generating unique values for these keys, as well as an associated insertion trigger. If you insert a record into one of the tables and omit a value for the key, the trigger will automatically generate a key value from the appropriate sequence.
After altering the table, you will also need to assign an Offsite value for each existing employee. In my Employee Demo application, the employees SCOTT, ALLEN, WARD, and TURNER work offsite; the others work onsite. Chapter 1 describes how to import the tables if they are not already in your workspace and discusses the APEX tools needed to make these modifications to them.
Required Background
This book is for people who are comfortable using a database system and want to learn how to write nontrivial web applications in APEX. Many of the techniques used to write APEX pages involve various skills in the following database and web design languages and technologies.
SQL
The most important skill you need is the ability to write SQL queries. All data access in APEX is performed via SQL statements, and the value of many properties involves SQL in some way. The more fluent you are in SQL, the more sophisticated your reports and forms can be. This book assumes that you are comfortable with SQL. For the most part, the Employee Demo application uses relatively simple SQL statements, but occasionally I include something more complex (such as an outer join or nested query) to illustrate what is possible.
HTML
This book also assumes that you have a basic familiarity with HTML—in particular, how tags such as <p>, <b>, <a>, and <img> can be used to format text and display images. I will ignore advanced features such as JavaScript and CSS.
PL/SQL
APEX uses PL/SQL to specify behavior. PL/SQL is Oracle’s programming language; its main feature is an embedded SQL syntax that makes it easy to write procedures that interact with the database You should have a rudimentary understanding of programming, although prior knowledge of PL/SQL is not necessary. This book introduces PL/SQL in Chapter 7 and uses only basic features of the language.
APEX
Finally, this book does not require you to have prior experience with APEX. Although it is possible to follow the book without actually using APEX, doing so seems rather pointless. So you should get an APEX account. The easiest and best way to get an account is by going to the apex.oracle.com site. Because I created my Employee Demo application from there, you should see the same screens that appear in this book.
Distinguishing Screens from Pages
APEX is a web application that is used to create other web applications. Thus, APEX has a home page, and its various tools have their own sets of pages. Throughout this book, I describe how to use APEX to build a page of an application. This can lead to some strange sentences, such as “Clicking the Run button from the application’s APEX home page runs its home page.” To avoid such confusion, I denote all APEX pages as “screens.” The previous sentence becomes “Clicking the Run button from the application’s home screen runs its home page,” which is less awkward and hopefully less confusing.
New to This Edition
Although APEX is a polished and highly functional application, it is (and always has been) a work in progress. The APEX developers have been relentless in their quest to improve the system and have not been shy about making large changes to the functionality of APEX and its user interface.
The previous edition of this book was written in 2015, just as APEX 5 was introduced. Since then, the user interface has undergone a series of significant changes. In fact, when I recently read through my APEX 5 book, I was dismayed to discover that its screenshots and instructions were often inaccurate and occasionally meaningless. Many APEX properties are now in different locations, sometimes with different names. New component types (in particular forms and interactive grids) have replaced old ones and have different functionality. Clearly it was time to revise the book.
The most significant additions to this book are the two new chapters on Forms and Interactive Grids. In APEX 5, a “form” was a static content region containing an intricately configured set of items and built-in processes. Now APEX has a dedicated Form region, which automatically generates the preconfigured items and processes for you and provides an easily understood set of properties for managing them.
Interactive grid regions have replaced the “tabular form” regions of APEX 5. They provide the same basic functionality as tabular forms—namely, the ability to update a report’s values in place—but interactive grids are easier to use, more powerful, and have a cleaner semantics. In fact, the Interactive Grids chapter (Chapter 11) of this book is much shorter than the older Tabular Forms chapter because interactive grids require much less explanation.
First and foremost, I would like to thank the APEX user community. Numerous people routinely and generously share their APEX knowledge by writing blogs, creating demonstration APEX sites, and answering all kinds of questions on the APEX web forums. I learned much from them. This book is my attempt to give something back.
I also want to thank my Apress editors, Jonathan Gennick and Jill Balzano. Jonathan convinced me to write the book, and then to revise it. He provided guidance and encouragement every step of the way. Jill was always supportive and smoothed out the inevitable bumps in the road.
Most importantly, I want to acknowledge my wife Amy. She has accompanied me through all three editions, listening to my ideas, helping me resolve technical issues, working through the APEX examples, and pointing out passages in the book that needed clarification. She is a relentless proofreader. I could have written the book without her, but it would not have been anywhere near as good. Thanks.
is a recently retired associate professor in the computer science department at Boston College. He taught college students for more than 35 years. His research specialty is database systems, and he thoroughly enjoys teaching the wonders of database technology to captive students.
—Armando Plascencia