CONSUMING WEB SERVICE FROM ORACLE DATABASE 11G USING
UTL_HTTP PACKAGE
Contents
1. Create access control list and
assign host
1.1 Create access control list
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
ACL
=> 'STREAM_TEST.XML',
DESCRIPTION
=> 'PERMISSIONS TO ACCESS ESUPPORT WEBSITE',
PRINCIPAL
=> 'MD86506',
IS_GRANT
=> TRUE,
PRIVILEGE
=> 'CONNECT');
COMMIT;
END;
/
1.2 Create Role and assign to
access control list
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
ACL
=> 'STREAM_TEST.XML',
PRINCIPAL
=> 'DBA',
IS_GRANT
=> TRUE,
PRIVILEGE
=> 'CONNECT',
POSITION
=> NULL);
COMMIT;
END;
/
1.3 Add hosts
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL
=> 'STREAM_TEST.XML',
HOST
=> '*.satyam.com);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL
=> 'STREAM_TEST.XML',
HOST
=> '172.16.187.25');
COMMIT;
END;
/
1.4 Test Access
CREATE OR
REPLACE PROCEDURE GETTITLE (PURL VARCHAR2)
IS
VRESULT CLOB;
BEGIN
VRESULT :=
REPLACE(UTL_HTTP.REQUEST(PURL),CHR(10),' ');
VRESULT :=
REGEXP_REPLACE(VRESULT,'.*<TITLE> ?(.+) ?</TITLE>.*','\1',1,1,'I');
DBMS_OUTPUT.PUT_LINE (VRESULT);
END;
/
SET SERVEROUTPUT
ON
EXECUTE GETTITLE
('HTTP://172.16.187.119:8050');
2. Write procedure to call web
service
2.1 UTL_HTTP
1. UTL_HTTP.BEGIN_REQUEST : UTL_HTTP.BEGIN_REQUEST('http://172.16.187.25:8000/sap/xi/adapter_plain?bs=FILE'||'&'||'namespace=http%3A//file2file.com'||'&'||'interface=FILEHTML'||'&'||'qos=EO','POST',NULL,NULL);
Opens http request to the specified URL
2. UTL_HTTP.SET_HEADER:
UTL_HTTP.SET_HEADER (req,'Content-Type','text/xml');
UTL_HTTP.SET_HEADER (req,'Content-Length','200');
Set communication parameters for the http request
3.
UTL_HTTP.WRITE_TEXT
UTL_HTTP.WRITE_TEXT (req,'<?xml
version="1.0" encoding="UTF-8"?><ns0:FILEHTML
xmlns:ns0="http://file2file.com"><row><EMPLID>865072</EMPLID><EMPLANAME>fromDB4</EMPLANAME></row></ns0:FILEHTML>');
Write data into http stream
4.
UTL_HTTP.RESPONSE:
-resp :=
UTL_HTTP.GET_RESPONSE (req);
Capture response of the http stream
5.
UTL_HTTP.END_RESPONSE:
Close request and response http connections
6.
UTL_HTTP.END_REQUEST:
Close request http connections
No comments:
Post a Comment