This discussion is archived
0 Replies Latest reply: Aug 24, 2013 9:11 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.



Legend

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