0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

Developing a Web Service from a PL/SQL Package

Purpose

In this tutorial, you create a web service from a PL/SQL package. You deploy the web service to OC4J and then test it.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Scenario
 Prerequisites
 Creating a Database Connection
 Installing the PL/SQL Package in the Database

Creating a New Application for the the Web Service

 Publishing the PL/SQL Package as a Web Service
 Deploying and Testing the Web Service
 Summary
 Related information

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, depending on your Internet connection, may result in a slow response time.)

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

Web services are discrete reusable software components that can be incorporated into applications. They build upon existing XML and HTTP protocols and use the following standards:

 WSDL (Web Services Description Language): an XML-based language for defining web services
 SOAP (Simple Object Access Protocol): an XML-based protocol for accessing services on the Web by using XML syntax to send commands over HTTP
 UDDI (Universal Description, Discovery, and Integration): a universal catalog of web services that enables software to discover and integrate with services on the Web

JDeveloper provides many features to help you to create and deploy web services and to find existing web services to use in your applications. This tutorial shows you how to create and deploy a web service based on an existing PL/SQL package. You deploy the resulting web service to OC4J and then test it to ensure correct functionality.

Back to Topic List

Scenario

You want to use JDeveloper to expose some functionality that currently exists in a PL/SQL package, as a web service.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Have access to or have installed Oracle JDeveloper (10.1.3.1.0). You can download it from Oracle Technology Network.

2.

Have started JDeveloper by double-clicking < jdev_install>\jdeveloper.exe .

If you receive a message asking if you want to migrate from a previous version, click No.

3.

Have access to or have installed the Oracle Sample Schemas, included with Oracle Database 10g.

The tutorial uses the HR schema. Specifically, the pages work with the EMPLOYEES table. Instructions for installing the HR schema are available online at:

http://www.oracle.com/technology/obe/obe10131jdev/common/OBEConnection.htm


4.

Have copied the following file to the temp directory on your local machine.


5.

Have started Oracle Application Server Containers for J2EE (OC4J) by double-clicking < jdev_install>\jdev\bin\start_oc4j.bat .

If prompted for a password, enter welcome1. Nothing displays as you enter the password. You will have to reenter it on the next line to confirm. If you are not asked for a password, for later steps you will need to know what password was set when OC4J was initially started.

Watch the resulting command window and wait until you see the message 'Oracle Containers for J2EE 10g (10.1.3.1.0) initialized'. Then you can minimize the window, but do not close it.


6.

Have created a connection in JDeveloper to OC4J:

  • Click the Connections tab (If the Connections tab is not visible, choose View > Connection Navigator from the menu).
  • Right-click the Application Server node and select New Application Server Connection from the context menu.
  • If the Welcome page of the Create Application Server Connection wizard displays, click Next.
  • On the Type page of the wizard, enter the Connection Name of oc4jconn and click Next.
  • On the Authentication page of the wizard, enter a Password of welcome1 (or whatever password was used when OC4J was initially started) and select Deploy Password, then click Next.
  • On the Connection page of the wizard, click Next.
  • On the Test page of the wizard, click Test Connection. After testing, the Status message should display Success!. (If it displays error messages, click Back and correct the connection information that you have entered. )
  • Click Finish.

Back to Topic List

Creating a Database Connection

Java Database Connectivity (JDBC) is a standard application programming interface (API) that is used for connecting a Java application to relational databases. JDeveloper's Connection Navigator maintains connection information for your application.

To create a JDBC connection to the HR schema, perform the following steps:

1.

Click the Connections tab on the Application Navigator. If the Connections tab is not showing, choose View | Connection Navigator from the JDeveloper main menu.

 

2.

Right-click the Database node and choose New Database Connection from the context menu.


3.

In the Create Database Connection Wizard, review the information on the Welcome page and then click Next.

 

4.

In the Connection Name field, enter hrconn . Click Next to continue.

 

5.

On the Authentication page: enter hr in the Username field and hr in the Password field. Check Deploy Password.

Click Next to continue.

 

6.

On the Connections page, the default values for the connection should be the following:

Driver: Thin

Host name: localhost

JDBC Port: 1521

SID: ORCL

Leave the fields set to these default values.

Click Next to continue.

 

7.

Click Test Connection.

If the database is available and the connection details are correct, you see the word Success! displayed in the Status window.

If an error occurs, verify the connection settings, click Back to make any necessary changes, and then retest the connection.

If the connection is successful, click Finish to complete the connection.

 

 

You have just created a connection to the database. In the next topic you install the PL/SQL package in this database.

 

 

 

Installing the PL/SQL Package in the Database

You should already have copied the EMP_PACKAGE.sql file to your temp directory, as suggested in the Prerequisites. If you have not already done this, you can find the file here.

To install the PL/SQL package in the HR schema, perform the following steps:

1.

In the Connections Navigator expand the Database node if it is not already expanded, and right-click the hrconn connection that you just created. Choose SQL*Plus from the context menu.

 

2.

Type hr as the password when prompted. Then press Enter.


3.

