0 Replies Latest reply: Aug 24, 2013 11:11 PM by 816802 RSS

    Issue with Calling Function as Web-services in PLSQL

    816802

      Hi All,

       

      I am trying to expose a function as web service.

      I followed the steps as given in the Oracle documentation.

       

      The below are the steps that I followed to do the same.

       

      Step 1: set the HTTP port

       

      EXEC dbms_xdb.sethttpport(8080);

       

      Step 2:  Add orawsv servlet

      CONN / AS SYSDBA

       

      DECLARE

        l_servlet_name VARCHAR2(32) := 'orawsv';

      BEGIN

        DBMS_XDB.deleteServletMapping(l_servlet_name);

       

        DBMS_XDB.deleteServlet(l_servlet_name);

       

        DBMS_XDB.addServlet(

        name => l_servlet_name,

        language => 'C',

        dispname => 'Oracle Query Web Service',

        descript => 'Servlet for issuing queries as a Web Service',

        schema => 'XDB');

       

        DBMS_XDB.addServletSecRole(

        servname => l_servlet_name,

        rolename => 'XDB_WEBSERVICES',

        rolelink => 'XDB_WEBSERVICES');

       

        DBMS_XDB.addServletMapping(

        pattern => '/orawsv/*',

        name => l_servlet_name);

      END;

      /

       

      Step 3: Validate whether step 2 is done or not.

      SQL> SET LONG 10000

      SQL> XQUERY declare default element namespace "http://xmlns.oracle.com/xdb/xdbconfig.xsd"; (:

        2         (: This path is split over two lines for documentation purposes only.

        3            The path should actually be a single long line.

        4         for $doc in fn:doc("/xdbconfig.xml")/xdbconfig/sysconfig/protocolconfig/httpconfig/

        5          webappconfig/servletconfig/servlet-list/servlet[servlet-name='orawsv']

        6         return $doc

        7  /

       

       

      Result Sequence----------------------------------------------------------------------

      <servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">

        <servlet-name>orawsv</servlet-name>

        <servlet-language>C</servlet-language>

        <display-name>Oracle Query Web Service</display-name>

        <description>Servlet for issuing queries as a Web Service</description>

        <servlet-schema>XDB</servlet-schema>

        <security-role-ref>

          <description/>

          <role-name>XDB_WEBSERVICES</role-name>

          <role-link>XDB_WEBSERVICES</role-link>

        </security-role-ref>

      </servlet>

       

      Step 4:

       

      CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON users;

       

      GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE TO test;

       

       

      GRANT XDB_WEBSERVICES TO test;

      GRANT XDB_WEBSERVICES_OVER_HTTP TO test;

       

      Note: As per the document as any object that is created can be exposed as a web-service.

       

      Step 5:

       

      Now I created a function in  in the TEST Account and when I checked the WSDL in a browser, I can the browser is showing the WSDL document.

       

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

       

      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>


      Step 6:

      Now I am trying to access this WSDL in a PLSQL package using UTL_HTTP package.


      CREATE OR REPLACE FUNCTION fn_test RETURN NUMBER

      AS

       

       

      g_debug  BOOLEAN := FALSE;

       

      TYPE t_request IS RECORD (

        method        VARCHAR2(256),

        namespace     VARCHAR2(256),

        body          VARCHAR2(32767),

        envelope_tag  VARCHAR2(30)

      );

       

       

      TYPE t_response IS RECORD

      (

        doc           XMLTYPE,

        envelope_tag  VARCHAR2(30)

      );

       

       

       

        l_request   t_request;

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

       

        PROCEDURE generate_envelope(p_request  IN OUT NOCOPY  t_request,

                               p_env      IN OUT NOCOPY  VARCHAR2) AS

      -- ---------------------------------------------------------------------

      BEGIN

        p_env := '<'||p_request.envelope_tag||':Envelope xmlns:'||p_request.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/" ' ||

                     'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema">' ||

                   '<'||p_request.envelope_tag||':Body>' ||

                     '<'||p_request.method||' '||p_request.namespace||' '||p_request.envelope_tag||':encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">' ||

                         p_request.body ||

                     '</'||p_request.method||'>' ||

                   '</'||p_request.envelope_tag||':Body>' ||

                 '</'||p_request.envelope_tag||':Envelope>';

      END;

       

       

      PROCEDURE show_envelope(p_env     IN  VARCHAR2,

                              p_heading IN  VARCHAR2 DEFAULT NULL) AS

      -- ---------------------------------------------------------------------

        i      PLS_INTEGER;

        l_len  PLS_INTEGER;

      BEGIN

        IF g_debug THEN

          IF p_heading IS NOT NULL THEN

            DBMS_OUTPUT.put_line('*****' || p_heading || '*****');

          END IF;

       

       

          i := 1; l_len := LENGTH(p_env);

          WHILE (i <= l_len) LOOP

            DBMS_OUTPUT.put_line(SUBSTR(p_env, i, 60));

            i := i + 60;

          END LOOP;

        END IF;

      END;

       

      PROCEDURE check_fault(p_response IN OUT NOCOPY  t_response) AS

      -- ---------------------------------------------------------------------

        l_fault_node    XMLTYPE;

        l_fault_code    VARCHAR2(256);

        l_fault_string  VARCHAR2(32767);

      BEGIN

        l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault',

                                               'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/');

        IF (l_fault_node IS NOT NULL) THEN

          l_fault_code   := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()',

                                                 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();

          l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()',

                                                 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();

          RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string);

        END IF;

      END;

       

       

      FUNCTION new_request(p_method        IN  VARCHAR2,

                           p_namespace     IN  VARCHAR2,

                           p_envelope_tag  IN  VARCHAR2 DEFAULT 'soap')

        RETURN t_request AS

      -- ---------------------------------------------------------------------

        l_request  t_request;

      BEGIN

        l_request.method       := p_method;

        l_request.namespace    := p_namespace;

        l_request.envelope_tag := p_envelope_tag;

        RETURN l_request;

      END;

       

       

       

      FUNCTION invoke(p_request IN OUT NOCOPY  t_request,

                      p_url     IN             VARCHAR2,

                      p_action  IN             VARCHAR2)

        RETURN t_response AS

      -- ---------------------------------------------------------------------

        l_envelope       VARCHAR2(32767);

        l_http_request   UTL_HTTP.req;

        l_http_response  UTL_HTTP.resp;

        l_response       t_response;

      BEGIN

        generate_envelope(p_request, l_envelope);

        show_envelope(l_envelope, 'Request');

        l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');

        UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');

        UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));

        UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);

        UTL_HTTP.write_text(l_http_request, l_envelope);

        l_http_response := UTL_HTTP.get_response(l_http_request);

        UTL_HTTP.read_text(l_http_response, l_envelope);

        UTL_HTTP.end_response(l_http_response);

        show_envelope(l_envelope, 'Response');

        l_response.doc := XMLTYPE.createxml(l_envelope);

        l_response.envelope_tag := p_request.envelope_tag;

        l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()',

                                                 'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"');

        check_fault(l_response);

        RETURN l_response;

      END;

       

       

      FUNCTION get_return_value(p_response   IN OUT NOCOPY  t_response,

                                p_name       IN             VARCHAR2,

                                p_namespace  IN             VARCHAR2)

        RETURN VARCHAR2 AS

      -- ---------------------------------------------------------------------

      BEGIN

        RETURN p_response.doc.extract('//'||p_name||'/child::text()',p_namespace).getstringval();

      END;

       

       

      BEGIN

       

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

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

        l_method      := 'ws_add';

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

        l_result_name := 'return';

       

       

        --http://st21-0296:8080/orawsv/TEST/GET_DESCRIPTION

       

        l_request := new_request(p_method       => l_method,

                                          p_namespace    => l_namespace); 

       

        l_response := invoke(p_request => l_request,

        p_url     => l_url,

        p_action  => l_soap_action

        );

       

       

        l_return := get_return_value(p_response  => l_response,

                                             p_name      => l_result_name,

                                           p_namespace => NULL);

       

       

        RETURN l_return;

       

      END  fn_test;

      /


      STEP 7:

      SELECT FN_TEST 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 "TEST.FN_TEST", line 114

      ORA-06512: at "TEST.FN_TEST", line 145

       

      I am getting the above error.

       

      Can any one let me know what is the issue here.

       

      Much appreciate your help here.

       

      Thanks,

      Madhu K.