Oracle APEX 4.0 Cookbook

Table of Contents

Oracle APEX 4.0 Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Creating a Basic APEX Application

Introduction

Creating an APEX 4.0 application

How to do it...

How it works...

Creating a simple form page

Getting ready

How to do it...

How it works...

There's more…

Creating a simple report

Getting ready

How to do it...

How it works...

See also

Implementing an interactive report

Getting ready

How to do it...

There's more...

Creating a chart

Getting ready

How to do it...

There's more...

Creating a map chart

Getting ready

How to do it...

How it works...

There's more...

Creating a navigation bar

Getting ready

How to do it...

There's more...

See also

Creating a list of values

Getting ready

How to do it...

How it works...

There's more...

Including different item types

Getting ready

How to do it...

Protecting a page using an authorization scheme

Getting ready

How to do it...

How it works...

There's more...

Securing an application with Authentication

Getting ready

How to do it...

Control the display of regions and items with Dynamic Actions

Getting ready

How to do it...

How it works...

There's more...

Creating a computation

How to do it...

There's more...

Creating an automated row fetch with a page process

Getting ready

How to do it...

How it works...

Putting some validation in a form

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a report with PL/SQL Dynamic Content

How to do it...

2. Themes and Templates

Introduction

Creating your own theme

Getting ready

How to do it...

How it works...

Importing a theme

Getting ready

How to do it...

How it works...

Creating a custom template

Getting ready

How to do it...

There's more...

Including images in your application

Getting ready

How to do it...

How it works...

There's more...

Referencing CSS classes in your application

Getting ready

How to do it...

How it works...

Controlling the layout

Getting ready

How to do it...

How it works...

There's more...

3. Extending APEX

Introduction

Adding JavaScript code to your application

Getting ready

How to do it...

How it works...

There's more...

Creating a tag cloud with AJAX

Getting ready

How to do it...

How it works...

Creating visual effects with JavaScript libraries

Getting ready

How to do it...

How it works...

There's more

Enhancing your application with the Google API

Getting ready

How to do it...

How it works...

See also

Including Google maps

Getting ready

How to do it...

How it works...

There's more...

See also

Embedding multimedia objects in your application

Getting ready

How to do it...

How it works...

There's more...

See also…

Creating a region selector

Getting ready

How to do it...

How it works...

Sending mail via APEX

Getting ready

How to do it...

How it works...

Uploading and downloading files

Getting ready

How to do it...

How it works...

There's more...

Calling APEX from an Oracle Form

Getting ready

How to do it...

How it works...

There's more...

4. Creating Websheet Applications

Introduction

Creating a websheet application

Getting ready

How to do it...

How it works

See also

Creating a page in a websheet

Getting ready

How to do it...

Adding a navigation section to a page

Getting ready

How to do it...

Implementing a datagrid

Getting ready

How to do it...

How it works...

See also

Allowing multiple users access to a websheet

Getting ready

How to do it...

How it works...

Creating an enhanced datagrid from a spreadsheet

How to do it...

There's more...

5. APEX Plug-ins

Introduction

Creating an item type plug-in

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a region type plug-in

Getting ready

How to do it...

How it works...

See also

Creating a dynamic action plug-in

Getting ready

How to do it...

How it works...

Creating a process type plug-in

Getting ready

How to do it...

How it works...

There's more...

6. Creating Multilingual APEX Applications

Introduction

Creating a translatable application

Getting ready

How to do it...

See also

Using XLIFF files

Getting ready

How to do it...

How it works...

Switching languages

Getting ready

How to do it...

Translating data in an application

Getting ready

How to do it

How it works

7. APEX APIs

Introduction

Updating a table with the hidden primary key

Getting ready

How to do it...

How it works...

Reading a checkbox programmatically

Getting ready

How to do it...

Creating help functionality with apex_application.help

Getting ready

How to do it...

How it works...

There's more...

Counting clicks with apex_util.count_click

Getting ready

How to do it...

How it works...

There's more...

Setting default item settings with apex_ui_default_update

Getting ready

How to do it...

How it works...

There's more...

Creating a background process with apex_plsql_job

Getting ready

How to do it...

How it works...

There's more...

8. Using Webservices

Introduction

Creating a SOAP webservice reference

Getting ready

How to do it...

How it works…

See also

Creating a REST webservice reference

Getting ready

How to do it...

How it works…

There's more...

Building a page on a webservice reference

Getting ready

How to do it...

How it works…

There's more…

9. Publishing from APEX

Introduction

Exporting to a comma separated file

Getting ready

How to do it...

How it works...

Creating a PDF report

Getting ready

How to do it...

How it works...

There's more...

Creating a report query

Getting ready

How to do it...

How it works...

Creating a report layout using Oracle BI Publisher

Getting ready

How to do it...

How it works...

Linking the report layout to the report query

Getting ready

How to do it...

How it works...

Calling a report from a page

Getting ready

How to do it...

How it works...

10. APEX Environment

Introduction

Setting up a development environment using subscriptions

How to do it...

Debugging an APEX application

Getting ready

How to do it...

There's more...

Debugging an APEX application remotely

Getting ready

How to do it...

How it works

Deploying an application with SQL Developer

Getting ready

How to do it...

Setting up version control with APEX and SVN

Getting ready

How to do it...

Setting up a production environment using an Apache proxy

Getting ready

How to do it...

Setting up the APEX Listener on Weblogic

Getting ready

How to do it...

11. APEX Administration

Introduction

Creating a workspace manually

Getting ready

How to do it...

How it works

Creating a workspace by request

Getting ready

How to do it...

There's more...

Creating a user

Getting ready

How to do it...

Adding a schema to your workspace

Getting ready

How to do it...

How it works

Setting a system message

Getting ready...

How to do it...

There's more...

Setting a workspace announcement

Getting ready

How to do it...

There's more...

Setting news items on the home page

Getting ready

How to do it...

There's more...

Creating a site-specific task list

Getting ready...

How to do it...

How it works…

There's more...

Creating a public theme

Getting ready...

How to do it...

How it works...

There's more...

Locking a workspace

Getting ready...

How to do it...

How it works...

There's more...

12. Team Development

Introduction

Creating a list of features

Getting ready

How to do it...

There's more...

Creating and assigning To-do's

Getting ready

How to do it...

Keeping track of bugs in the Bugtracker

Getting ready

How to do it...

There's more…

Creating Milestones

Getting ready

How to do it...

Using Feedback

Getting ready

How to do it...

There's more...

Using Follow-ups

Getting ready

How to do it…

How it works

Index

Oracle APEX 4.0 Cookbook

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

Marcel van der Plas

Michel van Zoest

Reviewers

Dimitri Gielis

Maarten van Luijtelaar

Surachart Opun

Development Editor

Maitreya Bhakal

Technical Editor

Neha Damle

Indexers

Monica Ajmera Mehta

Rekha Nair

Editorial Team Leader

Gagandeep Singh

Project Team Leader

Priya Mukherji

Project Coordinator

Srimoyee Ghoshal

Proofreader

Aaron Nash

Graphics

Geetanjali Sawant

Production Coordinator

Shantanu Zagade

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 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 atwww.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

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.

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

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.

Note

Warnings or important notes appear in a box like this.

Note

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a