5 Replies Latest reply: Aug 23, 2013 7:26 PM by 816802 RSS

    Question on webservices in Oracle


      Hi All,


      I am new to Oracle webservices and I am doing POC on this one.


      The requirement is as given below.


      W have a Middleware team who is expecting data from our oracle tables.


      What the analysts are saying is: They will develop a WSDL and provide us the webservice information. As per them the WSDL contains what columns they need data for and in what format.

      So I need to use that WSDL and get the data and send the data to the Middleware team using their WEBSERVICE.


      Correct me If I am wrong about anything given above.


      I am going through the Oracle documentation and I can see we are using UTL_HTTP packages to make a request and read the data from the URL. I did not see anywhere where I can generate data as per their requirement and provide my data as a webservice.


      Much appreciate your guidance here on how to create data as a webservice from oracle..




        • 1. Re: Question on webservices in Oracle

          Please do not ask the same question multiple times in different formats.


          As I've already responded to your other question, you need to use XDB. XDB support WebDAV, HTTP, HTTPS and FTP clients - as oppose to the standard database OCI or JDBC client.


          One of the features of XDB, is web service support - providing a web service framework for calling standard PL/SQL code in the database.

          • 2. Re: Question on webservices in Oracle

            Thanks for the response. Will look into this XDB.

            • 3. Re: Question on webservices in Oracle

              Hi Billy,


              I read the oracle documentation where we are setting the Web services and how we can call the web services or create a function and this function can be provided as a web service.


              Using Native Oracle XML DB Web Services


              I am trying to simulate the same and so I create a TEST account and granted the specific roles given by the document.

              I want to build a test case like as given below.


              Procedure PROC1 calls PROC2 and PROC2 has out parameters and send back the response to PROC1 which will see this data and send us a confirmation back.


              I want to simulate the same using webservices where I want to call my proc which will be called by a webservice and this webservice will send me a response back regarding the status.


              Appreciate your help here.




              • 4. Re: Question on webservices in Oracle

                Hi All,


                I am able to create a WSDL document given below.



                This WSDL is create for a function and this function just returns a value (2 in this case). The function does not accept any input parameters.


                Now I am trying to access this WSDL in a PLSQL code and it is not working.


                I googled and found one code in Oracle-Base  ORACLE-BASE - Oracle Consuming Web Services


                In this site the WSDL they provided is (http://www.oracle-base.com/webservices/server.php?wsdl).


                This URL they are replacing in the code in the below section


                l_url := 'http://www.oracle-base.com/webservices/server.php';

                  l_namespace := 'xmlns="http://www.oracle-base.com/webservices/"';

                  l_method := 'ws_add';

                  l_soap_action := 'http://www.oracle-base.com/webservices/server.php/ws_add';

                  l_result_name := 'return';


                Now I also simulated the same and my code looks as given below.


                CREATE OR REPLACE FUNCTION add_numbers

                  RETURN NUMBER


                  l_request   sys.soap_api.t_request;

                  l_response  sys.soap_api.t_response;

                  l_return    VARCHAR2(32767);


                  l_url          VARCHAR2(32767);

                  l_namespace    VARCHAR2(32767);

                  l_method       VARCHAR2(32767);

                  l_soap_action  VARCHAR2(32767);

                  l_result_name  VARCHAR2(32767);


                  --l_url         := 'http://www.oracle-base.com/webservices/server.php';

                  --l_namespace   := 'xmlns="http://www.oracle-base.com/webservices/"';

                  --l_method      := 'ws_add';

                  --l_soap_action := 'http://www.oracle-base.com/webservices/server.php/ws_add';

                  --l_result_name := 'return';


                  l_url         := 'http://st21-0296:8080/orawsv/TEST/FN_TEST';

                  l_namespace   := 'xmlns="http://madhu.com/"';

                  l_method      := 'ws_add';

                  l_soap_action := 'http://st21-0296:8080/orawsv/TEST/FN_TEST/ws_add';

                  l_result_name := 'return';





                  l_request := sys.soap_api.new_request(p_method       => l_method,

                                                    p_namespace    => l_namespace);



                  sys.soap_api.add_parameter(p_request => l_request,

                                         p_name    => 'int1',

                                         p_type    => 'xsd:integer',

                                         p_value   => 10);



                /*  sys.soap_api.add_parameter(p_request => l_request,

                                         p_name    => 'int2',

                                         p_type    => 'xsd:integer',

                                         p_value   => p_int_2);



                l_response := sys.soap_api.invoke(p_request => l_request,

                                                p_url     => l_url,

                                                p_action  => l_soap_action);



                l_return := sys.soap_api.get_return_value(p_response  => l_response,

                                                     p_name      => l_result_name,

                                                   p_namespace => NULL);



                  RETURN l_return;



                But when I execute the code


                I am getting the below error.


                select add_numbers  from dual;


                ERROR at line 1:

                ORA-31011: XML parsing failed

                ORA-19202: Error occurred in XML processing

                LPX-00104: Warning: element "HTML" is not declared in the DTD

                Error at line 2

                ORA-06512: at "SYS.XMLTYPE", line 48

                ORA-06512: at "SYS.SOAP_API", line 153

                ORA-06512: at "TEST.ADD_NUMBERS", line 43


                Can anyone let me know what would be the issue.




                • 5. Re: Question on webservices in Oracle

                  The WSDL document that I created is as given below for reference.


                  This XML file does not appear to have any style information associated with it. The document tree is shown below.


                  <definitions xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.com/orawsv/TEST/FN_TEST" xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" name="FN_TEST" targetNamespace="http://xmlns.oracle.com/orawsv/TEST/FN_TEST">






                  <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/TEST/FN_TEST" elementFormDefault="qualified">



                  <xsd:element name="SNUMBER-FN_TESTInput">











                  <xsd:element name="FN_TESTOutput">









                  <xsd:element name="RETURN" type="xsd:double"/>








                  <message name="FN_TESTInputMessage">



                  <part name="parameters" element="tns:SNUMBER-FN_TESTInput"/>




                  <message name="FN_TESTOutputMessage">



                  <part name="parameters" element="tns:FN_TESTOutput"/>




                  <portType name="FN_TESTPortType">



                  <operation name="FN_TEST">



                  <input message="tns:FN_TESTInputMessage"/>


                  <output message="tns:FN_TESTOutputMessage"/>




                  <binding name="FN_TESTBinding" type="tns:FN_TESTPortType">



                  <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>



                  <operation name="FN_TEST">



                  <soap:operation soapAction="FN_TEST"/>






                  <soap:body parts="parameters" use="literal"/>







                  <soap:body parts="parameters" use="literal"/>






                  <service name="FN_TESTService">



                  <documentation>Oracle Web Service</documentation>



                  <port name="FN_TESTPort" binding="tns:FN_TESTBinding">



                  <soap:address location="http://st21-0296:8080/orawsv/TEST/FN_TEST"/>