SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with Calling Function as Web-services in PLSQL

816802Aug 25 2013 — edited Aug 27 2013

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

<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

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

</message>

</operation>

</portType>

<soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>

<soap:operation soapAction="FN_TEST"/>

<input>

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

</input>

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

</output>

</operation>

</binding>

<documentation>Oracle Web Service</documentation>

<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

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.

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 24 2013

Added on Aug 25 2013

6 comments

694 views