Using Oracle XML DB Web Services for Service-Oriented Architecture

Purpose

This tutorial also shows you how to use Oracle XML DB Web services for Service-Oriented Architecture.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Binary XML Table
 Reviewing the XML Schema in JDeveloper
 Using Oracle XML DB Web Services for Service-Oriented Architecture
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Since Oracle 9i Database Release 2, Oracle XML DB has been seamlessly integrated with the Oracle database to provide high-performance database-native storage, retrieval, and management of XML data. With the new Oracle Database 11g release, Oracle XML DB is taking another leap ahead with a rich set of new capabilities to simplify DBAs' tasks in managing XML data while further empowering XML and SOA application developers. Oracle XML DB now supports multiple database-native XML storage models and XML indexing schemes, SQL/XML standard operations, W3C standard XQuery data model and XQuery/XPath languages, In-place XML schema evolution, database-native web services, high performance XML publishing, XML DB repository, and versioning and access control. This tutorial covers using Oracle XML DB Web services for Service-Oriented Architecture.

Oracle XML DB Web Services

Web Services have become an industry- standard way for both exchanging information and giving access to business logic. The new Oracle XML DB web services capability provides access to SQL, XQuery, PL/SQL, and other database elements as a web service. For example, one web service allows users to issue SQL and XQuery queries and receive the results as XML, and another can provide access to all PL/SQL functions and procedures stored inside the DB as web services.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should first complete the following steps:

1.

Install Oracle Database 11g and make sure the OE , HR users are unlocked.

2. Download and install Oracle JDeveloper (10.1.3.3) : Base Install, Studio Edition
3. Set environment variables to connect to Oracle Database 11g.
4.

Download and unzip the xmldb2_b.zip file into your working directory (i.e.wkdir)

Note: If you use an earlier version of Oracle JDeveloper, the screenshots may sligthly differ.

Additional Notes for Prerequisites:

If you do not unlock the OE, and HR users during Oracle Database 11g installation, perform the following:

a. Open a terminal window, and type the following command:

sqlplus sys/oracle as sysdba;

b. Once you get connected as sys, then, unlock the OE, and HR accounts.Then, grant dba role to OE, and HR. Grant SELECT_CATALOG_ROLE to OE. In the SQL*Plus window, type the following commands. Alternatively, run the script accounts.sql:

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
ALTER USER OE IDENTIFIED BY OE ACCOUNT UNLOCK;
GRANT DBA to OE, HR;
GRANT SELECT_CATALOG_ROLE TO OE;

You must set the HTTP port to 8080. Open a terminal window and execute the following statements.

sqlplus sys/oracle as sysdba;
exec dbms_xdb.sethttpport('8080');

If you have already performed the Using Oracle XML DB to Store, Query, and Access XML and Relational Data OBE, you do not need to set the HTTP port to 8080 again.

To set environment variables to connect to Oracle Database 11g, perform the following :

a. Open a terminal window, and search for .bash_profile.

b. Edit the .bash_profile to make sure of the following:

  • ORACLE_HOME environment variable is set
  • ORACLE_HOME/lib is added to LD_LIBRARY_PATH environment variable

For example, if your ORACLE_HOME is $ORACLE_BASE/product/11.1.0/db_1 make sure that your .bash_profile includes the following entries:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH

export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH

c. If you make the changes to .bash_profile, execute the script as follows:

./.bash_profile

 

Back to Topic List

Creating a Binary XML Table

In this tutorial, you use the SQL Developer tool. After creating a database connection, you set the script pathing reference in SQL Developer. You create a binary XML table and insert rows with data selected from the PURCHASEORDER table.

Perform the following steps:

 

1.

Open a terminal window, and go to the location of SQL Developer install. Then, start SQL Developer. Use the following commands.

cd /u01/app/oracle/product/11.1.0/db_1/sqldeveloper
sh sqldeveloper.sh


2.

In SQL Developer, you must create a database connection as OE user. Perform the following steps.

