Calling a Web Service From The Database

An Oracle JDeveloper How To Document
Written by Susan Duncan
March, 2004

Contents

Introduction
Install SOAP Client Stack in Oracle Database
Grant Java Permissions to HR Schema
Publish and Run HelloWorld Java Class as Web Service
Create a client-side proxy to call the web service
Load the Java classes into the database
Test your Web Service Call
Troubleshooting
Conclusion

Introduction

Web services allow applications to programatically access remote content and application functionality using industry-standard mechanisms, without any dependency on the provider's platform, the location, the service implementation, or the data format.

Database Web services work in two directions: database as service provider, i.e. calling from the outside in, which lets client applications access the database via Web services mechanisms; and database as service consumer, i.e. calling from the inside out, which lets a SQL query or an application module in a database session consume an external Web Service.

This How To document describes the steps to create the files necessary to consume a Java web service in the database. It gives step by step instructions for loading these files into the database and testing the call out to a web service running in Oracle Containers for Java (OC4J). It uses a very simple HelloWorld service as an example. It gives tips on testing and debugging the service.

Full details of all the features of JDeveloper discussed in this article can be found in the JDeveloper Help documentation

Install SOAP Client Stack in Oracle Database

This is a one-off install to enable calls from the database to web services. If you have already installed SOAP Client Stack, skip this section. For detailed instructions on installing the stack see Install JAR Files In the Database

Grant Java Permissions to HR Schema

Your schema needs the correct Java 2 Permissions. See Chapter9 (Security) of the Database Java Developer's Guide for a detailed explanation. You can check whether the correct permissions already exist using the table browser (see step 4). The example in this HowTo uses the HR schema, you might set the permissions as follows.

  1. In the Connections navigator, use the context menu of your SYS connection to open a SQL Worksheet


  2. Amend the following code snippet to grant socket permission to your schema (in this example HR) and socket (localhost:8888). Paste the snippet into the SQL Worksheet and click the SQL Run icon.
    begin
    dbms_java.grant_permission( 'HR', 'SYS:java.net.SocketPermission', 'localhost:8888', 'connect, resolve');
    end;
  3. Check that the command completes successfully
  4. View your new entry in the JAVA$POLICY table (exapnd the Tables node for your SYS user, scroll to the table and double-click on it. Move to the Data tab)
  5. Repeat for other permissions
    dbms_java.grant_permission('HR','SYS:java.util.PropertyPermission','*','read,write')
    dbms_java.grant_permission( 'HR', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' )

    To disable a permission you can use dbms_java.disable_permission(164) where '164' is the Key column from the table (see above example). To permanently delete a permission first disable and then delete dbms_java.delete_permission(164)

Publish and Run HelloWorld Java Class as Web Service

This HowTo does not go into the detail of these steps. However, to follow this HowTo here are some tips to create and deploy a web service based on the HelloWorld Java class

  1. Create HelloWorld Java class

    public class HelloWorld
    {
    public HelloWorld()
    {
    }
    public String sayHello(String name)
    {
    return "Hello" + name;
    }
    }

  2. Publish as Oracle J2EE web service named HelloWorldService
  3. Deploy to your OC4J instance

Create a client-side proxy to call the web service

  1. Generate client side stub (accept all the defaults, you don't need to add a Main method)


  2. Add a static method (required for use in the database) to instantiate and call the service. See the Database Java Developer's Guide for more detail

    public static String callWS(String s)
    {
    HelloWorldServiceStub stub = new HelloWorldServiceStub();
    try
    {
    String result= stub.sayHello(s);
    return result;
    } catch (Exception ex)
    {
    String error = ex.toString();
    return error;
    }
    }

Load the Java classes into the database

Create a deployment profile to load the calling classes into the database

  1. Select the Loadjava and Java Stored Procedure Deployment Profile

  2. Accept the default name and location for the profile
  3. In the Profile Properties Dialog, click on Contributors.
  4. By default, the compiled classes (from the Project Output Directory) are loaded into the database. If you want to load the Java source files (and have the database compile them) you need to change this and load the files from the Project Source Path. You might want to do this while you are in development or perhaps debugging so that you can update and replace files and be sure that they are re-compiled from the latest source.


  5. Select Filters, deselect all the files except HelloWorldServiceStub (this is the only file you need to deploy to the database)

Create a PL/SQL wrapper to call the Java Stored Procedure

  1. Select the stored procedure deployment profile you have created
  2. Use the context menu to Add a Stored Procedure. This creates a PL/SQL wrapper function to callWS Java method that you use to call the service from your database applications


  3. Select the callWS method. Note that, as a static method, it is the only method that can be published as a stored procedure. Click OK to create the SQL file


  4. Use the context menu of the new callWS function in the Navigator to preview the generated SQL statement.

Deploy to the database

Use the context menu of your deployment profile to deploy the profile and the callWS function to your database

Test your Web Service Call

Create a Test function

  1. In the Connections Navigator, refresh your database connection to see the newly deployed Function CALLWS and the new JavaClass HelloWorldServiceStub
  2. Use the context menu of the Functions node to create a new Test Function


  3. Add the following to call the PL/SQL wrapper HR.CALLWS

    FUNCTION "TEST" RETURN VARCHAR2
    AS
    mystring varchar2(2000);
    BEGIN
    mystring := HR.CALLWS(' tester');
    RETURN(mystring);
    END;
  4. Use the context menu of Test to Run the function. Adjust the size of the returned String (v_return) and the dbms_output code so that any exception returned by the stub's callWS method is displayed in the log window


  5. Click OK to run Test and see the result passed back from the web service running in OC4J

Troubleshooting

Problem

Possible Solution

ORA-29541: class HR.mypackage/MyHelloStub could not be resolved
ORA-06512: at "HR.CALLWS", line 0
ORA-06512: at "HR.TEST", line 6
ORA-06512: at line 5
Process exited.
Disconnecting from the database hr16179.
SOAP stack not loaded

:

Ensure that the OC4J instance where your web service is deployed is up and running with your web and that the stub reflects that in its endpoint
You can check this by copying the contents of the String_endpoint URL from the WSDL into a browser. You should be able to access the service using the OC4J test harness.
This is especially important if you are running the service using the JDeveloper embedded OC4J (rather than a standalone instance) - you need to change the port to 8988
If you need to make a change to the stub you will have to go to your database connection and Drop the deployed stub and callWS function before you can re-deploy them from your project

 Check that your database schema has all necessary permissions granted. See Grant Java Permissions to HR Schema

Conclusion and where to get more information

Although the initial release of JDeveloper 10g does not seamlessly support the creation and loading of files required to call web services from the database, it provides tools to achieve these tasks with little hand coding needed from the developer.

Further Information
SOAP Client Stack Install
Oracle Database Java Developer's Guide
OTN Web Services Technology Centre
false ,,,,,,,,,,,,,,,