Oracle Application Express 3.2 – The Essentials and More电子书

Oracle Application Express 3.2

Table of Contents

Oracle Application Express 3.2

Credits

About the Authors

About the Reviewers

Preface

What is the aim of the book

Who this book is for

What this book covers

Standards and assumptions

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. An Introduction to APEX

Web-based

Native Web based

Data Centric

A declarative development tool

RAD tool

Application Builder

SQL Workshop

Utilities

Globalization, localization, and NLS

APEX architecture

Summary

2. What we need to know to effectively use APEX

SQL and PL/SQL

HTML and CSS

JavaScript

HTML

The DOM (Document Object Model)

DOM walking

DOM methods

DOM events

The APEX context

CSS

The cascading effect

The style sheets

CSS properties

CSS selectors

HTML tags selectors:

Pseudo-class selectors:

HTML ID selectors:

Class selectors

Compound (nested) class selectors:

Contextual selectors:

The !important keyword

The APEX angle

Where to store our external CSS file?

How to load our CSS code?

Globalization and NLS

JavaScript

JavaScript features

Object-Oriented

Case sensitive

JavaScript variables

JavaScript arrays

JavaScript operators

Blocks of code

Conditional blocks of code:

Repetitive (loop) blocks of code:

JavaScript functions

The APEX angle

Where to store our external JavaScript file?

How to load our external JavaScript file?

Globalization and NLS

PL/SQL and JavaScript (advanced)

JavaScript and server-side resources (advanced)

The APEX JavaScript API

$x(pItemId)

$v(pItemId)

$s(pItemId, pValue)

$x_UpTill(pItemId, tagName, className)

$x_Show(pItemId) , $x_Hide(pItemId)

redirect (location)

Summary

3. APEX Basic Concepts

Session state

Session state firing points

Session state and the application page life cycle

Session state and the page rendering phase

Using computations in the page rendering phase

Using processes in the page rendering phase

Special session state processes

Session state and the page processing phase

Computations and processes in the page processing phase

Manually saving to session state

Using the APEX_UTIL.SET_SESSION_STATE API procedure

Session state and AJAX

Using the f?p URL notation

Referencing persistence session state

Session state and stored procedures

Using the v() and nv() functions wisely

Session state scope and persistence

Reviewing session state from the application builder

Substitution strings

APEX engine built-in substitution strings

Substitution strings that contain data

Substitution strings that contain HTML code

Application substitution strings

Application or page items

Referencing substitution strings

Using the #ITEM# notation

Using the &ITEM. notation

Referencing substitution strings in SQL and PL/SQL code

The bind variable notation

The APEX built-in v() and nv() functions

Shortcuts

Defining a shortcut

The name field

The type field

The shortcut field

Shortcuts availability

Themes and templates

Themes

Templates

Page templates

Region templates

Reports templates

Breadcrumbs templates

List templates

Calendar templates

Label templates

Button templates

Popup List Of Values template

Templates and the APEX upgrade process

Theme, templates, and good practice

The f?p URL notation

The p segments

1—App

2—Page

3—Session

4—Request

5—Debug

6—CC (Clear Cache)

7—itemName

8 itemValue

9—PR (Printer friendly)

Item values including the colon character

APEX multi-lingual support

The APEX built-in translation mechanism

Single language applications

Single application with Multi-lingual user interface

National Language Support (NLS) in this book

Summary

4. The Application Builder Basic Concepts and Building Blocks

The APEX Workspace

Start working with APEX

Logging in to the APEX IDE

The APEX IDE home page

The APEX Application Builder

The Application Builder home page

The Import button

The Create button

The Application IDE home page

The Pull Down menu section

The application pages section

The detailed list of application pages

The Download link

The Page component

The Page Rendering column

The Hide/Show icon bar

The Page Processing column

The Hide/Show icon bar

The Shared Components column

The Hide/Show icon bar

The Action Bar

Creating an application page

Editing the Page Definition page

The Page section

A page region

Creating a page region

Editing a page region

Region identification

Caching

Summary

5. APEX Items

Naming conventions and rules

Oracle Identifiers naming rules

Case sensitive APEX item names

Application items

Application items and security issues

Referencing application items

Application items and JavaScript

Page items

The Name section

The Name field

The Display As field

The Displayed section

The Sequence field

The Region field

The layout fields

The Drag and drop wizard

The Label section

The Label field

