Web Services in Oracle Database 10g and beyond

currygeckoSoftware and s/w Development

Dec 2, 2013 (3 years and 7 months ago)

168 views

Web Services in Oracle
Database 10
g

and beyond

Ekkehard Rohwedder

Manager, Web Services

Oracle Corporation

Session id: 40064

Content


Motivation


Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


Motivation


Who are you?


Are you writing programs in the Database?
Using SQL, PL/SQL, or Java?


Then you may
want to


consume external Web Services


E.g. weather, stock prices, tax tables, products,
genome data


E.g. as SQL query data sources

=> Enterprise Information Integration:

=> Can use table functions to virtualize Web
Services as tables


Motivation


Who are you?


Want to get to Resources in Database?

By using Web Services?


Then you
want to


provide Database Web Services


E.g. weather, stock prices, tax tables, products,
genome data


E.g. PL/SQL packages and Java stored
procedures; XML, MultiMedia; messaging
capabilities



Content


Motivation



Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


An application or component with

Three Things
:

1.
a URI

2.
interacts through XML via internet

3.
interfaces/binding described in XML

Web Services



An Overview from 10,000 feet

Web Services



An Overview from 10,000 feet

Three Specifications

1.
SOAP


the XML
-
based message protocol

2.
WSDL


the service description

3.
UDDI


the Web Service “phone directory”


Service Provider



package and deploy

Service Consumer



connect

Web Services



An Overview from 10,000 feet

http://www.myserver.com/a/b

Firewall

Service Provider



package and deploy


implement




Service Consumer



connect


invoke service/methods

Invoke: XML

SOAP message

Web Services



An Overview from 10,000 feet

http://www.myserver.com/a/b

Firewall

<se:Envelope


xmlns:se=

“http://schemas.xmlsoap.org/soap/envelope/”


se:encodingStyle=

"http://schemas.xmlsoap.org/soap/encoding/”>

<se:Body>


<Add xmlns="urn:add
-
operation">


<arg1>20</arg1>


<arg2>20</arg2>


</Add>

</se:Body>

</se:Envelope>


Service Provider


implement


package and deploy


describe and publish


Service Consumer


UDDI
Repository

Publish WSDL

Web Services



An Overview from 10,000 feet

<wsdl:definitions>


<wsdl:types></wsdl:types>


<wsdl:message></wsdl:message>


<wsdl:portType></wsdl:portType>


<wsdl:binding>


<wsdl:operation></wsdl:operation>


</wsdl:binding>


<wsdl:service></wsdl:service>

</wsdl:definitions>



connect


invoke service/methods

Service Provider


implement


package and deploy


describe and publish


Service Consumer


find/locate


bind


connect


invoke service/methods


UDDI
Repository

Web Services



An Overview from 10,000 feet

<wsdl:definitions>


<wsdl:types></wsdl:types>


<wsdl:message></wsdl:message>


<wsdl:portType></wsdl:portType>


<wsdl:binding>


<wsdl:operation></wsdl:operation>


</wsdl:binding>


<wsdl:service></wsdl:service>

</wsdl:definitions>


Get or Locate WSDL

Service Provider


implement


package and deploy


describe and publish


Service Consumer


find/locate


bind/connect


invoke service/methods


UDDI
Repository

3
-
Connect,


Invoke SOAP

1
-
Publish WSDL

2
-

Get or Locate


WSDL, Bind

Web Services



An Overview from 10,000 feet


Shortcut


Content


Motivation


Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


Service Provider


implement


package and deploy


describe and publish


Service Consumer

Java and PL/SQL


bind


connect


invoke service/methods


Connect,


invoke SOAP

Get WSDL and bind

Consumed by the Database



Web Service call
-
outs

Oracle

Database


access external Web
Services from DB
code: stock quotes,
weather info, Web
search, scientific data,
enterprise data, etc.



Service Provider


implement


package and deploy


describe and publish



Connect,


invoke SOAP

Web Service Call
-
Outs

Java
WS

Client
Stack

Oracle Database

Java
Client
Proxy


PL/

SQL
Wrap
per

SQL

Engine

Table

Function

D
yn

I
nv

I
tf

Web Service Call
-
Outs
-

