Вызов хранимой PL/SQL-функции из ADF

Наконец-то разобрался как вызывать хранимые PL/SQL процедуры/функции из ADF, и для тех кто тоже хочет разобраться, но еще этого не сделал (а также для себя, чтобы не забыть), я написал это пошаговое руководство. За основу приложения взят пример из учебника “Developing Rich Web Applications With Oracle ADF” для стандартной демо-схемы БД Oracle HR.

1) В БД добавляем простую функцию (я решил подсчитать сумму зарплаты по департаменту):

create or replace function get_sum_salary_by_dept 
(
  p_dept_id in number 
) return number as 
  v_result number;
begin
  select sum(salary)
    into v_result
    from employees
    where department_id = p_dept_id;
  return v_result;
end get_sum_salary_by_dept;

2) Создаем имплементацию Application Module:
2

3) В получившийся java-класс добавляем общий метод (для удобства я его сделал статическим) запуска хранимых функций:

    // Some constants
        public static int NUMBER = Types.NUMERIC;
        public static int DATE = Types.DATE;
        public static int VARCHAR2 = Types.VARCHAR;

    //Делаем метод статическим и добавляем передачу в него транзакции
    /* В import надо добавить
       import java.sql.CallableStatement;
       import java.sql.SQLException;
       import java.sql.Types;
       import oracle.jbo.JboException;
       import oracle.jbo.server.DBTransaction;
    */
    public static Object callStoredFunction(DBTransaction tr, int sqlReturnType, String stmt,
                                            Object[] bindVars) {
      CallableStatement st = null;
      try {
        // 1. Create a JDBC CallabledStatement  
        st = tr.createCallableStatement(
               "begin ? := "+stmt+";end;",0);
        // 2. Register the first bind variable for the return value
        st.registerOutParameter(1, sqlReturnType);
        if (bindVars != null) {
          // 3. Loop over values for the bind variables passed in, if any
          for (int z = 0; z < bindVars.length; z++) {
            // 4. Set the value of user-supplied bind vars in the stmt
            st.setObject(z + 2, bindVars[z]);
          }
        }
        // 5. Set the value of user-supplied bind vars in the stmt
        st.executeUpdate();
        // 6. Return the value of the first bind variable
        return st.getObject(1);
      }
      catch (SQLException e) {
        throw new JboException(e);
      }
      finally {
         if (st != null) {
            try {
              // 7. Close the statement
              st.close();
            }
            catch (SQLException e) {
               throw new JboException(e);
            }
         }
      }
   }

4) Генерируем класс для объекта сущности:

4

5) В получившийся класс добавляем метод:

public Number callGetSumSalaryByDept() {
      Number n = this.getDepartmentId();
      return (Number)AppModuleImpl.callStoredFunction(getDBTransaction(),
               AppModuleImpl.NUMBER, «get_sum_salary_by_dept(?)», new Object[]{n});
}

 

6) Генерируем классы для объекта представления и строки представления:

6

7) В класс объекта представления (в моем примере DepartmentsViewImpl.java) добавляем метод, вызывающий метод класса сущности:

    public Float getDepartmentSalarySum(Row row){
        DepartmentsViewRowImpl currentRow = (DepartmentsViewRowImpl)row;
        DepartmentsImpl departmentEntity = currentRow.getDepartments();

        Float res = departmentEntity.callGetSumSalaryByDept().floatValue();
        return res;
    }

 

8) В свойствах представления, на вкладке Java, в разделе Client Interface, кликаем кнопку редактирования и, в открывшемся окне, переносим наш метод в Selected:

8

9) С моделью на этом все. Нажимаем Save All и Rebuid All и переходим к интерфейсу

10) Для управления я взял основную страницу приложения (в моем примере DeptEmpPage.jsf). В AppModuleDataControl присутствует наш метод:

11

11) Из палитры компонентов бросаем на страницу кнопку (Button):

11_

12) У отмеченной кнопки нажимаем на выпадающее меню (Button Actions, справа от кнопки) и выбираем Bind to ADF Control в котором выбираем наш метод:

