PL/SQL + BI Publisher + Customer Calendar

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 errors

I 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 errors

Putting 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