Run the EMP_PACKAGE.sq l file that you downloaded to your temp directory.

The package is created in the database.

 

4.

In the Connections Navigator, expand the hrconn node, and the HR schema node. Scroll down to the Packages node and expand it. You should see the package that you just installed.

 

 

Back to Topic List

Creating a New Application for the Web Service

You need to create an application and a project to hold the web service that you are going to create.

To create an application and project, perform the following steps:

1.

n the Applications Navigator, right-click the Applications node and select New Application from the context menu.

 

2.

In the Create Application dialog, enter the Application Name PL_SQL_WS.

In the Application Template field ensure that No Template [All Technologies] is selected.

Click OK


3.

In the Create Project dialog, enter a Project Name of GetEmployee and click OK.

 

4.

The Applications Navigator should now look like this:

 

Back to Topic List

Publishing the PL/SQL Package as a Web Service

To create the web service from the PL/SQL package, perform the following steps:

1.

In the Connections Navigator, expand the EMP_FETCHER package node and right click EMP_FETCHER. This is the header part of the package. Choose Open from the context menu.

 

2.

The Code Editor opens and displays the code for the package header. The code shows that the PL/SQL package gets an input value for an employee number and returns the record for that employee.

 

3.

In the Connections Navigator, right click the EMP_FETCHER > EMP_FETCHER node and select the Publish as Web Service option.

 

4.

In the Select J2EE Web Service Version, select J2EE 1.4 (JAX-RPC) and click OK.

 

5.

On the Welcome page of the Create PL/SQL J2EE Web Service page, click Next.

 

6.

In Step 1of the wizard, ensure the GetEmployee.jpr project is selected in the Target Project list, then click Next.

Notice that JDeveloper defaults the name to the project you are currently in.

 

7.

In Step 2 set the Web Service Name to EmpWebService and ensure that the SOAP 1.1.Binding option is selected. Click Next.

 

8.

In Step 3 click Next to accept the default options.

 

9.

In Step 4 check the GET_EMP function that you want to publish as a web service. Click Next.

 

10.

In Step 5 click Next to accept the default type mappings.

 

11.

In Step 6 click Next since you don't require any user defined type mappings for this web service.

 

12.

In Step 7, click Next to accept the defaults and then Finish.

 

13.

The web service, EmpWebService is created, and the EmpWebService .wsdl file is opened in the Design Editor.

 

14.

Click the EmpWebService node in the Applications Navigator and open the Structure pane to display the elements generated for this Web Service.

The EmpWebServiceBase.java class has all the database connection parameters in it.The Web Services generator uses JPublisher to generate the Web Service.

 

15.

Return to the WSDL in the Design Editor and expand some of the nodes to see details of the package exposed as a web service.

In the Bindings section of the diagram experiment with clicking on the input and output entries.

The WebServices engine knows how to call the package and method from this wsdl description.

Double click the EmpWebService tab on top of the WSDL Design editor to reduce the design pane to its previous size.

 

16.

Click the Source tab at the bottom of the WSDL, and then scroll down to the soap:address location tag. It should look something like http://<your machine name>/PL_SQL_WS-GetEmployee-context-root/EmpWebServiceSoapHttpPort. Copy this URL to the clipboard; you use it in the next topic.

 

 

 

At this point, the wsdl has been generated. It doesn't matter if the underlying implementation is a PL/SQL package or a Java Class. The beauty of web services is that you are abstracted from the underlying implementation of the service.

 

 

Back to Topic List

Deploying and Testing the Web Service

To deploy and test the web service that you have created , perform the following steps:

1.

In the Applications Navigator, expand the Resources node. Right-click WebServices.deploy, and select Deploy to > oc4jconn from the context menu.
( Note: This assumes that you have started OC4J and have created a connection to it as described in Prerequisites.)

In the Configure Application dialog, click OK.

The log window should show successful deployment.

 

2.

Open a browser and paste the URL you copied from the WSDL file above, which is the EmpWebService endpoint page generated by OC4J.

The endpoint page for the EmpWebService displays. Note the getEmp operation.


3.

The page displays an area where you can specify parameters for the operation. In the box provided enter the number 100, to return the record of the employee with the Id 100. Click Invoke.

 

4.

After you invoke the web service, the window displays the SOAP envelope containing the test result, which is the record of Employee 100, King, who is the President of the organization.

 

5.

Retest the getEmp method with a different value:

  • Click Back to return to the getEmp page.
  • In the parameter value field enter the number 120, to return a different employee.
  • Click Invoke.
  • The Test Result page contains the result, which is the record of the employee with the Id 120,and the last name, Weiss

 

 

Summary

In this tutorial you used JDeveloper to create a web service from a PL/SQL package. You then deployed the web service to OC4J and tested it in a browser.

Related Information

To learn more about using web services, refer to:

 Service-Oriented Architecture Technology Center on the OTN web site
 Web Services Management on OTN
 Web Services Forum on OTN
 Securing Web Services using JDeveloper and WS-Security on OTN

 Place the cursor over this icon to hide all screenshots.