Having read the article and perceived the understanding of the Oracle BI Publisher product critically, namely, at the time of the then version 220.127.116.11.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;
FUNCTION GET( pCountry IN VARCHAR2
create or replace PACKAGE BODY REF_CURSOR_TEST AS
pCountry IN VARCHAR2
IF ( pCountry = 'USA' ) THEN
OPEN l_cursor FOR
FROM orders d
WHERE d.customer_id IN (101,102);
OPEN l_cursor FOR
SELECT * FROM EMPLOYEES;
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.
An example of auto-generated markup on the second sample.xml.
Structure of XSL-FO tags for the first REF CURSOR type.
9. Structure of XSL-FO tags for the second type REF CURSOR.
Setting a condition on the first conditional region.
Setting a condition on the second conditional region.
Loading the finished 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.