a. In the Connections tab, right-click Connections and select New Connection.

b. The New/Select Database Connection window appears. Enter the following details, and click Test to make sure that the connection has been set correctly.

Connection Name: oe
UserName: oe
Password: oe
Hostname: localhost or < hostname> if you are using a remote machine
Port: 1521
SID: orcl

If you select the Save Password check box, the password is saved to an XML file. Therefore, once you close SQL Developer connection and open again, you will not be prompted for the password.



c. The test status shows success. Click Connect.

 

3.

To run the scripts by using the @ syntax, you can set the script pathing reference in SQL Developer. Perform the following steps:

a. Select Tools > Preferences > Database > Worksheet Parameters. Then, click Browse.

b. Browse to the location of your working directory that has the SQL scripts. Then, click Open.

c In the Preferences window, verify the script path in the Select default path to look for scripts field. Click OK.

 

4.

Run the script create_sl_bix_table.sql .

@create_sl_bix_table.sql

DROP TABLE po_sl_bix_table PURGE;

CREATE TABLE PO_SL_BIX_TABLE OF XMLType
XMLType STORE AS SECUREFILE BINARY XML;
/
INSERT INTO po_sl_bix_table
SELECT object_value FROM purchaseorder;
/

SELECT COUNT(*) FROM po_sl_bix_table
/

COMMIT
/

 

In the above section, you learned how to connect to SQL Developer and set script pathing reference. You also learned how to c reate an XMLType table with binary XML storage, and populate the table with data.

Back to Topic

 

Back to Topic List

Reviewing the XML Schema in JDeveloper

You need to perform the following tasks:

 Create a WebDAV Connection
 Review the XML Schema

Back to Topic List

Create a WebDAV Connection

Before you access the XML Schema documents in XML DB, you need to create a WebDAV connection. Perform the following steps:

1.

Start JDeveloper. Run the <jdev_install>/jdev/bin/jdev file.

cd /JDeveloper/jdev/bin
./jdev

Alternatively, double-click the JDeveloper icon on the desktop.

 

2.

Click the Connections Navigator tab. In the Connections window, right-click WebDAV Server, and select New WebDAV Connection...

 

3.

The Create WebDav Connection window appears. Enter the following details, then click Test Connection.

URL location: <hostname>:8080/
Connection Name: WebDavConnection1
User Name: oe
Password: oe

 

4.

The test connection status shows successful. Click OK.

 

5.

Expand WebDavConnection1 connection that you just created.

 

Back to Topic

Review the XML Schema

Now you can review the XML Schema . Perform the following steps:

1.

Expand home - OE - PurchaseOrders - 2002 - Apr. In the list, right click the first document ,and select Open. This opens the XML document.

 

2.

Scroll down in the Connections window, and right-click purchaseOrder.xsd. Then, select Open.

 

3.

The XML Schema is shown in a graphical layout. Expand PurchaseOrder.

 

4.

Expand Actions.

 

5.

Expand Action and you see that there are two type objects, User and Date.

 

6.

Now, go back to the XML Document to see the definition. Click the XML Document tab. Note that the Action object contains a user type of KPARTNER.

 

Back to Topic List

Using Oracle XML DB Web Services for Service-Oriented Architecture

You can implement a service-oriented architecture using a new web service endpoint native to Oracle XML DB. Perform the following tasks:

Enable the database-native Web service end point in Oracle XMLDB

Develop a Web service client application

View Web service request and response using Http Analyzer

Back to Topic List

Enabling the database-native Web service end point in Oracle XML DB

1.

Open a terminal window and enter the following commands:

cd wkdir
sqlplus sys/oracle as sysdba

 

2.

Enable the database-native Web service endpoint in Oracle XML DB. Execute the following script:

@setWebServiceEvent11g.sql

