Working with REF CURSOR Object Variables in Oracle BI Publisher 12c

Having read the article and perceived the understanding of the Oracle BI Publisher product critically, namely, at the time of the then version 11.1.1.7.150120, I would like to note that everything mentioned in the cited article could be done on BI Publisher and entrusted to any non-programming user. Plus, the product had a trial and stand-alone version. The Java API was supported, and the call to the product as a web service was already completed.

A slightly more interesting question that was received from the readers of my blog: how in the 12th version to work with variables of the REF CURSOR type for mass migration from a home-made reporting system to the Oracle BI EE 12c product.

Consider the code that will return REF CURSOR depending on the parameter.

create or replace PACKAGE REF_CURSOR_TEST AS
  TYPE refcursor IS REF CURSOR;
  pCountry  VARCHAR2(10);
  pState   VARCHAR2(20);
  FUNCTION GET( pCountry IN VARCHAR2
    ) RETURN  
REF_CURSOR_TEST.refcursor;
  END;
  
  create or replace PACKAGE BODY REF_CURSOR_TEST AS
  FUNCTION GET(
   pCountry  IN VARCHAR2
   )
  RETURN REF_CURSOR_TEST.refcursor
  IS
  l_cursor REF_CURSOR_TEST.refcursor;
  BEGIN
     IF ( pCountry = 'USA' ) THEN
       OPEN l_cursor FOR 
       SELECT  *
       FROM orders d
       WHERE d.customer_id IN (101,102);
     ELSE
       OPEN l_cursor FOR 
       SELECT * FROM EMPLOYEES;    
     END IF;   
     
     RETURN l_cursor;
   END GET;
  END REF_CURSOR_TEST;

This code is provided in the standard documentation, but it is difficult to use, especially in the limited amount of time resource and the availability of developers of the migrated system.

Further, step by step, it is described how it is easier to implement.

We make a data model with a parameter.

Sample NON-standard SQL call that will return REF CURSOR. The name itself is already causing confusion during implementation.

Type checking, mandatory sample.xml. 7 rows returned from one table.

Type checking, mandatory sample.xml. Returned 108 rows from another table.

An example of auto-generated markup on the first sample.xml.

The first type of table was returned by the server

An example of auto-generated markup on the second sample.xml.

The second type of table is returned in the server

Structure of XSL-FO tags for the first REF CURSOR type.

The first piece of markup on the first XML sample dumped

9. Structure of XSL-FO tags for the second type REF CURSOR.

The second piece of markup on the second XML sample dumped

Setting a condition on the first conditional region.

We put a condition on the first conditional region by the parameter value

Setting a condition on the second conditional region.

We put the value on the second conditional region according to the parameter value

Loading the finished markup into the report.

Don't forget to load the markup into the report — Don’t forget to load the markup into the report

Based on the technology described in the article, several hundreds of different reports were migrated without radical rewriting of the code, with the possibility of quick visual debugging and making changes to the generation of reports.

It is recommended to use it for bulk migrations of “self-written” reporting systems based on Oracle, or for XML extraction procedures that are compatible with the described technique.