Category: Oracle

Делал это для подгрузки данных в 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;

В рамках программы восстановления утраченных знаний, написал пример использования 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;