Calling a Web Service From The Database

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.

   
   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

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

• Oracle Database Java Developer's Guide
• OTN Web Services Technology Centre