Oracle Application Express 3.2 - The Essentials and More

By Arie Geller , Matthew Lyon
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. An Introduction to APEX

About this book

Developing data-centric web applications can be a real challenge as it is a multi-disciplinary process. There are many technologies involved in the client side (HTML, CSS, JavaScript, and so on); the interaction with the database, on the server side; the typeless nature of the web environment; and above all, the need to put it all together. This needs to be done in a manner that will allow the end users to do their job in the simplest and most efficient way, while enriching their user experience. How often have you wished that developing such applications could be uncomplicated and straightforward? This book will show you that it's possible, and teaches you how to do it, using Oracle Application Express (APEX).

With this practical guide to APEX, you'll learn how to easily develop data-centric web applications for the Oracle environment. The book covers the development cycle of an APEX application, reviewing the major APEX principles and building blocks chapter by chapter. It starts with the basic skills you need to get going when developing with APEX. Later, you will learn advanced issues, such as how to build tailor-made forms and reports, using APEX APIs, AJAX, and so on. It not only deals with the "How" but also with the "Why", and before long you will be able to understand APEX concepts, and use them to expand and enhance the built-in features, wizards, and tools.

The book starts with the design phase, including building the necessary database objects infrastructure; continues with ways to implement the application logic (on the server side) and the User Interface (on the client side), whilst showing you how to enhance your applications' features and functionality according to your specific needs; and it ends with application deployment.

The book emphasizes and clearly documents areas such as Globalization, Localization, and developing multi-lingual applications, and includes a special discussion about Right-To-Left (RTL) support for APEX applications, documented here for the first time.

Throughout the book, there are many screenshots and snippets of code, taken from working APEX applications. The book is accompanied by demo APEX applications that you can download and install in your APEX environment, thoroughly analyze, and learn from as you read the book.

Publication date:
June 2010
Publisher
Packt
Pages
644
ISBN
9781847194527

 

Chapter 1. An Introduction to APEX

Oracle Application Express APEX for short, or by its former name HTML DB is a declarative, Web-based RAD (Rapid Application development) tool. It is used to develop native Web-based, data centric applications.

Let's clarify some of these terms, and understand what they mean in the APEX context.

Web-based

The term Web-based appears in the description of both the development tool and its product the APEX application. We are using a Web browser to both develop and run our applications. The advantages are obvious. Web browsers are installed, almost by default, on most of the workstations we are using, independently of the workstation hardware or operating system. It means that on the client side, we don't need anything else to start developing with APEX, or to run APEX applications. Moreover, it means very easy access to our applications, both internally using Intranet or externally, using the Internet. If we add all the modern smart phones and other PDAs on the market today, which include built-in Web browsers, we'll come to the conclusion that the range of options to access our APEX applications is very wide.

Another important advantage when using a Web browser is the ability to free us from hardware and local operating system dependencies. As the APEX Application Builder actually generates HTML code, we (as developers) don't have to worry about the hardware specifications, or the local operating system our end users will be using, to run our APEX application. As long as they have access to an HTML supporting Web browser, we are covered.

However, Web based application development can also be a drawback. Unfortunately, not all Web browser manufacturers have fully adopted all the international standards in this field. As a result, there are cases in which different Web browsers will display the same code page differently. The variety of Web browsers in the market today, like Internet Explorer, Firefox, Chrome, Opera and Safari, just to name a few, support and implement HTML, XHTML, CSS and JavaScript versions and levels, in different ways and styles. There are known cases where even the same Web browser brand, but in different versions, or across local operating systems, delivers different results with the same code. We need to be aware of this, and take actions to resolve it if we want to ensure cross-browser compatibility code for our applications (which will apply to greater numbers of potential users). Sometimes we need to take hard decisions and waive the support of certain web browsers in order to keep our code simple and maintainable.

A list of supported Web browsers can be found in the APEX release notes. For version 3.2, which is the latest APEX version at the time of writing this book, it can be found at http://download.oracle.com/docs/cd/E14373_01/relnotes.32/e13365/toc.htm#BGBCEGBI

 

Web-based


The term Web-based appears in the description of both the development tool and its product the APEX application. We are using a Web browser to both develop and run our applications. The advantages are obvious. Web browsers are installed, almost by default, on most of the workstations we are using, independently of the workstation hardware or operating system. It means that on the client side, we don't need anything else to start developing with APEX, or to run APEX applications. Moreover, it means very easy access to our applications, both internally using Intranet or externally, using the Internet. If we add all the modern smart phones and other PDAs on the market today, which include built-in Web browsers, we'll come to the conclusion that the range of options to access our APEX applications is very wide.

