6 Replies Latest reply: Aug 27, 2013 4:56 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.



       

        • 1. Re: Issue with Calling Function as Web-services in PLSQL
          Billy~Verreynne

          Difficult to spot errors in a large chunk of code, lacking line numbers for starters.

           

          A couple of comments on the code.

           

          Build a proper SOAP envelope using a tool such as SoapUI (Open Source). This will provide you with a template you can copy and paste into your PL/SQL code.

           

          Use substitution variables/tokens to format the template into a SOAP envelope. See Re: Calling webservice from PLSQL for an example.

           

          Do not use DBMS_OUTPUT that way in order to display the SOAP response. It is a very clunky and primitive interface. And can have a nasty impact on server memory (and thus server performance). Consider a message log table (persistent debug output), or use a pipeline table function (interactive debug output), instead.

           

          Keep things simple for starters. You have confirmed your web service is working via XDB (the WSDL is returned). Test that web service using something like SoapUI.

           

          You next need a PL/SQL web service client. Write a basic WebBrowser function (pipelined) that does a GET. Extend this to a PUT. Add SOAP envelope support.

           

          Doing it in incremental steps of complexity makes the learning and understanding curve a lot easier to handle.

          • 2. Re: Issue with Calling Function as Web-services in PLSQL
            Mike Kutz

            Just a suggestion:

            If you can change the requirements to be a REST service, I'd install Oracle APEX and the Oracle APEX Listener. (both are free)

            Point -> click -> fill in PL/SQL block -> done.

             

            ok, its a few more steps than that, but it is about that easy.

             

            MK

            • 3. Re: Issue with Calling Function as Web-services in PLSQL
              816802

              Thanks a lot billy for your guidance. Will look into the steps you have mentioned.

               

              Thanks

              Madhu K,

              • 4. Re: Issue with Calling Function as Web-services in PLSQL
                Billy~Verreynne

                Mike, why a REST service?

                 

                I still struggle to understand how this is at all any better than a plain URL Software deals with a URL. A "meaningful and readable" URL is lost on s/w using it.  (load of blah on Wikipedia about the benefits/advantages using it, IMO)

                 

                Humans use bookmarks or base URLs (domain name only). It is unreasonable to expect a human to know and use the REST syntax for accessing a specific service or function.

                 

                So why then REST at all?

                 

                BTW, Apex also supports interfacing with standard web services.

                • 5. Re: Issue with Calling Function as Web-services in PLSQL
                  Mike Kutz

                  I don't know about you but, if someone:

                  • copy+pastes code they find on the internet
                  • remove identification of the original author
                  • don't quote the source
                  • basically:  claim the code as their own.

                   

                  I consider that 'plagiarism'.

                   

                  The original source seems to come from:

                  ORACLE-BASE - Oracle Consuming Web Services

                  http://www.oracle-base.com/dba/miscellaneous/soap_api.sql

                   

                  That code seems to be for the 9i environment.

                   

                  IF YOU WANT TO EXPOSE A PROCEDURE/FUNCTION/PACKAGE AS A WEB SERVICE

                  Use a middle tier server.

                  Your setup (steps 1-5) is NOT recommended -- (I believe Oracle said this... but I haven't looked for documentation/proof.)

                   

                  APEX allows you to easily expose them as REST services.

                  Some IDEs have plugins that allow you to "point-click-deploy as SOAP".  (I think JDeveloper is one of them)

                   

                  IF YOU WANT TO CALL A WEB SERVICE FROM PL/SQL

                  Take a look at code written for your database version and examples from there.

                  eg APEX_WEB_SERVICES and UTL_DBWS

                  ORACLE-BASE - UTL_DBWS - Consuming Web Services in Oracle 10g

                   

                  As Billy has said:  start simple.  grow from there.

                  • 6. Re: Issue with Calling Function as Web-services in PLSQL
                    816802

                    Hi Mike,

                     

                    I am new to webservices and I am trying all the approaches that I am finding in Internet. I did not mention anywhere that I invented the code here.

                    Yes, I am searching Internet, reading Oracle documentation and trying to simulate what is there on the internet and trying to figure our solutions for my questions and asking Oracle forums gurus for help.

                     

                    I will look into all your suggestions and try to learn what I can.

                     

                    Thanks for your suggestions Mike.

                     

                    Regards,

                    Madhu K,