Oracle Application Express 3
The Essentials and More
Develop Native Oracle database-centric web
applications quickly and easily with Oracle APEX
Arie Geller Matthew Lyon
J j enterpririse
PUBLISHING
BIRMINGHAM-MUMBAI
Table of Contents
Preface 1
Chapter 1: An Introduction toAPEX 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: Whatwe 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
Thestylesheets 22
CSSproperties 24
CSS selectors 24
The (important keyword 27
The APEXangle 28
Whereto store ourexternal CSS file? 28
Howto load our CSS code? 29
Globalization and NLS 29
JavaScript 30
JavaScript features 30
Object-Oriented 30
Case sensitive 31
JavaScriptvariables 31
JavaScriptarrays 32
JavaScriptoperators 32
Blocks of code 34
JavaScriptfunctions 35
The APEX angle 35
Where to storeourexternalJavaScriptfile? 36
How to load ourexternal JavaScriptfile? 36
Globalization andNLS 37
PL/SQLandJavaScript(advanced) 38
JavaScriptand server-side resources (advanced) 38
The APEXJavaScriptAPI 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
UsingtheAPEX_UTILSET_SESSION_STATEAPI procedure 48
Session state and AJAX 49
Usingthe f?p URL notation 50
Referencing persistence session state 50
Session state and storedprocedures 51
Session state scope and persistence 53
Reviewing session state from the application builder 54
Substitution strings 55
APEX enginebuilt-in substitution strings 55
Substitution stringsthat contain data 56
Substitutionstringsthat contain HTML code 58
Application substitution strings 58
Applicationor page items 59
Referencing substitution strings 59
Usingthe #ITEM# notation 59
Usingthe&ITEM. notation 60
Referencingsubstitution stringsin SQLand PL/SQLcode 61
Shortcuts 62
Defining a shortcut 63
Thenamefield 64
Thetypefield 64
The shortcut field 64
Shortcuts availability 65
Themes andtemplates 66
Themes 66
Templates 68
Page templates 68
Region templates 69
Reportstemplates 70
Breadcrumbstemplates 70
Listtemplates 70
Calendartemplates 70
Labeltemplates 70
Button templates 70
PopupList Of Valuestemplate 70
Templates and the APEX upgradeprocess 71
Theme, templates, and good practice 71
Thef?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(ClearCache) 74
7— itemName 75
8— itemValue 75
9— PR (Printer friendly) 76
Itemvalues includingthe colon character 76
APEX multi-lingual support 77
The APEX built-in translation mechanism 77
Single language applications 77
Single applicationwith Multi-lingualuserinterface 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
Startworking withAPEX 82
Logging in to the APEX IDE 83
TheAPEX IDE home page 84
The APEX Application Builder 85
The Application Builder home page 85
TheImportbutton 86
The Create button 87
TheApplication IDE home page 88
The Pul! Downmenusection 89
Theapplication pagessection 90
The Page component 92
ThePage Rendering column 94
The Hide/Show icon bar 94
ThePage Processing column 95
The Hide/Show icon bar 95
The Shared Components column 95
The Hide/Show icon bar 96
The Action Bar 96
Creating an application page 97
Editing the Page Definitionpage 99
ThePage section 99
A page region 102
Creatingapageregion 102
Editing a pageregion 104
Summary 107
Chapter 5: APEX Items 109
Naming conventions and rules 109
Oracle Identifiers naming rules 109
Case sensitive APEX item names 110
Application items 110
Application items and security issues 111
Referencing application items 112
Applicationitems and JavaScript 112
Page items 112
The Namesection 113
The Name field 113
The DisplayAs field 113
The Displayed section 114
The Sequence field 114
The Region field 114
The layoutfields 114
The Label section 118
The Labelfield 118
The Horizontal / VerticalAlignmentfield 118
The Templatefield 119
The HTML Table Cell Attributes field 119
of
The Element section 120
The item dimension fields 120
The HTML Form Element Attributes field 121
The Form ElementOptionAttributes field 121
The Pre/Post Element Textfields 121
The Source section 122
TheSource Used field 122
TheSourceTypefield 123
Maintain session state 124
Source valueorexpression 125
Post CalculationComputation 125
Format Mask 125
The Default section 126
The List of Values section 127
Static LOV 127
DynamicLOV 129
Defines LOV 130
The Security section 131
The Conditions section 132
TheConditionTypefield 132
TheExpression 1 /Expression2 fields 133
The Read Only section 134
The Helptext section 135
The Configuration section 135
The Comments section 135
APEX pre-defined item types 135
Text based items 135
Multi-option based items 138
Specialfunctionality items 144
Creating APEX items programmatically 147
Summary 147
Chapter 6: APEX Buttons 149
Button styles 149
HTML Button 150
Imagebased button 150
Template Driven button 151
Button positioning 152
Create new button(s) 153
Button position 154
Create a button in a region position 155
Button attributes 155
Imageattributesortemplate(notforHTML) 157
Display properties 157
Branching(optional) 158
Conditionaldisplay 158
Create a button displayed among thisregion's items 159
Edita button 160
TheREQUESTfeature 160
The REQUEST scope 160
Referencing REQUEST 161
Condition byREQUEST 162
Using JavaScriptwith "among this region's items" buttons 162
Re-visit the Buttonssection 164
Summary 165
Chapter 7: APEX Computations 167
Create a page levelcomputation 167
Compute item 168
Sequence 168
Computation point 168
PageRendering computation points 168
Page Processing computation point 169
Computation type 169
Computation 171
Condition 171
Create an application levelcomputation 172
Computation item 172
Frequency 172
Editcomputations 173
Computations and Session State 173
Summary 174
Chapter 8: APEX Validations 175
Data validation 175
HowAPEX validation works? 176
Creating a new validation 177
Validation level 178
Createmultiple notnull validations 178
Item 179
Validation method 179
SQL 180
PL/SQL 181
Item Not Null 184
Item string comparison 184
Regular Expression 185
Sequence and Name 186
Sequence andValidation Name 186
ErrorDisplayLocation 186
Validation 188
Conditions 189
Page levelvalidation 190
Editvalidations 190
Client-side validation 191
Implementing client-side validation 191
Client-sideversus server-side validations 192
Summary 192
Chapter 9:APEX Processes 193
Application Processes 194
Creating a newApplication Process 194
Name 195
Sequence 195
Point 195
Source 196
Error message 197
Conditionality 198
EditApplication Processes 198
Localization andApplication Processes 199
Page level processes 200
Creating a new page level process 200
ProcessType 201
PL/SQL 201
ResetPagination 202
OnDemand 204
Session State 204
Datamanipulation 205
Web Services 209
FormPagination 210
Close pop-up window 210
Edit page level process 211
Source:AutomaticRowProcessing(DML) 211
On Demand processes 213
Summary 213
Chapter 10: APEX Branches 215
Applicationflow 216
Creating a new branch 216
Branchpoint 217
- Ivii]
Branch type 218
BranchtoFunction Returning a Page 218
Branch to Function Returninga URL 219
Branch to PL/SQLprocedure 219
Branch to page 220
Branch to pageacceptprocessing(not common) 221
Branchto Page IdentifiedbyItem(Useitem name) 221
Branch topageorURL 222
Branchto URLidentified byitem (Useitem name) 224
Sequence and branch conditioning 224
Edit branch 225
Otherways to control the application flow 226
Branch with Buttons 226
Branch with PL/SQL 226
Branch with JavaScript 226
Branch withan item 227
Navigational aids 227
Summary 227
Chapter 11: APEX SQL Workshop 229
Navigating around the SQL workshop 230
ObjectBrowser 232
Exploring the database with the Object Browser 232
Creating newdatabase objects 234
Ul defaults 238
Creating a package 239
SQL Commands 239
TheSQL Commands code area 240
TheSQL Commands results area (Display Pane) 242
SQL Scripts 244
Query Builder 245
The Query Builderworking area (Design Pane) 246
The Query Builder results area (Output Pane) 248
Summary 249
Chapter 12: APEX Forms 251
Sources forcreating a form 252
Creating a newform 254
Theform primary key 255
Theform items 257
Theform DML options 258
The form branches 259
A newform has been created 259
ThePage Rendering phase 260
The items section 261
ThePage Rendering Processes section 263
The Page Processing phase 263
The Validations section 264
The Page Processing Processessection 264
The Branches section 266
Manually creatingan APEX form 266
Summary 267
Chapter 13: APEX Reports 269
Reportregions 269
Creatinga simple report using awizard 270
Start the report region wizard 271
Region type 271
Report implementation 272
Displayattributes 272
SQL source 273
Query Builder 274
Report attributes 274
Conditional display 276
Wizardcompletion 276
Modifying a report manually 277
Editing a Region Definition 278
The identification section 278
Theuserinterfacesection 278
Thesourcesection 279
Thecaching section 280
Theheader and footersection 280
Editing ReportAttributes 281
Thecolumn attributes section 281
Thelayoutandpagination section 283
Themessages section 284
The report exportsection 284
The breakformattingsection 285
Theexternalprocessing section 286
Editing Columnattributes 287
Thecolumndefinition section 287
The column formatting section 287
The tabular form element section 289
The column link section 290
Conditionaldisplay 290
Editing print attributes 290
The printing section 291
The page attributessection 292
The page header section 292
The column headingsection 292
Thereportcolumnssection 292
The pagefooter section 293
EnablingPDF printing 293
Building a custom report row layouttemplate 294
Building a parameterized report 295
Charts 296
Summary 297
Chapter 14: Tabular Forms 299
What is aTabular Form? 299
Using awizardto create a Tabular Form 300
Start the Report Region Wizard 300
Region type 300
Form implementation 301
IdentifyTable/View Owner 301
IdentifyTable/View Name 302
Identifycolumns to display 302
Identify Primary Key 304
Primary keysource 304
Updateable columns 306
Page and region attributes 306
Button labels 307
Branching 308
Confirmation 308
Wizard completion 309
Show me theTabular Form 310
What doesthe Tabular Formwizard add tomypage? 310
Advanced note onthe Update process 312
How does the TabularForm wizard organizethe data on a page? 313
The client side 314
Theserver side 316
Report regions revisited 317
Validating the TabularForm 319
Enhancing ValidationErrors Messages 321
Manually buildingaTabular Form 331
Displaying the TabularForm 332
APEXJTEMAPI 333
Rendering theTabular Form 335
Referencing andvalidating Tabular Form items
Using checkboxes 338
DML onthe Tabular Form 341
Delete 341
Update 342
Insert 345
Summary 347
Chapter 15: Calendars 349
Calendar region 349
Using a wizard to create a calendar 350
Start the calendarregion wizard 350
Region type 350
Calendarimplementation 351
Region attributes 352
Calendar source 353
Define columns 354
Viewingthe calendar 354
Modifying a calendar manually 355
Region Definition 356
RegionSource 356
Calendar Attributes 357
The CalendarDisplaysection 357
The DisplayAttributessection 358
TheColumn Linksection 359
TheDayLink section 359
Summary 360
Chapter 16: Interactive Reports 361
Interactive Report Regions 361
Select Columns 363
Filter 363
Sort 365
Control break 365
Highlight 366
Compute 367
Aggregate 368
Chart 368
Flashback 369
Save report 370
Reset 370
Download 370
Converting an Existing ReportRegion 371
Create an Interactive Report using awizard 371
Start the Interactive Report RegionWizard 372
Region Type 372
Report Implementation 372
Display attributes 373
Source 374
Conditional Display 374
Wizard completion 375
Manually modifying an Interactive Report 375
ReportAttributes 376
Column Attributes 376
ColumnGroups 377
DefaultReport Settings 377
Pagination 378
Search Bar 378
Download 379
Link Column 380
Advanced Attributes 381
Column attributes 381
Linking to Interactive Reports 383
Summary 384
Chapter 17: AJAX with APEX 385
The AJAXtechnology 386
The XMLHttpRequest object 386
Communication 386
Data format 387
AJAX implementation in APEX 387
AJAX support on the client side 388
Thehtmldb_Get object 388
1—obj 388
2—flow 389
3—req 390
4— page 390
5— instance 390
6— proc 391
7—queryString 392
Codeexamples 392
The htmldb_Getmethods 394
.Add(name.val) 394
.AddParam(name,val) 395
General remarks 396
.get(mode, startTag, endTag) 397
.GetAsync(pVar) 402
of
Namespacefor the APEXAJAX framework 404
AJAX support onthe server side 405
Applicationon-demand PL/SQL process 405
Stored(packaged)procedure 406
Handling errors in the AJAX process 407
Debuggingafailed AJAX process 407
Examples of using AJAX in APEX 408
Multiple calendars on a single application page 408
Thecalendarmain page(page xx) 409
The small calendars page (page yy) 411
Checkbox persistence in Tabular Forms (Reports) 413
ReportonCUSTOMERS 414
The AJAXclient-sideJavaScriptfunctions 416
The AJAX server-side processes 421
Summary 423
Chapter 18: Globalization and Localization With
APEX Applications 425
A brief introductionto Globalization and Localization 426
Globalization 426
Localization 427
Native IDE support ofmultiple languages 427
Loading another language into APEX IDE 428
Unloading the APEX IDE translated language 429 Loading another language into APEXRuntime Environment 430
Multi-languagesupport 430
How dowe start Globalization support? 431
Settingthe APEXapplication language 431
Date format 433
Adding orediting Globalization 434
Load Data—Localization 436
Translating TextMessages 437
The TextMessagestranslation wizard 438
Managethe Text Messages repository 440
UsingAPEX Shortcut with TextMessages 442
Dynamic Translations 444
UsingtheDynamicTranslationsrepository 445
UsingtheAPEX_LANG APIs 446
APEX_LANG.MESSAGE 446
APEX_LANG.LANG 449
Multi-lingual applications 451
Thetranslation mechanism home page 452
1—Application Language Mapping 452
2— Seed andexport 455
3-—Trans!ating the text 458
4—Importandpublish the translation 460
Editingthe TranslatableTextrepository 462
Running translated applications 463
Deployingtranslatedapplications 463
Summary 465
Chapter 19: Right-To-Left Support in APEX 467
Whywe need special Right-To-Left support 467
BasicRight-To-Left scenarios 469
Right-To-Left as a single language 470
Right-To-Left as a translation language 470
APEX templates with Right-To-Left support 471
Page templates 471
The Headersection 471
The Body and Footer sections 474
Tab attributes sections 475
Button templates 475
Popup List ofValues Template 476
All the othertemplate types 478
The APEX Shuttle item 478
Labels and input fields alignment 482
Date and time 483
Summary 483
Chapter 20: Deploying APEXApplications 485
What dowe need to deploy? 485
Supporting Objects 486
Messages 488
Prerequisites 490
Substitutions 492
Build Options 492
Validations 492
Create/UpdateaValidation 493
Install 494
CreateScript 494
ScriptAttributes 495
Define Script 496
Create Scriptsto Install Files 496
Upgrade 497
Deinstallation 498
Export 498
Exporting an Application 498
Exporting a page 500
Exporting components 500
Components 501
Components by Page 502
Application Attributes 503
Build Option Status 504
Export Components 504
Importingan application 505
Start the ImportWizard 505
Specify File 505
File ImportConfirmation 506
Install 506
Supporting Objects installation 507
Command lineexportutility 508
Summary 509
Chapter 21: The APEX Runtime Environment 511
What is the APEX Runtime Environment? 511
Setting up a Runtime Environment 512
Managing the APEX Runtime Environment 512
Installing applications 512
APEX_INSTANCE_ADMINAPI 513
Viewing APEXreports 514
Oracle SQL Developer 514
Summary 516
Chapter 22: Security 517
APEX security features overview 517
Authentication Schemes 518
Adding an Authentication Scheme 520
Creation method 521
Selectingapre-builtAuthentication Scheme 521
Addingaloginpage 522
Specifying LDAP settings 522
Authentication Scheme name 523
Changing the current Authentication Scheme 523
Custom Authentication Scheme 524
Authentication reports 526
Authorization Schemes 527
Create anAuthorization Scheme 527
Creation method 528
Authorization Scheme details 528
Assigning Authorization Scheme to pages, regions, and items 530
Resetting Authorization Scheme results 531
Authorization Reports 531
Session state protection 531
Security Attributes 534
Authentication 534
Authorization 535
Database Schema 535
Session Timeout 535
Session State Protection 536
Virtual Private Database 536
Summary 536
Chapter23: Application Conversion 537
APEXapplication conversion 537
Planning andunderstanding the application 538
APEX Application Migrations 538
Creating an APEXworkspace 539
Converting MicrosoftAccess applications 540
Exporting Microsoft Access metadata 540
Migrating the Access database to Oracle 542
Creatinga repository 543
CapturingMicrosoft Access exportedXML 543
Converting toanOracle Model 544
Generatingschema 545
CreatinganOracle database 546
Creating a Migration Project 546
The Migration Projectpage 547
Generating the application 549
Select Application Objects 549
UserInterface 551
Confirmation 552
Converting Oracle Forms applications 552
Converting Oracle Forms to XML 553
Database 553
APEX Migration Project 554
Reviewing and editing application metadata 555
Annotations 557
Including objectsin conversion 558
Generating the application 558
Applicationname 559
CreatePages 559
UserInterface 562
Confirmation 562
What's leftto do? 563
Summary 564
Chapter 24: APEX Best Practices 565
Don't re-invent the wheel 566
Be innovative when necessary 567
Learn the APEX IDE 567
The Dragand Drop Layout wizard 567
The Action Bar 568
The Page Definition viewoptions 568
Setting up workspaces 570
Application ID 571
Page numbering and page groups 572
Use Oracle naming conventions 573
Use bind variables 573
Don't hardcode, use Substitution Strings 574
Use PL/SQL packages 574
Protect your businesslogic secrets 574
Re-useable code 575
Shared Components 575
PageZero 575
Pre-defined and built-in resources 575
User Interface Defaults 576
Neverchange the providedAPEX resources 576
Themes and Templates 576
JavaScript and CSS 577
Nevertrust theend user 578
Client-side validation 578
Don'trelyon database constraints 578
Create yourown restorepoint 578
Multi-userenvironment 579
Multi-lingualenvironment 580
Team development 580
Lock your pages 580
Version control 581
Documentdevelopmentstandards 581
Thou shall enter comments 582
APEX views(APEX dictionary) 583
APEXApplication Reports 583
APEX Runtime-only version 585
Backup, backup, andmore backup 585
Summary 586
Appendix: APEX Installation, Upgrade, and Configuration Tips 587
Obtaining APEX 587
Reading the relevant documentation 587
Back up your database and APEXapplications 588
Use AL32UTF8 in your DAD 588
PL/SQL Web Toolkit 589
EPG - Embbeded PL/SQL Gateway 590
EnablingFTP onEPG 590
Enablingremote access to EPG 590
EPG versus OHS 591
Utilizing static files caching 591
Obfuscate your DAD file 592
Upgrading an APEX Instance 592
Copythe new images directory 594
Themes and Templates in the Upgrade process 594
Summary 597
Index 599
