Oracle APEX Best Practices

296 Pages·2012·5.457 MB·English

by  Alex Nuijten, Iloon Ellen-Wolff, Learco Brizzi

Oracle APEX Best Practices
Accentuate Oracle APEX development with proven
best practices
Learco Brizzi
Iloon Ellen-Wolff
Alex Nuijten
professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
Oracle APEX Best Practices
Copyright © 2012 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: October 2012

Production Reference: 1181012

Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-400-2
www.packtpub.com
Cover Image by Sandeep Babu ([email protected])
Credits
Authors Project Coordinators
Learco Brizzi Yashodhan Dere
Iloon Ellen-Wolff Jovita Pinto
Alex Nuijten
Proofreader
Aaron Nash
Reviewers
Dimitri Gielis
Surachart Opun Indexers
Tejal Soni
Michel van Zoest
Hemangini Bari
Acquisition Editors
Dilip Venkatesh Graphics
Valentina Dsilva
Dhwani Devatar
Aditi Gajjar
Lead Technical Editor
Susmita Panda Production Coordinator
Nilesh R. Mohite
Technical Editors
Prasad Dalvi Cover Work
Nilesh R. Mohite
Lubna Shaikh
About the Authors
Learco Brizzi received his MSc in Informatics in 1993 and then he started
working with the early versions of Oracle Forms (3.0), Reports (1.1), and Designer
(1.2.1). When WebDB was launched, he created his first steps towards building
Internet applications. When APEX (HTMLDB) hit the market, he saw the potential
of this tool and built a complete music download store with it in 2004, including
integration with e-mail, reports, and payment service providers. This was one of the
very first serious applications ever built with APEX. Nowadays, Learco is dedicated
to the APEX and PL/SQL development. He is a very enthusiastic technician,
trainer of advanced APEX courses, presenter at conferences, organizer of seminars,
committee member of the OGh (Oracle user group in Holland) and member of the
editorial-committee for the Oracle magazine OGh Visie. Together with a companion
Learco started his own company, Itium, in 1999, which specializes in Oracle
technology. In May 2010, Learco was awarded the Oracle ACE membership.
You can contact Learco at [email protected].
I would like to thank my wife Judith, and my children Emma
(my nine-year-old daughter) and Dante (my six-year-old son) for
their patience and support they gave me while writing this book.
Most of the work had to be done in the evenings and weekends
(our family time together).

For me this was the first time I wrote a book and didn't know what
to expect. I didn't expect that it would take so much time, but it was
fun to do and I learned a lot of new things about Oracle and APEX
as well.

I would also like to thank my fellow writers Alex and Iloon.
Together we were a great team and kept each other sharp.

Last but not least I would like to thank the editors of the book who
had good comments and suggestions on our material.
Iloon Ellen-Wolff started working with Oracle 21 years ago, employed by several
software houses in the Netherlands. Her experience goes back to Oracle Forms
version 2 and Report Writer.
Almost 14 years ago, she started working for Oracle Support Services for the
developer competency (Oracle Forms, Oracle Reports, SQL Developer, and
Application Express), assisting customers by solving their software-related problems.
During those years, she applied the knowledge she had gained in many ways such as
coaching new engineers, team leading, teaching the Application Express courses, and
seminars for Oracle University.
Aside from being a very senior team member with excellent troubleshooting and
customer skills, in Oracle Support, she acts as Advanced Resolution Engineer.
One of her responsibilities in Oracle Support is Global Technical Lead Application
Express. She works closely with Sustaining Engineering, Product Development, and
Product Management of the Application Express team.
Starting last year, she is now involved in the Oracle Cloud project. She is member
of the platform as a service readiness team. Involved in testing Application Express
in the Cloud and being a trainer in this area, she enjoys the challenge to prepare
her engineers for the Cloud and ultimately to support Oracle customers in the
very near future!
Besides that, she is a frequent speaker for different Oracle User Groups about
Application Express, SQL Developer, and Oracle Public Cloud.
I would like to take the opportunity to thank the Application Express
development team and particularly Joel Kallman as Development
Director for the continuing effort to make Application Express an
excellent product.

I'd also like to thank my husband, Albert Ellen, for assisting me in
getting the layout right and supporting me in writing this book. Of
course, it took a big amount of time to achieve this goal.

