Progressive Skill

1
Progressive Skill
2
Who Are We?
We are a full services Business Consulting and
Internet Technology Company. Our mission is to
improve business process and or generate net
income for our customers. We focus on database
driven solutions leveraging open source code
models so that our customers are not Hostage to
the technology.
3
Projects/Clients
4
Rapid Application Development

• Quick Development of Software Applications
• Visual Tools for Development
• Very Little Custom Coding/Programming
• Easier to Manage Code and Functionality
• Faster Testing and Debugging
• Simpler Collaboration among multiple skill sets
• Shorter Timelines Lower Budgets
• Greater Flexibility with changing/adding
  requirements

5
Introduction to Application Express(formerly
HTML DB)
6
Abstract

• You may have heard about Oracle Application
  Express or HTML DB.
• It's a powerful and easy way to quickly develop a
  database-centric HTML Web application. You can
  install APEX on your own machines or run in a
  hosted environment on Oracle's site. All
  development (construction of pages) is done
  through the browser interface. You can use the
  SQL Workshop and the Data Workshop to run SQL and
  add data to your environment. This easy-to-use
  tool will impress you in no time at all!

7
Agenda

• APEX Defined
• Uses who, what, when, where, why
• Building applications with APEX
• APEX Administration and Monitoring
• Tips and Techniques

8
APEX Defined
9
What is APEX?

• Oracles answer to Wizards
• Meta Data (lots of it!)
• Dynamic HTML (fast!)
• PL/SQL - No Java
• Does anything you can do in SQL or PL/SQL
• Nice Web-based team development environment

10
Its Origin

• Originated from Tom Kytes ASK TOM site
• Used mod_plsql to develop
• Became Project Marvel then HTML DB
• Now its APEX
• Also referred to as Einstein

11
Youre a good candidate for APEX if you fit the
following profile

• You are, or have developers who are, skilled in
  SQL and PL/SQL
• Your application is database-centric
• Your application can be developed using a hosted
  service
• You are comfortable with a declarative/4GL
  programming model
• You want to migrate an Access / Excel application
  to Oracle

12
Scalable?

• APEX.oracle
• 5,500 active workspaces
• Dell 6550 4x2.0 Ghz Xeon Processors
• 6 GB RAM
• Red Hat AS 2.1
• 5,500 schemas

• DB Size 75 GB
• SGA(System Global Area) Size 1.5GB
• Average Unique Page Views 25,000 per weekday
• Average Page View Time lt.5 seconds
• 1 DBA manages 15 other DBs

13
Major Components

• Administration
• SQL Workshop
• Application Builder

14
Components

• SQL Workshop
• Interact with your database as with SQLPlus, but
  visual
• Data dictionary and object browsing, query by
  example
• Administration
• Manage users
• Manage services
• Monitor activity

• Application Builder
• Centerpiece of APEX
• Loaded with wizards
• Reports, forms and charts
• Connect pages using branches
• 75 pre-defined widgets
• Basic HTML, pop-up lists, calendars, etc.
• Full data entry validation

15
More Features

• Session and State Management
• Each user has a session
• Valid username/password is required
• User Interface Control
• Look-and-feel is separate from application logic
• Extensive templates

• Flexible Authentication and Authorization Schemes
• Can use pre-built / built-in methods or custom

16
Installing APEX

• Apply for a free account at apex.oracle.com
• Set up other developers / users for testing
• Can optionally download and install locally
• Run the script on your 9iR2 or better database
  (also need Apache listener)
• Down / Upload your applications to your home
  system
• Continue development on your home system
• Oracle 10g Companion CD
• Oracle Express Edition
• Develop your application (read the Help messages
  they really do help!)
• Excellent support via OTN discussion forum

17
Architecture
Browser
Oracle Database APEX
Oracle HTTP Server
18
Architecture B
Browser
Firewall
Oracle Database APEX
Oracle HTTP Server
Instead of the Oracle HTTP Server, you can use
the HTTP Server supplied with Oracle Application
Server 9i or 10g
19
APEX Uses

