Oracle APEX Cookbook

444 Pages·2013·7.24 MB·English

Oracle APEX
Cookbook
Second Edition
Create reliable, modern web applications for desktop and
mobile devices with Oracle Application Express
Marcel van der Plas
Michel van Zoest
BIRMINGHAM - MUMBAI
Oracle APEX Cookbook
Second Edition
Copyright © 2013 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
Second Edition: October 2013
Production Reference: 1221013
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78217-967-2
www.packtpub.com
Cover Image by Artie Ng ([email protected])
Credits
Authors Project Coordinator
Marcel van der Plas Shiksha Chaturvedi
Michel van Zoest
Proofreader
Reviewers Elinor Perry-Smith
Learco Brizzi
Indexer
Benjamin Kweku Intsiful
Hemangini Bari
Tony Jedlinski
Surachart Opun
Graphics
Ronald Rood
Ronak Dhruv
Iloon Ellen-Wolff
Production Coordinator
Acquisition Editor Arvindkumar Gupta
Kunal Parikh
Cover Work
Lead Technical Editor Arvindkumar Gupta
Susmita Panda
Technical Editors
Dennis John
Adrian Raposo
Gaurav Thingalaya
About the Authors
Marcel van der Plas was born in 1969 in Eindhoven and still lives there. He has been
an Oracle Consultant for over 15 years. Marcel has worked on many projects with Oracle
Designer, Oracle Forms, and Oracle Reports. Later on, he became interested in APEX and
did some projects with APEX. Marcel has been working on an APEX project for an electronics
company for over 2 years.
Marcel currently works for Ciber. Other companies he worked for are Atos Origin and
Whitehorses. For Whitehorses, he wrote some articles (White books) about Oracle.
Marcel wrote the Oracle APEX 4.0 Cookbook, Packt Publishing, with Michel van Zoest. You can
find APEX blogposts from Marcel at http://orclapex.blogspot.com. Besides that, you
can follow Marcel on Twitter (@whmvdp).
Michel van Zoest is a consultant with years of experience in building web applications
using Oracle technologies 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. He also blogs at the company website on http://blog.
whitehorses.nl and he regularly writes White book articles (in Dutch) for Whitehorses.
You can reach Michel on Twitter at @mvzoest or e-mail him at [email protected].
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 Publishing and his
invaluable help in the early stages of the book.
Furthermore, I would like to thank all reviewers for their hard work in
reviewing our drafts. This book has become so much better; thanks to
you guys.
I would also like to thank my employer, Whitehorses, for the support that I
have been given.
And last but not the 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 and my son Kris, this result would not have
been possible.
About the Reviewers
Learco Brizzi started working with the early versions of Oracle Forms (3.0), Reports
(1.1), and Designer (1.2.1), after he received his Master of Science degree in Information
Technology in 1993. When WebDB was launched, Learco took the first steps in building
Internet applications. When APEX (HTML DB) hit the market, he saw the potential of this tool
and built a complete music download store with it in 2004, including integration with reports
and payment service providers. This was one of the very first serious applications ever built
with APEX. Nowadays, Learco is dedicated to APEX and PL/SQL development.
He is a very enthusiastic technologist, trainer of advanced APEX courses, organizer of
seminars, board member of the OGh (the Oracle user group in the Netherlands), and member
of the editorial committee for Oracle magazine OGh Visie. For OGh Visie, he's responsible for
the yearly APEX World event that had about 300 attendees in 2013. Since 2013, he's also a
member of the APEX Feature Advisory Board.
In 1999, Learco started his own company, Itium, that specializes in Oracle technology and
launched the APEX plug-in website for community APEX plug-ins: http://www.apex-
plugin.com. He is the co-author of the book Oracle APEX Best Practices, published by
Packt Publishing.
In May 2010, Learco was awarded the Oracle ACE membership.
You can contact Learco by e-mail [email protected] or Twitter (@LBrizzi).
Benjamin Kweku Intsiful is a Ghanaian-born technologist who has been working
with Oracle products since the turn of the century. During his years working with the retail,
government, finance, and construction sectors, Intsiful has actively researched and applied
cutting-edge technologies from the Oracle product range and Microsoft paradigms.
His current tool of choice is Application Express (APEX) involved with projects, creating
applications pushing the growing limits of this ever-maturing product in Africa.
He is a certified Oracle Application Express expert, which he proudly highlights in his portfolio.
Past experiences are heavy with PL/SQL, Microsoft C#, and Active Server Pages. Notable
projects include Voters Management System (VMS) for the Electoral Commission of Ghana.
He has participated in a core team of developers for a complex forms project at the Nation
Health Insurance Authority (NHIA) of Ghana.
He is keenly interested in researching and sharing product knowledge on new and sometimes
under-utilized but useful functionality at user group events.
Occasionally you'll find him in the OTN forums, PL/SQL Challenge, and Top Coders, exhibiting
his skillsets or blogging at www.bkintsiful.blogspot.com.
I would like to thank the Almighty God, who made this possible. I would also
like to thank my mum and family for supporting me through thick nights
when I was stressed, but kept me company to review this awesome book.
Tony Jedlinski is an accomplished professional with over 30 years' experience in
database development including over 20 years' experience in designing and developing
custom Oracle-based applications.
He is a recognized expert in Oracle Application Express and is Oracle Application Express
Developer certified (September, 2010).
Tony is the President of Konoso LLC (www.konoso.com) and has presented technical
papers on Oracle Application Express at national and international conferences, including
Oracle World (2004, 2006), IOUG Live! (2004, 2005), and Collaborate 2006 - 2011, 2013.
He was named HTML DB (Oracle Application Express) Developer of the Year in 2005 by
Oracle Magazine.
As the Executive Vice President and Director of Web Initiatives—Independent Oracle User
Group (IOUG), he led a volunteer effort to design and develop a flexible website hosting
system for over 40 Regional User Groups and Special Interest Groups.
Surachart Opun has over 10 years' experience in Information Technology. He is a
computer engineer and lives in Thailand. His interests include learning about Oracle
Technology and other new technologies. He worked with Internet Service Provider Business for
over 8 years. He is an Oracle ACE, Oracle 10g RAC Certified Expert, Oracle 11g Performance
Tuning Certified Expert, Oracle EM12c Certified Implementation Specialist, and an Oracle
10g/11g Certified Professional.
He also has experience in implementation, migration, and management of Oracle Database in
telecommunication business. He has spent time to help people who are interested in Oracle
Products. He's currently planning and developing an Oracle APEX group in Thailand.
He was also the technical reviewer of the following books:
f Oracle APEX 4.0 Cookbook, Packt Publishing
f Oracle APEX Best Practices, Packt Publishing
f Oracle Database 11g Performance Tuning Recipes: A Problem-Solution
Approach, APress
Surachart blogs at http://surachartopun.com and http://oraclethai.blogspot.
com. You can reach him via Twitter (@surachart) or via LinkedIn at http://th.linkedin.
com/in/surachartopun.
Ronald Rood is an innovating Oracle DBA with over 20 years of IT experience. He has
built and managed cluster databases on about each and every platform that Oracle ever
supported, from the famous OPS databases in Version 7 until the latest RAC releases,
currently being 11g. Ronald is constantly looking for ways to get the most value out of the
database to make the investment for his customers even more valuable. He knows how to the
handle the power of the rich Unix environment very well, and this is what makes him a first
class troubleshooter and solution architect. Apart from the spoken languages such as Dutch,
English, German, and French, he also writes fluently in many scripting languages.
Currently Ronald is a principal consultant and Oracle ACE working for Ciber in the Netherlands
where he cooperates in many complex projects for large companies where downtime is not an
option. Ciber (CBR) is a global IT services company with a client-focused approach.
Ronald often likes getting involved in Oracle forums, writes his own blog (http://ronr.
blogspot.com) called From errors we learn..., writes for various Oracle-related magazines and
also has written a book, Mastering Oracle Scheduler in Oracle 11g Databases, Packt Publishing,
where he fills the gap between the Oracle Documentation and the customers questions.
Ronald has lots of certifications, some of them are as follows:
f Oracle Certified Master
f Oracle Certified Professional
f Oracle Database 11g Tuning Specialist
f Oracle Database 11g Data Warehouse Certified Implementation Specialist
Ronald fills his time with Oracle, his family, sky-diving, radio-controlled model airplane flying,
running a scouting group, and having a lot of fun.
"A problem is merely a challenge that might take a little time to solve"
-Ronald Rood