The Horizontal / Vertical Alignment field

The Template field

The HTML Table Cell Attributes field

The Element section

The item dimension fields

The HTML Form Element Attributes field

The Form Element Option Attributes field

The Pre/Post Element Text fields

The Source section

The Source Used field

Only when current value in session state is null

Always, replacing any existing value in session state

The Source Type field

Maintain session state

Source value or expression

Post Calculation Computation

Format Mask

The Default section

The List of Values section

Static LOV

Dynamic LOV

Defines LOV

LOV related wizards

The Security section

The Conditions section

The Condition Type field

The Expression 1 / Expression 2 fields

The Read Only section

The Help text section

The Configuration section

The Comments section

APEX pre-defined item types

Text based items

Multi-option based items

Special functionality items

Creating APEX items programmatically

Summary

6. APEX Buttons

Button styles

HTML Button

Image based button

Template Driven button

Button positioning

Create new button(s)

Button position

Create a button in a region position

Button attributes

Image attributes or template (not for HTML)

Display properties

Position

Sequence

Branching (optional)

Conditional display

Create a button displayed among this region's items

Edit a button

The REQUEST feature

The REQUEST scope

Referencing REQUEST

Condition by REQUEST

Using JavaScript with "among this region's items" buttons

Re-visit the Buttons section

Summary

7. APEX Computations

Create a page level computation

Compute item

Sequence

Computation point

Page Rendering computation points

Page Processing computation point

Computation type

Computation

Condition

Create an application level computation

Computation item

Frequency

Edit computations

Computations and Session State

Summary

8. APEX Validations

Data validation

How APEX validation works?

Creating a new validation

Validation level

Create multiple not null validations

Item

Validation method

SQL

Exists

Not Exists

SQL Expression

PL/SQL

PL/SQL Expression

PL/SQL Error

Function Returning Boolean

Function Returning Error Text

Multi-lingual support for error messages

Item Not Null

Item string comparison

Item specified is a valid date

Regular Expression

Sequence and Name

Sequence and Validation Name

Error Display Location

On Error Page

Inline

Validation

Conditions

Page level validation

Edit validations

Client-side validation

Implementing client-side validation

Client-side versus server-side validations

Summary

9. APEX Processes

Application Processes

Creating a new Application Process

Name

Sequence

Point

Source

Error message

Conditionality

Edit Application Processes

Localization and Application Processes

Page level processes

Creating a new page level process

Process Type

PL/SQL

Reset Pagination

On Demand

Session State

Data manipulation

Automatic Row Fetch

Automatic Row Processing (DML)

Multi Row Update, Multi Row Delete, and Add rows to tabular form

Web Services

Form Pagination

Close pop-up window

Edit page level process

Source: Automatic Row Processing (DML)

On Demand processes

Summary

10. APEX Branches

Application flow

Creating a new branch

Branch point

Branch type

Branch to Function Returning a Page

Branch to Function Returning a URL

Branch to PL/SQL procedure

Branch to page

Branch to page accept processing (not common)

Branch to Page Identified by Item (Use item name)

Branch to page or URL

Save state before branching

Branches URL and JavaScript

Branch to URL identified by item (Use item name)

Sequence and branch conditioning

Edit branch

Other ways to control the application flow

Branch with Buttons

Branch with PL/SQL

Branch with JavaScript

Branch with an item

Navigational aids

Summary

11. APEX SQL Workshop

Navigating around the SQL workshop

Object Browser

Exploring the database with the Object Browser

Creating new database objects

UI defaults

Creating a package

SQL Commands

The SQL Commands code area

The SQL Commands results area (Display Pane)

SQL Scripts

Query Builder

The Query Builder working area (Design Pane)

The Query Builder results area (Output Pane)

Summary

12. APEX Forms

Sources for creating a form

Creating a new form

The form primary key

The form items

The form DML options

The form branches

A new form has been created

The Page Rendering phase

The items section

The Page Rendering Processes section

The Page Processing phase

The Validations section

The Page Processing Processes section

The Branches section

Manually creating an APEX form

Summary

13. APEX Reports

Report regions

Creating a simple report using a wizard

Start the report region wizard

Region type

Report implementation

Display attributes

SQL source

Query Builder

Report attributes

Conditional display

Wizard completion

Modifying a report manually

Editing a Region Definition

The identification section

The user interface section

The source section

The caching section

The header and footer section

Editing Report Attributes