12

После этого откроется окно Edit Action Binding:

12-2

В этом окне, в параметрах, в колонке Value, нужно открыть выпадающий список и выбрать Show El Expression Builder. В открывшемся окне выбрать источник откуда параметр получит строку (в моем примере текущая строка):
12-3

Нажимаем OK везде, где оно есть

13) В свойствах кнопки, в поле Text переименовываем ее в что-либо более человечное Подмигивающая рожица

14) Из Data Control кидаем возвращаемое значение метода на страницу и выбираем Text –> ADF Output Text w/ Label (можем сразу и изменить label):
14

15) Теперь создадим Partial trigger, чтобы после вызова функции, в компоненте Output Text отобразилось возвращаемое значение. Для этого в свойствах текста (Output Text), в разделе Behavior, нажимаем правую кнопку на свойстве Partial Triggers и выбираем Edit. В открывшемся окне переносим, созданную ранее кнопку в Selected:
15
Нажимаем OK

16) Сохраняем и запускаем наше приложение. После запуска нажимаем кнопку и наслаждаемся результатом:
16

Ссылки:
Мой готовый пример
руководство Developing Rich Web Applications With Oracle ADF
раздел документации Developing Fusion Web Applications with Oracle Application Development Framework – 16 Extending Business Components Functionality

Типовая задача с собеседований

На которой я почему-то залипаю (при том что знаю решение):

Есть таблица:

create table t
  (id number,
   field_name varchar2(20),
   string_value varchar2(20),
   number_value number);
  
insert into t (id, field_name, string_value, number_value)
  values (1, ‘Name’, ‘John’, null);
insert into t (id, field_name, string_value, number_value)
  values (1, ‘Surname’, ‘Smith’, null); 
insert into t (id, field_name, string_value, number_value)
  values (1, ‘Salary’, null, 100); 
 
insert into t (id, field_name, string_value, number_value)
  values (2, ‘Name’, ‘Bill’, null);
insert into t (id, field_name, string_value, number_value)
  values (2, ‘Surname’, ‘Dow’, null); 
insert into t (id, field_name, string_value, number_value)
  values (2, ‘Salary’, null, 200);   
commit;

надо ее “развернуть” на 90 градусов.

решение простое:

select t.id,
       max(decode(field_name, ‘Name’, string_value)) Name,
       max(decode(field_name, ‘Surname’, string_value)) Surname,
       max(decode(field_name, ‘Salary’, number_value)) Salary
  from t
  group by t.id;

Отключение триггеров и констрейнтов

Делал это для подгрузки данных в Oracle из дампов. Может кому пригодится.

Сначала создаем 2 таблицы:

create table tmp_dbobj 
  (name varchar2(50), 
   otype varchar2(50));
   
create table tmp_cons_log 
  (cname varchar2(50), 
   ctype varchar2(1), 
   tname varchar2(50), 
   err_text varchar2(2000), 
   cols varchar2(1000), 
   vals varchar2(1000));

Отключаем:

--Выключение констрейнтов
declare
  vDDL varchar2(2000);
begin
  --triggers
  for c_trg in (select * from user_triggers where status = 'ENABLED') loop
    vDDL := 'alter trigger '||c_trg.trigger_name||' disable';
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_trg.trigger_name, 'trigger');
    commit;
  end loop;
  --FK
  for c_cons in (select * from user_constraints 
                 where constraint_type in ('R') and status = 'ENABLED'
                   --and constraint_name not like 'SYS%'
                 ) loop
    vDDL := 'alter table '||c_cons.table_name||' disable constraint '||c_cons.constraint_name;
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_cons.constraint_name, 'constraint');
    commit;
  end loop;
  --UK
  for c_cons in (select * from user_constraints 
                 where constraint_type in ('U') and status = 'ENABLED' 
                 ) loop
    vDDL := 'alter table '||c_cons.table_name||' disable constraint '||c_cons.constraint_name;
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_cons.constraint_name, 'constraint');
    commit;
  end loop;
