logo

6 апр. 2014 г.

BI Publisher 11g + R (time series forecasting)

Всем привет!
Сегодня наконец появилось графоманское настроение и я хочу описать решение по использованию среды вычислений R в Oracle BI.
Думаю, не нужно говорить, что из себя представляет R.
Очевидно, что это крутая штука – раз компания Oracle включила ее в платную опцию Advanced Analytics (Oracle R Enterprise).
Крутая она хотя бы потому, что мировым научным сообществом под нее пишется огромное количество бесплатных библиотек, позволяющих анализировать ваши данные. Так в данном сообщении я продемонстрирую использование библиотеки forecast, позволяющей строить прогнозные модели данных временных рядов.
Библиотеку разработал и поддерживает профессор Rob Hyndman

Хочу заметить, что опция Oracle Advanced Analytics платная.
Да, она обеспечивает тесную интеграцию ваших данных (внутри БД Oracle) и вычислительного движка R, что существенно ускоряет обработку действительно больших объемов данных.
Но что если данных, которые нужно обрабатывать, у вас не очень немного. Или денег на оплату опции жалко…
В этом случае можно вполне обойтись интеграцией R-среды и вашей БД аналогично приводимому далее примеру.

А тут будет картинка для привлечения внимания.
А именно – результат работы в виде отчета Oracle BI Publisher, отображающего прогноз среднемесячной температуры на основании накопленной статистики температур.



Основная идея данного примера в том, что внутри БД Oracle с помощью PL/SQL пакета формируется файл с инструкциями R в файловой системе сервера БД;
затем с помощью Java Stored Procedure вызывается на выполнение утилита RScript с параметром-созданным файлом;
среди инструкций R внутри файла содержатся как обращения к БД с помощью специальной библиотеки ROracle,
так и логика по генерации прогнозных значений с помощью библиотеки forecast;
сгенерированные и записанные обратно в БД прогнозные данные визуализируются в Oracle BI Publisher.

Разобью пример на следующие шаги:
1) Загрузка в БД Oracle данных о среднемесячных температурах по регионам России.
2) Установка инструментальной среды R на сервер с БД Oracle. Инсталляция необходимых библиотек.
3) Создание R-скрипта, считывающего данные из БД, генерирующего прогноз и отображающего сформированные данные в виде графика.
4) Создание PL/SQL пакета, формирующего R-скрипт как файл в файловой системе сервера БД, запускающего R-скрипт.
5) Создание отчета в Oracle BI Publisher, визуализирующего сформированные R-скриптом прогнозы.

Документирую все шаги. Сообщение получается длинным, поэтому некоторые части текста скрыты спойлерами.

Загрузка в БД Oracle данных о среднемесячных температурах по регионам России

Открыть спойлер


Установка инструментальной среды R на сервер с БД Oracle. Инсталляция необходимых библиотек

1) Скачиваем с http://cran.rstudio.com/ дистрибутив с инструментальной средой R и базовым набором библиотек.
У меня используется сервер БД Oracle с операционной системой Windows.
Поэтому я скачиваю дистрибутив под Windows.

2) Установим R учитывая битность платформы


3) Откроем установленную инструментальную среду R.
Как я уже говорил, ценность этого инструмента во многом определяется наличием огромного количества библиотек.
Загрузим две, которые потребуются нам в работе: forecast и ROracle
Сделать это можно как напрямую в среде R с помощью команды
install.packages("forecast")
Тогда потребуется указать зеркало, с которого будет производиться закачка библиотеки.
Очевидно, что данный метод предполагает наличие интернет соединения на сервере.


Если же доступа к интернету напрямую с сервера нет, то можно выкачать нужные пакеты и инсталлировать их вручную:
скачать zip-архив с пакетом-библиотекой (например, Windows binary пакета forecast с сайта http://robjhyndman.com/software/forecast/
и загрузить с помощью среды R через пункт меню "Пакеты" – "Установить пакеты из локальных zip-файлов")

Сложнее будет процесс установки пакета ROracle – данная библиотека нужна нам для получения и записи данных БД напрямую из R.
a) Скачаем дистрибутив пакета с http://cran.r-project.org/web/packages/ROracle/index.html
b) Изучаем инструкцию по инсталляции
c) Устанавливаем библиотеку DBI


