by Husam Khalaf
I had a requirement to automate running a set of BI Publisher reports using the corporate fiscal calendar. The problem with BI Publisher scheduler is that is uses the normal calendar and there is no way to integrate a custom calendar instead. So I had to choose between two options to solve this problem:
1 - Utilize BI Publisher's Web Services API using Java code.
2 - Utilize BI Publisher's Web Services API using PL/SQL code.
The first option was more popular, I could google it and I found some examples that I could start with. The problem is that I am not a big fan of Java, and last time I've done coding in Java was a few years ago. On the other hand, I love PL/SQL, and I've done web services calls using custom PL/SQL before, such as integrating to CRM OnDemand and some Online Payment Gateway, but the problem was that I've never done that with BI Publisher. So I had to google this option first and unfortunately I could find almost nothing regarding this, so I had to start almost from scratch. I found two documents that were helpful to some extent:
- BI Publisher Web Services documentation.
- APEX and BI Publisher integration example done by Tyler Muth.
Long story short, I was able to accomplish this goal using PL/SQL, and I thought it may be a nice idea to share my experience if someone comes across a similar requirement. Here is a summary of what I've done:
- I created a variables table to store some parameters that may be different in different environments (Development, Testing and Production):
BIP_WS_CONFIG
| ATTR | VAL |
| NS | xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="xmlns=http://1.2.3.4:9999/oxp/service/PublicReportService |
| REP_ABS_PATH | /National Sales/BIP Reports/DPI/ |
| WSDL_URL | http://1.2.3.4:9704/xmlpserver/services/PublicReportService |
| USERNAME | bip_service_acct |
| PWD | D486ACCFD |
| BIP_SERVER | 1.2.3.4 |
| BIP_PORT | 9999 |
Where
9999 can be replace by your BI Publisher port#, and
1.2.3.4 can be replaced by you BI Publisher server. I am also encrypting the BI Publisher service account password using some custom encryption function that I won't demonstrate here.
- I created a utility package to handle web service calls in general, I tried not to make it specific to BI Publisher web services for reusability.
create or replace
PACKAGE pkg_webservice_utl
AS
g_app_name VARCHAR2(50);
FUNCTION make_request(
p_appl IN VARCHAR2,
p_url IN VARCHAR2,
p_action IN VARCHAR2 default 'SOAPAction',
p_version IN VARCHAR2 default '1.1',
p_envelope IN CLOB,
p_proxy_override IN VARCHAR2 default null,
p_wallet_path IN VARCHAR2 default null,
p_wallet_pwd IN VARCHAR2 default null) RETURN XMLTYPE;
FUNCTION parse_xml (
p_appl IN VARCHAR2,
p_xml IN XMLTYPE,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 default null) RETURN VARCHAR2;
FUNCTION clob_to_varchar2( p_clob_in CLOB) RETURN VARCHAR2;
FUNCTION encrypt( P_STR VARCHAR2 ) RETURN RAW;
FUNCTION decrypt( P_XCRYPT VARCHAR2 ) RETURN VARCHAR2;
END pkg_webservice_utl;
/
show errors
create or replace
PACKAGE BODY PKG_WEBSERVICE_UTL AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Package WEB_SERVICE_UTL
-- This package provides functions that can be used to
-- invoke web services
-- Example: Invoke web service call to run and schedule BI
-- Publisher reports
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
FUNCTION clob_to_varchar2 (p_clob_in CLOB) RETURN VARCHAR2
AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function CLOB_TO_VARCHAR2
-- Purpose:
-- This function coverts a clob to varchar2
-- Returns: The passed clob in varchar2 format
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
v_strt INTEGER := 1;
v_chunk_size INTEGER := 4000;
v_return VARCHAR2(32767) := NULL;
v_err_return NUMBER;
BEGIN
IF DBMS_LOB.getlength ( p_clob_in ) > 32767 THEN
RETURN NULL;
END IF;
-- Parse the CLOB
WHILE LENGTH (NVL(v_return,0)) <> DBMS_LOB.getlength ( p_clob_in )
LOOP
v_return := v_return || DBMS_LOB.SUBSTR ( p_clob_in,
v_chunk_size,
( v_chunk_size * ( v_strt - 1 ) ) + 1 );
v_strt := v_strt + 1;
END LOOP;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
--log the error in some error table
return null;
END clob_to_varchar2;
FUNCTION make_request (
p_appl IN VARCHAR2,
p_url IN VARCHAR2,
p_action IN VARCHAR2 DEFAULT 'SOAPAction',
p_version IN VARCHAR2 DEFAULT '1.1',
p_envelope IN CLOB,
p_proxy_override IN VARCHAR2 DEFAULT NULL,
p_wallet_path IN VARCHAR2 DEFAULT NULL,
p_wallet_pwd IN VARCHAR2 DEFAULT NULL ) RETURN XMLTYPE
AS
-----------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function MAKE_REQUEST
-- Purpose:
-- This function submits a web service call in HTTP request
-- and utilizes the oracle package to construct HTTP
-- requests and read HTTP responses
-- Returns: The HTTP response (SOAP) in XML format
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
TYPE HEADER IS RECORD (NAME VARCHAR2(256), VALUE VARCHAR2(1024));
TYPE header_table IS TABLE OF HEADER INDEX BY BINARY_INTEGER;
v_request_cookies utl_http.cookie_table;
v_response_cookies utl_http.cookie_table;
v_http_req utl_http.req;
v_http_resp utl_http.resp;
v_hdrs header_table;
v_request_headers header_table;
v_hdr HEADER;
v_clob CLOB;
v_raw_data RAW(512);
v_response VARCHAR2(2000);
v_name VARCHAR2(256);
v_hdr_value VARCHAR2(1024);
v_line VARCHAR2(1000);
v_status_code PLS_INTEGER;
v_env_len INTEGER := 0;
v_err_return NUMBER;
BEGIN
g_app_name := p_appl;
v_env_len := v_env_len + lengthb(clob_to_varchar2(p_envelope));
dbms_output.put_line('v_env_lenb= '||v_env_len);
dbms_output.put_line('Setting proxy');
utl_http.set_proxy (proxy => p_proxy_override);
dbms_output.put_line('Setting timeout');
utl_http.set_persistent_conn_support(true);
utl_http.set_transfer_timeout(180); -- 180 seconds
-- set wallet if needed
IF instr(lower(p_url),'https') = 1 THEN
utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
END IF;
-- set cookies if necessary
IF V_request_cookies.count > 0 THEN
utl_http.clear_cookies;
utl_http.add_cookies(v_request_cookies);
END IF;
dbms_output.put_line('Begining HTTP request');
v_http_req := utl_http.begin_request(p_url, 'POST');
-- set standard HTTP headers for a SOAP request
dbms_output.put_line('Setting HTTP request headers');
utl_http.set_header(v_http_req, 'Proxy-Connection', 'Keep-Alive');
IF p_version = '1.2' THEN
utl_http.set_header(v_http_req, 'Content-Type', 'application/soap+xml; charset=UTF-8; action="'||p_action||'";');
ELSE
utl_http.set_header(v_http_req, 'SOAPAction', p_action);
utl_http.set_header(v_http_req, 'Content-Type', 'text/xml; charset=UTF-8');
END IF;
dbms_output.put_line('Setting header length');
utl_http.set_header(v_http_req, 'Content-Length', v_env_len);
dbms_output.put_line('Setting headers from v_request_headers');
--set headers from v_request_headers
FOR i in 1.. v_request_headers.count LOOP
utl_http.set_header(v_http_req, v_request_headers(i).name, v_request_headers(i).value);
END LOOP;
dbms_output.put_line('Reading the envelope and write it to the HTTP request');
-- read the envelope, convert to UTF8 if necessary, then write it to the HTTP request
utl_http.write_text(v_http_req, clob_to_varchar2(p_envelope));
-- get the response
dbms_output.put_line('getting the response');
v_http_resp := utl_http.get_response(v_http_req);
dbms_output.put_line('Response status_code: ' ||v_http_resp.status_code);
dbms_output.put_line('Response reason_phrase: ' ||v_http_resp.reason_phrase);
dbms_output.put_line('Response http_version: ' ||v_http_resp.http_version);
-- set response code, response http header and response cookies global
v_status_code := v_http_resp.status_code;
utl_http.get_cookies(v_response_cookies);
FOR i in 1..utl_http.get_header_count(v_http_resp) LOOP
utl_http.get_header(v_http_resp, i, v_name, v_hdr_value);
v_hdr.name := v_name;
v_hdr.value := v_hdr_value;
v_hdrs(i) := v_hdr;
END LOOP;
v_request_headers := v_hdrs;
dbms_output.put_line('converting the HTTP response');
BEGIN <>
LOOP UTL_HTTP.read_raw(v_http_resp, v_raw_data, 512);
v_clob := v_clob || UTL_RAW.cast_to_varchar2(v_raw_data);
END LOOP response_loop;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
dbms_output.put_line('End of body in response loop');
UTL_HTTP.end_response(v_http_resp);
WHEN OTHERS THEN
dbms_output.put_line('Unkown error in response loop:'||sqlerrm);
return null;
END;
dbms_output.put_line('Response length: '||LENGTH(v_clob) );
dbms_output.put_line('HTTP response:');
FOR i in 0..CEIL(LENGTH(v_clob) / 512)-1 LOOP
v_line := SUBSTR(v_clob, i * 512 + 1, 512);
dbms_output.put_line('[' || LPAD(i, 2, '0') || ']: ' || v_line);
EXIT WHEN i > 50 - 1;
END LOOP;
dbms_output.put_line('Closing HTTP request and response');
IF v_http_req.private_hndl IS NOT NULL THEN
UTL_HTTP.end_request(v_http_req);
END IF;
IF v_http_resp.private_hndl IS NOT NULL THEN
UTL_HTTP.end_response(v_http_resp);
END IF;
dbms_output.put_line('Converting response text to XML');
return xmltype.createxml(v_clob);
EXCEPTION
WHEN OTHERS THEN
--log the error in some error table
return null;
END make_request;
FUNCTION parse_xml
( p_appl IN VARCHAR2,
p_xml IN XMLTYPE,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2
AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function parse_xml
-- Purpose:
-- This function reads SOAP response content in XML format
-- and parses it to
-- extract certain response values
-- Returns: A variable of varchar2 data type
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
v_response VARCHAR2(32767);
v_err_return NUMBER;
BEGIN
g_app_name := p_appl;
dbms_output.put_line('Parsing result from SOAP response XML');
v_response := dbms_xmlgen.convert(p_xml.extract(p_xpath,p_ns).getstringval(),1);
dbms_output.put_line(v_response);
return v_response;
EXCEPTION
WHEN OTHERS THEN
--log the error in some error table
return null;
END parse_xml;
END PKG_WEBSERVICE_UTL;
/
show errorsI created a function that uses this utility package to schedule a BI Publisher report:
create or replace
FUNCTION fn_schedule_report
( P_REPORT_NM VARCHAR2 ,
P_FORMAT VARCHAR2, P
_BURST NUMBER DEFAULT 1 ) RETURN NUMBER
IS
-------------------------------------------------------------------------------
-----------------------------***************-----------------------------------
-------------------------------------------------------------------------------
-- Function FN_SCHEDULE_REPORT
-- Purpose:
-- This function utilizes the WEB_SERVICE_PKG to schedule / run BIP Publisher
-- reports through web service calls
-- Returns: Job ID if sucess , 0 if failure
-------------------------------------------------------------------------------
-----------------------------***************-----------------------------------
-------------------------------------------------------------------------------
v_response VARCHAR2(32767);
v_ns VARCHAR2(4000);
v_url VARCHAR2(500);
v_job_name VARCHAR2(500);
v_report_name VARCHAR2(500);
v_report_abs_path VARCHAR2(500);
v_report_rel_path VARCHAR2(500);
v_bip_server VARCHAR2(500);
v_username VARCHAR2(50);
v_password VARCHAR2(50);
v_seq NUMBER;
v_soap_env CLOB;
v_xml XMLTYPE;
v_burst NUMBER;
v_err_return NUMBER;
v_port NUMBER;
BEGIN
-- get web service paramerters from the BIP_WS_CONFIG variables table
select trim(val)
INTO v_ns
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'NS';
select trim(val)
into v_report_rel_path
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'REP_ABS_PATH';
select trim(val)
into v_url
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'WSDL_URL';
select trim(val)
into v_username
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'USERNAME';
SELECT trim(val)
into v_bip_server
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'BIP_SERVER';
SELECT trim(val)
into v_port
FROM BIP_WS_CONFIG
WHERE upper(attr) = 'BIP_PORT';
select pkg_webservice_utl.decrypt(trim(val)) val
into v_password
FROM BIP_WS_CONFIG
where upper(attr) = 'PWD';
IF p_burst = 1 THEN
v_burst := 1;
ELSE
v_burst := 0;
END IF;
-- set report name
v_report_name:= p_report_nm;
-- generate a new JOB id
select bip_job_id.nextval
into v_seq
from dual;
v_job_name := substr(v_report_name,instr(v_report_name,'/')+1 )||' #'||v_seq;
v_report_abs_path := v_report_rel_path||v_report_name||'/'||v_report_name||'.xdo';
dbms_output.put_line('absolute path:'|| v_report_abs_path);
v_soap_env := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<pub:scheduleReport xmlns:pub="xmlns=http://'||v_bip_server||':'||v_port||'/oxp/service/PublicReportService">
<scheduleRequest>
<deliveryRequest>
</deliveryRequest>
<reportRequest>
<attributeFormat>'||lower(p_format)||'
<reportAbsolutePath>'||v_report_abs_path||'
</reportRequest>
<userJobName>'||v_job_name||'
<scheduleBurstringOption>'||v_burst||'
</scheduleRequest>
<userID>'||v_username||'
<password>'||v_password||'
</pub:scheduleReport>
</soapenv:Body>
</soapenv:Envelope>';
dbms_output.put_line('calling make_request function');
v_xml := pkg_webservice_utl.make_request
( p_appl => P_APP,
p_url => v_url,
p_envelope => v_soap_env );
v_response := pkg_webservice_utl.parse_xml(p_app, v_xml,'//scheduleReportReturn/text()',v_ns);
-- v_response is expected to be a numeric value "job id" if the report is
-- successfully scheduled, check if value is numeric
IF REGEXP_LIKE (v_response, '^[0-9]*$') THEN
dbms_output.put_line('Job ID:'||v_response||' submitted successfully');
return v_response;
ELSE
dbms_output.put_line('Report Schedule Request Failed');
return 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- log error into some error log
dbms_output.put_line(sqlerrm);
return 0;
END fn_schedule_report; - Finally, I wanted to get the status of the scheduled report. This is a little bit tricky because I could not find a BI Publisher web service operation that returns this information. The only 3 operations that I thought may help were:
- -getScheduledReportStatus and getScheduledReportInfo operations: Only return info about a job that is still in the scheduler. Once the scheduled report is kicked off, it will be removed from the 'scheduler', so null will be returned.
- -getScheduledReportHistoryInfo: Always returned null, I could not figure why is that, but it may be for the same reason above.
So after doing some research, I figured that there are two BI Publisher tables that can be utilized to obtain a scheduled report status:
- XMLP_SCHED_JOB: maintains information about scheduled jobs. Once a report is scheduled an record will be inserted into this table along with information about that job.
- XMLP_SCHED_OUTPUT: maintains information about running jobs (reports) or completed ( with success or failure) jobs. Once a report is kicked off a record will be inserted into this table along with information about that job. This is the table that we need.
So I wrote a function to get the status of an execute report as follows:
create or replace
FUNCTION FN_GET_REPORT_STAT( P_APP VARCHAR2, P_JOB_ID NUMBER ) RETURN NUMBER
IS
------------------------------------------------------------
-----------------------------***************----------------
-------------------------------------------------------------- Purpose:
-- This function checks that status of a report by Job ID
-- Returns: 0 if sucess 'S', 1 if failed 'F', 2 if pending 'C'
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
v_stat CHAR(1) := 'C';
v_timeout NUMBER;
v_duration NUMBER;
v_err_return NUMBER;
BEGIN
IF p_job_id = 0 THEN --invalid job id
return 1;
ELSE
SELECT status
INTO v_stat
FROM xmlp_sched_output
WHERE job_id = p_job_id;
END IF;
IF v_stat = 'S' THEN -- success
dbms_output.put_line('Report job# '||p_job_id||' finished successfully');
return 0;
ELSIF v_stat = 'C' THEN -- Pending
dbms_output.put_line('Report job# '||p_job_id||' is pending');
return 2;
ELSE -- Failure or others like deleted, suspended..etc
dbms_output.put_line('Report job# '||p_job_id||' failed');
return 1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--Job is not started yet, so a record won't exist yet in xmlp_sched_output
dbms_output.put_line('Job# '||p_job_id||' is not started yet and record does not exist yet in xmlp_sched_output');
return 2;
WHEN OTHERS THEN
--log error in some error table
return 1;
END FN_GET_REPORT_STAT;
/
show errorsPutting it all together:
I created a Unix shell script that does the following:
- Call
FN_SCHEDULE_REPROT( P_REPORT_NM => 'Report name',
P_FORMAT => 'Pdf',
P_BURST => 1 )
- Pass the retuned value to function
FN_GET_REPORT_STAT
- If returned status from the
FN_GET_REPORT_STAT function is 0 then return 'success'
- If returned status from the
FN_GET_REPORT_STATfunction is 1 then return 'fail'
- If returned status from the
FN_GET_REPORT_STAT function is 2 then loop every X minutes up to Y minutes (using the Unix sleep function) while status = 2 and check for status again as above ..etc
On Wednesday, I attended Cary Millsap's 
