THE EXPERT’S VOICE® IN ORACLE Beginning Oracle Application Express 4 Your ticket to easy and robust web-application development using Oracle's powerful toolset for power-users, programmers, and database administrators Doug Gault, Karen Cannell, Patrick Cimolini, Martin Giffy D'Souza, and Timothy St. Hilaire For your convenience Apress has placed some of the front matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them. Contents at a Glance Contents ..................................................................................................................... v About the Authors .................................................................................................... xv Acknowledgments .................................................................................................. xvii ■Chapter 1: An Introduction to APEX 4 ..................................................................... 1 ■Chapter 2: A Developer’s Overview ......................................................................... 7 ■Chapter 3: Identifying the Problem & Designing the Solution ............................... 31 ■Chapter 4: SQL Workshop ...................................................................................... 39 ■Chapter 5: Application and Navigation .................................................................. 61 ■Chapter 6: Forms and Reports – The Basics ......................................................... 99 ■Chapter 7: Forms and Reports – Advanced ......................................................... 155 ■Chapter 8: Programmatic Elements .................................................................... 207 ■Chapter 9: Security ............................................................................................. 245 ■Chapter 10: Application Deployment ................................................................... 273 ■Chapter 11: Understanding Websheets ............................................................... 281 ■Chapter 12: A Websheet Example ....................................................................... 313 ■Chapter 13: Extended Developer Tools ................................................................ 333 ■Chapter 14: Managing Workspaces .................................................................... 355 ■Chapter 15: Team Development .......................................................................... 375 Index ....................................................................................................................... 405 iv C H A P T E R 1 ■ ■ ■ An Introduction to APEX 4 Welcome to the wonderful world of Oracle Application Express (APEX). You’re about to learn how to use a tool that we believe will revolutionize the way you think about and approach writing web-based Oracle systems. It certainly has for us. Prior to the advent of APEX, developing fully interactive web based systems over data that resided within an Oracle database almost always meant learning a new and often complex language like Java, .NET, or PHP and then figuring out how to integrate your chosen language seamlessly with the data that resided in your database. Often this also meant trying to incorporate business rules that were already coded in the form of PL/SQL. In such situations, it could take months or even years just to become proficient enough with your chosen language to begin to write a functional system. If you’re like many, you’ll become frustrated with the fact that you’ve spend an inordinate amount of time to do what seems like a relatively easy task. Fear not! The days of long-winded and complex web development platforms may just be behind us. What is APEX? APEX is a 100% browser-based rapid application development (RAD) tool that helps you to create rich interactive Oracle-based web applications very quickly and with relatively little programming effort. There are many RAD development tools and platforms on the market. If you’re dealing with data that resides in an Oracle database, there are a number of things that makes APEX distinctive and thus more attractive as a development platform. First and foremost is the fact that APEX is built on and uses as its core languages, SQL, and PL/SQL. This is a huge advantage for those of you who have already been working with the Oracle database because it means you can immediately draw on what you know. Even for those who don’t have an Oracle background, if you are going to be working with an Oracle database, you’ll need to learn about its particular flavor of SQL and will at some point likely find a need for the PL/SQL procedural language. This fact becomes even more beneficial if you are migrating an Oracle-based system to APEX that already has a significant amount of business logic coded into stored PL/SQL program units. In this instance, you can almost immediately take advantage of that logic with very little effort or change to the existing code. Another great advantage is that APEX is a declarative tool that provides a feature rich core designed to make your job easier. Because APEX takes care of many of the underlying functions common to all web-based applications you are able to focus on the logic specific to your application. A large share of what you need to accomplish can be done by using one of the many built-in wizards provided as part of the APEX Application Builder. The wizards walk you through the process of defining what you want your application to do and then stores that information as metadata. Once the wizard is complete you can edit and enhance the functionality, or even replace it with your own custom SQL and PL/SQL routines. After you become proficient with APEX you might even find yourself bypassing the wizards altogether and generating more complex definitions directly. 1 CHAPTER 1 ■ AN INTRODUCTION TO APEX 4 During the course of the book, you will discover that you will likely want a few other tools at your disposal, but in truth, you could easily develop a very rich application using nothing but your web browser and what APEX provides for you. A Brief History of APEX APEX has been around for quite some time—perhaps even longer than most people know. The first public release of APEX, or HTML DB as it was called then, came in 2004, but its history reaches back quite a long way. Ancient History APEX has its roots in technology that has been around for quite some time. In fact, parts of the PL/SQL Web Toolkit, which is used under the covers by APEX to generate the HTML that is sent to the browser, date back to as early as 1994. At that point in time, you could actually write web applications in PL/SQL by hand, and unfortunately we did. This required not only a thorough knowledge of PL/SQL and HTML but also the patience of a saint and the determination of a headstrong mule. The end result wasn’t very pretty, and it was definitely not secure by today’s terms, but it was functional, if somewhat limited. Not long after, Oracle introduced PL/SQL Server Pages (PSPs). This involved first coding the static HTML and including special Oracle markup to indicate where dynamic data would go. Once you had the output looking as you wanted, you then ran it through a program called LOADPSP. This would translate the raw HTML and the special Oracle markup into a PL/SQL procedure that, again, used the PL/SQL Web Toolkit to emit the HTML including the dynamic data you requested. At the time, this was a huge leap forward. I worked at a company where we actually built an entire framework around using PSP technology and deployed it at several clients. Finally, in 1997, WebDB came on the scene. The true grandfather of what we now call APEX, WebDB was revolutionary in that it was a 100% web-based tool that allowed developers to design web applications. It was written entirely in PL/SQL even though Java seemed to be taking over the world. Developers could point WebDB at their database and generate code that would produce forms, reports, charts, and calendars. There was no session state management or templates; once the code was generated, there was no going back through the tool. WebDB allowed a large number of companies who wanted to jump on the web-based bandwagon to do so without spending vast amounts of time and effort in retraining their staff. As a tribute to its success, I know of a number of companies that still have WebDB system running in production environment. Unfortunately WebDB’s days were numbered. Because it generated code (and if you didn’t like the code it generated, then too bad for you), it had already begun to fade from favor by the time it was absorbed into Oracle’s Portal product. However, creator Mike Hichwa did not forget the glimpse of greatness that WebDB had seen. More Recent History Around 1999, Oracle’s CEO, Larry Ellison presented Mike Hichwa (VP of Software Development) with the task of creating an internal calendaring and scheduling system for Oracle Corp. The original remit was to use WebDB to generate the initial code and then hand code all the changes from that point forward. Mike, however, saw this as an opportunity to completely re-write WebDB into something that could be far more useful. Thus, with the help of Joel Kallman and Tom Kyte, Oracle Flows was born. Based on the success of the internal Calendaring and Scheduling system, the team was allowed to move forward towards making Oracle Flows a product. In 2001, using what then known as Flow Builder, 2 CHAPTER 1 ■ AN INTRODUCTION TO APEX 4 Mike and team begin implementing systems for various customers, including one situation where they managed to replace a Java development project that was going horribly wrong. By 2003, the team had proven the tool’s power and they were given permission to release it as a product. HTMLDB 1.5 was released to the public as a no-cost option of Oracle 10gR1. Since then, various releases have been introduced; each providing improved features and functionality. The following is a very brief list of the releases and some of the more notable features: • HTMLDB 1.6 (2004) introduced themes, master-detail forms, page groups, page locking, and some multilingual capabilities. • HTMLDB 2.0 (2005) introduced SQL Workshop, graphical query builder, database object browser, and session state protection. • APEX 2.2 (2006) introduced packaged applications, the APEX dictionary views, and the access control wizard. • APEX 3.0 (2007) introduced PDF printing with BI Publisher, migration from Microsoft Access, and page and region caching. • APEX 3.1 (2008) introduced Interactive Reports, runtime-only installation capability, and improved security. • APEX 3.2 (2009) introduced a migration helper for Oracle Forms based systems and various security enhancements. APEX 4 and the Future And so we arrive at the present day with the release of APEX 4.0. In my opinion, this release of APEX has truly brought the development environment into the realm of “forces to be reckoned with.” The entire focus of APEX 4 was to make development of rich interactive Web 2.0 applications easier by making the process as declarative as possible. APEX 4 has introduced so many new features—indeed, new ways to attack problems—that it will be hard not to choose APEX as the preferred development platform for Oracle-based applications. APEX 4.0’s Dynamic Actions provide a way for you to define client side behaviors, such as enabling or disabling fields or regions declaratively without JavaScript. With some JavaScript knowledge under your belt, you can create complex dynamic actions that do client side calculations, AJAX, and more. An improved Charting Engine based on AnyChart 5.1 provides declarative Flash-based charts, gauges, maps, and Gantt charts. All chart types are interactive and drillable, and several charts can be combined into a dashboard style interface. Probably the most exciting new feature is the new plug-in architecture that provides an extensible framework allowing APEX Community members to build and share their own custom item, region, process, and dynamic action types. While the ramifications of this might not be immediately apparent, the possibilities of what can and will be developed using the plug-in architecture are virtually limitless— and that is very good news for all APEX developers. As users of the APEX development platform, we no longer have to wait for the APEX Team to respond to specific feature requests. We can now take the future of APEX into our own hands and code missing features, actions, and item types ourselves. In fact, I see a future where the APEX Team themselves use the plug-in architecture to extend APEX in many different directions. I almost can’t overstate the significance of plugins. While APEX 4 is definitely a giant leap forward from the architecture of APEX 3, the plug-in architecture blows the doors wide open to change from the broad and growing community of APEX developers. APEX now comes with a Team Development feature that eases the management of the development process by tracking features, to-do lists, bugs, and milestones. A user feedback mechanism is also included that allows users to provide inline feedback while using the system. The feature automatically 3 CHAPTER 1 ■ AN INTRODUCTION TO APEX 4 captures the user’s session state information so you can see exactly what was going on during their session. You can then take this information and create a bug or a to-do entry with the simple click of a button. Websheets provide a fast and direct way for end users to gather and share information without IT intervention. Armed with only a web browser and access to the Websheets application, end users can define page content, data grids, and reports and decide who else in the enterprise has access to that data. Websheets page content supports standard wiki syntax and pages can be organized hierarchically. Users can also add annotations to pages and content in the form of files, notes, and tags. What You Need to Get Started The goal of this book is to get you started using APEX, to launch you in a way that enables you to grow towards mastery of the product. To begin, you need three things: access to an APEX instance, access to a web browser, and a copy of SQL Developer. Access to an APEX Instance This is definitely a hands-on book, so to work through the examples and exercises you’ll need access to an instance of APEX 4. There are a number of different ways you can access APEX; depending on your level of comfort and expertise with Oracle, some may be better for you than others. Here is a description of the three most common scenarios: • By far the easiest is to sign up for an account on Oracle’s hosted version of APEX at http://apex.oracle.com. It’s free for non-production applications and is a great place to get started, as you don’t have to worry about installing either the database or APEX. • If you already have an Oracle database installed locally, you can download and install APEX 4 into that instance. Simply go to the Oracle APEX home page at http://otn.oracle.com/apex and download the latest version of the software. • If you don’t have an Oracle database already but would like to install one locally, you can download a free developers license from OTN at . Both Oracle 10g and 11g will run APEX 4; however, Oracle 11g will allow you to install APEX as an option in the database install. While having a locally accessible instance of the Oracle database will give you more direct access to the data, it’s definitely not necessary to complete the exercises in this book. All code and instructions have been written so that they can be completed on Oracle’s hosted instance with no special access required. ■ Note Oracle provides very good documentation on the installation process for both the database and APEX, so it is not covered in detail here. However, if you are planning to install APEX 4 on an environment in your organization, you will want to coordinate with the database administrator responsible for that instance to ensure no mishaps occur. 4 CHAPTER 1 ■ AN INTRODUCTION TO APEX 4 Web Browser The APEX documentation states that to view or develop APEX applications, the web browser must support cookies, JavaScript, HTML 4.0, and CSS 1.0. However, although you can deploy to any browser that support these things, the list of supported browsers is fairly narrow. Currently, the following browsers are supported: Internet Explorer 7+, Firefox 3.5+, Apple’s Safari, and Google Chrome. I don’t want to get into a religious debate about which web browser is the best on the market, but my preference for development is Firefox. There are a number of free add-ons and plug-ins (Such as FireBug and The Web Developer Toolbar) that will help you with APEX development. Note that because of the difference in the way each browser interprets HTML and JavaScript, you must test your application in any and all web browsers that your target audience might use. SQL Developer As mentioned before, all the exercises and scripts in the book can be loaded and run directly within the APEX interface. However, if you have chosen to install or have access to a local instance of the Oracle database, a SQL IDE will definitely make your life easier. SQL Developer is a free SQL and PL/SQL IDE provided by Oracle. You can download SQL Developer from the Oracle Technology Network’s home page at http://otn.oracle.com/ Using SQL Developer you can browse database objects, edit row data, develop and test stored PL/SQL program units, code and test SQL statements, and interactively debug PL/SQL code. SQL Developer also has many direct integration points with APEX that make monitoring and maintaining APEX instances and applications easier. We won’t cover those in this book, but it’s definitely worth your time to look into this tool. Summary Oracle Application Express has come a long way from its simple beginnings and we are poised at the beginning of a new cycle of growth for the APEX community. APEX 4 provides so much possibility and promise that it’s hard not to be excited about what the future holds. With that spirit, we start you on your journey to discover how APEX can make development easier and more fun. 5 C H A P T E R 2 ■ ■ ■ A Developer’s Overview You’re probably anxious to get started, but there are a few concepts that you should understand before you jump into APEX development headfirst. This chapter will introduce the fundamental development architecture of APEX and then will walk you through the different areas of the developer interface. You’ll be delving deeper into the details as you go through the book and put the architecture to work for you, but it will help tremendously to know how things are structured ahead of time. This chapter is designed to ease you in, but it isn’t a complete guided tour of every nook and cranny. Be patient; you’ll get there. The Anatomy of a Workspace APEX was designed from the beginning to be a multi-tenant architecture where many different development environments (called workspaces) could exist within a single APEX instance. For instance apex.oracle.com, Oracle’s free hosted instance, holds over 10,000 active workspaces, each of which is a completely separate environment unable to see or interact with any of the other workspaces. You can think of this as Software as a Service (SaaS) or a cloud computing architecture, but basically, it means that each workspace is distinct and segregated from all others. In simple terms, each workspace represents a virtual private container in which developers create and deploy their APEX applications. The development process takes place within the context of a workspace so it’s important to know how a workspace is structured. Figure 2–1 uses database entity relationship diagram parlance to help explain the makeup of the objects within a workspace. I’ll explore the details in the following sections. Figure 2–1. Logical makeup of a workspace 7 CHAPTER 2 ■ A DEVELOPER’S OVERVIEW APEX Users To login to an APEX workspace you must have access to a valid APEX user. There are a number of different user roles available that dictate what you are able to do when you log in. The roles are as follows: Instance Administrators are special users that manage and maintain the overall APEX instance. They are able to set instance level preferences and messages, create and manage workspaces, monitor space utilization, and many other actions related to the overall APEX installation. Instance Administrators are only able to log into the special INTERNAL workspace, which houses the APEX Admin Services application. Workspace Administrators are responsible for managing the details of a specific workspace and can manage user accounts related to the workspace, monitor workspace activity, view log files, override developer locks and settings, etc. Although it is not good practice, the Workspace Administrator can also act as a developer, creating and modifying applications. Developers are the users who create and edit the applications within the workspace. They have access to the underlying tables in the schema(s) assigned to the workspace and may create and modify database objects and stored PL/SQL units. Most people writing APEX applications only need this level of access. End Users are only able to run applications within a workspace. They do not have direct access to any of the underlying database objects, nor do they have access to any of the APEX development modules. End users can’t log directly into a workspace. APEX Users are specific and unique to a workspace, meaning that you can have a user of the same name in multiple workspaces within a single APEX instance, but each of these users is unique. They can have their own password, settings, and are not linked together in any way. When you’re developing, you should get in the habit of logging in as a Developer as opposed to a Workspace Administrator. There are several safeguards available to help keep developers from stepping on each other within a workspace. If you log in as an administrator, these safeguards will be bypassed and you may accidently interfere with something someone else is working on. While this won’t be a problem in a workspace with only one developer, it’s still good to get yourself into that habit. ■ Note In the context of this book, we’ll be using the last three types of user. We’re going to assume that APEX has been installed, a workspace has been created, and you have been given the Workspace Administrator’s login credentials. If you’re using the hosted instance at apex.oracle.com, then the username you were given when you signed up will have the credentials of a Workspace Administrator. If, however, you are using a local instance, either refer to the APEX documentation or get your Instance Administrator to help you set up a workspace. Applications, Pages, Regions, and Items Although a workspace starts off basically empty, you can have many applications that reside within a workspace. While there is no specific rule, it’s likely that all of the applications within a workspace will 8