2 Replies Latest reply on Apr 23, 2019 2:53 PM by sturgipa

    REPORT_OBJECT_STATUS always 'Scheduled' for BI Publisher Report

    sturgipa

      I'm using sample code from the Oracle BI Publisher Integration section of the Oracle Forms 12c New Features white paper, which includes calling REPORT_OBJECT_STATUS to get the status of the report job from a BI Publisher server.

       

           -- Ask BIP to run the report.

           v_rep := RUN_REPORT_OBJECT(repid);

       

           /* DO SOMETHING ABOUT CHECKING STATUS HERE The call to BIP is asynchronous. Therefore, it will be necessary to check on its status periodically if

                notifying the user is desired. The report_object_status built-in can be used for that check.

                Likely a timer would be used to periodically check the status. Using a loop is not recommended.

       

                Example: rep_status := report_object_status(v_rep);

           */

       

      This suggests that REPORT_OBJECT_STATUS should work similarly to the way it does for Oracle Reports.  But when I call it for an OraBIP type report, it always returns the value "Scheduled". Whether called immediately after RUN_REPORT_OBJECT, or long after the report has been delivered and is showing a status of "Success" in the BI Publisher Report Job History display, REPORT_OBJECT_STATUS returns "Scheduled".

       

      Is there something wrong on my side (code or configuration) or is this expected behavior for BI Publisher reports?   

      (Forms and BI Publisher version are both 12.2.1.3.0)

        • 1. Re: REPORT_OBJECT_STATUS always 'Scheduled' for BI Publisher Report
          Michael Ferrante-Oracle

          There is likely a problem in your code.  There are a variety of ways to make the call to BIP, however because the call is asynchronous the method you use will depend on the desired application behavior.

           

          Here is one way to accomplish the task.  I am copying code from a working form, so if you want to use it, replace the bind variables with your own or use plsql variables.  Because this uses a Forms timer, be careful.  Avoid having the timer expire too often.  In other words, for reports that generally complete very quickly you probably should not expire the timer any more often than every 10 seconds or so.  For longer running reports, the timer probably shouldn't expire but once every few minutes.  Expiring the timer too often will result in excessive network traffic and degrade the user's experience as the application is momentarily interrupted each time it expires.

           

          In the trigger or procedure used to make the initial call, do something like this:

           

          -- THIS IS JUST A SNIPPET.  MORE SETUP CODE WOULD GO ABOVE

          -- Make the request to BIP to run the report.

               v_rep := RUN_REPORT_OBJECT(repid);      

          -- Set v_rep as a global so we can check the status later in a different trigger.

               :GLOBAL.REPORT_STATUS := v_rep;

               -- CHECKING INITIAL STATUS HERE         

          IF v_rep IS NOT NULL Then

                 rep_status     := report_object_status(v_rep);

                 :BI_JOB := v_rep;

                 SYNCHRONIZE;

               -- Start timer so we can check status periodically.

               -- Be sure to terminate timer when the run has completed. Do this in the WTE trigger.

                  If rep_status IN ('Scheduled','Running','Active') Then

                      :BLK_BIP.BI_STATUS := 'Report status: ' || rep_status;                 

                      timer_id := CREATE_TIMER('bistatus_timer', 10000, REPEAT);                 

                  Else

                  -- A status of Failed likely was returned from BIP

                      :BLK_BIP.BI_STATUS := 'Report status: Failed' ; 

                      SET_ITEM_PROPERTY ('BLK_BIP.BI_RUN', ENABLED, PROPERTY_TRUE);                 

                  End if;

          ELSE

          -- Assume v_rep is NULL for some reason

              -- ADDED FOR DEBUGGING - this should probably be done better

                  :BLK_BIP.BI_STATUS := 'Something went wrong.';

                  -- Message ('Something went wrong.');

                  SET_ITEM_PROPERTY ('BLK_BIP.BI_RUN', ENABLED, PROPERTY_TRUE);

                  SYNCHRONIZE;

                  RAISE FORM_TRIGGER_FAILURE;

          End if;

           

          Then in a WHEN-TRIGGER-EXPIRED trigger, you need to check the status and when done, destroy the time and do something now that the report has been generated.

           

          DECLARE

              expired_timer CHAR(50);

              rep_status varchar2(150);

          BEGIN

              CLEAR_MESSAGE; -- Clear message stack to ensure a dialog doesn't appear.

              -- Determine which timer just expired

              expired_timer := GET_APPLICATION_PROPERTY(TIMER_NAME);

          ------ TIMER 1 ----------                     

              IF expired_timer = 'BISTATUS_TIMER' THEN

                  rep_status     := report_object_status(:GLOBAL.REPORT_STATUS);

                  -- Determine if request is healthy and display status

                  IF rep_status LIKE 'Scheduled' THEN 

                      :BLK_BIP.BI_STATUS := 'Report status: ' || rep_status;     

                  ELSIF rep_status LIKE 'Running' THEN

                      :BLK_BIP.BI_STATUS := 'Report status: ' || rep_status;

                  ELSIF rep_status LIKE 'Success' THEN                          

                      SET_ITEM_PROPERTY ('BLK_BIP.BI_RUN', ENABLED, PROPERTY_TRUE);

                      :BLK_BIP.BI_STATUS := 'Report status: ' || rep_status;

                      -- MESSAGE ('Report generated');

                      Delete_Timer('BISTATUS_TIMER');  

                      SYNCHRONIZE;

                      -- Display completed report if access via a virtual path is available.

                      -- This would require the use of outputing to local file system.

                      -- Output should be staged in a directory virtually mapped by web server (e.g. OHS)

                      -- WEB.SHOW_DOCUMENT(' URL TO REPORT OUTPUT ');

                  -- Something went wrong after request was received by BI Server. Tell the user.                         

                  ELSIF rep_status LIKE    'Delivery has Error' THEN                 

                      SET_ITEM_PROPERTY ('BLK_BIP.BI_RUN', ENABLED, PROPERTY_TRUE);                 

                      :BLK_BIP.BI_STATUS := 'Report status: ' || rep_status || '. Check BI job history.';

                      Delete_Timer('BISTATUS_TIMER');  

                  END IF; 

              END IF;         

          End;

           

           

           

          • 2. Re: REPORT_OBJECT_STATUS always 'Scheduled' for BI Publisher Report
            sturgipa

            Thank you for the code and the sanity check, Michael.  

             

            I found that the BIP report status calls had been raising an "Entity not found for the search filter" exception in the bipublisher.log.  When I switched the user running the report from one that uses the ADAuthenticator, to one that uses the Weblogic DefaultAuthenticator, the status calls began returning the expected return values from your code. 

             

             

            Paul Sturgis