The column attributes section

The layout and pagination section

The messages section

The report export section

The break formatting section

The external processing section

Editing Column attributes

The column definition section

The column formatting section

The tabular form element section

The column link section

Conditional display

Editing print attributes

The printing section

The page attributes section

The page header section

The column heading section

The report columns section

The page footer section

Enabling PDF printing

Building a custom report row layout template

Building a parameterized report

Charts

Summary

14. Tabular Forms

What is a Tabular Form?

Using a wizard to create a Tabular Form

Start the Report Region Wizard

Region type

Form implementation

Identify Table/View Owner

Identify Table/View Name

Identify columns to display

Identify Primary Key

Primary key source

Updateable columns

Page and region attributes

Button labels

Branching

Confirmation

Wizard completion

Show me the Tabular Form

What does the Tabular Form wizard add to my page?

Advanced note on the Update process

How does the Tabular Form wizard organize the data on a page?

The client side

The server side

Report regions revisited

Validating the Tabular Form

Enhancing Validation Errors Messages

Changing the default error message phrase

Add row number column

Using Page Error for the Validation error message(s)

Using JavaScript and AJAX

Manually building a Tabular Form

Displaying the Tabular Form

APEX_ITEM API

Rendering the Tabular Form

Referencing and validating Tabular Form items

Using checkboxes

DML on the Tabular Form

Delete

Update

Insert

Summary

15. Calendars

Calendar region

Using a wizard to create a calendar

Start the calendar region wizard

Region type

Calendar implementation

Region attributes

Calendar source

Define columns

Viewing the calendar

Modifying a calendar manually

Region Definition

Region Source

Calendar Attributes

The Calendar Display section

The Display Attributes section

The Column Link section

The Day Link section

Summary

16. Interactive Reports

Interactive Report Regions

Select Columns

Filter

Sort

Control break

Highlight

Compute

Aggregate

Chart

Flashback

Save report

Reset

Download

Converting an Existing Report Region

Create an Interactive Report using a wizard

Start the Interactive Report Region Wizard

Region Type

Report Implementation

Display attributes

Source

Conditional Display

Wizard completion

Manually modifying an Interactive Report

Report Attributes

Column Attributes

Column Groups

Default Report Settings

Pagination

Search Bar

Download

Link Column

Advanced Attributes

Column attributes

Column Definition

List of Values

Column Link

Linking to Interactive Reports

Summary

17. AJAX with APEX

The AJAX technology

The XMLHttpRequest object

Communication

Data format

AJAX implementation in APEX

AJAX support on the client side

The htmldb_Get object

1—obj

2—flow

3—req

4—page

5—instance

6—proc

7—queryString

Code examples

The htmldb_Get methods

.Add(name,val)

.AddParam(name,val)

General remarks

.get(mode, startTag, endTag)

The mode parameter

The startTag and endTag parameters

Code examples

Restrictions with AJAX pulling content

Pulling report with pagination

.GetAsync(pVar)

The pVar function

Namespace for the APEX AJAX framework

AJAX support on the server side

Application on-demand PL/SQL process

Stored (packaged) procedure

Handling errors in the AJAX process

Debugging a failed AJAX process

Examples of using AJAX in APEX

Multiple calendars on a single application page

The calendar main page (page xx)

The 'Previous/Next Month' region

The 'Main Calendar' region

The small calendars page (page yy)

Checkbox persistence in Tabular Forms (Reports)

Report on CUSTOMERS

The report SQL query

The report headers

The AJAX client-side JavaScript functions

The updateCB() JavaScript function

The toggleAllCB() JavaScript function

The header_CB_Status() JavaScript function

The AJAX server-side processes

update_CB

updateCB_all

Summary

18. Globalization and Localization With APEX Applications

A brief introduction to Globalization and Localization

Globalization

Localization

Native IDE support of multiple languages

Loading another language into APEX IDE

Unloading the APEX IDE translated language

Loading another language into APEX Runtime Environment

Multi-language support

How do we start Globalization support?

Setting the APEX application language

Date format

Using the APEX Date Picker

Adding or editing Globalization

Load Data—Localization

Translating Text Messages

The Text Messages translation wizard

Manage the Text Messages repository

Using APEX Shortcut with Text Messages

Dynamic Translations

Using the Dynamic Translations repository

Using the APEX_LANG APIs

APEX_LANG.MESSAGE