Согласно пункту инструкции по установке "Windows - Oracle Client (Oracle home based client)" создаем
переменные окружения OCI_INC и OCI_LIB64 (либо OCI_LIB32 на 32-битной платформе)
В моем случае это:
OCI_INC = d:\app\product\11.2.0\dbhome_1\OCI\include
OCI_LIB64 = d:\app\product\11.2.0\dbhome_1\BIN


Не забываем проверить, чтобы в переменной окружения PATH был указан путь bin-каталога БД Oracle:
PATH = d:\app\product\11.2.0\dbhome_1\BIN;…
А также чтобы была корректно установлена переменная окружения ORACLE_HOME:
ORACLE_HOME = d:\app\product\11.2.0\dbhome_1

d) Качаем утилиту RTools (версия 3.1) по адресу http://cran.r-project.org/bin/windows/Rtools/ (она позволяет компилировать R-бинарники их исходных кодов, но нам она потребуется для компиляции и корректной установки библиотеки ROracle)
Устанавливаем RTools с параметрами по умолчанию.
По завершению установки добавляем в начало переменной окружения PATH пути до bin-директорий RTools и его компилятора, а также путь до bin-директории самой среды R:
PATH = c:\Program Files\R\R-3.0.3\bin\x64;c:\Rtools\bin;c:\Rtools\gcc-4.6.3\bin;…

e) Открываем командную строку, переходим в каталог с загруженным архивом исходником библиотеки ROracle (в моем случае это C:\TEMP)
И запускаем команду
R CMD INSTALL ROracle_1.1-11.tar.gz


f) Если все сделано правильно, то при выполнении команды в среде R
library(ROracle)
Не будет выдано никаких сообщений об ошибках.


Создание R-скрипта, считывающего данные из БД, генерирующего прогноз и отображающего сформированные данные в виде графика
Теперь мы готовы обратиться из среды R к БД Oracle за данными с помощью библиотеки ROracle,
с помощью библиотеки forecast сгенерировать прогнозные значения и визуализировать их в виде графика в той же среде R.

