CERN: Building Real-World Solutions with Oracle Application Express

1
CERN Building Real-World Solutions with Oracle
Application Express
Joel Kallman (Oracle) Giovanni Chierico (CERN)
Oracle Open World San Francisco 24 October 06
CERN Globe of Science and Innovation in Geneva
2
Overview

• CERN
• Oracle Application Express
• Why we chose it
• IT environment
• Completed projects
• CERN Training Application
• Safety Information Registration
• Evaluation
• The good
• The bad
• Is Oracle APEX good for my project?
• Final thoughts
• Questions

3
CERN

• Centre Européen pour la Recherche Nucléaire
• The world's largest particle physics laboratory
• Founded in 1954, 20 European Member States but
  worldwide scientific collaborations
• CERN's business is pure science to provide
  scientists with tools to study the building
  blocks of matter and the forces that hold them
  together. These tools are Accelerators and
  Detectors
• Plays a vital role in developing the technologies
  of tomorrow
• IT Birthplace of the Web Worlds largest
  scientific Grid

4
CERN the LHC accelerator
5
Administrative Information Services

• 40 people group responsible for all
  administrative applications and corporate data at
  CERN covering
• Finances 1 BCHF standard budget
• Material biggest scientific machine built on
  Earth
• Human Resources 180000 people database
• Experiment Support 8000 scientists from 75
  countries

6
Oracle Application Express

• Is a rapid web application development tool for
  the Oracle database.

Oracle APEX
PL/SQL Engine
Oracle APEX Metadata
Application Data
7
Why we chose it

• Rapid development cycle
• Interface Creation
• Business Logic (PL/SQL based)
• Deployment
• Good looking results
• Easy integration with Oracle environment
• In-house Oracle expertise
• Bundled for free with RDBMS

8
IT Environment
Single Sign-On
View Controller (JEE)
Oracle RDBMS
Oracle APEX
Foundation
9
Applications

• Around 10 applications developed and put in
  production in 1 year.
• Presentation test cases
• CERN Training Application
• Safety Information Registration

10
CERN Training Application

• Goal Manage CERN Training courses
• Create courses and sessions
• Manage registrations
• Manage resources (rooms,)
• Interface with financial workflow apps
• Use CERN single sign-on
• Size (users/population)
• 5500 registrations / year
• 250 courses
• Replace old Designer application
• Reuse existing db
• Data
• Interface with related applications
• Custom components
• Web Course Catalog

Registration Workflow
Oracle APEX Request Catalog Management
Send Email Notifications
Charge courses
11
Various user profiles and access rights
Integration with CERN Single Sign-on
Linked with CERNs Electronic Document Handling
system
12
Context Tooltips
CERN official holidays
Custom Calendar (HTP.P)
13
CTA Project Results

• Investment 6 man months
• Painless migration
• Reused existing data repository
• Links from and to related apps
• 1 year in production with no problems
• 6000 course registrations
• 3000 distinct trainees
• 100K transactions charged
• Much improved usability and efficiency

14
Safety Information Registration

• Goal provide self-service safety training
  courses and tests on the web
• 2 different authentications
• CERN Single Sign-on (worldwide accessible)
• CERN Card scanner (local self-service kiosk)
• Custom interface (to work with touch screens)
• Integrated with IT various data sources
• Insert data in Oracle HR, using PL/SQL APIs
• Provide admin/reporting tools
• Easy to extend with new content

ApEx
Validtest
Single Sign On
CERN AccessControl System
Card Reader
Course Data
15
Customized Template for touch screen interface
Customized input elements
Multi-lingual interface
16
Integration with picture database
Javascript driven buttons
Vertical reporting
17
Standard SVG graph widgets
18
SIR Project Results

• Investment 4 man months
• One year in production
• 2 different courses offered (4 more coming)
• 11000 courses registered
• 15000 tests passed
• Very few performance and stability issues

19
Oracle APEX Evaluation

• Did it meet our expectations?
• What have we learned from its use?
• How does it compare to other frameworks?

20
The good

• Quickly create web apps
• Professional looking results
• Good existing UI widgets
• Easy to integrate with Oracle infrastructure
• Painless deployment
• Very intuitive development interface
• Completely web based
• Full power of PL/SQL!
• Fast performance

21
The bad

• Difficult to reuse components
• Dev time doesnt scale with app complexity
• Impossible to modify/extend existing widgets
• Proprietary solution
• Tricky to debug
• Limited version control

22
JEE Comparison
Effort

• Very small entry cost
• Very convenient for simple applications
• Doesnt scale too well with app complexity

JEE
Oracle APEX
?
Results
Oracle APEX convenience zone
23
Is Oracle APEX good for my project?
Horizontal
Vertical
Business Logic
Multi-level Business Logic
Oracle APEX excels with

• Horizontal applications
• Standard Interfaces
• Simple Validation Business Logic
• Oracle experienced teams

24
Final Thoughts

• Take full advantage of DBMS PL/SQL!
• Materialized views, DB Links, DBMS_,
• Put Business logic into DB
• Easier code reuse
• Better exception handling
• Powerful transaction management
• Easier debugging
• Be generic
• Use placeholders for generic regions
• Use conditionals Regions, Reports, Items,
  Processes
• Read the docs!

25
Questions?

• ?