APEX_LANG.LANG

Multi-lingual applications

The translation mechanism home page

1—Application Language Mapping

2—Seed and export

3—Translating the text

4—Import and publish the translation

Editing the Translatable Text repository

Running translated applications

Deploying translated applications

Summary

19. Right-To-Left Support in APEX

Why we need special Right-To-Left support

Basic Right-To-Left scenarios

Right-To-Left as a single language

Right-To-Left as a translation language

APEX templates with Right-To-Left support

Page templates

The Header section

Right-To-Left as a single language

Right-To-Left as a translation language

The Body and Footer sections

Tab attributes sections

Button templates

Popup List of Values Template

All the other template types

The APEX Shuttle item

Labels and input fields alignment

Date and time

Summary

20. Deploying APEX Applications

What do we need to deploy?

Supporting Objects

Messages

Prerequisites

Substitutions

Build Options

Validations

Create/Update a Validation

Install

Create Script

Script Attributes

Define Script

Create Scripts to Install Files

Upgrade

Deinstallation

Export

Exporting an Application

Exporting a page

Exporting components

Components

Components by Page

Application Attributes

Build Option Status

Export Components

Importing an application

Start the Import Wizard

Specify File

File Import Confirmation

Install

Supporting Objects installation

Command line export utility

Summary

21. The APEX Runtime Environment

What is the APEX Runtime Environment?

Setting up a Runtime Environment

Managing the APEX Runtime Environment

Installing applications

APEX_INSTANCE_ADMIN API

Viewing APEX reports

Oracle SQL Developer

Summary

22. Security

APEX security features overview

Authentication Schemes

Adding an Authentication Scheme

Creation method

Selecting a pre-built Authentication Scheme

Adding a login page

Specifying LDAP settings

Authentication Scheme name

Changing the current Authentication Scheme

Custom Authentication Scheme

Authentication reports

Authorization Schemes

Create an Authorization Scheme

Creation method

Authorization Scheme details

Assigning Authorization Scheme to pages, regions, and items

Resetting Authorization Scheme results

Authorization Reports

Session state protection

Security Attributes

Authentication

Authorization

Database Schema

Session Timeout

Session State Protection

Virtual Private Database

Summary

23. Application Conversion

APEX application conversion

Planning and understanding the application

APEX Application Migrations

Creating an APEX workspace

Converting Microsoft Access applications

Exporting Microsoft Access metadata

Migrating the Access database to Oracle

Creating a repository

Capturing Microsoft Access exported XML

Converting to an Oracle Model

Generating schema

Creating an Oracle database

Creating a Migration Project

The Migration Project page

Generating the application

Select Application Objects

Page Definition

User Interface

Confirmation

Converting Oracle Forms applications

Converting Oracle Forms to XML

Database

APEX Migration Project

Reviewing and editing application metadata

Annotations

Including objects in conversion

Generating the application

Application name

Create Pages

Page Definition

User Interface

Confirmation

What's left to do?

Summary

24. APEX Best Practices

Don't re-invent the wheel

Be innovative when necessary

Learn the APEX IDE

The Drag and Drop Layout wizard

The Action Bar

The Page Definition view options

Setting up workspaces

Application ID

Page numbering and page groups

Use Oracle naming conventions

Use bind variables

Don't hardcode, use Substitution Strings

Use PL/SQL packages

Protect your business logic secrets

Re-useable code

Shared Components

Page Zero

Pre-defined and built-in resources

User Interface Defaults

Never change the provided APEX resources

Themes and Templates

JavaScript and CSS

Never trust the end user

Client-side validation

Don't rely on database constraints

Create your own restore point

Multi-user environment

Multi-lingual environment

Team development

Lock your pages

Version control

Document development standards

Thou shall enter comments

APEX views (APEX dictionary)

APEX Application Reports

APEX Runtime-only version

Backup, backup, and more backup

Summary

A. APEX Installation, Upgrade, and Configuration Tips

Obtaining APEX

Reading the relevant documentation

Back up your database and APEX applications

Use AL32UTF8 in your DAD

PL/SQL Web Toolkit

EPG—Embbeded PL/SQL Gateway

Enabling FTP on EPG

Enabling remote access to EPG

EPG versus OHS

Utilizing static files caching

Obfuscate your DAD file

Upgrading an APEX Instance

Copy the new images directory

Themes and Templates in the Upgrade process

Summary

Index