Another important advantage when using a Web browser is the ability to free us from hardware and local operating system dependencies. As the APEX Application Builder actually generates HTML code, we (as developers) don't have to worry about the hardware specifications, or the local operating system our end users will be using, to run our APEX application. As long as they have access to an HTML supporting Web browser, we are covered.

However, Web based application development can also be a drawback. Unfortunately, not all Web browser manufacturers have fully adopted all the international standards in this field. As a result, there are cases in which different Web browsers will display the same code page differently. The variety of Web browsers in the market today, like Internet Explorer, Firefox, Chrome, Opera and Safari, just to name a few, support and implement HTML, XHTML, CSS and JavaScript versions and levels, in different ways and styles. There are known cases where even the same Web browser brand, but in different versions, or across local operating systems, delivers different results with the same code. We need to be aware of this, and take actions to resolve it if we want to ensure cross-browser compatibility code for our applications (which will apply to greater numbers of potential users). Sometimes we need to take hard decisions and waive the support of certain web browsers in order to keep our code simple and maintainable.

A list of supported Web browsers can be found in the APEX release notes. For version 3.2, which is the latest APEX version at the time of writing this book, it can be found at http://download.oracle.com/docs/cd/E14373_01/relnotes.32/e13365/toc.htm#BGBCEGBI

 

Native Web based


APEX, in all its previous forms, was always designed to operate in the Web environment. The final product of the APEX Application Builder is HTML code that can be run directly on any Web browser that supports the HTML 4.0 and above standard. APEX Application Builder also incorporates special features into generated applications, as a direct result of operating in the Web environment. Further on, we will discuss APEX architecture and its main features, but for now we will mention session state and Optimistic Locking, as distinguished Web environment features which help us overcome the stateless nature of this environment.

The APEX Application Builder fully supports CSS (Cascading Style Sheets) and JavaScript, as integrated components of APEX applications. That includes built-in CSS files as part of a wider page layout and format mechanism, which we'll address later in the book, and a JavaScript library. The JavaScript library also includes, among a variety of general and APEX related functions, an AJAX (Asynchronous JavaScript and XML) framework. This allows even novice developers to utilize this advanced technology, and produce high quality applications, compatible with the latest trends in the market like Web 2.0, etc.

 

Data Centric


APEX was designed to produce applications that store, retrieve, manipulate, and display data, from Oracle Databases. This means that if your application's main logic doesn't revolve around data manipulation, it may be that APEX is not your optimal application development tool. However, if data manipulation is at the heart of your application, APEX could be your best bet.

APEX is actually a collection of PL/SQL packages, which "live" inside the Oracle database. It means that APEX automatically inherits all the outstanding features, often mentioned with regards to the Oracle database environment: high performance, robustness, reliability, enhanced security, scalability, and more. In addition, APEX can natively utilize the very rich SQL and PL/SQL environment, including the built-in packages that the Oracle database has to offer, and use them to manipulate the data in the optimal ways a RDBMS can offer.

Moreover, APEX can utilize special functionality features that are included in the various database versions and editions, like Oracle XML DB, Oracle Text, and Oracle Multimedia.

Another advantage in this context is that, like the client side which relies on Web browsers to gain hardware independence, (alongside operating system independence), the APEX engine relies on the database platform for the same purpose. As long as we have a proper running version of an Oracle database, starting with Oracle database 9.2.0.3 and above (including the free version, Oracle XE), we can ignore the hardware and operating systems aspects of the server.

 

A declarative development tool


APEX is a declarative tool. It means that we, as developers, concentrate more on the "What needs to be done", and less on the "How to do it". Think, for example, about SQL. In a SELECT statement, when we are using the ORDER BY clause, we are actually telling the database what we need - a sorted data result set, but we don't tell it how to actually do the sorting. In fact, the entire SELECT statement, just like SQL itself, is a declarative statement. In APEX, we are telling the Application builder that we need to lay out an HTML item on the page, or retrieve certain records from the database, and the APEX engine generates the proper code for doing that, both on the server side and the client side.