Installation



Preloaded in Oracle 10g Database JVM


initdbws.sql

script (under
[OH]/sqlj
)


loads
utl_dbws_jserver
,
sqljutl

JAR files


creates
SYS.UTL_DBWS

package



JAX
-
RPC client stack for Web Services


in JavaVM


grant
java.net.SocketPermission

to
users of WS client library



Web Service Call
-
Outs


Java Clients



Using
D
ynamic
I
nvocation
I
nterface:

Service s = factory.createService(serviceQname);


// Note: can also consume a WSDL document



Call call = service.createCall(portQname);

call.setTargetEndpointAddress(endpoint);
call.setProperty(…,…); …

call.setReturnType(returnTypeQname);

call.setOperationName(opQname);
call.addParameter(pname, ptype, pmode);


Object[] params = { … };

Object result = call.invoke(params);


Web Service Call
-
Outs


Java Clients



Generate static client proxy with JPublisher
jpub
-
proxywsdl=
WSDL
-
location

-
user=
user
/
pwd


Creates static JAX
-
RPC client
-
proxy code


Loads Java code into the database


Invoke via client interface


CInterf

itf = new
Client
_Impl().get
CInterf
Port());

((Stub)itf). _setProperty(…,…); …


StringHolder p_inout = new StringHolder(“…”);

Integer result = itf.operation(p_inout, p_in);



Web Service Call
-
Outs


PL/SQL clients



Dynamic PL/SQL clients use
UTL_DBWS

package


svc := UTL_DBWS.createService(
WSDLLocation
);

call := UTL_DBWS.createCall(svc,
operation
);

retval := UTL_DBWS.invoke(call,
args
);

outargs := UTL_DBWS.get_output_values(call);



uses ANYDATA to represent argument values
(primitive types only)


all names are qualified


explicitly close services and calls when done

Web Service Call
-
Outs


PL/SQL clients



Static PL/SQL clients (packages) are automatically
created by JPublisher


generation of Java “glue” to expose static Java methods


generation of PL/SQL package call
-
specs


generation of SQL object types for complex arguments


installation of Java and PL/SQL code into database


Can be invoked from SQL and from PL/SQL code


SELECT WS_PACK.WS_OP(...) FROM ...


BEGIN ... x := WS_PACK.WS_OP(...); END;


Content


Motivation


Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


The Database Provides

-

Web Service Call
-
Ins


Database Capabilities


PL/SQL and Java Stored Procedures


SQL queries and DML


XML capabilities (XDB)


Advanced Queueing (AQ) and Streams


Access from JDBC

=> now through
Web Services

Hosted as J2EE

Web Service


delegate


package and deploy


describe and publish



Connect,


invoke SOAP

Get WSDL and bind

The Database Provides

-

Application Server Hosts Web Service

Oracle

Database

Service Consumer


find/locate


bind/connect


invoke service/methods

Service
Implementation

(PL/SQL, SQL,
Java, XML, AQ, …)

Oracle

Application Server

JDBC

Web Service Call
-
Ins


Oracle Application Server framework


J2EE
-
based, JAX
-
RPC Web Services

=> deployment, security, management


Tools


JDeveloper, Enterprise Manager


Web Services Assembler (JPublisher),

DCM Control


Web Service Call
-
Ins


Web Services Assembler configuration


PL/SQL package, Java class, or SQL statement that is
to be exposed; data source at runtime


connection information for Java code generation


name and package of generated Java class


Web context


EAR deployment, securing, UDDI publishing


Enterprise Manager or DCM Control


Use JDeveloper wizard to automate task


browse connection, right click over package and
publish as Web Service

Web Service Call
-
Ins




Web Services Assembler configuration


<db
-
port>

<uri>
/statelessSP
</uri>


<schema>
scott/tiger
</schema> <!
--

db schema
--
>

<db
-
conn>
jdbc:oracle:thin:@host:5521:sqlj
</db
-
conn>


<!
--

db connection
--
>

<datasource
-
location>
jdbc/ds
</datasource
-
location>




<!


runtime datasource
--
>

<port
-
name>
Company
</port
-
name> <!
--

Java interface
--
>

