No results found

Your search did not match any results.

We suggest you try the following to help find what you’re looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.
Trending Questions

Calling a Web Service From The Database

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

Contents

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

    fig1

  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)

    fig2

  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

    fig3

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)

    fig4

  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

    fig5

  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.

    fig6

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

    fig7

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

    fig8

  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

    fig9

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

    fig10

Deploy to the database

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

fig11

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

    ig11

  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

    fig12

  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

fig12

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

fig13

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