Oracle Application Express 3.2: The Essentials and More

644 Pages·2010·16.19 MB·English

by  Arie Geller

Oracle Application Express 3.2
The Essentials and More
Develop Native Oracle database-centric web
applications quickly and easily with Oracle APEX
Arie Geller
Matthew Lyon

professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
Oracle Application Express 3.2
The Essentials and More
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: June 2010
Production Reference: 1250510
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-847194-52-7
www.packtpub.com
Cover Image by Vinayak Chittar ([email protected])
Credits
Authors Editorial Team Leader
Arie Geller Akshara Aware
Matthew Lyon
Project Team Leader
Reviewers Priya Mukherji
Ashish Agarwal
Ben Burrell Project Coordinator
Katie McLaughlin Zainab Bagasrawala
Penny Cookson
Proofreaders
Acquisition Editor Aaron Nash
James Lumsden Jeff Orloff
Development Editors Indexer
Dhiraj Chandiramani Hemangini Bari
Rakesh Shejwal
Graphics
Technical Editor Geetanjali Sawant
Pallavi Kachare
Production Coordinator
Copy Editor Aparna Bhagat
Leonard D'Silva
Cover Work
Aparna Bhagat
About the Authors
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.
I would like to thank my surrounding family for their great patient
and support, and also to my friends and customers for their
understanding, during the very demanding period of writing
this book.

I would also like to thank Joel Kallman and Scott Spadafore (RIP)
from the APEX development team, for their invaluable help and
insight on APEX, over the years.

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.
I would like to thank my lovely wife Zoe and beautiful baby girl
Rose for their help and support while writing this book.
About the Reviewers
Ashish Agarwal has the distinct privilege of being one of the less than a few
hundred Oracle Certified Masters in the world (http://www.oracle.com/
technology/ocm/aagarwal.html). He is also a certified Project Management
Professional from PMI, USA & Oracle Certified Professional in Oracle 10g, 9i, 8i, and
8. With a total industry experience of more than 12 years in Information Technology
Field, exhaustive experience and Knowledge in Managing Oracle Databases
and Oracle Applications (ERP and CRM Suites), administration is his expertise.
Participating in the design of Infrastructure Architecture for Oracle Products and
managing data centre kind of environment with multiple databases and servers
are some of his other experience areas.
Ashish has worked with companies like Panasonic, Oracle, Sapient, and Mercer
across the geographies of India, Singapore, Malaysia, Canada, and the UK, and is
now currently running DBCON—a Database & Oracle APEX consulting company
based in Singapore. DBCON leverages on the power of the Internet to provide live
online training, application development, management and maintenance, support,
and troubleshooting in Oracle technologies to anyone in the world. DBCON also
leverages on the power of the Internet to engage the best people in their respective
areas from across the globe.
Attracted to Oracle Application Express because of its simplicity and productivity,
Ashish regularly conducts live online training in Oracle Application Express through
his company DBCON. Anyone from anywhere in the world can attend this training
with just a PC and internet connection. To learn more about live online training in
Oracle Application Express please visit http://www.dbcon.com or e-mail at
[email protected].
Ben Burell is an Oracle developer currently based in the Isle of Man. He is an active
contributor to the Oracle APEX forum as 'Munky' and sporadically blogs about
APEX related tips and tricks at http://munkyben.wordpress.com.
He enjoys problem solving while listening to music, meaning that he quite enjoys
his job!
Katie McLaughlin is a software developer and consultant who has been
working with Oracle Apex for many years, developing enterprise applications as
well as for smaller business requirements. She currently works for an international
software house, developing financial applications for some of the largest companies
in the world.
I'd like to thank my partner, Karl, for all his kindness and for
bringing many a cup of tea during the time I worked on this book.
Penny Cookson has been working with Oracle products since 1987. Penny is
the Managing Director and Principal Consultant for SAGE Computing Services,
an Australian company which specializes in providing Oracle education and
consulting services. She has written and conducted training courses and provided
expert advice in a wide range of products, including Application Express, and is a
regular presenter at Oracle and User Group conferences. She was Oracle Magazine's
Educator of the Year in 2004 and is an Oracle ACE.
Table of Contents
Preface 1
Chapter 1: An Introduction to APEX 7
Web-based 7
Native Web based 8
Data Centric 9
A declarative development tool 9
RAD tool 10
Application Builder 10
Utilities 11
Globalization, localization, and NLS 11
APEX architecture 12
Summary 14
Chapter 2: What we need to know to effectively use APEX 15
SQL and PL/SQL 16
HTML and CSS 16
JavaScript 16
HTML 17
The DOM (Document Object Model) 17
DOM walking 19
DOM methods 19
DOM events 20
The APEX context 21
CSS 21
The cascading effect 22
The style sheets 22
CSS properties 24
CSS selectors 24
Table of Contents
The !important keyword 27
The APEX angle 28
Where to store our external CSS file? 28
How to load our CSS code? 29
Globalization and NLS 29
JavaScript 30
JavaScript features 30
Object-Oriented 30
Case sensitive 31
JavaScript variables 31
JavaScript arrays 32
JavaScript operators 32
Blocks of code 34
JavaScript functions 35
The APEX angle 35
Where to store our external JavaScript file? 36
How to load our external JavaScript file? 36
Globalization and NLS 37
PL/SQL and JavaScript (advanced) 38
JavaScript and server-side resources (advanced) 38
The APEX JavaScript API 39
Summary 41
Chapter 3: APEX Basic Concepts 43
Session state 44
Session state firing points 44
Session state and the application page life cycle 45
Session state and the page rendering phase 45
Session state and the page processing phase 48
Manually saving to session state 48
Using the APEX_UTIL.SET_SESSION_STATE API procedure 48
Session state and AJAX 49
Using the f?p URL notation 50
Referencing persistence session state 50
Session state and stored procedures 51
Session state scope and persistence 53
Reviewing session state from the application builder 54
Substitution strings 55
APEX engine built-in substitution strings 55
Substitution strings that contain data 56
Substitution strings that contain HTML code 58
Application substitution strings 58
Application or page items 59
Referencing substitution strings 59
Using the #ITEM# notation 59
Using the &ITEM. notation 60
Referencing substitution strings in SQL and PL/SQL code 61
[ ii ]
Table of Contents
Shortcuts 62
Defining a shortcut 63
The name field 64
The type field 64
The shortcut field 64
Shortcuts availability 65
Themes and templates 66
Themes 66
Templates 68
Page templates 68
Region templates 69
Reports templates 70
Breadcrumbs templates 70
List templates 70
Calendar templates 70
Label templates 70
Button templates 70
Popup List Of Values template 70
Templates and the APEX upgrade process 71
Theme, templates, and good practice 71
The f?p URL notation 71
The p segments 71
1—App 72
2—Page 72
3—Session 73
4—Request 73
5—Debug 74
6—CC (Clear Cache) 74
7—itemName 75
8—itemValue 75
9—PR (Printer friendly) 76
Item values including the colon character 76
APEX multi-lingual support 77
The APEX built-in translation mechanism 77
Single language applications 77
Single application with Multi-lingual user interface 78
National Language Support (NLS) in this book 78
Summary 78
Chapter 4: The Application Builder Basic Concepts and
Building Blocks 79
The APEX Workspace 79
Start working with APEX 82
Logging in to the APEX IDE 83
[ iii ]