<prefix>
acme
</prefix> <!
--

Java package
--
>

… t
he database resource that is published as a Web Service …


</db
-
port>


Invocation:

java
-
jar wsa.jar
-
config
config.xml


Web Service Call
-
Ins




PL/SQL packages



… t
he database resource that is published …

==

<
plsql
-
package
>

<!
--

db package

to be exposed
--
>
<name>
Company
</name>


<!


optional: methods to expose
--
>
<method>method1</method>
<method>method2</method>

</
plsql
-
package
>

Web Service Call
-
Ins




Java classes in the DB



… t
he database resource that is published …

==

<
db
-
java
>

<!


server
-
side Java class

to be exposed
--
>
<name>
foo.bar.Baz
</name>


<!


optional: methods to expose
--
>
<method>method1</method>

<method>method2</method>

</
db
-
java
>


Will expose
public static

methods with serializable
arguments.

Web Service Call
-
Ins




PL/SQL packages and Java classes



Automatic support for PL/SQL types: BOOLEAN,
record, and table types.

Generation of SQL object types as necessary


Native Java calls use Java serialization over JDBC


OUT and IN OUT arguments supported in generated
Java code


holder classes in 10g JAX
-
RPC


JavaBeans in 9.0.4 stack


JPublisher generates Java wrapper classes


this is
useful by itself outside of DB Web Services


Web Service Call
-
Ins




SQL Queries and DML statements



… t
he database resource that is published …

==

<
sql
-
statement
>

<operation> <!
--

a SQL Query
--
>


<name>
getEmp

</name>


<statement>
select ename from emp


where ename=:{myname VARCHAR}


</statement></operation>


<operation> <name>
updateEmp
</name> <!
--

SQL DML
--
>


<statement>
update emp SET sal=sal+500


where ename=:{theName VARCHAR}
</statement>

</operation>

</
sql
-
statement
>
>


Web Service Call
-
Ins




SQL Queries and DML statements



:{myname VARCHAR}

is a SQL host variable


DML statements automatically committed / rolled back.
Provide a batch (array) API as well


SQL queries / REF CURSOR arguments mapped to


structured data (JavaBean format), or


WebRowset or SQL/XML formats


SYS.XMLTYPE mapped to XML subtrees

Content


Motivation


Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


Database Grid Services


GRID =
efficient utilization of resources


GRID infrastructure defined by GGF (Global Grid
Forum) is based on Web Services technologies


DAIS Working Group in GGF

(Database Access and Integration Services WG):


databases become GRID resources


generic access mechanism: “XML version of JDBC”


distribution of results

Database Grid Services


DAIS Specification


generic

access

=> standard way for accessing the DB via XML

=> distribution mechanism


leveraging DB
capabilities of Advanced Queueing, Streaming


server
-
side XML support further integrated with
DBWS


support for asynchronous invocation and
notification mechanisms for DBWS


full JDeveloper support (
today
: PL/SQL call
-
in)

Content


Motivation


Web Services
(An overview from 10,000 feet)


Consumed by the Database: Web Service call
-
outs


Installation


Java and PL/SQL clients


The Database Provides: Web Service call
-
ins


PL/SQL and Java


DML and Queries


Database Grid Services


Conclusion


Conclusion

Database Web Services

is


invoking database APIs such as SQL, PL/SQL,
and Java through Web Services


re
-
use PL/SQL and Java stored procedures


utilize SQL queries and DML statements


handle result sets, XML documents, Relational, Text,
Spatial, Binary and Multi Media data


accessing external Web Services as SQL data
sources from Java and PL/SQL database code


Conclusion


JAX
-
RPC client in Oracle JavaVM for call
-
outs


Oracle Application Server for DB call
-
ins


create, deploy, secure, and publish Web
Services


JDeveloper IDE


Oracle Enterprise Manager


command line: Web Service Assembler


(with JPublisher), DCM Control

Conclusion


What Next…


Visit the Oracle Database Web
Services demo on the DEMOgrounds


Visit the Web Services pages on OTN


http://otn.oracle.com


Download the 10g Oracle Application
Server preview with JAX
-
RPC Web
Services

Q U E S T I O N S

A N S W E R S