end;

Данные об отключенных объектах сохраняются в таблицу tmp_dbobj.

Включаем, при этом, если констрейнт не включается происходит проверка, результаты которой (записи на которых констрейнт падает) сохраняются в tmp_cons_log (ВНИМАНИЕ! Тут используется функция – строковый агрегатор, отсутствующая в Oracle):

--проверка и включение констрейнтов
declare
  vSQL varchar2(1000);
  vDDL varchar2(2000);
  vCount number;
begin
  --Уникальные констрайнты
  for c1 in (select a.* 
               from tmp_dbobj a,
                    user_constraints b
               where a.otype = 'constraint'
                 and b.constraint_name = a.name 
                 and b.constraint_type = 'U') loop
    for c_cols in (select constraint_name, table_name, str_agg(column_name) cols, str_agg(column_name||' is null') null_cols
                     from user_cons_columns 
                     where constraint_name = c1.name
                     group by constraint_name, table_name) loop
      c_cols.null_cols := replace(c_cols.null_cols,',',' and ');
      vSQL := 'select 1 from dual where not exists (select '||c_cols.cols||', count(*) cnt from '||
        c_cols.table_name||
        ' where not ('||c_cols.null_cols||') group by '||c_cols.cols||' having count(*)>1 )';
      begin
        execute immediate vSQL into vCount;
        vDDL := 'alter table '||c_cols.table_name||' enable constraint '||c1.name;
        execute immediate vDDL;
        delete from tmp_dbobj where name = c1.name;
        commit;
      exception
        when no_data_found then
          vSQL := 'begin '||
                  'for c1 in ( select '||replace(c_cols.cols,',','||'',''||')||' vals from ('||
                  'select '||c_cols.cols||', count(*) cnt from '||c_cols.table_name||
                  'where not ('||c_cols.null_cols||') group by '||c_cols.cols||' having count(*)>1 )) loop'||
                  'insert into tmp_cons_log (cname, ctype, tname, err_text, cols, vals)'||
                  'values ('''||c_cols.constraint_name||''', ''U'', '''||c_cols.table_name||''', ''Дублирующие значения'', '''||
                  c_cols.cols||''', c1.vals); '||
                  'end loop; commit; end;';
          execute immediate vSQL;
      end;
    end loop;
  end loop;
  --Все остальные (FK)
  for c1 in (select a.*, b.table_name
               from tmp_dbobj a,
                    user_constraints b
               where a.otype = 'constraint'
                 and b.constraint_name = a.name 
                 and b.constraint_type = 'R') loop
    begin
      vDDL := 'alter table '||c1.table_name||' enable constraint '||c1.name;
      execute immediate vDDL;
      delete from tmp_dbobj where name = c1.name;
      commit;
    exception
      when others then
        for c_cols in (select col.constraint_name, col.table_name, r_col.table_name r_table_name,
                              fdc_str_agg(col.column_name) cols, 
                              fdc_str_agg('r.'||r_col.column_name||' = a.'||col.column_name) w_cols,
                              fdc_str_agg('a.'||col.column_name||' is not null') w_cols2
                         from user_constraints con,
                              user_cons_columns col,
                              user_cons_columns r_col
                         where con.constraint_name = c1.name
                           and col.constraint_name = con.constraint_name
                           and r_col.constraint_name = con.r_constraint_name
                           and col.position = r_col.position
                         group by col.constraint_name, col.table_name, r_col.table_name) loop
          vSQL := 'begin '||
                  'for c2 in ( select '||replace(c_cols.cols,',','||'',''||')||' vals from '||
                  c_cols.table_name||' a '||
                  'where not exists (select 1 from '||c_cols.r_table_name||' r '||
                  'where '||replace(c_cols.w_cols,',',' and ')||')'||
                  'and '||replace(c_cols.w_cols2,',',' and ')||
                  ') loop '||
                  'insert into tmp_cons_log (cname, ctype, tname, err_text, cols, vals)'||
                  'values ('''||c_cols.constraint_name||''', ''R'', '''||c_cols.table_name||''', ''Нет родительской записи'', '''||
                  c_cols.cols||''', c2.vals); '||
                  'end loop; commit; end;';
          dbms_output.put_line(vSQL);
          execute immediate vSQL;
        end loop;
    end;
  end loop;
  --триггера
  for c1 in (select * from tmp_dbobj where otype = 'trigger') loop
    vDDL := 'alter trigger '||c1.name||' enable';
    execute immediate vDDL;
    delete from tmp_dbobj where name = c1.name;
    commit;
  end loop;  
end;
Опубликовано в Oracle

Динамический SQL в Oracle. Пример

В рамках программы восстановления утраченных знаний, написал пример использования dbms_sql:

DECLARE
  cSQL NUMBER; —Идентификатор курсора
  vSubjOld NUMBER := 000000; —Старый ID
  vSubjNew NUMBER := 111111; —Новый ID
  vSQL VARCHAR2(1000);
  vID NUMBER;
  vUpdateSQL VARCHAR2(1000);
  vRet NUMBER;
BEGIN
  —Курсор по таблицам с полями которые нужно обработать
  FOR c_tab IN (SELECT DISTINCT tb.TABLE_NAME, cl.COLUMN_NAME
                  FROM user_tab_columns cl,
                       user_tables tb
                  WHERE cl.column_name IN (‘SUBJECT_ID’,’SUBJ_ID’,’DECLARANT_ID’,’PAYER_ID’,’CARRIER_ID’,’BROKER_ID’)
                    AND tb.TABLE_NAME=cl.TABLE_NAME
                    AND tb.TABLE_NAME NOT LIKE ‘EXP_%’
                    AND tb.TABLE_NAME NOT LIKE ‘LEG_%’
                    AND tb.table_name NOT LIKE ‘TMP_%’) LOOP
    dbms_output.put_line(c_tab.table_name||’.’||c_tab.column_name);
    vUpdateSQL := ‘update ‘||c_tab.table_name||’ ‘||
                  ‘set ‘||c_tab.column_name||’ = :1 ‘||
                  ‘where ‘||c_tab.column_name||’ = :2 ‘;
    —открыть курсор
    cSQL:=dbms_sql.open_cursor;
    BEGIN
      vSQL:=’select id ‘||
            ‘from ‘||c_tab.table_name||’ ‘||
            ‘where ‘||c_tab.column_name||’ = :subj_id’;
      dbms_sql.parse(cSQL, vSQL, dbms_sql.native);
      —биндим переменные
      dbms_sql.bind_variable(cSQL, ‘subj_id’, vSubjOld);
      —Определяем колонки
      dbms_sql.define_column(cSQL,1,vID);
      —запускаем
      vRet := dbms_sql.execute(cSQL);
      LOOP
        —В Oracle 11g есть чудесная функция dbms_sql.to_refcursor позволяющая сделать все проще без всех этих условий
        IF dbms_sql.fetch_rows(cSQL)>0 THEN
          dbms_sql.column_value(cSQL,1,vID);
          dbms_output.put_line(‘ID: ‘||vID);
          —Меняем
          EXECUTE IMMEDIATE vUpdateSQL USING vSubjNew, vSubjOld;
        ELSE
          EXIT;
        END IF;
      END LOOP;
      dbms_sql.close_cursor(cSQL);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
        dbms_sql.close_cursor(cSQL);
    END;     
  END LOOP;
  —…
END;

Опубликовано в Oracle

Трассировка в Oracle

Для себя, чтобы не забыть как это делается.

Трассировка SQL:

1) Включаем трассировку ALTER SESSION SET SQL_TRACE = TRUE;
2) Выполняем запрос
3) Выключаем трассировку
4) Профилируем трассировочный файл (каталог в параметре user_dump_dest) командой
tkprof <in_file> <out_file> explain=plsql/oracle sort=<exeela/plsela/fchela>

Трассировка PL/SQL

0) Создаем под SYS’ом трассировочные представления (файл <home>rdbmsadmintracetab)
1)
ALTER SESSION SET PLSQL_DEBUG = TRUE;
2)
ALTER [PROCEDURE | FUNCTION | PACKAGE] <name> COMPILE DEBUG [BODY];
3) DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.<level>
) где level: trace_<all/enabled>_calls, trace_<all/enabled>_exceptions, trace_<all/enabled>_sql, trace_<all/enabled>_lines
4) Выполняем код, который хотим трассировать
5) Выключаем трассировку процедурой DBMS_TRACE.CLEAR_PLSQL_TRACE
6) Смотрим представления SYS.PLSQL_TRACE_RUNS и SYS.PLSQL_TRACE_EVENTS

Опубликовано в Oracle

Борьба с мутантами в Oracle

Странно, что никто из читающих мой блог этого не заметил (а некоторые, я знаю точно, должны были). Дело в том что метод описанный мной в посте Оптимизация “исторического” отчета в Oracle вызывает мутации таблиц (ORA-4091). Далее я, в кратце опишу как с этим бороться (интересующиеся могут посмотреть решение у Тома Кайта тут, суть моего решения такая же, но немного отличается, в частности, я инициализирую пакетную переменную явно, а не присваиваю ей пустую переменную).

Триггер на котором происходит мутация:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  —MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate)),  —Тут предполагается, что функция CalcRest ходит не только по дочерним таблицам, но и по MAIN_TABLE, отсюда и мутации
        rest_date=sysdate
    where id=:new.main_id;
end;

Пример функции CalcRest (очень сильно упрощенный вариант):
create or replace function CalcRest(pid in number, ReportDate in date) return number is
  Result number;
begin
  —Изначальная сумма
  select summa
    into vSumma
    from main_table
    where id=pid;
  —Собираем суммы потраченного
  select sum(summa)
    into vSumma2
    from detail_table
    where main_id=pid;
  Result:=vSumma-vSumma2;
  return(Result);
end CalcRest;

Добавляем пакет (только спецификацию, без тела) с публичной переменной:
create or replace package pck_rest is
  type rest_tbl is table of
number;
 
rests rest_tbl; –Табличная переменная в которую будем сохранять id записей в main_table для которых надо пересчитать
end pck_rest;

Добавляем statement-level триггер в котором инициализируем переменную:
create or replace trigger detail_table1_rest_bfr
  before insert or update or delete on detail_table1
begin
  pck_rest.rests:=pck_rest.rest_tbl(); –Явная инициализация переменной через конструктор
end detail_table1_rest_bfr;

Изменяем row-level триггер:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
declare
  vID number;
  vInitID number;
  function CheckID (pId in number) return number is –функция для проверки дубликатов в табличной переменной, нужно это для того, чтобы потом не пересчитывать и не обновлять одну и туже запись несколько раз
    Result number:=0;
  begin
     for i in 1..pck_rest.rests.count loop
      if pck_rest.rests(i)=pId then
        Result:=pId;
      end if;
    end loop;
    return Result;
  end;

begin
  if inserting then
    vInitID:=:new.main_id;
  else
    vInitID:=:old.main_id;
  end if;
  —Проверяем на дубли

  vID:=CheckID(vInitID);
  —Если дублей не найдено добавляем новое значение

  if vID=0 then
    pck_rest.rests.extend;
    pck_rest.rests(pcg_rest.rests.last):=vInitID;
  end if;
end;

Добавляем сохраняющий statement-level триггер:
create or replace trigger detail_table1_rest_aft
  after insert or update or delete on detail_table1
declare
  vRest number;
begin
  for i in 1..pck_rest.rests.count loop
    —Пересчитываем
    vRest:=CalcRest(pck_rest.rests(i),trunc(sysdate));
    —Сохраняем
    update main_table
        set rest=vRest,
             rest_date=trunc(sysdate)
        where id=pck_rest.rests(i);
  end loop;
end;

Мутанты побеждены!

Опубликовано в Oracle