Web Services Inside Oracle Via PL/SQL using Oracle9i database

Using JDeveloper
 
This   is intended for jdeveloper users.
How To Create a Database Web ServicesCreate a new database connection in connection navigator
Right click packages and choose New PL/SQL Package
In the Create PL/SQL Package dialog box choose a name and press ok button
Right click your new package and choose New Package Body
Enter the following code for the package and package body:

  PACKAGE "PACK1" AS
    function sayhello2 return varchar2;
  END;


  PACKAGE BODY "PACK2" AS
    function sayhello return varchar2 is
      begin
        return 'hello';
      end;
  END;

Right click your new package and choose Publish as Web Service
In the Create PL/SQL Web Service press next button
In the first step choose a package name for your web service and press next button
In the second step choose the program unit you want to expose and press next button
In the third step check the web service end point and press next button
Click finish button
Right click your new web service in the application navigator and choose Generate Sample Java Client
Add the line below to the main method of the EmbeddedMyWebService1Stub.java

  System.out.println(stub.sayhello());

Right click the web service container in the application navigator and choose Run
Right click the EmbeddedMyWebService1Stub.java in the application navigator and choose Run
See the message in the Log Window.

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.

It appears you are describing how to make a web service, which in not what I want.

I already have the URL of the service I want to use (IE http//somesite/service/fetchvalue.wsdl).  The web service I want to use has already been deployed (by some other Company, site, etc).  I have no idea about the details about this function utilized by the service (all I have is the URL).

What I want to do is have gain access to this service this URL has via PL/SQL.  so instead of going out to a web site (http//somesite/service/fetchvalue.wsdl) an launching it and running it I want to just create a package (and what ever else is needed in the backend) so a user can go to a SQL prompt and do a simple function call that call this web service.  (this way you can call it against a whole set of results (for example:  select mystr, external_web_service.decodestring(mystr) as returned_str from my_table;)


BTW:  I have Oracle9i JDeveloper Version 9.0.2.822

This site appears to start the introduction to sort of what I want:

http://www.oracle-base.com/articles/9i/ConsumingWebServices9i.php

I can not get the examples to work though.

Even:
http://technology.amis.nl/blog/?p=358

might be good using the 'UTL_HTTP' items:  IE:    http_req utl_http.req; and http_resp utl_http.resp;

I just need to figure how to construct the required 'soap_request' and then how the extract the right information from the XMLType.createXML(soap_respond).

Yes UTL_HTTP can gain access to WEB sizes and open a browser via PL/SQL program. I am not sure, but think that this program has to run on the client site.

Do you know if there is a way to use the UTL_HTTP to see a list of of the valid service (functions) allowed by the web service you are calling (and the corresponding wrappers and call return the value (SOAPAction))?

Normally you have to provide a Help function that will display this. Do not know if you can investigate via the package ...

Finally got it to work.  Lots of dead end leads.
The basic skeleton is attached below (still needs to be cleaned up, but should give other's the basic idea):

CREATE OR REPLACE FUNCTION FETCH_WEB_SERVICE_XML_CALL (
   in_value   IN   VARCHAR2)
   RETURN VARCHAR2
IS
   soap_request       VARCHAR2 (32767);
   soap_respond       VARCHAR2 (32767);
   http_req           UTL_HTTP.req;
   http_resp          UTL_HTTP.resp;
   resp               XMLTYPE;
   response           VARCHAR2 (2000);
   start_tag          VARCHAR2 (200)   := '<variable_tag>';
   end_tag            VARCHAR2 (200)   := '</variable_tag>';
   l_operation        VARCHAR2 (200)   := 'WebServiceFunction';
   l_service_url      VARCHAR2 (255)   := 'http://yoururls.com/services/MainCall';
   l_xmlns            VARCHAR2 (255)   := 'http://yoururls.com/services/';
   l_literal          VARCHAR2 (255)   := 'Encapsulating_Literal';
   l_start_position   INTEGER (9);
   l_end_position     INTEGER (9);
BEGIN
   soap_request :=
         '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <ns1:'
      || l_operation
      || ' xmlns:ns1="'
      || l_xmlns
      || '" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
       <'
      || l_literal
      || ' xsi:type="xsd:string">'
      || in_value
      || '</'
      || l_literal
      || '>
      </ns1:'
      || l_operation
      || '>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
   http_req := UTL_HTTP.begin_request (l_service_url
                                      ,'POST'
                                      ,UTL_HTTP.http_version_1_0);
   UTL_HTTP.set_header (http_req
                       ,'Content-Type'
                       ,'text/xml');
   UTL_HTTP.set_header (http_req
                       ,'Content-Length'
                       ,LENGTH (soap_request));
                       
   -- This SOAP Action may or may not be needed
   -- depends on your service.                       
   UTL_HTTP.set_header (http_req
                       ,'SOAPAction'
                       ,http_req.method);
   UTL_HTTP.write_text (http_req, soap_request);
   http_resp := UTL_HTTP.get_response (http_req);
   UTL_HTTP.read_text (http_resp, soap_respond);
   UTL_HTTP.end_response (http_resp);
   resp := XMLTYPE.createxml (soap_respond);
 
   IF resp IS NULL THEN
      response := 'NOTHING IN RETURNED';
   ELSE
      resp := resp.EXTRACT ('/soap:Envelope/soap:Body/child::node()'
                           ,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
      response := resp.getstringval ();
      l_start_position := INSTR (UPPER (response), UPPER (start_tag)) + LENGTH (start_tag);
 
      IF l_start_position > 0 THEN
         l_end_position := INSTR (UPPER (response), UPPER (end_tag)) - l_start_position;
 
         IF l_end_position < l_start_position THEN
            response := TRIM (SUBSTR (response
                                     ,l_start_position
                                     ,l_end_position));
         ELSE
            response := 'INVALID END TAG';
         END IF;
      ELSE
         response := 'INVALID START TAG';
      END IF;
   END IF;
 
   RETURN response;
END;
/

Select allOpen in new window

I have it working for returning strings.
The one web service returns a image file in the XML (Not a link or url to the image) but the raw image info.
For example the returned XML files looks like:

<?xml version='1.0' encoding='UTF-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
  <geResponse xmlns:tns="http://yoururls.com/xsd">
<tns:return>
VWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJip.......
...
...
...
lpFjEePmHc0AYcTbdKjCSSqxGZAo4HPHPrV/w+yR6Ab
</tns:return>
</geResponse >
</soapenv:Body>
</soapenv:Envelope>

This 'return'ed item can be farily large - too large to fit into a VARCHAR2 (32767) item.
I wanted to take just this returned item and drop into a database.  I can drop the entire XML return into a CLOB field but that does not seem to help me fetch the image out of the XML.  What would be best is to fetch that item out and into a BLOB field in a table.  IE:  insert returned item into a blob field on a table.  Any Ideas?

Have try using oracle xmltype ad return type?