Working declaratively in APEX means that we are not generating traditional (3 GL) program code. Instead, we are working with a series of wizards and property sheets, which allows us to define all the metadata we need in order to generate an application page's code. APEX includes sets of pre-defined wizards, supported HTML objects, supported database objects and data types, page rendering options and procedures, after submit processes and DML options, navigation and branching options, and more. We can use all of these to declare our application page's forms, reports, charts, etc., with their layouts and application/business logic. The APEX engine translates it all into an HTML code for the client side, and SQL and PL/SQL code for the server side. Whenever the predefined options don't give us the exact solution we need, APEX allows us to use our own SQL and PL/SQL code for the server side, and HTML/XHTML, CSS, and JavaScript code, for the client side. As mentioned before, it also allows us the use of AJAX technology, within a built-in framework, to query the server side, while running on the client side without submitting the page.

 

RAD tool


APEX provides us with a full development environment, allowing us to develop, test, and deploy our applications. APEX includes three modules: Application Builder, SQL Workshop, and Utilities.

Application Builder

The Application Builder allows us to develop and test our applications, without leaving the development environment. While working in the Application Builder, it automatically adds a dedicated developer toolbar to every page in our application, making it very easy to toggle between the running page and its code within the Application Builder. The developer toolbar also provides the developer with more options to assist in the development process, like checking session state, running in debug mode, etc. Moreover, the Application Builder allows us to manage, monitor, and control all the application's shared components, supporting objects, and deployment scripts. In addition, the Application Builder includes tools for exporting and importing applications and application components.

Note

Some of these terms might be unfamiliar to you at this stage, but don't be alarmed as we'll cover them all further down this book.

SQL Workshop

The SQL Workshop includes a series of tools which allow us to create, manage, and inspect our application data infrastructure. This including wizards to create and manipulate database objects, scripts and queries, and the SQL Commands facility, which allows us to run SQL and PL/SQL code in the context of APEX. This can be very useful, as some of the APEX features and APIs are not available outside the APEX context (e.g. SQL*Plus) for security reasons.

Utilities

The Utilities module includes a number of tools to help us manage the APEX development-surrounding environment, especially regarding the database. It includes tools for the import and export of data into and out of the database, the APEX data dictionary, and several database monitoring and reporting tools.

The APEX environment also includes a special administration module, which allows us to define the APEX working environment, developers, and users.

All these modules include declarative, wizard-based tools, which makes the application development cycle a very rapid one. These development environment allows us to quickly define a prototype of our application, and then expand it with all the finer details.

 

Globalization, localization, and NLS


APEX was designed with globalization, localization, and NLS (National Language Support) in mind. To begin with, the APEX interface is translated into nine languages, other than English. The languages are German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese.

APEX can utilize all the globalization, localization, and NLS features that the Oracle database has to offer, and add some of its own. The Application Builder Shared Components module includes a Globalization section, which allows us to define relevant parameters for globalization, localization, and NLS. It also includes a wizard to guide us through the process of translating our entire application into secondary languages.

APEX takes into account the client side NLS settings when dealing with the import and export of data into and out of the database. It also takes into account the database NLS settings, in order to properly sort data, display dates, number format (e.g. decimal point or comma) and the local currency, among others.

APEX supports the XLIFF (XML Localization Interchange File Format) standard, which allows us to translate an entire APEX application into any language we need, without re-writing the application code. APEX provides a built-in mechanism to simultaneously run the same application in multi language User Interfaces, giving us several options to determine the application language, including the option of matching the APEX application language to the end user's browser language preferences. Moreover, if we need to support a language which is not included in the native supported languages list, this mechanism will allow us to specifically translate the built-in APEX engine strings and messages into that language, allowing us to develop and support APEX application in any language that our database can support.

Note

Since version 2.0, the client side communicates with the server side, using the AL32UTF8 character set, regardless of the database character set. Due to some of the advanced technologies APEX is using (like AJAX), this setting is mandatory. If you are going to work in a multi-language environment, it would be optimal to set your database character set to AL32UFT8. This will ensure a true multi-language environment, while eliminating the need to employ any character set conversions between the client and the server. Oracle is advising users to use AL32UTF8 as their default choice for the database character set.

Throughout this book, we will devote special attention to globalization, localization, and NLS issues in the APEX environment, including RTL (Right-To-Left) support examples.

 

APEX architecture


