Solved

Web Services Inside Oracle Via PL/SQL using Oracle9i database

Posted on 2007-11-16
13
2,908 Views
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 9.2.0.8.0 - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - 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.






0
Comment
Question by:jim_1234567890
13 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.

0
 

Author Comment

by:jim_1234567890
Comment Utility
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


0
 

Author Comment

by:jim_1234567890
Comment Utility


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.

0
 

Author Comment

by:jim_1234567890
Comment Utility
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).

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jim_1234567890
Comment Utility
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))?

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Normally you have to provide a Help function that will display this. Do not know if you can investigate via the package ...
0
 

Author Comment

by:jim_1234567890
Comment Utility
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;

/

Open in new window

0
 

Author Comment

by:jim_1234567890
Comment Utility
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?





0
 

Expert Comment

by:vipvaishu
Comment Utility
Have try using oracle xmltype ad return type?
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now