Oracle APEX 4.0 Cookbook

310 Pages·2010·20.81 MB·English

by  M. 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])
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
Table of Contents
Chapter 3: Extending APEX 81
Introduction 81
Adding JavaScript code to your application 82
Creating a tag cloud with AJAX 85
Creating visual effects with JavaScript libraries 91
Enhancing your application with the Google API 96
Including Google maps 98
Embedding multimedia objects in your application 104
Creating a region selector 108
Sending mail via APEX 112
Uploading and downloading files 116
Calling APEX from an Oracle Form 120
Chapter 4: Creating Websheet Applications 123
Introduction 123
Creating a websheet application 124
Creating a page in a websheet 126
Adding a navigation section to a page 128
Implementing a datagrid 130
Allowing multiple users access to a websheet 133
Creating an enhanced datagrid from a spreadsheet 136
Chapter 5: APEX Plug-ins 141
Introduction 141
Creating an item type plug-in 142
Creating a region type plug-in 149
Creating a dynamic action plug-in 153
Creating a process type plug-in 157
Chapter 6: Creating Multilingual APEX Applications 161
Introduction 161
Creating a translatable application 162
Using XLIFF files 166
Switching languages 172
Translating data in an application 174
Chapter 7: APEX APIs 179
Introduction 179
Updating a table with the hidden primary key 179
Reading a checkbox programmatically 183
Creating help functionality with apex_application.help 186
Counting clicks with apex_util.count_click 190
Setting default item settings with apex_ui_default_update 194
Creating a background process with apex_plsql_job 197
ii
Table of Contents
Chapter 8: Using Webservices 201
Introduction 201
Creating a SOAP webservice reference 201
Creating a REST webservice reference 207
Building a page on a webservice reference 210
Chapter 9: Publishing from APEX 217
Introduction 217
Exporting to a comma separated file 217
Creating a PDF report 220
Creating a report query 224
Creating a report layout using Oracle BI Publisher 225
Linking the report layout to the report query 227
Calling a report from a page 229
Chapter 10: APEX Environment 231
Introduction 231
Setting up a development environment using subscriptions 232
Debugging an APEX application 237
Debugging an APEX application remotely 244
Deploying an application with SQL Developer 250
Setting up version control with APEX and SVN 252
Setting up a production environment using an Apache proxy 255
Setting up the APEX Listener on Weblogic 258
Chapter 11: APEX Administration 261
Introduction 261
Creating a workspace manually 262
Creating a workspace by request 264
Creating a user 268
Adding a schema to your workspace 269
Setting a system message 271
Setting a workspace announcement 272
Setting news items on the home page 273
Creating a site-specific task list 274
Creating a public theme 276
Locking a workspace 279
Chapter 12: Team Development 281
Introduction 281
Creating a list of features 283
Creating and assigning To-do's 288
Keeping track of bugs in the Bugtracker 291
Creating Milestones 293
iii
Table of Contents
Using Feedback 297
Using Follow-ups 300
Index 305
iv
Preface
Oracle Application Express 4.0 is a rapid web application development tool that works with
the Oracle database. Using features like Plug-ins and Dynamic Actions, APEX helps you build
applications with the latest techniques in AJAX and JavaScript.
The Oracle Application Express 4.0 Cookbook shows you recipes to develop and deploy reliable,
modern web applications using only a web browser and limited programming experience.
With recipes covering many different topics, it will show you how to use the many features of
APEX 4.0.
You will learn how to create simple form and report pages and how to enhance the look of
your applications by using stylesheets. You will see how you can integrate things such as Tag
Clouds, Google Maps, web services, and much more in your applications. Using Plug-ins,
Dynamic Actions, BI Publisher, Translations, and Websheets, you will be able to enhance
your applications to a new level in APEX.
This book will show you how to be agile in the development of your web applications by using
Team Development, debugging, and third-party tools.
After reading this book, you will be able to create feature-rich web applications in Application
Express 4.0 with ease and confidence.
What this book covers
Chapter 1, Creating a basic APEX application, describes the basic steps to create an
APEX application. We will learn to make an intranet application where employees can
get information.
Chapter 2, Themes and Templates, presents some recipes which will make your application
look better using themes and templates by creating your own theme, including images in it
and so on.
Preface
Chapter 3, Extending APEX, shows us how to we will extend our application with some nice
features like visual effects, a tag cloud, and a Google map.
Chapter 4, Creating Websheet Applications, teaches us how to create a websheet application,
create a page in the application, add a navigation page to the websheet, and allow multiple
users to access the websheet.
Chapter 5, APEX Plug-ins, describes the four types of plug-ins: Item type, Region type, Dynamic
action, and Process type plug-ins.
Chapter 6, Creating Multilingual APEX Applications, shows us how we can fully translate an
application using built-in functionality to translate applications, without having to rebuild
the application completely and adding something of ourselves to easily switch between
languages.
Chapter 7, APEX APIs, shows us how to use APIs as they offer a lot of flexibility and speed in
developing web applications.
Chapter 8, Using Webservices, teaches us how to use webservices in APEX.
Chapter 9, Publishing From APEX, shows you how to export reports and get the output in some
kind of digital format and how to interact with BI Publisher.
Chapter 10, APEX Environment, contains recipes that will show how to set up and use a
development environment, how to use version control and how to deploy Application Express
on a web container with the APEX Listener.
Chapter 11, APEX Administration, shows you how to create a workspace, how to create users
on the workspace and how to manage the workspaces.
Chapter 12, Team Development, we will see how we can take advantage of the features in
Team Development in our project. Each recipe will show how a part of Team Development
can be put to use in a specific part of the project cycle.
What you need for this book
APEX 4.0 or higher.
Oracle RDBMS database 10.2.0.3 or higher.
Either one of the following Internet browsers:
 Microsoft Internet Explorer 7 or later
 Mozilla Firefox 3.5 or later
 Google Chrome 4.0 or later
 Apple Safari 4.0 or later

Preface
Who this book is for
This book is aimed both at developers new to the APEX environment and at intermediate
developers. More advanced developers will also gain from the information at hand.
If you are new to APEX you will find recipes to start development. If you are an
experienced user you will find ways to work smarter and more easily with APEX
and enhance your applications.
A little knowledge of PL/SQL, HTML and JavaScript is assumed.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of
information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "We can include other contexts through the use of
the include directive."
A block of code is set as follows:
select rv_meaning display_value
, rv_low_value return_value
from app_ref_codes
where rv_domain = 'ADDRESSES'
When we wish to draw your attention to a particular part of a code block, the relevant lines or
items are set in bold:
select rv_meaning display_value
, rv_low_value return_value
from app_ref_codes
where rv_domain = 'ADDRESSES'
In this book, code snippets are followed by a small note, for example, [1346_01_01.txt] which
points to that particular code file in the code bundle available on the Packt website.
Any command-line input or output is written as follows:
# cp /usr/src/asterisk-addons/configs/cdr_mysql.conf.sample
/etc/asterisk/cdr_mysql.conf
New terms and important words are shown in bold. Words that you see on the screen, in
menus or dialog boxes for example, appear in the text like this: "clicking the Next button
moves you to the next screen".