Monday, October 26, 2009

Memanggil Service WebMethods melalui PL/SQL

Pada contoh ini, ada beberapa fungsi penting yang digunakan untuk memanggil webservices yang terdapat pada Oracle, yaitu UTL_HTTP dan XMLTYPE, tapi fungsi-fungsi ini hanya bisa dijalankan untuk veri 9i keatas, untuk 8i kayaknya harus bikin fungsi pakai native Java-nya. Fungsi utama untuk memanggil webservice atau request (dengan metode POST/GET) pada PL/SQL terletak pada fungsi UTL_HTTP. Sedangkan XMLTYPE digunakan untuk parsing xml.

Berikut ini adalah contoh API untuk memanggil services pada webMethods, setelah disesuaikan format envelope-nya dengan API pada webmethods.


----------------------------code------------------------------------------------

CREATE OR REPLACE PACKAGE WEBMETHOD.soap_api AS
-- --------------------------------------------------------------------------
-- Name : http://forum.swamedia.co.id
-- Author : Bhangun
-- Description : API untuk memanggil web services pada webMethods.
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 21-OCT-2009 Bhangun Initial Creation
-- --------------------------------------------------------------------------

TYPE t_request IS RECORD (
method VARCHAR2(256),
service VARCHAR2(256),
namespace VARCHAR2(256),
body VARCHAR2(32767),
envelope_tag VARCHAR2(30)
);

TYPE t_response IS RECORD
(
doc XMLTYPE,
envelope_tag VARCHAR2(30)
);



PROCEDURE set_proxy_authentication(p_username IN VARCHAR2,
p_password IN VARCHAR2);

FUNCTION new_request(p_method IN VARCHAR2,
p_service IN VARCHAR2,
p_namespace IN VARCHAR2,
p_envelope_tag IN VARCHAR2 DEFAULT 'SOAP-ENV')
RETURN t_request;


PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request,
p_name IN VARCHAR2,
p_type IN VARCHAR2,
p_value IN VARCHAR2);

FUNCTION invoke(p_request IN OUT NOCOPY t_request,
p_url IN VARCHAR2,
p_action IN VARCHAR2)
RETURN t_response;

FUNCTION get_return_value(p_response IN OUT NOCOPY t_response,
p_name IN VARCHAR2,
p_namespace IN VARCHAR2)
RETURN VARCHAR2;

END soap_api;
/
--------------------------------------------------------------------------


ini Body-nya
-------------------------code-------------------------------------------------
CREATE OR REPLACE PACKAGE BODY WEBMETHOD.soap_api AS
-- --------------------------------------------------------------------------
-- Name : http://www.oracle-base.com/dba/miscellaneous/soap_api
-- Author : DR Timothy S Hall
-- Description : SOAP related functions for consuming web services.
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 04-OCT-2003 Tim Hall Initial Creation
-- 23-FEB-2006 Tim Hall Parameterized the "soap" envelope tags.
-- 08-JUN-2006 Tim Hall Add proxy authentication functionality.
-- --------------------------------------------------------------------------

g_proxy_username VARCHAR2(50) := NULL;
g_proxy_password VARCHAR2(50) := NULL;


-- ---------------------------------------------------------------------
PROCEDURE set_proxy_authentication(p_username IN VARCHAR2,
p_password IN VARCHAR2) AS
-- ---------------------------------------------------------------------
BEGIN
g_proxy_username := p_username;
g_proxy_password := p_password;
END;
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
FUNCTION new_request(p_method IN VARCHAR2,
p_service IN VARCHAR2,
p_namespace IN VARCHAR2,
p_envelope_tag IN VARCHAR2 DEFAULT 'SOAP-ENV')
RETURN t_request AS
-- ---------------------------------------------------------------------
l_request t_request;
BEGIN
l_request.method := p_method;
l_request.service := p_service;
l_request.namespace := p_namespace;
l_request.envelope_tag := p_envelope_tag;
RETURN l_request;
END;
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request,
p_name IN VARCHAR2,
p_type IN VARCHAR2,
p_value IN VARCHAR2) AS
-- ---------------------------------------------------------------------
BEGIN
p_request.body := p_request.body||'<'||p_name||' xsi:type="'||p_type||'">'||p_value||'';
--DBMS_OUTPUT.PUT_LINE('<'||p_name||' xsi:type="'||p_type||'">'||p_value||'');
END;
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
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:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">' ||
'<'||p_request.envelope_tag||':Body>' ||
'<'||p_request.method||':'||p_request.service||' xmlns:'||p_request.method||'="'||p_request.namespace||'">' ||
p_request.body ||
'' ||
'' ||
'';


