Как использовать PL/SQL процедуры и функции в ODI.

Сегодня хочу опубликовать перевод статьи "How to use PL/SQL procedures and functions in ODI", и, заодно, попробовать описанные там методы на практике.

В одном из пакетов, которые я разрабатывал, используется сохраненная процедура СУБД, которая сканирует таблицу в БД и возвращает некоторое значение. Было бы хорошо избавиться от нее, чтобы пакет был самодостаточным. В этом случае и отдавать на промышленную эксплуатацию пришлось бы всего один сценарий, и менять алгоритм процедуры можно было бы без администратора СУБД.

Итак, перевод:

---

Привет народ.
Очень часто я получаю письма с вопросами, как использовать PL/SQL функции и процедуры в ODI. В общем-то, все, что вам нужно это анонимные блоки PL/SQL. И всё.

Ситуация 1.
Вам необходимо вызвать некую PL/SQL процедуру, которая имеет 3 входящих параметра, перед запуском интерфейса. Процедура описывается следующим образом:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 in date)

Решение:
Создайте процедуру ODI с одним шагом. Установите в этом шаге технологию Oracle, выберите правильную схему, в которой находится нужная нам процедура PL/SQL и напишите следующий код:

(прим. пер. #ODI_var1 и #ODI_var2 это переменные ODI, которым необходимо установить нужные значения перед запуском процедуры ODI в пакете.)

begin
MY_PLSQL_PROC(#ODI_var1, #ODI_var2, sysdate);
end

Вуаля! Можно вызывать сохраненные процедуры! И, да, я знаю, что показал простейший вариант. Ок, давайте попробуем что-то более сложное.

Ситуация 2.
Вам необходимо вызвать процедуру, которая принимает 2 входных параметра и 1 выходной параметр, перед вызовом интерфейса. Выходной параметр возвращает код ошибки, если таковая произойдет.
Описание процедуры:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 out varchar2)

Решение:
Создайте процедуру ODI с одним шагом. Установите в этом шаге технологию Oracle, выберите правильную схему, в которой находится нужная нам процедура PL/SQL и напишите следующий код:

Declare
v_ret varchar2(1000);

Begin

MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);
If v_ret is not null then
raise_application_error(-20001, ‘The following error is raised: ‘ || v_ret);
end if;

end;

Этот код позволит вам получить код ошибки в Операторе, и, даже, используя функцию API getPrevStepLog, отправить сообщение с описанием ошибки на электронную почту.
(прим. пер. см. также здесь)

Ок. Можем ли мы еще усложнить пример?

Ситуация 3:
Вам необходимо вызвать процедуру, которая принимает 2 входных параметра и 1 выходной параметр, перед вызовом интерфейса. Возвращенное значение должно быть передано в переменную ODI для дальнейшего использования.
Описание процедуры:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 out varchar2)

Решение:
Создайте ODI процедуру с тремя шагами.
Шаг 1.
Технология Oracle, правильная логическая схема и следуюший код:

create or replace package P$_temp as
pv_ret VARCHAR2(1000);
function get_ret return varchar2;
end P$_temp;

Шаг 2.
Технология Oracle, та же логическая схема и следуюший код:

create or replace package body P$_temp as
function get_ret return varchar2 as

begin
return pv_ret;
end get_ret;

end P$_temp;

Шаг 3.
Технология Oracle, та же логическая схема и следуюший код:

Declare
v_ret varchar2(1000);

Begin

MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);
P$_temp.pv_ret := v_ret;

end;

Теперь, чтобы получить значение в переменной ODI достаточно создать эту переменную, и добавить следующий код в закладку Refresh (используйте ту же логическую схему, что и для процедуры):

select P$_temp.get_ret from dual

Вставьте обновление этой переменной после вызова процедуры в ODI, и получите значение возвращенного PL/SQL процедурой значения в переменную.

Итак, друзья, это всё. И это не так уж и сложно, на самом деле.

---

Рассмотрим, что происходит в третьем примере более подробно. Фактически, первый шаг процедуры создает модуль (package), второй шаг - тело модуля, а на третьем шаге происходит непосредственно вызов необходимой нам процедуры PL/SQL и сохранение возвращенного значения в переменной модуля.

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

С моей точки зрения, хорошей практикой было бы удаление этого модуля после того, как он больше не нужен.

С другой стороны, возможно, имеет смысл для всего ETL процесса определить некий общий модуль, который бы постоянно присутствовал в СУБД, в котором можно было бы предусмотреть все возможные переменные для получения значений из процедур PL/SQL в переменные ODI.