Calling webservis directly from database

A few days ago I need to call a web service inside Oracle. I research on it. I found that Oracle has implemented a built-in package which called UTL.DBWS to handle web services operations. I try on it. But I could not get the response XML. I asked forums.oracle but again could not get the answer. I wrote the script as described below:

DECLARE
ws_service UTL_DBWS.service;
ws_call UTL_DBWS.CALL;
ws_wsdl_url uritype;
ws_request_xml xmltype;
ws_response_xml xmltype;
BEGIN
ws_wsdl_url := urifactory.getUri(url => 'http://10.85.249.85:1112/ws?WSDL');
ws_service := utl_dbws.create_service(ws_wsdl_url, 'WS');
ws_call := utl_dbws.create_call(ws_service, NULL, 'process');
utl_dbws.set_property(ws_call, 'OPERATION_STYLE', 'DOCUMENT');
ws_request_xml := xmltype('');
ws_response_xml := utl_dbws.invoke(ws_call, ws_request_xml);
utl_dbws.release_call(ws_call);
utl_dbws.release_service(ws_service);
dbms_output.put_line(ws_response_xml.getstringval);
END;

Unfortunately I got some errors:
ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: Failed to invoke end componentFailed to invoke method
ORA-06512: at "SYS.UTL_DBWS", line 403
ORA-06512: at "SYS.UTL_DBWS", line 400

And then I try to use old fashion way with UTL_HTTP built-in package. I wrote some code and fullfill the requirements.

Firstly I created a type which somehow means class in object oriented languages. I must mention that, PL/SQL improved with object relational features by Oracle. You can almost do everything you can do in object orineted languages such as Java or C#.

CREATE OR REPLACE TYPE REQUEST AS OBJECT
(
-- Author : Mennan
-- Created : 07.09.2006
-- Purpose : For Web Service Use

-- Public Attributes
url VARCHAR2(128),
action VARCHAR2(128),
xml xmltype,

--Constructor
CONSTRUCTOR FUNCTION REQUEST(url VARCHAR2, action VARCHAR2, xml xmltype)
RETURN SELF AS RESULT,

-- Member functions
MEMBER FUNCTION invoke_service RETURN xmltype
)
/
CREATE OR REPLACE TYPE BODY REQUEST IS

-- Author : Mennan
-- Created : 07.09.2006
-- Purpose : For Web Service Use

--Constructor:To construct the object with url, action and xml attributes
CONSTRUCTOR FUNCTION REQUEST(url VARCHAR2, action VARCHAR2, xml xmltype)
RETURN SELF AS RESULT IS
BEGIN
SELF.url := url;
SELF.action := action;
SELF.xml := xml;
RETURN;

END;

--invoke_service:To invoke the Web Service with constructed attributes and return XML response
MEMBER FUNCTION invoke_service RETURN xmltype IS
soap_envelope VARCHAR2(32767);
soap_envelope_length NUMBER;
soap_response VARCHAR2(32767);
http_request utl_http.req;
http_response utl_http.resp;
response_xml xmltype;
BEGIN
soap_envelope := '';
soap_envelope := soap_envelope || xml.getStringVal();
soap_envelope := soap_envelope || '';
soap_envelope_length := length(soap_envelope);

http_request := utl_http.begin_request(url, 'POST', 'HTTP/1.0');
utl_http.set_header(http_request, 'Content-Type', 'text/xml');
utl_http.set_header(http_request, 'Content-Length', soap_envelope_length);
utl_http.set_header(http_request, 'SOAPAction', action);
utl_http.write_text(http_request, soap_envelope);

http_response := utl_http.get_response(http_request);
utl_http.read_text(http_response, soap_response);
utl_http.end_response(http_response);

response_xml := xmltype.createxml(soap_response);
response_xml := response_xml.extract('//soap:Body/child::node()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');

RETURN response_xml;
END;

END;
/

I created another object to handle file operations. The object abstracts all file processing. It can be used somewhere else to work with files.