• Who, What, When,
• Where, Why

20
What can you do with APEX?

• Select Journal Article review system (developed
  in 2 weeks)
• Web Collaboration application (developed in 2
  days)
• General purpose Customer Survey (developed in 1
  day)
• Survey Administration screens (developed in 2
  hours)

21
Select Journal Article review
22
Web Collaboration application
23
Customer Survey
24
Survey Administration
25
Testing Application
26
Creating an APEX Application

• Generally speaking

27
Log into Workspace

• Use the Workspace you chose, the username and
  password emailed to you

28
Home Menu

• Youre ready to begin
• Build Applications
• Work in SQL
• Work with Data
• Manage users

29
Migrate Data

• Import data from Spreadsheets, XML or Delimited
  Files

30
Load Northwinds?

• I loaded the Northwinds Database from SQL Server
• Used Periscope to load/copy data
• Once loaded, view data using SQL Workshop

31
Drill and Create

• Can
• Drill into data
• Count rows
• Insert/Edit rows
• Query by Example
• View components
• Much more

32
Use the Wizards

• APEXs many wizards will simplify your life
  use them!
• Reports, Charts, Forms, Calendars, Trees, etc.

33
Create a Hardware Inventory Application
34
Building an application from scratch in 5
simple steps

• Login with developer privileges
• Use the SQL tools to make your tables,
  constraints, triggers, procedures etc.
• Start a new application (use the wizard)
• Customize the application (check each change as
  you make it)
• Deploy to the users!

35
ExampleHardware Inventory App

• Record and view hardware inventory
• Searchable report
• Present analysis graphically as well as
  numerically
• Design for novice users
• Remote users with access to Internet

36
Main Menu
37
Import Data (SQL Workshop)
38
Converting Data

• Previously, data was stored in an Excel
  spreadsheet
• Conveniently, this can be made into an Oracle
  table by simply copying and pasting into the APEX
  create table wizard
• Select Region
• Copy from Excel

39
Import Data Wizard
40
Paste Your Data In
41
Set Column Preferences
42
Create a Primary Keyand sequence
43
Object Detail for Table Created (in Object
Browser)
44
Create Lookup Table

• Select column for look up
• Specify lookup table specifics

45
Create an Application
46
Create the application form scratch (this time)
47
Add Report and Form based on Hardware table

• Notice inclusion of Analysis pages

48
Specify Details for Analysis
49
Could add as many pages as you want
50
Select Levels of Tabs
51
Can Share Components for other applications
52
Specify Security and Language
53
Choose a Theme
54
Verify and Finish
55
Run your new Application!(you will need to log
in)
56
Instant Application!

• Searchable report
• Links to edit individual rows

57
Instant Application!

• Edit form with
• Calendar widget
• Picklist based off lookup table

58
Instant Application!

• Analysis Chart and Report

59
Good start but improvements can be made

• Makes codes more meaningful
• Format output
• Edit page through edit link

60
Three basic areas for each page
61
Elements of a Page

• Shared Components
• Tabs
• Navigation bar
• Templates
• Regions
• Items
• Labels
• Buttons
• Widgets

62
Most things are created through WIZARDS

• Step by step
• Collect essential info

63
EVERYTHINGhas attributes and almost everything
has an edit page
All of the basic attributes collected through the
wizards, and many more detailed attributes are
editable through an elements attribute/edit page.
All of this information is called Meta Data.
64
Attribute/Edit Pages

• Attributes in regions by logical groups
• Quick navigation to each region
• Many items are select lists
• Almost all have context sensitive help

65
Reuse of Shared Components
LOV used for both report and edit
66
Easy Data Validation

• Some validation created automatically based on
  database.
• Others easily created with wizard.

67
Validation Wizard

• Choose the item
• Specify the validation type

68
Validation Wizard

• Choose subtype
• Specify sequence and where error message is to be
  displayed

69
Validation Wizard

• Depending on method, provide validation details
• Specify error message
• Always look for help provided
• Examples
• Item lookups