create or replace procedure addServletMapping (pattern varchar2,
servletname varchar2,
dispname varchar2,
servletclass varchar2,
servletschema varchar2,
language varchar2,
description varchar2,
securityRole xmltype) as
xdbconfig xmltype;
begin
xdbconfig := dbms_xdb.cfg_get();
select deleteXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-name="' || servletname || '"]'
)
into xdbconfig
from dual;

if (language = 'C') then
select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list',
'servlet',
xmlElement
(
"servlet",
xmlAttributes('http://xmlns.oracle.com/xdb/xdbconfig.xsd' as "xmlns"),
xmlForest
(
servletname as "servlet-name",
language as "servlet-language",
dispname as "display-name",
description as "description"
),
securityRole
)
)
into xdbconfig
from dual;
else
select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list',
'servlet',
xmlElement
(
"servlet",
xmlAttributes('http://xmlns.oracle.com/xdb/xdbconfig.xsd' as "xmlns"),
xmlForest
(
servletname as "servlet-name",
language as "servlet-language",
dispname as "display-name",
description as "description",
servletclass as "servlet-class",
servletschema as "servlet-schema"
)
)
)
into xdbconfig
from dual;
end if;

select deleteXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping[servlet-name="' || servletname || '"]'
)
into xdbconfig
from dual;

select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings',
'servlet-mapping',
xmltype
(
'<servlet-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
<servlet-pattern>'||pattern||'</servlet-pattern>
<servlet-name>'||servletname||'</servlet-name>
</servlet-mapping>'
)
)
into xdbconfig
from dual;

dbms_xdb.cfg_update(xdbconfig);

end;
/

call addServletMapping(
'/orawsv/*',
'orawsv',
'Oracle Query Web Service',
null,
null,
'C',
'Web Services Servlet',
xmltype(
'<security-role-ref>
<role-name>XDB_WEBSERVICES</role-name>
<role-link>XDB_WEBSERVICES</role-link>
</security-role-ref>'
)
)
/

call addServletMapping(
'/orawsdl/*',
'orawsdl',
'Oracle WSDLs',
null,
null,
'C',
'WSDL Servlet',
xmltype(
'<security-role-ref>
<role-name>XDBWEBSERVICES</role-name>
<role-link>XDBWEBSERVICES</role-link>
</security-role-ref>'
)
)
/

grant XDB_WEBSERVICES to oe
/

-- For 11g only
grant XDB_WEBSERVICES_OVER_HTTP to oe
/
grant XDB_WEBSERVICES_WITH_PUBLIC to oe
/

-- Clean up afterward
drop procedure addServletMapping
/

 

Back to Topic

Developing a Web service client Application

1.

Switch to JDeveloper. In the navigator window, click the Add to Applications icon.

 

2.

In the Select application(s) to add window, go to your <working directory>/DBWS directory. Then, select OBE_DBWS.jws, and click Open.

At this point, if you see a warning window, click Yes. You may see this warning because of the application migration to the latest JDeveloper version file format.

 

3.

Right-click OBE_DBWS , and select New Project...

 

4.

The New Gallery window appears. From the Items list, select Empty Project, and click OK.

 

5.

In the Create Project window, enter XQueryWS for Project Name. Accept the default Directory Name, and click OK.
Note: The default directory name will be <working directory>/DBWS/XQueryWS.

 

6.

Expand OBE_DBWS. Right-click XQueryWS, and select New...

 

7.

In the Categories section, Expand Business Tier, and select Web Services.

 

8.

The list of Items is displayed in the right window pane. Select Web Service Proxy, and click OK.

 

9

This launches a Create Web Service Proxy wizard, in which you generate a java proxy for calling the web service. Click Next.

 

10.

You should select a WSDL document containing service descriptions, and then choose service in the document to create a proxy for. Browse in your <working directory>, and select orawsdl.xml file. Then, click Open.


 

11.

Click Next.

 

12.

The Building Model window pops up for a while and shows the building model status for service orawsdl.xml.The next step is to select the endpoint URL that the proxy will use to accept the ports of the service. Accept the default selection of the radio button Run against a service deployed to Embedded OC4J. Click Next.


 