I would also like to thank co-authors on this book, Alex and Learco.
Alex Nuijten works as a database developer and Expertise Lead (database
development) for AMIS Services BV in Nieuwegein, The Netherlands. Besides his
consultancy work, he conducts training classes, mainly in SQL and PL/SQL. Alex
has been a speaker at numerous international conferences, such as ODTUG, Oracle
Open World, UKOUG, IOUG, OGh, and OBUG. He is also a frequent contributor
at the Oracle Technology Network forum for SQL and PL/SQL. He has written many
articles in Oracle-related magazines, and at regular intervals he writes about Oracle
database development on the AMIS Technology Blog (technology.amis.nl), as well
as on his own blog, Notes on Oracle (nuijten.blogspot.com). In August 2010, Alex
was awarded the Oracle ACE Director membership.
I think this may be the hardest section to write. There are so many
people that inspired me and from whom I have learned so much
along the way. And each one of them helped me in writing my
chapters. It would be impossible to try to name you all and therefore
do injustice to the ones I let slip. My colleagues, fellow members
of the OTN forums and in the ACE program, the bloggers, the
Oracle user groups all over the globe, basically everyone I ever dealt
with—a big thank you to you all.

There are still some people I have to name in this section explicitly.

Let me start by thanking my co-authors, Iloon and Learco. Writing
this book was quite a journey. Thank you for allowing me to join you
with this adventure, it was a great experience.

Without the technical reviewers, this book would have looked totally
different. Thank you, Dimitri Gielis, Surachart Opun, and Michel
van Zoest, for your feedback and suggestions on improving the
quality of the book. Any outstanding errors in my chapters are, of
course, my own.

Without the continuing support of my wife Rian, son Tim, and
daughter Lara, I could never have worked on this. Preparing
presentations, writing articles and chapters take an enormous
amount of time and my family understands this, and they are alright
with it. I could never do all that without you, I love you so much.

Finally I would like to thank the Packt editorial and production
teams who have worked on this book, especially Yashodhan Dere,
Susmita Panda, Dilip Venkatesh, Dhwani Devatar, and Jovita Pinto.
About the Reviewers
Dimitri Gielis was born in 1978. Together with his family he lives in
Leuven, Belgium.
At an early age, Dimitri started experimenting with computers (Apple II and IBM XT),
and he quickly got to know that 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 came across 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 as
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 that 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.
In 2012, Dimitri was part of the LA OTN Tour where he presented different APEX topics.
You can contact Dimitri at [email protected].
Surachart Opun has been working on Oracle products for over seven years. He
has worked with Internet Service Provider Business for over eight years. He is Oracle
ACE, OCE RAC 10g, and OCP 10g and 11g. He has experience in implementation,
migration, and management of Oracle Database in telecommunication business and
so on. He has spent time in helping people who are interested in the Oracle products
as contributor. He is working on APEX since version 3 and has worked on APEX
implementation and migration.
His blog is at http://surachartopun.com.
Michel van Zoest was born in 1976. He currently lives in Bergambacht, The
Netherlands with his wife, two daughters, and son.
In 2000, he started working as an Oracle Consultant. He now has more than 12 years
of experience in building (web) applications by using the Oracle technologies such as
Oracle (web) Forms, Oracle Designer, MOD_PLSQL, ADF, SOA Suite, and of course,
APEX.
Michel was one of the first Oracle Application Express Developer Certified Experts
in the world.
He currently works at Whitehorses in The Netherlands and runs his own blog at
http://www.aboutapex.com. As well as that, he blogs at the company website
at http://blog.whitehorses.nl and he regularly writes Whitebook articles
(in Dutch) for Whitehorses.
Michel is also one of the authors of the Oracle APEX 4.0 Cookbook published by Packt
in December 2010.
You can contact Michel via his website or by emailing him at michel.van.zoest@
whitehorses.nl.
Table of Contents
Preface 1
Chapter 1: Prepare and Build 7
History and background 7
Installing APEX 9
Runtime or full development environment 10
Build status 10
Tablespaces 10
Converting runtime environment into a full development
environment and vice versa 11
Choosing a web server 12
OHS 13
EPG 13
APEX Listener 14
Creating a second administrator 16
APEX web interface 17
Command line 18
Other accounts 19
Database 19
Data model 19
Creating the database objects 21
Other tools 22
PL/SQL usage 23
Creating a workspace 23
Creating administrators, developers, and users 24
User Interface Defaults 25
Attribute Dictionary 25
Table Dictionary 25
Creating User Interface Defaults 26
Page Zero 29