Transcript Using Web Services

Consuming Web Services
In Oracle Application Express (ApEx)
1
Bradley D. Brown
http://bradleydbrown.blogspot.com

TUSC


University of Denver



Adjunct Professor
Graduate Class – New Venture Creation
Clients


Founder, Author, Chief Technology Officer
IntelliReal, OAB, EventConnex, Jepp, Sun, …
Groups



YPO, OOW, IOUG, ODTUG, LAOUG, RMOUG, etc.
Oracle Fusion Middleware Director/Ace
IOUC Fusion Council
3
Agenda

Refresher / Introduction


Creating Your Web Services




Manually
Using iPerspective
Consuming Your Web Services in ApEx





Manually
Using iPerspective
Registering Your Web Services in ApEx


N-tier scalable architecture
Form with a Report
Standalone Report
Standalone Graph
2 Dimensional Array
Other Technologies - AJAX, LOVs, REST/JSON/XML, MTOM
4
Refresher - N-tier Architecture

Used this approach
and architecture for
a customer of ours

Delivered a robust,
scalable ApEx
environment

30k users in “single
tier”

Limitless in this
architecture
5
The Results - Dashboard Page

Consuming Services



Graph
Tabular Report
Drill into points
6
More Services - Top Valued Properties

Can change the number of
most expensive properties

Can add a city to look into

Can drill into any property
7
Creating Your Web Services
 Manually
 Using iPerspective
 Next
Registering and Consuming Web Services
using ApEx
8
Manual Web Service Creation

SDO – Service Data
Object

 JDeveloper
 Eclipse
 CRUD for Tables/Views

Technical Service –
Package
 Limited Functionality
 Doesn’t work for
 Overloaded
 In-out
 Inline records
Choose an IDE
 NetBeans
 Etc.

Write Your Services
 Wizards
 By Hand

Good “Step-by-Step”
presentation on this
topic – entire
presentation alone
9
iPerspective Web Service Creation

SDOs based on Existing Tables and Views

Technical Data Services based on Packages

SDO Based on a custom SQL Statement

Flexible SDO


Dynamic SQL, returning 2 dimensional array
APIs and More…
10
SDOs based on Existing Tables and Views

Choose the Tables
and/or Views to create
SDOs for

Add them to a Release

Deploy the Release

You’re done
11
Technical Data Services based on Packages

Choose the Packages,
Procedures, Functions
to include

Add them to your
Release

Deploy the Release

You’re Done!
12
SDO Based on a custom SQL Statement

Main Menu

Create a Complex JDBC
Monocle
 Name Your Monocle
 Type (or paste) Your SQL
Statement
 Choose the Release(s) to
include the object into

Deploy the Release

You’re Done!
13
Flexible SDO

Add a New Flexible
Monocle
 Name the Monocle
 Enter a Comment
 Decide whether the
object should be tied to a
specific Database
Gateway or if this should
be passed as a parameter
 Choose the Release(s) to
include it in

You’re Done!
14
Other SDO Types

Email
 Structured
 Unstructured

Indexes
 Can index any column
from any remote object
 Dynamic or fixed
 Any server
 Any folder
 Any user

SDK
 Write your own code

Custom Developed
 If there is an API you
want us to integrate, just
ask us
15
Registering Your Web Services in ApEx
 Manually
 Using iPerspective
16
Manual Web Service Registration

At Application Level

Go to Shared
Components

Under Logic, choose
Web Service References

Create a New Reference

If you use a service
registry (UDDI), answer
yes, otherwise, no
17
Create the Web Service Reference

Paste in the location of
your WSDL

Confirm the operations
are what you expect

Click on “Create
Reference”

Service is Registered in
ApEx

Ready to Create a Form
or Report on Service
18
iPerspective to ApEx Service Registration

Automated registry of a
release’s services

Within a release, choose
“New Deployment
Server”

Add a New ApEx
Workspace Application

Enter the Workspace
Name and App ID
19
Wrap up the Automated Registration

Enter a developer
username/password

Now when you deploy
to the app server(s), the
services will be
deployed to ApEx’s Web
Service repository also
20
Consuming Your Web Services in ApEx
 Form with a Report
 Standalone Report, Graph
 2 Dimensional Array
21
Form with a Report

Create Form and
Report on Web
Service (after
registering) or…

Create a New
Region on a Page

Choose Form

Then Choose Form
and Report on a
Web Service
22
Choose the Service for the Form/Report

Choose the operation to
retrieve the data

Defaults are fine, but
change the names of
the regions, pages, etc.

The Service’s inputs will
automatically be placed
as input fields on the
form. I prefer to leave
them for now (change
to hidden later)
23
Results (from the Web Service)

Can rename the
collection if you would
like, click the radio
button

Choose the fields you
want to include in the
results that are
displayed (in report)

Want tabs?

Agree with Summary?
 Create Form and Report
24
Run the Page

See the inputs

No output yet…

Fill in the rows at a
minimum, click submit

See the results in a
report below the form

Now you can format the
results…, add sorting,
etc.
25
What Did the Wizard Write?

Inputs are text fields on
the page

Results are a report,
which is a SQL query

Process executes the
Web Service to return
the data into a
collection

Let’s look at the
details…
26
Web Service Request

On submit of the page

Need to change process
point if you want it to
execute before you click
on submit

Binds fields on page to
the parameters in the
service

You can see the service
it’s calling (that you
selected)
27
Creating a Report on a Web Service

SQL Query

Select statement using
extractValue syntax

Pulls from the collection
that the Web service
process generates