Для этого скопируем и вставим в окно среды R следующий набор команд:
library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t where t.observer_num = 26063 and t.year_num >= 2000)
SELECT measure
  FROM (select t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY year_num, month_num
")
data <- fetch(rs)
p_startYear <- 2000
p_startMonth <- 1
p_endYear <- 2008
p_endMonth <- 12
p_numOfPredictedMonths <- 12

dataTS <- ts(data$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
dataFit <- HoltWinters(dataTS)
forecastFit <- forecast(dataFit, p_numOfPredictedMonths)
plot(forecastFit)

где строка
con <- dbConnect(drv,"DWH","DWH","ORCL")
содержит обращение к tns-записи ORCL из файла tnsnames.ora, соответствующего прилинкованной БД с помощью переменных окружения.


Замечу, что для обратной загрузки данных в Oracle следует выполнить следующие команды R:
forecastData <- data.frame(MEASURE = forecastFit$mean, MONTH_KEY = c(time(forecastFit$mean)) )
dbWriteTable(con, "TEMPERATURE_FORECAST", forecastData, append = TRUE)

Где первая команда генерирует набор данных (таблицу) из 2 столбцов – столбца MEASURE с прогнозными значениями вложенного ряда mean переменной forecastFit и столбца MONTH_KEY.
А вторая команда – загрузка набора данных в таблицу TEMPERATURE_FORECAST по открытому ранее соединению с БД с опцией Append (догрузки).

Создание PL/SQL пакета, формирующего R-скрипт как файл в файловой системе сервера БД, запускающего R-скрипт
Итак, теперь мы умеем из R среды обращаться к БД Oracle за данными, на основании полученных данных строить прогнозы временных рядов
(я специально не углубляюсь в детали библиотеки forecast – на сайте автора очень подробно все документировано) .

Но основной сущностью рассматриваемой здесь интеграции является БД Oracle.
Именно в ней хранятся исходные данные и туда же должны загружаться обработанные с помощью R (прогнозные) данные.
Значит, мы должны уметь из Oracle формировать R-скрипты с нужными наборами команд как файлы в файловой системе,
запускать эти скрипты на выполнение и удалять их по завершению работы.

Для этого я предлагаю использовать следующие пакеты:
PKG_UNLOADER (копия пакета Unloader Тома Кайта)
и
PKG_JAVA_COMMON_TOOLS (набор java stored procedure по java source fndjavafile)

Вот так будет выглядеть PL/SQL-код, генерирующий R-скрипт и запускающий его на выполнение:
declare
  l_dir_path       varchar2(512);
  l_file_separator varchar2(4);
  l_rows           number;

  l_query varchar2(32000);

  l_command varchar2(512);
  l_output  DBMS_OUTPUT.chararr;
  l_lines   INTEGER := 1000;

BEGIN

  --вычислим путь к директории TEMP_DIR
  select DIRECTORY_PATH
    into l_dir_path
    from all_directories
   where DIRECTORY_NAME = 'TEMP_DIR';

  l_file_separator := PKG_JAVA_COMMON_TOOLS.get_file_separator;

  --создадим универсальный R-скрипт для генерации прогнозных значений
  l_query := 'select 
''
library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t where t.observer_num = 26063 and t.year_num >= 2000)
SELECT measure
  FROM (select t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY year_num, month_num
")
data <- fetch(rs)
p_startYear <- 2000
p_startMonth <- 1
p_endYear <- 2008
p_endMonth <- 12
p_numOfPredictedMonths <- 12

dataTS <- ts(data$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
dataFit <- HoltWinters(dataTS)
forecastFit <- forecast(dataFit, p_numOfPredictedMonths)
forecastData <- data.frame(MEASURE = forecastFit$mean, MONTH_KEY = c(time(forecastFit$mean)) )
dbWriteTable(con, "TEMPERATURE_FORECAST", forecastData, append = TRUE)
'' as txt from dual';

  l_rows := PKG_UNLOADER.run(p_query      => l_query,
                             p_town       => '',
                             p_tname      => '',
                             p_mode       => 'truncate',
                             p_dbdir      => 'TEMP_DIR',
                             p_filename   => 'R_forecast',
                             p_separator  => '',
                             p_enclosure  => '',
                             p_terminator => '',
                             p_ctl        => 'NO',
                             p_header     => 'NO');

  --вызываем созданный ранее R-скрипт (необходимо включить путь до Rscript в PATH)
  l_command := 'Rscript "' || l_dir_path || l_file_separator || 'R_forecast.dat"';

  PKG_JAVA_COMMON_TOOLS.host_command(l_command);
  --для отладки    
  --DBMS_OUTPUT.get_lines(l_output, l_lines);
  --FOR i IN 1 .. l_lines LOOP
  --  DBMS_OUTPUT.put_line(l_output(i));
  --END LOOP;    

  --удалим R-скрипт
  l_rows := PKG_UNLOADER.remove(p_dbdir    => 'TEMP_DIR',
                                p_filename => 'R_forecast.dat');
end;
/

После его выполнения запрос к таблице с прогнозными значениями возвращает следующие данные:



Создание отчета в Oracle BI Publisher, визуализирующего сформированные R-скриптом прогнозы
Наконец сведем все шаги воедино.
В виде отчета BI Publisher, который с помощью before report триггера будет выполнять PL/SQL код по генерации прогнозных данных;
визуализировать сформированные данные в виде графика;
с помощью after report триггера очищать сформированные данные.

Так как отчет может выполняться параллельно несколькими пользователями – следует разграничивать используемые в нем данные.
Для этого создадим sequence в БД.
create sequence R_FORECAST_SEQ;

Пересоздадим таблицу с результатами (порядок следования столбцов важен!):
create table TEMPERATURE_FORECAST
(
  measure       VARCHAR2(50),
  month_key     NUMBER,
  sess_key      NUMBER,
  observer_num  NUMBER    
)

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

Создадим PL/SQL пакет PKG_R_FORECAST с функциями before_report и after_report.
create or replace package PKG_R_FORECAST as

  P_YEAR_BEG      varchar2(30);
  P_YEAR_END      varchar2(30);
  P_OBSERVER_NUM  varchar2(30);

  function get_sess_key return integer;

  function before_report(P_YEAR_BEG      in varchar2,
                         P_YEAR_END      in varchar2,
                         P_OBSERVER_NUM  in varchar2) return boolean;

  function after_report return boolean;

end PKG_R_FORECAST;
/
create or replace package body PKG_R_FORECAST as

  G_SESS_KEY integer;

  function get_sess_key return integer as
  begin
    return G_SESS_KEY;
  end get_sess_key;

  function before_report(P_YEAR_BEG      in varchar2,
                         P_YEAR_END      in varchar2,
                         P_OBSERVER_NUM  in varchar2) return boolean as  
  
    l_dir_path       varchar2(512);
    l_file_separator varchar2(4);
    l_rows           number;
  
    l_query varchar2(32000);
  
    l_command varchar2(512);
    l_output  DBMS_OUTPUT.chararr;
    l_lines   INTEGER := 1000;
  
  BEGIN
  
    G_SESS_KEY := R_FORECAST_SEQ.Nextval;
  
    --вычислим путь к директории TEMP_DIR
    select DIRECTORY_PATH
      into l_dir_path
      from all_directories
     where DIRECTORY_NAME = 'TEMP_DIR';
  
    l_file_separator := PKG_JAVA_COMMON_TOOLS.get_file_separator;
  
    --создадим универсальный R-скрипт для генерации прогнозных значений
    l_query := 'select 
''
library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t 
   where ((coalesce(null, ' || P_OBSERVER_NUM || ') is null) or (t.observer_num in (' || P_OBSERVER_NUM || '))) 
     and t.year_num >= ' || P_YEAR_BEG || '
     and t.year_num <= ' || P_YEAR_END || ')
SELECT observer_num, measure
  FROM (select t.observer_num, t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.observer_num, t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.observer_num, t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.observer_num, t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.observer_num, t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.observer_num, t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.observer_num, t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.observer_num, t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.observer_num, t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.observer_num, t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.observer_num, t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.observer_num, t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY observer_num, year_num, month_num
")
data <- fetch(rs)
p_startYear <- ' || P_YEAR_BEG || '
p_startMonth <- 1
p_endYear <- ' || P_YEAR_END || '
p_endMonth <- 12
p_numOfPredictedMonths <- 12
for (i in 1:length(unique(data$OBSERVER_NUM))) { 
   iterObserver <- unique(data$OBSERVER_NUM)[i]
   iterObserverData <- data[data$OBSERVER_NUM == iterObserver,]
   iterObserverDataTS <- ts(iterObserverData$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
   iterObserverDataFit <- HoltWinters(iterObserverDataTS)
   iterObserverForecastFit <- forecast(iterObserverDataFit, p_numOfPredictedMonths)
   iterObserverForecastData <- data.frame(MEASURE = iterObserverForecastFit$mean, MONTH_KEY = c(time(iterObserverForecastFit$mean)) )
   iterObserverForecastData["SESS_KEY"] <- ' || G_SESS_KEY || '
   iterObserverForecastData["OBSERVER_NUM"] <- iterObserver
   dbWriteTable(con, "TEMPERATURE_FORECAST", iterObserverForecastData, append = TRUE)
}
'' as txt from dual';
  
    l_rows := PKG_UNLOADER.run(p_query      => l_query,
                               p_town       => '',
                               p_tname      => '',
                               p_mode       => 'truncate',
                               p_dbdir      => 'TEMP_DIR',
                               p_filename   => 'R_forecast_' || G_SESS_KEY,
                               p_separator  => '',
                               p_enclosure  => '',
                               p_terminator => '',
                               p_ctl        => 'NO',
                               p_header     => 'NO');
  
    --вызываем созданный ранее R-скрипт (необходимо включить путь до Rscript в PATH)
    l_command := 'Rscript "' || l_dir_path || l_file_separator ||
                 'R_forecast_' || G_SESS_KEY || '.dat"';
  
    PKG_JAVA_COMMON_TOOLS.host_command(l_command);
    --для отладки    
    --DBMS_OUTPUT.get_lines(l_output, l_lines);
    --FOR i IN 1 .. l_lines LOOP
    --  DBMS_OUTPUT.put_line(l_output(i));
    --END LOOP;    
  
    --удалим R-скрипт
    l_rows := PKG_UNLOADER.remove(p_dbdir    => 'TEMP_DIR',
                                  p_filename => 'R_forecast_' || G_SESS_KEY ||
                                                '.dat');
  
    return true;
  
  END before_report;

  function after_report return boolean as
  
  BEGIN
  
    delete from TEMPERATURE_FORECAST t where t.sess_key = G_SESS_KEY;
  
    COMMIT; --!!!!
  
    return true;
  
  END after_report;

end PKG_R_FORECAST;
/

Создадим сам отчет в BI Publisher




Текст запроса набора данных MAIN:
with t as
 (select t.*
    from TEMPERATURE_DATA t
   where ((coalesce(null, :P_OBSERVER_NUM) is null) or
         (t.observer_num in (:P_OBSERVER_NUM)))
     and t.year_num >= :P_YEAR_BEG
     and t.year_num <= :P_YEAR_END),
t_src as
 (SELECT observer_num,
         year_num || lpad(month_num, 2, '0') as month_key,
         measure
    FROM (select t.observer_num,
                 t.year_num,
                 1              as month_num,
                 t.ind_1        as measure
            from t
          union all
          select t.observer_num, t.year_num, 2 as month_num, t.ind_2
            from t
          union all
          select t.observer_num, t.year_num, 3 as month_num, t.ind_3
            from t
          union all
          select t.observer_num, t.year_num, 4 as month_num, t.ind_4
            from t
          union all
          select t.observer_num, t.year_num, 5 as month_num, t.ind_5
            from t
          union all
          select t.observer_num, t.year_num, 6 as month_num, t.ind_6
            from t
          union all
          select t.observer_num, t.year_num, 7 as month_num, t.ind_7
            from t
          union all
          select t.observer_num, t.year_num, 8 as month_num, t.ind_8
            from t
          union all
          select t.observer_num, t.year_num, 9 as month_num, t.ind_9
            from t
          union all
          select t.observer_num, t.year_num, 10 as month_num, t.ind_10
            from t
          union all
          select t.observer_num, t.year_num, 11 as month_num, t.ind_11
            from t
          union all
          select t.observer_num, t.year_num, 12 as month_num, t.ind_12 from t)),
t_rslt as
 (select f.observer_num,
       trunc(f.month_key) ||
       lpad(round((f.month_key - trunc(f.month_key)) * 12 + 1), 2, '0') as month_key,
       round(to_number(f.measure,
                 '9999999999D99999999999999999999999999999',
                 'NLS_NUMERIC_CHARACTERS=''.,'''),2) as measure
  from TEMPERATURE_FORECAST f
   where f.sess_key = PKG_R_FORECAST.get_sess_key
   )
select s.observer_num, s.month_key, s.measure, 'Source' as measure_type
  from t_src s
union all  
select to_char(r.observer_num),
       r.month_key,
       r.measure,
       'Forecast' as measure_type
  from t_rslt r


12 комментариев:

  1. Добрый день!
    Занимаюсь попытками запустить предрассчет данных для отчета BIP.
    Собственно, есть огромная проблема - не отрабатывает запуск пакетированной функции с параметрами.
    Параметр описан, дефолт задан.
    Собственно, не проходит view data в data model.
    Говорит - Failed to load XML.
    Внутри лога - PLS-00302: component 'PERIOD_DATE_START' must be declared
    Очень нужна помощь знающего человека - в какую сторону копать.
    Заранее огромное спасибо!

    ОтветитьУдалить
  2. Добрый день!
    Попробуйте создать в спецификации пакета, который объявлен как defaulePackage для отчета, переменные для ВСЕХ параметров отчета.

    ОтветитьУдалить
    Ответы
    1. Еще раз добрый день!
      Огромное спасибо за оперативный ответ!
      К сожалению зря прождал все выходные пока включат сервер - не сработало. То есть пакетные переменные описал, описание вызова функции в BIP обновил, но снова ошибка:
      Failed to load XML.
      Правда, теперь в логе жалоба только на одну из двух переменных:
      ORA-06550: line 4, column 42:
      PLS-00201: identifier 'PERIOD_DATE_BEGIN' must be declared
      А в прошлый раз(без задания пакетных переменных) было:
      ORA-06550: line 2, column 18:
      PLS-00302: component 'PERIOD_DATE_END' must be declared
      ORA-06550: line 2, column 1:
      PL/SQL: Statement ignored
      ORA-06550: line 3, column 18:
      PLS-00302: component 'PERIOD_DATE_BEGIN' must be declared
      ORA-06550: line 3, column 1:
      PL/SQL: Statement ignored

      Удалить
    2. Что-то я совсем потерялся - сейчас все проходит, в смысле - без ошибки, но переменные пустые, хотя в BIP дефолты для переменных проставлены.
      Определял - что с данными - простым селектом из дуала:
      select 1 from dual
      where :PERIOD_DATE_BEGIN > to_date('01.01.1990','dd.mm.yyyy')
      and :PERIOD_DATE_END > to_date('01.01.1990','dd.mm.yyyy')
      union all
      select 2 from dual
      where :PERIOD_DATE_BEGIN < to_date('01.01.1990','dd.mm.yyyy')
      and :PERIOD_DATE_END < to_date('01.01.1990','dd.mm.yyyy')
      union all
      select 3 from dual
      where :PERIOD_DATE_BEGIN is null
      and :PERIOD_DATE_END is null

      Собственно вопрос - куда копать?)

      Удалить
    3. Вы не могли бы прислать мне на почту код пакета и скриншоты модели данных отчета, открытой в редакторе (скриншот вкладки "Набор данных", "Параметры" и общей вкладки) ?

      Удалить
    4. 1) Очень рекомендую создать в системе логирующий пакет - https://sites.google.com/site/obi2ru/PKG_LOG.zip?attredirects=0&d=1
      С его помощью можно отслеживать что именно происходит в PL/SQL пакете.
      Пример вызова:
      PKG_LOG.info('Мой супер пакет', 'Получено значение PERIOD_DATE_BEGIN равное ' || PERIOD_DATE_BEGIN);

      2) Подозреваю, что вы определили в спецификации пакета переменные PERIOD_DATE_BEGIN и PERIOD_DATE_END с типом DATE.
      КРАЙНЕ рекомендую определить их с типом varchar2(100), а также создать в спецификации пакета переменные
      PERIOD_DATE_BEGIN_DT Date;
      PERIOD_DATE_END_DT Date;
      которые заполнять в процедуре пакета, являющейся beforeReport-триггером отчета:
      PERIOD_DATE_BEGIN_DT := to_date(PERIOD_DATE_BEGIN, 'YYYY/MM/DD');
      (форматную маску поймете когда будете логирующим пакетом писать в лог значения параметров)

      Удалить
    5. 1)
      К сожалению пока логирование не отладил - весь в мыле, сегодня на права в ГАИ сдаю)
      Но увидел жалобу на RESULT_CACHE - ее надо прописать как глобальную?

      2) Сделал, в смысле поменял тип на varchar2, добавил преобразование -

      3) http://tempfile.ru/file/3095823
      Вдогонку - третий слайд с результатами функции BEFORE_REPORT
      Результат для BERFORE_REPORT_2 - такой же

      Удалить
    6. С логирующим пакетом вам будет намного проще. поверьте!

      Удалите параметры ваших beforeReport-функций в пакете.
      Внутри функций ссылайтесь на переменные в спецификации пакета - это и будут ваши параметры.
      Не забудьте сделать to_date для строковых переменных.
      Форматная маска будет, скорее всего, MM-dd-yyyy.

      Удалить
    7. Прошу прошения, сидя в ГАИ совершенно забыл что база-то у нас десятка) Так что вариант с result_cache не подходит((
      Но логирование таки сделал для этой процедуры через таблицу, и проблему решил - оказывается дело было в том, что BIP передавал параметры следующим образом:
      09-ОКТ-09 12.00.00,000000000 AM
      И оттуда все проблемы и росли)
      Огромное спасибо за помощь!)

      С уважением, Иван.

      Удалить
  3. Здравствуйте, в нашей конторое только начинаем работать с ORACLE Publisher и не знаем многий функционал, подскажите пожалуйста есть ли какая возможнотьс генерить отчет и отправлять , только в том случае если есть данные, а если запрос ничего не возвращает ничего собственно и не делать ? или в паблишер в целом так невозможно работать

    ОтветитьУдалить
    Ответы
    1. Такая возможность есть (смотрите триггеры)

      Удалить