Сегодня хочу опубликовать перевод статьи "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.
| < Предыдущая | Следующая > |
|---|