Copy the SQL if you
want to use it in a graph
28
Adding a Graph/Chart

Add a new region

Choose Chart

Chart type
 Flash, SVG, HTML

Wizard fills in a lot of
blanks for you, you can
change the title if you
wish
29
Design the Look and Feel

Play with the chart type,
animation, titles, axes,
etc and click on update
to get a rough idea of
what the graph will look
like on the page

Click Next when it’s
looking good

You can always change
it later
30
Bit of “fooler” logic now…

ApEx doesn’t like the
actual query syntax, so I
always put in
 Select 1, 2, 3 from dual

Click on “Create
Region”

Then Edit the Region
and click on the “Series
1” link
31
Now for the Right Statement

Change the Series Name

Syntax of query is
 Select link, label, value1,
value2 from table…

Note that I checked
“Save Query without
Validation”

Also make sure you have
enough “max rows” for
the data you want to
display - Apply
32
Run the Page – Success!

Hit the Service just once

Returned results into a
collection

Displayed in a table

Displayed in a graph

How cool is that!
33
Standalone Report (or Graph)

Add a new region/page

Report on a Web Service
Result (result is the key
word)
34
Going Through the Motions

Fill in the wizard values
for the page and region
names

Specify tabs or not…

Tell ApEx how to write
the SQL – by hand or
from a WSDL

Pick the Service that will
be used to retrieve the
data (how it writes the
SQL)
35
OK, am I done?

Fill in the collection

Pick a template

How many rows to
show at a time?

Which fields would you
like to see from the
results?

Confirm, Finish and Run
the page…
36
Surprise…

Where are the results?

There’s no Web Service
reference – it doesn’t
know where to pull that
data set from…

It wrote a nice SQL
statement, but the
collection isn’t
populated…so neither
is the report…
37
Let’s Get the Data

Add a new page
rendering process

We want to pull from a
Web Service

Name the service

On Load will work – i.e.
before the page is
displayed, we should call
this service
38
This Looks Familiar…

Now you can pick the
service and operation
that you want to call

I used static values here,
but you could pull from
items that you’re going
to add to the page

Make sure you place it
into a collection – it
better be the same
name
39
Try Again…

Create the process, run
the page…

Now we have data!

MUCH better!
40
SQL for 2 Dimensional Array

We have a service that
allows you to pass

ApEx’s wizard doesn’t
know how to handle it
 Database connection

You must edit the SQL it
will create and use the
position() statement

Going to Create a Form
and a Report for this
example…
 SQL Statement
 Number of Rows
 Include header as first?

Returns 2 dimensional
array with Rows[] which
contain columnValues[]
41
Build it like any other service…

Pick the Service and the
operation

Fill in the wizard values
for the form and report

Form includes the input
variables for the service
operation

Name your collection
and select the radio
button
42
Looking Good, then Not

See the Rows and the
columnValues, but
doesn’t see it as an array

Go ahead and choose,
create the form/report

Run it…at first it looks
just like before

Fill in the form, submit

Oh boy, what does that
mean?
43
Digging in

The SQL statement
seems to be OK, but
how will it read from the
array?

Ah ha…it can’t

But, with an array
syntax, it can
 [position()=1]

Easy enough to change
based on how many
columns we pull
44
Run it Again – and BAM

Wow, a totally dynamic
query – how’s that for a
flexible Service API!

That’s powerful!
45
Next Steps

Behind the scenes I
typically build my where
clause, etc.

At that point I hide
those fields
46
Loose Coupling

When you create an
application against Web
Services, you can port
the application to any
server

The data comes from
the Services, not the
database

No export/import
required

This makes adding new
“App Servers” a breeze!

If fields change, nothing
breaks!
 Added a column, no
problem
 Removed a column, no
problem, just shows as
empty
47
N-tier Architecture

Using this
architecture you’re
creating an n-tier
architecture

Scales limitlessly

Oracle isn’t painting
pages any more!

Your DBAs will be
happy

Can be your services
or someone elses!
48
AJAX for LOVs

Can incorporate AJAX
into your Application

MTOM is basically binary
XML for Web Services

iPerspective creates
XML and JSON that can
be consumed by AJAX
tools

This is where things will
move to

SQL*Net like footprint

See my other papers,
presentations on YUI
and cool tips with ApEx,
Google Maps, etc.
 Yahoo UI
 Yahoo Pipes
 GoogleMashup
 Google Web Toolkit
49
Summary – Consuming Web Services

Creating Your Web Services

Registering Your Web Services

Consuming Your Web Services in ApEx

N-tier Scaling

Core vs. Context

Other Technology - AJAX, LOVs, REST/JSON/XML, MTOM
50
Questions?
51
Brad’s Papers and Presentations
Java-based Oracle Web
Development

Practical Portal Practices

Implementing JSP in Portal

Java Server Pages

UltraSearch

JavaMail

Search Engines

Java for the PL/SQL Developer

Utl_smtp and Utl_http

Web Cache – achieving 150 the
performance

iFS

9iAS Installation, Configuration,
and Tuning

JavaScript


Wireless
Top DBA scripts for Web
Developers

Security

52
Other TUSC Presentations and Papers

Tuning



Database
SQL
Applications

PL/SQL

New Features

Forms, Reports

Security

Designer

Migrations

Team Management

Discoverer & BI

Uncommon Leaders

Built-in Packages

Workflow

DBA topics
53
Copyright Information

Neither TUSC nor the author guarantee this document to be
error-free. Please provide comments/questions to
[email protected].

TUSC © 2008. This document cannot be reproduced without
expressed written consent from an officer of TUSC.
54