70
MOST things can beCONDITIONAL!

• Many, many different types of conditions
  available.
• Item is displayed or processed depending on the
  outcome of the condition.

71
Validation

• Message displayed by item, central location or
  both
• Navigation not allowed until validations pass

72
Security

• Two levels
• Authentication, controls initial access to the
  application
• Authorization, can control access to tabs, pages,
  regions, items, buttons, read/write vs read only,
  ..
• Use APEX built in functionality, totally custom
  or somewhere in between.

73
Make a backup!
74
Save the export
75
Deployment Process
76
APEX Administration

• Administrator controls Workspaces
• Workspaces control developer access and provide
  resources.

77
APEX Administration

• Workspaces can be requested.
• Workspaces are provisioned
• Initial users are created, more can be created by
  the workspace administrator
• Workspaces/Applications can be monitored.

78
Workspace Administration

• Workspace administrator manages services
• Creates and alters workspace / application users
• Monitors Activity

79
Workspace Administration
Many reports and charts are available to
monitor workspace activity.
Many application reports are also available
(to developers also)
80
APEX Tips and Techniques
81
Sample Applications

• The Sample Applications have great examples
• Take some time to understand the applications
• Standard Sample Common Concepts

82
Spend Time onAnalysis/Design

• Time Well Spent
• Follows standard design principals
• Storyboard approach makes it a visual process
• Pay me now or pay me more later
• Poor design, results in a poor application
• Important to understand Web development to design

83
Storyboard Design

• Take the time to map our your tabs, menus, layout
  and design

84
Debugging

• To view the current value of any object (if
  youre a developer), you can click on the session
  button at any time

85
Help!

• The online help that is available is very well
  written as a reference
• The OTN Discussion Forum is very helpful!

http//forums.oracle.com/forums/forum.jspa?forumID
137
86
Creating a Popup

• You can specify a TARGETNEW for an href if you
  want the URL / Link to pop up a new page when
  clicked
• This is useful when referencing other sites

87
Adding Images

• To add images, static HTML / Docs and CSS files
  to the application, click on
• Shared Components
• Images
• CSS
• Static Files

88
Advanced Topics

• Debugging
• Using Collections
• Authentication Authorization
• Administration
• Deployment Process
• Available Functions APIs
• Monitoring Activity

89
Debugging

• Run any page in your application
• Turn on debug mode by clicking on Debug in the
  developer toolbar
• Timing is processor time, not rendering time
• Append p_traceYES to your URL to generate SQL
  Trace File

http//hostname.com/pls/htmldb/f?p1001p_traceY
ES
90
Using Collections

• Enable you to temporarily capture rows and
  columns of data
• Data is persistent throughout a users unique
  session
• Examples of when to use a collection
• A wizard with more than one page
• A shopping cart application

91
Authentication Authorization

• Establish User Identity Through Authentication
• Provide Security Through Authorization

92
Administration

• An HTML DB Administrators main task is to manage
  the instance of HTML DB
• Monitor Activity to ensure good performance
• Some common tasks include
• Approving Service Change Requests
• Managing Schema to Workspace Mappings
• Managing Schemas Tablespaces
• All Other Associated DBA functions

http//hostname.com/pls/htmldb/htmldb_admin
93
APEX Summary

• For additional information, check out
• http//apex.oracle.com
• Externally Hosted Instance
• OTN Discussion Forums
• HTML DB How To Documents

94
variableName.

• variableName is used when you need to substitute
  a variable as when you are building a link to
  another page
• You often need to place a period at the end of
  the name
• For example
• f?papp_id.2session

95
Summary

• Powerful
• Relatively Easy
• Fast
• Secure
• Really works
• Hosted

96
Questions and Answers
97
Where to Get More Information

• htmldb.oracle.com
• metalink.oracle.com
• technet.oracle.com
• otn.oracle.com
• www.tusc.com
• Oracle Press, Oracle HTML DB Handbook by
  Lawrence C Linnemeyer and Bradley D. Brown