This discussion is archived
5 Replies Latest reply: Aug 23, 2013 5:26 PM by 816802 RSS

Question on webservices in Oracle

816802 Newbie
Currently Being Moderated

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

 

Thanks,

MK.

  • 1. Re: Question on webservices in Oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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
    816802 Newbie
    Currently Being Moderated

    Thanks for the response. Will look into this XDB.

  • 3. Re: Question on webservices in Oracle
    816802 Newbie
    Currently Being Moderated

    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.

     

    Thanks,

    MK.

  • 4. Re: Question on webservices in Oracle
    816802 Newbie
    Currently Being Moderated

    Hi All,

     

    I am able to create a WSDL document given below.

    http://st21-0296:8080/orawsv/TEST/FN_TEST?wsdl

     

    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

    AS

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

    BEGIN

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

    END;

     

    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.

     

    Thanks,

    MK.

  • 5. Re: Question on webservices in Oracle
    816802 Newbie
    Currently Being Moderated

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

     

     

    <types>

     

     

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

     

     

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

     

     

    <xsd:complexType>

     

     

    <xsd:sequence></xsd:sequence>

     

    </xsd:complexType>

    </xsd:element>

     

    <xsd:element name="FN_TESTOutput">

     

     

    <xsd:complexType>

     

     

    <xsd:sequence>

     

     

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

     

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    </xsd:schema>

    </types>

     

    <message name="FN_TESTInputMessage">

     

     

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

     

    </message>

     

    <message name="FN_TESTOutputMessage">

     

     

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

     

    </message>

     

    <portType name="FN_TESTPortType">

     

     

    <operation name="FN_TEST">

     

     

    <input message="tns:FN_TESTInputMessage"/>

     

    <output message="tns:FN_TESTOutputMessage"/>

    </operation>

    </portType>

     

    <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"/>

     

     

    <input>

     

     

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

     

    </input>

     

    <output>

     

     

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

     

    </output>

    </operation>

    </binding>

     

    <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"/>

     

    </port>

    </service>

    </definitions>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points