CREATE OR REPLACE TYPE FILE_OPERATIONS AS OBJECT
(
-- Author : Mennan
-- Created : 08.09.2006
-- Purpose : For File Operations
NAME VARCHAR2(64),

STATIC FUNCTION read_text(file_name VARCHAR2, file_dir VARCHAR2)
RETURN VARCHAR2,
STATIC FUNCTION read_xml(file_name VARCHAR2, file_dir VARCHAR2)
RETURN xmltype,
STATIC FUNCTION write_text(file_name VARCHAR2,
file_dir VARCHAR2,
content_text VARCHAR2) RETURN BOOLEAN,
STATIC FUNCTION write_xml(file_name VARCHAR2,
file_dir VARCHAR2,
content_xml xmltype) RETURN BOOLEAN

)
/
CREATE OR REPLACE TYPE BODY FILE_OPERATIONS IS

-- Author : Mennan
-- Created : 08.09.2006
-- Purpose : For File Operations

STATIC FUNCTION read_text(file_name VARCHAR2, file_dir VARCHAR2)
RETURN VARCHAR2 IS
content VARCHAR2(32767) := '';
file_handle utl_file.file_type;
file_line VARCHAR2(512);
BEGIN

file_handle := utl_file.fopen(file_dir, file_name, 'r');
--dbms_output.put_line('file handle created....');
IF utl_file.is_open(file_handle) THEN
--dbms_output.put_line('File is open...');
LOOP
BEGIN
utl_file.get_line(file_handle, file_line);
IF file_line IS NULL THEN
--dbms_output.put_line('Reach end of file...');
EXIT;
END IF;
--dbms_output.put_line(file_line);
content := content || file_line;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;

END;
END LOOP;
END IF;
--dbms_output.put_line('File read...');
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
--dbms_output.put_line('File closed...');
END IF;

RETURN content;

EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR(-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR(-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060, 'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR(-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR(-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR(-20063, 'FSEEK Param Less Than 0');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20099, 'Unknown UTL_FILE Error');

END;

STATIC FUNCTION read_xml(file_name VARCHAR2, file_dir VARCHAR2)
RETURN xmltype IS
content VARCHAR2(32767);
xml xmltype;
BEGIN
content := FILE_OPERATIONS.read_text(file_dir, file_name);
content := REGEXP_REPLACE(content, '^[<][\?].*[\?][>]', '');
--dbms_output.put_line(content);
xml := xmltype.createxml(content);

RETURN xml;
END;

STATIC FUNCTION write_text(file_name VARCHAR2,
file_dir VARCHAR2,
content_text VARCHAR2) RETURN BOOLEAN IS
file_result BOOLEAN;
file_handle utl_file.file_type;
BEGIN

file_handle := utl_file.fopen(file_dir, file_name, 'w');
utl_file.put(file_handle, content_text);
file_result := TRUE;
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
RETURN file_result;

EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR(-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR(-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060, 'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR(-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR(-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR(-20063, 'FSEEK Param Less Than 0');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20099, 'Unknown UTL_FILE Error');

END;

STATIC FUNCTION write_xml(file_name VARCHAR2,
file_dir VARCHAR2,
content_xml xmltype) RETURN BOOLEAN IS
file_result BOOLEAN;
content VARCHAR2(32767);
BEGIN
content := content_xml.getStringVal();
file_result := FILE_OPERATIONS.write_text(file_dir, file_name, content);
RETURN file_result;
END;

END;
/

Then I cretaed a package to handle web service operations. I included function and procedures to complete the task.

CREATE OR REPLACE PACKAGE web_service_engine IS

-- Author : Mennan
-- Created : 08.09.2006 14:03:53
-- Purpose : To Operate Web Service

--Constants
--First constructor the constants as your WSDL
WS_URL CONSTANT VARCHAR2(64) := 'http://10.23.249.125:1112/ws?WSDL';
WS_ACTION CONSTANT VARCHAR2(16) := 'processTheAction';

--Procedures and Functions
PROCEDURE call_service_via_file(ws_request_file_name IN VARCHAR2);
FUNCTION call_service_via_xml(ws_request_xml IN xmltype) RETURN xmltype;
FUNCTION call_service_via_text(ws_request_text IN VARCHAR2) RETURN VARCHAR2;

END web_service_engine;
/
CREATE OR REPLACE PACKAGE BODY web_service_engine IS

-- Author : Mennan
-- Created : 08.09.2006
-- Purpose : To call Web Service
-- It takes an XML Request as xmltype and returns an XML response as xmltype
FUNCTION call_service_via_xml(ws_request_xml IN xmltype) RETURN xmltype IS
ws_response_xml xmltype;
ws_request REQUEST;
start_time NUMBER;
finish_time NUMBER;
time_interval NUMBER;
BEGIN
start_time := dbms_utility.get_time;
ws_request := REQUEST(WS_URL, WS_ACTION, ws_request_xml);
ws_response_xml := ws_request.invoke_service;
finish_time := dbms_utility.get_time;

dbms_output.put_line('-----------------------------------------------------------------------');
dbms_output.put_line('Web Service Call Process Finished....');
dbms_output.put_line('Statistics:');
time_interval := (finish_time - start_time) / 100;
dbms_output.put_line('Total Execution Interval :' ||
to_char(time_interval) || ' seconds');
dbms_output.put_line('-----------------------------------------------------------------------');
RETURN ws_response_xml;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20300, 'Error On Executing Web Service...');

END call_service_via_xml;

-- Author : Mennan
-- Created : 08.09.2006
-- Purpose : To call Web Service
-- It takes an XML Request as varchar2 and returns an XML response as varchar2
FUNCTION call_service_via_text(ws_request_text IN VARCHAR2) RETURN VARCHAR2 IS
ws_request_xml xmltype;
ws_response_xml xmltype;
ws_response_text VARCHAR2(32767);
ws_request_textxml VARCHAR2(32767);
BEGIN
ws_request_textxml := REGEXP_REPLACE(ws_request_text, '^[<][\?].*[\?][>]', '');
ws_request_xml := xmltype.createxml(ws_request_textxml);

ws_response_xml := call_service_via_xml(ws_request_xml);
ws_response_text := ws_response_xml.getStringVal();
RETURN ws_response_text;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20200, 'Unknown Error. Most Probably XML is not valid...');

END call_service_via_text;

-- Author : Mennan
-- Created : 08.09.2006
-- Purpose : To call Web Service.
-- It takes an XML Request file in directory and creates an XML response file in directory
PROCEDURE call_service_via_file(ws_request_file_name IN VARCHAR2) IS

ws_response_file_name VARCHAR2(128);

--First Create directories in Oracle "CREATE DIRECTORY" command
ws_request_file_dir VARCHAR2(64) := 'WS_REQUESTS';
ws_response_file_dir VARCHAR2(64) := 'WS_RESPONSES';

ws_request_xml xmltype;
ws_response_xml xmltype;
time_identifier VARCHAR2(32);
results BOOLEAN;

BEGIN

ws_request_xml := FILE_OPERATIONS.read_xml(ws_request_file_dir, ws_request_file_name);
ws_response_xml := call_service_via_xml(ws_request_xml);
time_identifier := to_char(SYSDATE, 'YYYYDDMMHH24MISS');
ws_response_file_name := time_identifier || '_' || ws_request_file_name;
results := FILE_OPERATIONS.write_xml(ws_response_file_dir, ws_response_file_name, ws_response_xml);

END call_service_via_file;

END web_service_engine;
/

This is almost done. I want to show how to call the web services:

--via File
--When calling via file please create directories and put request xml files in.
--Check web_service_engine.call_service_via_file procedure
BEGIN
web_service_engine.call_service_via_file(ws_request_file_name => 'ws.xml');
END;


--Via XML
DECLARE
request_xml xmltype;
response_xml xmltype;
BEGIN
request_xml := xmltype.createxml('....');
response_xml := web_service_engine.call_service_via_xml(ws_request_xml => request_xml);
END;

--Via Text
DECLARE
request_text VARCHAR2(32767);
response_text VARCHAR2(32767);
BEGIN
request_text := '....';
response_text := web_service_engine.call_service_via_text(ws_request_text => request_text);
dbms_output.put_line(substr(response_text, 0, 255));
dbms_output.put_line(substr(response_text, 255, 512));

/*Output is:
-----------------------------------------------------------------------
Web Service Call Process Finished....
Statistics:
Total Execution Interval :0 seconds
-----------------------------------------------------------------------



*/
END;