Wednesday, August 31, 2011

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: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
ATTRVAL
NSxmlns: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_URLhttp://1.2.3.4:9704/xmlpserver/services/PublicReportService
USERNAMEbip_service_acct
PWDD486ACCFD
BIP_SERVER1.2.3.4
BIP_PORT9999

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

14 comments:

Kris said...

um...yeah.

Javier Viquez said...

Hey great idea, I trying to use, but had some problems.

Response status_code: 500
Response reason_phrase: Internal Server Error

can you mail me to send more details? jviquez@yahoo.com

Javier Viquez said...

Can you email me to ask more detail, I'm tryng to make someone like your excellent idea but had problems.

Sam Khalaf said...

Sure Javier, I will be glad to help.
I will shoot you an email and we can discuss your issue, please provide as much details as you have..

Sam

Anonymous said...

Hi,
This example is exactly matches with my requirement. There are some gaps in understanding the parameter values in function pkg_webservice_utl.make_request.
I'm getting error ORA-24247 now and trying to solve it. COuld you please mail me at imanindita@gmail.com

Anindita said...

Could you please guide how to hanld error-
Response status_code: 500
Response reason_phrase: Internal Server Error.

I've corrected ora-24247 and now this response I'm getting.
My email id: imanindita@gmail.com.
Regards.

Romano said...

The code in your post has been very helpful. Thank you for sharing it.

a said...

I am using the same sample procedure but it is not working I am getting v_response null from
pkg_webservice_utl.parse_xml(p_app, v_xml,'//scheduleReportReturn/text()',v_ns); can you tell me the exact value for Attribute NS in BIP_WS_CONFIG table

Anonymous said...

Is this code working. Can you please provide me the sample code.

ann said...

it's very urgent for fulfill my requirment. I used the same sample code but some how it is not working. your help would be really appreciate.

Ann said...

Any update?

Sam Khalaf said...

All, Sorry I was not following this thread. Chet brought this to my attention.

Anonymous & Ann:
I do not have the code up and running at this moment, but I am pretty sure I posted the code after it was tested and proven to work.
Could you be more specific? the "code does not work" is not the perfect starting point to troubleshoot any issues you may have. Could you please provide error code(s) that you are receiving?

a?
Please look at the Config table above, it shows the value of the NS param that I used. Remember that you need to make some changes based on your BIP environment and server IP/hostname (where the BIP webservices API is hosted)

Anonymous said...

Hello,
I tried to use pl/sql code and when i am trying to schedule report it is giving error. Can somebody help me on this issue?

-------------
declare
v_soap_env clob;
v_xml XMLTYPE;
v_response varchar2(32767);
p_app varchar2(50) := 'anjan';
v_url varchar2(100) := 'http://xmlns.oracle.com/oxp/service/v11/PublicReportService';
p_format varchar2(3) := 'pdf';
p_abspath varchar2(300) := '/test/All_In_Fee_Report/All_In_Fee_Report.xdo';
p_job varchar2(50) := 'All_In_Fee_Report';
p_user varchar2(30) := 'Administrator';
p_paswd varchar2(6) := 'SADMIN';
begin
v_soap_env := '






'||lower(p_format)||'
'||p_abspath||'

'||p_job||'
'||1||'

'||p_user||'
'||p_paswd||'


';
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()','xmlns="http://xxxxxx:9704/xmlpserver/services/PublicReportService"');
IF REGEXP_LIKE (v_response, '^[0-9]*$') THEN
dbms_output.put_line('Job ID:'||v_response||' submitted successfully');
else
dbms_output.put_line(v_response);
dbms_output.put_line('Report Schedule Request Failed');
END IF;
end;
--------------

Output Details:
anonymous block completed
v_env_lenb= 724
Setting proxy
Setting timeout
Begining HTTP request
Parsing result from SOAP response XML

Report Schedule Request Failed

Rahul "The Saviour" said...

This article was very helpful in implementing the BI publisher report scheduling using API call through PLSQL. Thanks a lot for your help.