Oracle APEX Reporting Tips & Tricks

By George Bara

Take advantage of all the exciting Reporting features of Oracle Application Express 4.2. Designed for a hands-on approach, this book will give you in-depth practical guidelines from George Bara, a well-known APEX expert and blogger.

From Classic to Interactive Reports, Web Services and PDF Printing, this book is must-have for all developers that want to take advantage of the Oracle APEX reporting engine.

• Language: English
• Publisher: Lulu.com
• Release date: May 8, 2013
• ISBN: 9781304020222

Book preview

Oracle APEX Reporting Tips & Tricks

Oracle APEX Reporting Tips & Tricks

GEORGE BARA

Copyright

Oracle APEX Reporting Tips & Tricks

Copyright © 2013 George Bara

First Edition

Published on May 8, 2013

All Rights Reserved.

ISBN: 978-1-304-02022-2

Introduction

Ever wanted to build a web application extremely fast, without the need of learning a new programming language? Ever wondered why it is still too complicated to quickly create web pages with forms and reports and why every Rapid Application Development tool out-there becomes rapid only after spending a few months learning it?

Well, there is a hidden gem called Oracle Application Express (APEX) that might just be the answer for one-off web application developers, database integrators and expert programmers alike. Using a highly declarative environment you can now build professional web applications with a click-and-click approach. The real surprise here is that this tool comes from Oracle, a corporation famous for valuing expensive locked products. APEX is free, but still not open source and has spawned of an internal project meant to make like for database developers and administrator easier.

My experience with APEX began in 2009 while working for a Life Insurance company, as an Oracle database developer. The main task was integrating multiple software systems, both internal and customer-facing. Lucky enough, all software components were using Oracle databases; hard enough there was little time and resources to develop the user interfaces in Java, .NET, PHP or any other programming language and framework. The IT manager had little experience with APEX, but great faith in its capabilities. So it began a 3-years long intensive experience with APEX 3.2, at a time when documentation was sparse, the experts were just a few and just a dozen enthusiasts were talking about APEX in public events, forums or message boards.

And so begins the history of this book. Everything that I didn’t found in Oracle documentation or forums, I had to invent and write down. Every time I embarked on a new APEX project I would reuse what I had previously build or learn. And after I while I decided to not let all this ad-hoc documentation go to a waste, so I started a blog together with my colleague and friend Claudiu Moldovan: http://www.apexninjas.com. Part of this book was originally written either as internal documentation for my use, either as blog postings on my blog. The rest is still part of the craziness that an Oracle developer develops while trying to accomplish sparks and glitter with a somewhat rigid development platform. I called it APEX – Reporting Tips & Tricks and that’s what it is: a collection of software experiments, workarounds and magic tricks that have helped me greatly while developing in APEX.

Meanwhile APEX has also moved in the cloud and it part of the Oracle Cloud as a hosted and managed database instance provisioned also with the latest APEX version: https://cloud.oracle.com/mycloud/f?p=service:home:0.

About the Author

Before we get started, just a little bit about me, the author.

My name is George Bara and I am currently working as a Business Consultant in the software industry. I’ve been working with Oracle products since 2006, developing software for automated logistic systems and insurance companies. I am an Oracle Certified Associate since 2008 and started working with APEX since 2009. I am passionate about Text Analytics, Big Data concepts, Statistical Machine Translation and Software Delivery Processes.

I hope you find this useful and please send me feedback at:

Email: [e-mail] or [e-mail]

Website: http://www.apexninjas.com/blog/the-authors/george/

Twitter: https://twitter.com/ApexNinjas

Chapter 1: Introducing APEX

Oracle Application Express, common known as APEX, is a Rapid Application Development (RAD) tool that has reached a certain high level of maturity with the launch of version 4.0 in June 2010. APEX combines fast development cycles for web-based applications revolving around an Oracle database with a strong developer base and dedicated evangelists promoting the technology. The programming technique is highly declarative in a web-based environment, with little programming effort required.

APEX uses a unique concept that can be considered opposite of all the current web development trends. While nowadays everything in a web application must be as loosely coupled to the database as possible, with emphasis on the client side interaction, APEX has a radical approach, as everything is stored in the database, from the data to the meta-data that generates the web pages. A web server is used to generate HTML pages directly from the database, where both the web page data and metadata is stored. Although APEX comes free of cost, all the development is done within an Oracle database using unique Oracle concepts, with all the backend processing and most of the frontend processing being performed by the Oracle database stored procedures. This unique approach is an opposite to the current web development community’s efforts, where most of emphasis is put in creating a 2.0 web stack using frameworks that would minimize the interaction between the database server and the front end.

