Web Services Inside Oracle Via PL/SQL using Oracle9i database

I am trying to create a Package that will encapsulate all the required parts to successfully call a Web service.
I already have the 'wsdl' HTTP site and it works 100% via a HTML call.

What I want to access the web service via the a PL/SQL call.  That is I want to connect to the database (for example  scott@myserver) and at the SQL prompt execute my_web_service_call.fetch_data('My text') and return the data from the web service.

My database consists of:
Oracle9i Enterprise Edition Release - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release - Production

I have done nothing to special with the database or schema that I want to use this from.

I have looked around a fair amount but have not found a full encapsulated package that I could just replace the web service html call with the web service URL I have for use on the Oracle 9 database.

Full step by step details of how to implement this would be the best.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

    function sayhello2 return varchar2;

    function sayhello return varchar2 is
        return 'hello';

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


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.
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
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

return( 'Hello '|| name );
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());
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:

7.      Run the Web Services client by selecting 'Run'. The output will be:
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.

jim_1234567890Author Commented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jim_1234567890Author Commented:

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


I can not get the examples to work though.

jim_1234567890Author Commented:

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.
jim_1234567890Author Commented:
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 ...
jim_1234567890Author Commented:
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):

   in_value   IN   VARCHAR2)
   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);
   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">
      || 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
      || '>
      || l_operation
      || '>
   http_req := UTL_HTTP.begin_request (l_service_url
   UTL_HTTP.set_header (http_req
   UTL_HTTP.set_header (http_req
                       ,LENGTH (soap_request));
   -- This SOAP Action may or may not be needed
   -- depends on your service.                       
   UTL_HTTP.set_header (http_req
   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);
      response := 'NOTHING IN RETURNED';
      resp := resp.EXTRACT ('/soap:Envelope/soap:Body/child::node()'
      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
            response := 'INVALID END TAG';
         END IF;
         response := 'INVALID START TAG';
      END IF;
   END IF;
   RETURN response;

Open in new window

jim_1234567890Author Commented:
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/">
  <geResponse xmlns:tns="http://yoururls.com/xsd">
</geResponse >

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?
PAQed with points refunded (500)

EE Admin

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.