Recently I had to find a way to build a LOV for all issues inside Serena's Mashup system.
I found that Serena have a Web-Service interface. I downloaded it and was able to extract the exact SOAP envelop that I need to send to Mashup
Here is the full code:
create function Build_Mashup_Lov return XX_MASHUP_USSUES_TAB pipelined isl_mashup_host varchar2(200);
l_mashup_user varchar2(200);
l_mashup_pass varchar2(200);
l_mashup_table varchar2(200); --points to the mashup table id
soap_request varchar2(30000);
soap_respond varchar2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
l_clob CLOB;
resp XMLType;
i integer;
/*-----
* This cursor returns a VARRAY of XMLTypes or XMLSequenceType
* given a base XMLType and an XPath search string.
-------*/
CURSOR crsrNodeList(cv_doc XMLType,
cv_path VARCHAR2) IS
SELECT XMLSequence(extract(cv_doc, cv_path)) XML
FROM dual; /* XMLSequenceType Object the will contain an array of XMLType Objects */
nodeList XMLSequenceType;
begin
--get mashup host url
select profile_option_value
into l_mashup_host
from fnd_profile_options_vl v,
FND_PROFILE_OPTION_VALUES t
where v.profile_option_name='XXPCK_CCS_HOST'
and v.profile_option_id = t.profile_option_id
and level_id=10001;
select profile_option_value
into l_mashup_table
from fnd_profile_options_vl v,
FND_PROFILE_OPTION_VALUES t
where v.profile_option_name='XXPCK_CCS_TABLE'
and v.profile_option_id = t.profile_option_id and level_id=10001;
select profile_option_value
into l_mashup_pass
from fnd_profile_options_vl v,
FND_PROFILE_OPTION_VALUES t
where v.profile_option_name='XXPCK_CCS_PASS'
and v.profile_option_id = t.profile_option_id
and level_id=10001;
select profile_option_value
into l_mashup_user
from fnd_profile_options_vl v,
FND_PROFILE_OPTION_VALUES t
where v.profile_option_name='XXPCK_CCS_USER_NAME'
and v.profile_option_id = t.profile_option_id
and level_id=10001;
soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:aewebservices70">
<soapenv:Header/>
<soapenv:Body>
<urn:GetItemsByQuery>
<!--Optional:-->
<urn:auth>
<!--Optional:-->
<urn:userId>'||l_mashup_user||'</urn:userId>
<!--Optional:-->
<urn:password>'||l_mashup_pass||'</urn:password>
<!--Optional:-->
<urn:hostname>'||l_mashup_host||'</urn:hostname>
</urn:auth>
<urn:tableID>'||l_mashup_table||'</urn:tableID>
</urn:GetItemsByQuery>
</soapenv:Body>
</soapenv:Envelope>
';
DBMS_LOB.createtemporary(l_clob, FALSE);
http_req:= utl_http.begin_request
( l_mashup_host
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
utl_http.set_header(http_req, 'Content-Length', length(soap_request));
utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
utl_http.write_text(http_req, soap_request);
http_resp:= utl_http.get_response(http_req);
BEGIN
LOOP
UTL_HTTP.read_text(http_resp, soap_respond, 32767);
--strip namespace
soap_respond:=replace(replace(soap_respond,'SOAP-ENV:',''),'ae:','');
soap_respond:=replace(soap_respond,'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"','');
DBMS_LOB.writeappend (l_clob, LENGTH(soap_respond), soap_respond);
END LOOP;
utl_http.end_response(http_resp);
--read CLOB to XML
resp := sys.xmltype.createXML(l_clob);
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
begin
--read CLOB to XML
resp := sys.xmltype.createXML(l_clob);
OPEN crsrNodeList(resp, '/Envelope/Body/GetItemsByQueryResponse/return');
FETCH crsrNodeList
INTO nodeList;
CLOSE crsrNodeList;
--loop nodelist
FOR x IN 1 .. nodeList.count() LOOP
pipe row (XX_MASHUP_USSUE_ITEM(issue_title => nodelist(x).extract('return/title/text()').getStringVal(),
issue_number => nodelist(x).extract('return/genericItem/itemName/text()').getStringVal(),
issue_id => nodelist(x).extract('return/genericItem/itemID/text()').getStringVal()));
END LOOP;
UTL_HTTP.end_response(http_resp);
DBMS_LOB.freetemporary(l_clob);
return;
exception
when others then
UTL_HTTP.end_response(http_resp);
DBMS_LOB.freetemporary(l_clob);
end;
END; -- end read respond text
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(http_resp);
end Build_Mashup_Lov;
Lets examine it .
First of all I am extracting credentials values and Web-Service URL from profiles. After this, building SOAP XML
DBMS_LOB.createtemporary(l_clob, FALSE);Creating temporary CLOB object , that will hold the response from the server
http_req:= utl_http.begin_request( l_mashup_host
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
utl_http.set_header(http_req, 'Content-Length', length(soap_request));
utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
utl_http.write_text(http_req, soap_request);
http_resp:= utl_http.get_response(http_req);
Create URL request with proper Context and get the response. We cannot read the response entierly to the CLOB, so we need to loop
the response and read each time the maximun amount of textual data. We are not interested in the namespace, so lets strip it. Append
textual data to the CLOB object
LOOPUTL_HTTP.read_text(http_resp, soap_respond, 32767);
--strip namespace
soap_respond:=replace(replace(soap_respond,'SOAP-ENV:',''),'ae:','');
soap_respond:=replace(soap_respond,'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"','');
DBMS_LOB.writeappend (l_clob, LENGTH(soap_respond), soap_respond);
END LOOP;
Create XML object from CLOB. We need it , since the response is also in the XML format and we can use build in XML engine inside PL/SQL
to extract required data
resp := sys.xmltype.createXML(l_clob);We will use XPATH expression to extract data we need from specific elements
OPEN crsrNodeList(resp, '/Envelope/Body/GetItemsByQueryResponse/return');FETCH crsrNodeList INTO nodeList;
CLOSE crsrNodeList;
All we need to do now is to loop "nodelist" object and use pipe to return it as table
FOR x IN 1 .. nodeList.count() LOOPpipe row (XX_MASHUP_USSUE_ITEM(issue_title => nodelist(x).extract('return/title/text()').getStringVal(),
issue_number => nodelist(x).extract('return/genericItem/itemName/text()').getStringVal(),
issue_id => nodelist(x).extract('return/genericItem/itemID/text()').getStringVal()));
END LOOP;
In order to use it now all I need to do is call
select * from table(Build_Mashup_Lov)
No comments:
Post a Comment