Oracle APEX 4.0 Cookbook

328 Pages·2010·10.97 MB·English

by  Marcel van der Plas Michel van Zoest

Oracle APEX 4.0 Cookbook
Over 80 great recipes to develop and deploy fast, secure,
and modern web applications with Oracle Application
Express 4.0
Marcel van der Plas
Michel van Zoest

BIRMINGHAM - MUMBAI
Oracle APEX 4.0 Cookbook
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system,
or transmitted in any form or by any means, without the prior written permission of the
publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the authors, nor Packt Publishing, and its
dealers and distributors will be held liable for any damages caused or alleged to be
caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: December 2010
Production Reference: 1081210
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849681-34-6
www.packtpub.com
Cover Image by Dan Anderson ([email protected])
Credits
Authors Editorial Team Leader
Marcel van der Plas Gagandeep Singh
Michel van Zoest
Project Team Leader
Reviewers Priya Mukherji
Dimitri Gielis
Project Coordinator
Maarten van Luijtelaar
Srimoyee Ghoshal
Surachart Opun
Proofreader
Development Editor
Aaron Nash
Maitreya Bhakal
Graphics
Technical Editor
Geetanjali Sawant
Neha Damle
Production Coordinator
Indexers
Shantanu Zagade
Monica Ajmera Mehta
Rekha Nair
Cover Work
Shantanu Zagade
About the Authors
Marcel van der Plas has been an Oracle Consultant for over 15 years. And from the
beginning, he learned to work with Oracle Forms, Oracle Reports, and Oracle Designer.
Marcel has worked on many projects with these tools. Later on, he became interested in
APEX and did some projects with APEX.
Marcel currently works for Ciber. Other companies he worked for are Atos Origin and
Whitehorses. For Whitehorses, he wrote some articles ("Whitebooks") about Oracle.
I would like to thank Michel van Zoest, my co-author for helping and working
together. I also want to thank the reviewers Maarten van Luijtelaar, Dimitri
Gielis, and Surachart Opun. Their comments were so valuable and helpful.
I would like to thank Douwe Pieter van den Bos for introducing us to Packt
and I would like to thank Packt for giving me this opportunity to write this
book. I would like to thank my employer, Ciber, for giving me the freedom to
write this book.

Last but not least, I would like to thank my wife Yvonne and my children
Vera, Laura, and Joey for inspiring and supporting me. At the same time, I
would like to apologize to them for not having time to play on the weekends
during the months that I wrote this book.
Michel van Zoest is a consultant with more than 10 years of experience in building
(web) applications using Oracle technology such as Oracle (web) Forms, Oracle Designer,
MOD_PLSQL, ADF, SOA Suite and of course, APEX.
He is one of the first Oracle Application Express Developer Certified Experts in the world.
He has used his APEX knowledge in projects for companies ranging in size from a single
employee to large multinationals. His experience in these projects has been used in the
realization of this book.
Michel currently works at Whitehorses in the Netherlands and runs his own blog at
http://www.aboutapex.com. Next to that, he blogs at the company website on
http://blog.whitehorses.nl and he regularly writes Whitebook articles (in Dutch)
for Whitehorses.
First of all, I would like to thank my co-author Marcel van der Plas. Thanks to
the easy way that we could work together, the writing of this book has gone
as smooth as possible.

I would like to thank the people at Packt Publishing for offering me the
chance to write this book. It has been a long process with a lot of hard work,
but I'm very happy with the result. I also would like to thank Douwe Pieter
van den Bos for introducing me and Marcel to Packt and his invaluable help
in the early stages of the book.

Furthermore, I would like to thank Maarten van Luijtelaar, Dimitri Gielis, and
Surachart Opun for their hard work in reviewing our drafts. This book has
become so much better thanks to you guys.

I also would like to thank my employer Whitehorses for the support I have
been given.

And last but not least, I would like to thank my family for their love and
support. Without the help of my wife Jamila and the "dikke kroelen" from my
daughters Naomi and Aniek, this result would not have been possible.
About the Reviewers
Dimitri Gielis was born in 1978. Together with his family he lives in Leuven, Belgium.
At an early age, Dimitri started working with computers (Apple II, IBM XT) and he quickly
knew he would like to work with computers and especially with databases all his life.
In 2000, Dimitri began his career working as a consultant for Oracle, Belgium where he
got in touch with almost every Oracle product. His main expertise was in the database
area, but at that time he was also exposed to HTMLDB, which was renamed Oracle
Application Express later on. From the very start he liked the Oracle database and
APEX so much that he never stopped working with it. Dimitri then switched to another
company to create an Oracle team and do pre-sales, to later create and manage an
Oracle Business Unit.
In 2007, Dimitri co-founded APEX Evangelists (http://www.apex-evangelists.com),
together with John Scott. APEX Evangelists is a company which specializes in
providing training, development, and consulting specifically for the Oracle
Application Express product.
On his blog (http://dgielis.blogspot.com) he shares his thoughts and experience
about Oracle and especially Oracle Application Express.
Dimitri is a frequent presenter at OBUG Connect, IOUG Collaborate, ODTUG Kaleidoscope,
UKOUG conference, and Oracle Open World. He likes to share his experience and meet
other people. He's also President of the OBUG (Oracle Benelux User Group) APEX SIG.
In 2008, Dimitri became an Oracle ACE Director. Oracle ACE Directors are known for their
strong credentials as Oracle community enthusiasts and advocates.
In 2009, Dimitri received the "APEX Developer of the year" award by Oracle Magazine.
You can contact Dimitri at [email protected].
Surachart Opun has been working with Oracle products for over six years. Surachart
is co-founder for Oracle User Group in Thailand. He is Oracle ACE, OCE RAC, and OCP
10g/11g. He implemented, migrated, and operated about Oracle Products including
Oracle Database, Application Express and so on. He contributes more about
Oracle Products.
Blog: http://surachartopun.com
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to
your book.
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files available? You can upgrade to the eBook version at www.PacktPub.com and
as a print book customer, you are entitled to a discount on the eBook copy. Get in touch
with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
 Fully searchable across every book published by Packt
 Copy and paste, print and bookmark content
 On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.
Table of Contents
Preface 1
Chapter 1: Creating a Basic APEX Application 7
Introduction 8
Creating an APEX 4.0 application 8
Creating a simple form page 12
Creating a simple report 15
Implementing an interactive report 19
Creating a chart 24
Creating a map chart 26
Creating a navigation bar 29
Creating a list of values 32
Including different item types 35
Protecting a page using an authorization scheme 38
Securing an application with Authentication 40
Control the display of regions and items with Dynamic Actions 42
Creating a computation 45
Creating an automated row fetch with a page process 46
Putting some validation in a form 49
Creating a report with PL/SQL Dynamic Content 55
Chapter 2: Themes and Templates 59
Introduction 59
Creating your own theme 60
Importing a theme 64
Creating a custom template 66
Including images in your application 70
Referencing CSS classes in your application 72
Controlling the layout 77