This discussion is archived
6 Replies Latest reply: Aug 27, 2013 2:56 PM by 816802 RSS

Issue with Calling Function as Web-services in PLSQL

816802 Newbie
Currently Being Moderated

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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

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

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

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

    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,

Legend

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