The core of APEX is a collection of PL/SQL packages, written using the PL/SQL Web Toolkit, and several hundred database tables, in which all the metadata regarding developed applications are stored.

The PL/SQL Web Toolkit is a collection of Oracle supplied database packages, which allow us to produce Web page code, directly in the database, using PL/SQL stored procedures. During run-time, and in real-time, the APEX engine queries the metadata tables and retrieves all the relevant information for a specific application page. Next, it uses the PL/SQL Web Toolkit to generate the application page's HTML code. This page's code also includes the proper linkage to the CSS and JavaScript resources that support proper page functionality, layout, and styles. Some internal tests run by the APEX development team, showed that the APEX engine performs all the real-time rendering processes, with an average overhead of less than 0.04 seconds per page. Pretty impressive in my opinion, and the APEX engine includes a server side caching mechanism, that can help us reduce that further.

It is important to understand that although the final product of APEX Application Builder is an HTML page code, no static files with any related HTML file extensions, are stored on the server. Each page is created upon demand a specific URI request. That, of course, gives us considerable flexibility with constructing the pages, e.g. the same page can be displayed somewhat differently for various users, or include conditioned components, depending on the user's security privileges, or role in the company, etc.

So, on one side we have the APEX engine, a collection of PL/SQL packages, running inside an Oracle database, and on the other side we have the application user, using a Web browser. How can we connect, and communicate between the sides? As we are in a Web environment, we are going to use the HTTP protocol, implemented in two technologies.

The first way is by using the Oracle HTTP Server (OHS) technology. OHS is based on the well-known (open source) Apache HTTP server, in its 1.3 or 2.0 versions. What interests us, for the APEX environment, is the Oracle plug-in module for this HTTP server, called mod_plsql.

OHS, with the mod_plsql module, is located between the client's Web browser and the Oracle database server. The mod_plsql module communicates with the database by mapping the Web browser request into PL/SQL stored procedures in the database. In turn, the PL/SQL stored procedures can manipulate the data in the database tables and generate HTTP responses, which can include HTML code, to be displayed on the client Web browser.

In order to communicate with the database, mod_plsql is using a DAD (Database Access Descriptor) file, which contains configuration parameters on how to connect with the database, which user and password to use, NLS parameters, and others.

The second technology to implement the HTTP communication protocol utilizes the Embedded PL/SQL Gateway running on the XML DB HTTP server, which is an integrated feature of the Oracle Database. Using the DBMS_EPG package, the Embedded PL/SQL Gateway can implement the core functionality of the OHS mod_plsql module, without installing it. This technology was first supported by APEX in the embedded version (2.1) of the Oracle XE database. Today, it's also supported by APEX 3.0 and above versions, running on Oracle 11g databases. APEX 3.0, which uses the embedded PL/SQL gateway, is installed by default as part of the 11g database installation process, and can be upgraded to any higher APEX version.

 

Summary


APEX is a RAD tool, running inside an Oracle database. Using a Web browser, it allows us to develop, test, and deploy a Web based, data centric application, in a declarative manner.

Although APEX is a declarative tool, we need to be familiar with some other programming technologies and resources, in order to optimally utilize it. That's in the next chapter.

About the Authors

  • Arie Geller

    Arie Geller is an independent IT consultant, with more than 30 years of experience with systems analysis, software development, IT infrastructure, etc. He started to use HTML DB 1.6 (the former name of APEX) to develop data centric Web applications in the Oracle environment, and continue doing so, with all the following versions, until today.

    Arie has specialized in developing Right-To-Left-oriented applications, and he brings his expertise and experience in this field into the book. Arie is also an active member of the APEX community on OTN, where he assists other members and shares his knowledge and experience as much as he can.

    Browse publications by this author
  • Matthew Lyon

    Matthew Lyon is a Senior Consultant at SRA Information Technology in Adelaide, South Australia. His involvement in enterprise software development extends across analysis, design, development, architecture, and project management, giving him a deep appreciation of the effective use of software technology. He has experience with a wide variety of technologies but has a passion for Oracle APEX and Java. Matthew runs APEX training courses for clients and is active in the Oracle community as a presenter and as a committee member of the South Australian Oracle User Group. Matthew has more than 10 years experience in software development and has been using APEX since HTMLDB version 1.6.

    Browse publications by this author
Book Title
Unlock this book and the full library for FREE
Start free trial