But with its powerful web servers that facilitate the generation of web pages directly from the database, such as Oracle HTTP Server and APEX Listener, the overhead that APEX brings on generating web pages from metadata stored in the database is minimal. According to a 2006 study, generating the HTML web pages from the Oracle database induces an overhead of 0.04 second per each page (Oracle Application Express Best Practices at page. 17 http://tek.io/YeHsOL).

This introductory chapter is focused on the technology and main features of Application Express: how APEX evolved in the last years, how to get started with the product, what the technology stack is that runs behind the scenes and how to get started creating simple reports.

During the next chapters we will focus on digging deeper into Classic Reports, Interactive Reports, Reports based on Web services and PDF printing. This chapter describes how to create basic reports and what are the main features. Going into more details on customizing the reports will be done in the following chapters.

A short history of Application Express

Oracle Application Express (or APEX) was officially released in 2006, after rebranding an Oracle product known as HTMLDB. With mainly the same features as HTMLDB, Oracle Application Express has since evolved into the current 4.2 version; a powerful software development tool with a fair amount of out-of-the-box features aimed at PL/SQL developers, although Oracle database development skills are no longer a must have.

The official history of APEX begins in 2006, when HTMLDB became Oracle Application Express.

Then, in 2007, APEX 3.0 was released, introducing Flash Charts and XLS-FO based PDF printing. In 2008, version 3.1 was released with the biggest new feature being Interactive Reports, the component that became synonymous with APEX. The subsequent release, APEX 3.2, gained more popularity, becoming the known stable version of APEX that was used to create web pages based on an Oracle Database. Version 4.0 was released in June 2010, version 4.1 in August 2011. The latest version is APEX 4.2, released in October 2012.

Even from the earlier versions of APEX, the main product features were focused on building forms and report fast and with little effort from the developer. Reporting in web based applications does not necessarily mean displaying records on the screen in a matrix-like structure. Reporting means also creating application components able to display multiple types of information, from text and numbers to images and. To use data from multiple sources, such as database tables and views or web services. To be able to download the report data in multiple formats and being able to filter, sort and apply calculations to the reports.

This first chapter will present the basics of APEX reporting and will focus on identifying the main application components for building reports. Also, I will also present the way APEX works with the different options of web servers to better understand how to choose an appropriate solution for small, medium or large reporting projects.

Getting Started with APEX

Oracle Application Express is a free Oracle product that enables developers to quickly create web based applications on an Oracle database by using just a web browser. The APEX tool runs in a web browser and allows the creation of web applications in a declarative manner, by simply selecting pre-built components, configuring them and bundling them up in a web application, all without the need to write too much code.

APEX can be downloaded from the official Oracle website, together with documentation: http://bit.ly/8ZdMSa

Although most of the underlying code is written in PL/SQL, getting started with APEX requires little knowledge of any programming language, except maybe some HTML and web basics. Being web-based, the development process consists of using a series of predefined pages and objects, from forms to reports and charts. All pages and components are based on top of Oracle objects, usually tables and views, so a schema management tool is embedded in the project. Creating tables, views and stored procedures can be done from APEX, so the entire development process can be encapsulated, at least at the early stages, within the product’s web pages.

Accessing APEX is done by accessing a URL in a browser, accessing an online development service which provides access to a virtual private database. There is nothing to install to start the development, except by the server deployment of the Oracle database and the APEX product, which is done once by the instance administrator. The developer will have to log in with a workspace name, a username and a password.

The APEX workspace start-up screen gives access to the main components:

The main components of the APEX development environment are:

Application Builder, where applications and application pages are created declaratively using wizards. Each application is composed by one or multiple pages, translated at runtime by one or multiple web resources and each page is split into several regions. Each of the page regions can contain text, custom PL/SQL, reports, charts, maps, calendars, web service references or forms. Also there are other objects that are specific not only to the pages but to the whole application, like application items, processes, computations, authentication and authorization schemes or navigation objects like tabs, lists or breadcrumbs.

SQL Workshop, a tool that enables