How to connect to the PeopleService web service using Databases
Oracle Documentation: https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/
MS SQL: https://blogs.msdn.microsoft.com/sqllive/2008/06/18/accessing-rest-based-web-services-using-sql-clr/
Oracle :
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
create user wc identified by wc default tablespace users temporary tablespace temp;
grant create procedure to wc;
grant execute on utl_http to wc;
grant create session to wc;
grant create type to wc;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'Layer7_Dev.xml',
description => 'A test of the Web Service functionality',
principal => 'WC',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;
begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'Layer7_Dev.xml',
host => 'gw-dev.its.yale.edu',
lower_port => 443,
upper_port => NULL);
end;
connect wc/wc
create type person_type as object
(
UPI varchar2(20),
NETID varchar2(20),
AFFILIATION varchar2(200),
PRIMARY_AFFILIATION varchar2(60),
LEGAL_FIRST_NAME varchar2(60),
LEGAL_LAST_NAME varchar2(60)
);
create type people as table of person_type;
create or replace package ws_consumer_pkg
as
function get_web_request(p_uri varchar2,
p_user_name varchar2,
p_password varchar2)
return clob;
function post_web_request(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return clob;
function get_people_data(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return people pipelined;
end ws_consumer_pkg;
create or replace package body ws_consumer_pkg
as
function get_web_request(p_uri varchar2,
p_user_name varchar2,
p_password varchar2)
return clob
as
l_request utl_http.req;
l_response utl_http.resp;
l_buffer varchar2(32767);
l_clob clob;
begin
dbms_lob.createtemporary(l_clob,false);
utl_http.set_detailed_excp_support(true);
utl_http.set_wallet('file:/etc/ORACLE/WALLETS/oracle', 'oracle007');
l_request := utl_http.begin_request(p_uri, 'GET', 'HTTP/1.1');
if ( p_user_name is not null and p_password is not null )
then
utl_http.set_authentication(l_request, p_user_name, p_password);
end if;
utl_http.set_header(l_request, 'user-agent', 'mozilla/4.0');
l_response := utl_http.get_response(l_request);
begin
loop
utl_http.read_text(l_response, l_buffer, length(l_buffer));
dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body
then
utl_http.end_response(l_response);
end;
return l_clob;
end get_web_request;
function post_web_request(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return clob
as
l_request utl_http.req;
l_response utl_http.resp;
l_buffer varchar2(32767);
l_clob clob;
begin
dbms_lob.createtemporary(l_clob,false);
utl_http.set_detailed_excp_support(true);
utl_http.set_wallet('file:/etc/ORACLE/WALLETS/oracle', 'oracle007');
utl_http.set_transfer_timeout(600);
l_request := utl_http.begin_request(p_uri, 'POST', 'HTTP/1.1');
if ( p_user_name is not null and p_password is not null )
then
utl_http.set_authentication(l_request, p_user_name, p_password);
end if;
utl_http.set_header(l_request, 'user-agent', 'mozilla/4.0');
utl_http.set_header(l_request, 'Content-Type', 'text/xml');
utl_http.set_header(l_request, 'Content-Length', length(p_post_body));
utl_http.write_text(l_request, p_post_body);
l_response := utl_http.get_response(l_request);
begin
loop
utl_http.read_text(l_response, l_buffer, length(l_buffer));
dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body
then
utl_http.end_response(l_response);
end;
return l_clob;
end post_web_request;
function get_people_data(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return people
pipelined
as
l_clob clob;
begin
l_clob := post_web_request(p_uri, p_post_body, p_user_name, p_password);
for i in (
with xdata as
( select xmltype(l_clob) as data from dual )
SELECT t.*
FROM xdata,
XMLTABLE(xmlnamespaces ('urn:edu.yale/people' as "ph"),
'/ph:People/ph:Person'
PASSING xdata.data
COLUMNS UPI VARCHAR2(30) PATH './ph:Identifiers/ph:Upi',
NETID VARCHAR2(30) PATH './ph:Identifiers/ph:NetId',
AFFILIATION VARCHAR2(200) PATH './ph:Identifiers/ph:Affiliation',
PRIMARYAFFILIATION VARCHAR2(60) PATH './ph:Identifiers/ph:PrimaryAffiliation',
LEGAL_FIRST_NAME VARCHAR2(60) PATH './ph:Names/ph:LegalName/ph:FirstName',
LEGAL_LAST_NAME VARCHAR2(60) PATH './ph:Names/ph:LegalName/ph:LastName'
) t
)
loop
pipe row(person_type(i.UPI, i.NETID, i.AFFILIATION, i.PRIMARYAFFILIATION,i.LEGAL_FIRST_NAME, i.LEGAL_LAST_NAME));
end loop;
end;
end ws_consumer_pkg;
select *
from table(ws_consumer_pkg.get_people_data(
'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService',
'<Request>
<Header>
<PageNumber>1</PageNumber>
<RecordsPerPage>50</RecordsPerPage>
</Header>
<AttributeFilter>
<Contact>Y</Contact>
<Demographic>Y</Demographic>
<Position>Y</Position>
<Appointment>Y</Appointment>
<Enrollment>Y</Enrollment>
<Organization>Y</Organization>
<Status>Y</Status>
</AttributeFilter>
<!--<PopulationFilter>
<Operator>AND</Operator>
<Primary_EDU_Person_Affiliation cmp="IN">EMPLOYEE</Primary_EDU_Person_Affiliation>
<Department_Code cmp="in">HPC111,HPC222</Department_Code>
<Planning_Unit_Code cmp="in">HPCC4003,HPCC4001</Planning_Unit_Code>
</PopulationFilter>-->
</Request>','s_ph_ybt_fin','xxxxxxxxx'
)
)
select *
from table(ws_consumer_pkg.get_people_data(
'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService',
'<Request>
<Header>
<PageNumber>1</PageNumber>
<RecordsPerPage>50</RecordsPerPage>
</Header>
<AttributeFilter>
<Contact>Y</Contact>
<Demographic>Y</Demographic>
<Position>Y</Position>
<Appointment>Y</Appointment>
<Enrollment>Y</Enrollment>
<Organization>Y</Organization>
<Status>Y</Status>
</AttributeFilter>
<!--<PopulationFilter>
<Operator>AND</Operator>
<Primary_EDU_Person_Affiliation cmp="IN">EMPLOYEE</Primary_EDU_Person_Affiliation>
<Department_Code cmp="in">HPC111,HPC222</Department_Code>
<Planning_Unit_Code cmp="in">HPCC4003,HPCC4001</Planning_Unit_Code>
</PopulationFilter>-->
</Request>','s_ph_ybt_fin','xxxxxxxxxx'
)
)
where Upi='10000051'
|
...