END;
-- ---------------------------------------------------------------------
-- ---------------------------------------------------------------------
PROCEDURE show_envelope(p_env IN VARCHAR2) AS
-- ---------------------------------------------------------------------
i PLS_INTEGER;
l_len PLS_INTEGER;
BEGIN
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;
-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------
PROCEDURE check_fault(p_response IN OUT NOCOPY t_response,
p_env IN VARCHAR2) 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||'-->>'||p_env);
RAISE_APPLICATION_ERROR(-20000, p_env);
END IF;
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);
l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.0');
IF g_proxy_username IS NOT NULL THEN
UTL_HTTP.set_authentication(r => l_http_request,
username => g_proxy_username,
password => g_proxy_password,
scheme => 'Basic',
for_proxy => TRUE);
END IF;
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);
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/"');
------------------liat
show_envelope(l_response.doc.getstringval());
check_fault(l_response,l_envelope);
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;
-- ---------------------------------------------------------------------

END soap_api;
/
---------------------------------------------------------------------------


ini fungsi untuk memanggil soap_api
-----------------------------code----------------------------------------------
CREATE OR REPLACE FUNCTION WEBMETHOD.getdata (param IN VARCHAR2)
RETURN VARCHAR2
AS
l_request soap_api.t_request;
l_response soap_api.t_response;
l_return VARCHAR2(32767);
l_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_method VARCHAR2(32767);
l_service VARCHAR2(32767);
l_soap_action VARCHAR2(32767);
l_result_name VARCHAR2(32767);

BEGIN
-- Set proxy details if no direct net connection.
--UTL_HTTP.set_proxy('server', NULL);
--UTL_HTTP.set_persistent_conn_support(TRUE);

-- Set proxy authentication if necessary.
soap_api.set_proxy_authentication(p_username => 'Administrator',
p_password => 'manage');

l_url := 'http://localhost:5555/soap/rpc';
l_method := 'riset';
l_namespace := 'http://www.webmethods.com/'||l_method;
l_service := 'getUp';
l_soap_action := '';
l_result_name := 'hasil';

l_request := soap_api.new_request(p_method => l_method,
p_service => l_service,
p_namespace => l_namespace);

soap_api.add_parameter(p_request => l_request,
p_name => 'input',
p_type => 'xsd:string',
p_value => param);

l_response := soap_api.invoke(p_request => l_request,
p_url => l_url,
p_action => l_soap_action);

--l_return := soap_api.get_return_value(p_response => l_response,
-- p_name => l_result_name,
-- p_namespace => l_namespace);

RETURN null;--l_return;
END;
/

---------------------------------------------------------------------------



ini contoh untuk memanggil fungsinya:

----------------------------code------------------------------------------------
DECLARE
RetVal VARCHAR2(200);
PARAM VARCHAR2(200);

BEGIN
PARAM := 'bhangun';

RetVal := WEBMETHOD.GETDATA ( PARAM );
COMMIT;
END;
----------------------------------------------------------------------------


dan ini adalah hasilnya :

----------------------------hasil------------------------------------------------
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">


bhangun


xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENC:root="1">
BHANGUN


----------------------------hasil------------------------------------------------


Untuk sementara ini fungsi soap_api.get_return_value sengaja tidak digunakan karena belum nemu format XPath untuk response-nya di webMethod. Keluarannya juga baru dalam bentuk output di konsol tapi nanti bisa disesuaikan dengan kebutuhan, mau dimasukan kedalam tabel, langsung diambil value-nya ato terserah.... Laughing hehhe maklum soalnya baru nyoba PL/SQL lagi jadi kudu belajar dulu yg lain-lainya Wink

Silahkan dikembangkan lagi.... Selamat mencoba!!
Diposting oleh di
Label: , , ,

1 comment:

Anonymous said...

Nice dispatch and this enter helped me alot in my college assignement. Thanks you on your information.

1:13 AM

Post a Comment

Subscribe to: Post Comments (Atom)