13.

Click Next.

 

14.

In this step, you see ORAWSVPort under the Ports node. Click Next.

 

15.

Accept the default mapping options, and select the Reuse Existing Type Classes check box. Click Next.

 

16.

Click Next. Then, click Finish. You have finished creating your proxy.

Now, you see the Generation in Progress window.

 

17.

JDeveloper generates code for invoking web services defined in the WSDL file. In this lesson, the main client program file is the ORAWSVPortClient.java.

Open the file ORAWSVPortClient.txt. Use a text editor to copy the content of this file. In the JDeveloper window, paste this code after the line that reads //Add your own code here.

myPort.setUsername("oe");
myPort.setPassword("oe");
String endPoint = myPort.getEndpoint();
xqueryws.proxy.Query_text xq_text = new Query_text();
xqueryws.proxy.Query xq = new Query();
xq_text.setType(xqueryws.proxy.Query_textType.XQUERY); xq_text.set_value("ora:view(\"PO_SL_BIX_TABLE\")/PurchaseOrder/Reference");
xq.setQuery_text(xq_text);
System.out.println("Invoking the web service");
SOAPElement res = myPort.XMLFromQuery(xq);
System.out.println("Done. You can now check your result with HTTP Analyzer.");

 

18.

To compile the code successfully, you should import javax xml.soap.SOAPElement.At this point, you receive a code assist in JDeveloper to import javax.xml.soap.SOAPElement.Press Alt-Enter to import.

 

19.

Now, expand the import..., node. You see the javax.xml.soap.SOAPElement added.

 

20.

In the navigator, expand OBE-DBWS > XQueryWS > Application Sources > xqueryws.proxy. Click ORAWSVServiceProxy to see the structure at the bottom window pane.

 

21.

In the ORAWSVServiceProxy-Structure window pane, double-click ORAWSVBinding_Stub.java.

 

22.

The code in ORAWSVBinding_Stub.java is displayed in the right window. Make sure all occurences of the ENDPOINT_ADDRESS_PROPERTY are set to http://localhost:8080/orawsv.

 

23.

Now, switch to ORAWSVPortClient.java window pane. Right-click on the code, and select Make. At the bottom of the window, Messages- Log shows successful compilation.

 

Back to Topic

View Web service request and response using Http Analyzer

1.

To view the Webservice request and response using HTTP Analyzer, first, you should set the web browser and proxy settings in JDeveloper.

In the JDeveloper window, go to Tools, and select Preferences...

 

2.

In the Preferences window, select Web Browser and Proxy. Make sure of the following, and click OK.

  • Use HTTP Proxy Server check box is selected
  • Host Name is localhost
  • Port Number is 8080
  • Exceptions text box is blank.

 

3.

In the JDeveloper window, go to View, and select Http Analyzer.

 

4.

The Http Analyzer appears at the bottom of the window. Click the Start Http Analyzer icon.

 

5.

Now, you run the project. In the JDeveloper window, go to Run, and select Run XQueryWS.jpr.

 

6.

In the Choose Default Run Target Window that appears, perform the following steps:

a. Click Browse.

b.Go to <working directory>/DBWS/XQueryWS/src/xqueryws/proxy . Then, select ORAWSVPortClient.java, and click Open.

c. Click OK.

 

7.

In the Log window that appears at the bottom, you see the following message.

calling http://localhost:8080/orawsv
Invoking the web service
Done. You can now check your result with HTTP Analyzer.
Process exited with exit code 0.

 

8.

Switch to Http Analyzer at the bottom window pane. Double-click on the last item.

Alternatively, you can select the last item and click Data.

 

9.

In the Http Analyzer window, the bottom left pane displays the web service request, and the bottom right pane displays the response.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create a binary XML table
 Review the XML Schema in JDeveloper
 Use XML DB Web Services for Service-Oriented Architecture

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document