Please see example PL/SQL procedure below. This is an example of a procedure written, which opens a cursor, and fetches XML content from a view on an Oracle database. This was talked about in the previous blog post i.e. using XMLDB to create an XML document within Oracle.
The Procedure essentially loops through the cursor, in batches with sleeps inbetween each batch. It sends the XML content to a listening Web API web service.
Could be a useful PL/SQL block if you don’t want to write a complex ODI knowledge module or OWB mapping.
create or replace procedure PRC_CALL_WEBSRVC (l_endpoint varchar2, i_batchamount integer,i_sleeptime integer) is
–l_file_name VARCHAR2 (30);
–l_file_path VARCHAR2 (200);
–l_xmldoc CLOB;
l_xmldoc varchar2(4000);
l_cursor varchar2(4000);
i_recordcount number;
i_crmprop_lookup number;
l_header varchar2(200);
l_footer varchar2(200) ;
lc_endPoint VARCHAR2(200) := ‘null’;
lc_soapAction VARCHAR2(200);
lc_soap_request VARCHAR2(32000);
l_documenreferenceid varchar2(100);
l_customerkey varchar2(100);
test_response integer;
http_req UTL_HTTP.REQ;
http_resp UTL_HTTP.RESP;
filehandler UTL_FILE.FILE_TYPE;
cursor c_data_v is
select xml_out,customerkey,documentreferenceid
from VW_WEBSVC
where 1=1
BEGIN
i_recordcount := 0;
–Setup Parameters
lc_endPoint := l_endpoint; — set local variable to variable passed into procedure
–Header
l_header := ‘<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”><soap:Body xmlns:ns1=”http://www.hilllaneconsulting.co.uk”><ns1:Customer>’;
l_footer := ‘</ns1:Customer></soap:Body></soap:Envelope>’;
–This endpoint needs to accept the XML content
–lc_endPoint := ‘http://hilllaneconsulting.co.uk:8080/event/DefaultSystem/CustomerLoad’;
lc_soapAction := ‘initiate’;
–Stage the Data
–this is handled in the mapping
execute immediate ‘insert into TBL_WEBSVC select * from VW_WEBSVC_GET’;
–Purge records older than 30 days that have a status of “HTTP 202 Success”
execute immediate (‘delete from tbl_websvc where webservice_call = ”HTTP 202 Success” and last_update_date <= sysdate-100’);
— Now use the cursor above or select from view VW_XML that will generate the XML document
— So open the cursor
— Loop through the cursor and retrieve each row, write the contents to l_xmldoc, then call the API to send data
for getdata_rec in c_gatedata_v
loop
–Setup batch sleep time
if i_recordcount = i_batchamount then
dbms_output.put_line(‘Sleeping for : ‘ || i_sleeptime || ‘ seconds whilst SOA recovers !! ‘);
–Sleep for x seconds
dbms_lock.sleep( i_sleeptime );
–Reset Record/Batch count
i_recordcount := 0;
dbms_output.put_line(‘Resuming After Sleep…..’);
end if;
dbms_output.put_line(‘Processing Document :’ || omcr003_rec.documentreferenceid || ‘ Record No : ‘ || i_recordcount );
–Clear buffer
l_xmldoc := ”;
l_xmldoc := l_header;
l_cursor := ”;
l_cursor := getdata_rec.xml_out;
l_documenreferenceid := getdata_rec.documentreferenceid;
l_customerkey := getdata_rec.customerkey;
–Append content
lc_soap_request := l_xmldoc || l_cursor || l_footer;
–At this point l_xmldoc should now contain everything we want to send to the API
–Comment out Start/Open whilst testing logic
–Start / Open the Request
–Uncomment for calling API
http_req:= utl_http.begin_request
(
lc_endpoint
, ‘POST’
, ‘HTTP/1.1’
);
utl_http.set_header(http_req
, ‘Content-Type’
, ‘text/xml;charset=UTF-8’); — dealing with plain text in XML documents
utl_http.set_header(http_req
, ‘Content-Length’
, lengthb(CONVERT(lc_soap_request, ‘UTF8’)));
utl_http.set_header(http_req
, ‘SOAPAction’
, lc_soapAction); — required to specify a SOAP communication
–Send the actual XML document here !!
utl_http.write_text(http_req, lc_soap_request);
— Retrieve HTTP response , test the response
http_resp := utl_http.get_response(http_req);
–Uncomment for calling API
–Set response manually
–test_response := 202; — success — Comment for calling API
–test_response := 0; — fail — Comment for calling API
–dbms_output.put_line(‘XML document that will be sent is :’ || lc_soap_request);
if http_resp.status_code = 202 then — Uncomment for calling API
–if test_response = 202 then — Comment for calling API
–Update staging table with success
update TBL_WEBSVC set webservice_call = ‘HTTP 202 Success’
where customerkey = getdata_rec.customerkey
else
–Update staging table with failure
update TBL_WEBSVC set webservice_call = ‘HTTP Send Failed’
where customerkey = getdata_rec.customerkey
end if;
utl_http.end_response(http_resp); –Uncomment for calling API
i_recordcount := i_recordcount+1;
exit when c_getdata_v%NOTFOUND;
end loop;
commit;
END;