Best Way To Call Web Service From PL/SQL on 11g

How to publish and consume a PL/SQL function as a Web Service using JDeveloper.

This note is for PL/SQL developers, starting with Web services technology and JDeveloper.

In this note, a PL/SQL function called "sayHelloWorld" is published as a Web Service.
Solution
Note that the PL/SQL function is a PL/SQL package function.
Create a PL/SQL function in the database.
Under SQL*Plus (under scott schema for example), enter the following code:
create or replace package HelloWorld
as
function sayHelloWorld (name IN VARCHAR2) return VARCHAR2;
end helloWorld;
/
show errors
create or replace package body HelloWorld as
function sayHelloWorld (name IN VARCHAR2) return VARCHAR2 is
begin

return( 'Hello '|| name );
end;
end helloWorld;
/
show errors
This will generate the function that will be exposed as a Web Service.
Create the Web Service using JDeveloper
1.      Start JDeveloper.
2.      Create a Database connection (call it "ORA92" for instance) related to the database where the function to publish has been declared. The connection's user must be the owner of the procedure (in this example scott), do not forget to select the check button "Deploy Password".
3.      Create a new Application called WebServicesPL containing an empty project called Project1.
4.      Right click on the project, and select New. In the General Gallery, select "Business Tier -> Web Services-> PL/SQL Web Service" and click on the OK button. The PL/SQL Web Service publishing Wizard is now started.
5.      First, you need to choose if you would like to create the Web Service as either a J2EE 1.4 or a J2EE 1.3 Web Service. Which one you choose is depending on which Application Server you later will deploy the Web Service to. As we in this example only uses the embedded OC4J container, we will choose "J2EE 1.4 (JAX-RPC) Web Service".
6.      If the Welcome page is displayed, then click on the Next button.
7.      On the Step 1 screen, select the Database connection that you have previously created ("ORA92"). Select the Database package from which the function belongs to: "HELLOWORLD". Leave the other options to their default values.
8.      In Step 2, just leave the default options as they are, and click Next.
9.      On the Step 3 screen, select the function "SAYHELLOWORLD", then click the Next button.
10.      In Steps 4 to 6, just leave the default options as they are, and click Next.
11.      In the Step 6 screen when you click on the Finish button, the Wizard will generate the necessary WSDL and Java files. It will also generate a Deployment profile that can be used to deploy the Web Service to an Application Server.
12.      Rebuild the project.
Test the Web Service within JDeveloper
1.      To build a client to test the Web services, right click on the Web Service and select 'Generate Web Service Proxy'.
2.      In the first step you choose if you would like the test client to connect to the embedded OC4J container, or to an external Application Server. In this example, we will use the 'Run against a service deployed to Embedded OC4J' option.
3.      This will create a Java client for the Web Service. You now just need to modify the code to include a call to the desired method(s), like:
System.out.println("calling " + myPort.getEndpoint());
myPort.sayhelloworld("test");
4.      Rebuild the project.
5.      Start the Web Service by right clicking on it and select 'Run'. This will start the Web Service on the embedded OC4J instance.
6.      The Web Service can be tested by using a Web Browser and point to the URL that is shown in the Embedded OC4J Log window:
http://localhost:8988/Application1-Project1-context-root/MyWebService2SoapHttpPort

7.      Run the Web Services client by selecting 'Run'. The output will be:
calling http://10.172.169.84:8988/Application1-Project1-context-root/MyWebService2SoapHttpPort
Hello test
Process exited with exit code 0.

Deploy the Web Service
1.      Create an Application Server connection to an OC4J server or an Application Server where the Web Service will be deployed.
2.      Right click on the WebServices.deploy node and select "Deploy to" option.
3.      Select the Application Server Connection related to the OC4J server / Application Server that was created in Step 1.
4.      The 'Configure Application Dialog' will appear, just use the default options here and click OK. This will start the deployment of the Web Service. Once it is finished, it can be accessed from the OC4J instance / Application Server.