Работа с объектными переменными типа REF CURSOR в Oracle BI Publisher 12c

Прочитав статью и восприняв понимание продукта Oracle BI Publisher критически, а именно, на момент существовавшей тогда версии 11.1.1.7.150120, хочется заметить, что все, указанное в цитируемой статье можно было сделать на BI Publisher и доверить любому непрограммирующему пользователю. Плюс к этому, продукт имел trial и stand-alone версию. Поддерживался API на Java, уже был доделан вызов продукта как web-сервиса.

Чуть более интересный вопрос, который был получен от читателей моего блога: как в 12-й версии работать с переменными типа REF CURSOR для массовой миграции с самодельной отчетной системы на продукт Oracle BI EE 12с.

Рассмотрим код, который вернет REF CURSOR в зависимости от параметра.

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;

Данный код приводится в стандартной документации, но вызывает сложность в использовании, особенно в ограниченном количестве ресурса времени и наличии разработчиков мигрируемой системы.

Далее по шагам расписано, как его проще имплементировать.

1. Делаем модель данных с параметром.

Параметр для процедуры вызова.

2. Образец вызова NON-standard SQL, который вернет REF CURSOR. Само название уже вызывает непонимание при имплементации.

Вызов процедуры с REF CURSOR.

4. Проверка типов, обязательное задание sample.xml. Вернулось 7 строк из одной таблицы.

7 строк возврата из процедуры

5. Проверка типов, обязательное задание sample.xml. Вернулось 108 строк из другой таблицы.

108 строк возврата из процедуры

6. Пример автосгенерированной разметки на первом sample.xml.

Первый тип таблицы вернул сервер

7. Пример автосгенерированной разметки на втором sample.xml.

Второй тип таблицы вернулся в сервере

8. Структура XSL-FO тегов у первого типа REF CURSOR.

Первая часть разметки на первом выгруженном образце XML

9.Структура XSL-FO тегов у второго типа REF CURSOR.

Вторая часть разметки на втором выгруженном образце XML

10. Задание условия на первом условном регионе.

Ставим условие на первый условный регион по значению параметра

11. Задание условия на втором условном регионе.

Ставим значение на второй условный регион по значению параметра

12. Подрузка готовой разметки в отчет.

Не забываем загрузить разметку в отчет

На основании описанной в статье технологии, было мигрировано несколько сотен различных отчетов без радикальной переделки кода, с возможностью быстрой визуальной отладки и внесением измений в формирование отчетов.

Ее рекомендуется использовать при массовых миграциях "самописных" отчетных систем, базирующихся на Oracle, либо процедурах извлечения XML, совместимыми с описанным технологическим приемом.