Invoiking Web-Services with pure PL/SQL and building LOV from the result

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 is
      l_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

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;

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() 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;

In order to use it now all I need to do is call

select *

from table(Build_Mashup_Lov)