[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Web Services Inside Oracle Via PL/SQL using Oracle9i database

Posted on 2007-11-16
Medium Priority
Last Modified: 2013-12-19
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.

Question by:jim_1234567890
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Expert Comment

ID: 20299596
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.
LVL 48

Expert Comment

ID: 20299602
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.


Author Comment

ID: 20300557
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 20301590

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


I can not get the examples to work though.


Author Comment

ID: 20301814

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

LVL 48

Expert Comment

ID: 20311009
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.

Author Comment

ID: 20313739
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))?

LVL 48

Expert Comment

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

Author Comment

ID: 20319921
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


Author Comment

ID: 20340700
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?


Expert Comment

ID: 20981578
Have try using oracle xmltype ad return type?

Accepted Solution

Computer101 earned 0 total points
ID: 22246957
PAQed with points refunded (500)

EE Admin

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question