Pull to refresh

PL/SQL через dblink

Reading time 15 min
Views 62K
On metalink, every one said there's no solution…
Oracle can't do that ...


Приходилось ли Вам реализовывать нестандартные решения? А в Oracle? Мне бы хотелось рассмотреть использование техник, позволяющих лучше узнать принципы работы СУБД, а в совокупности предоставляющие удобство для разработчика.


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

Пример выполнения обновления на сервере разработки

Пролог

Вы встречали вредных DBA? А работали с такими? На самом деле, обе стороны (Developer vs. DBA), добиваются одного результата, работоспособности системы, но с разных сторон. Впрочем, когда система расширяется, децентрализуется, но сохраняет целостность в реализации, то поддержка консистентного состояния программной оснастки может начать доставлять серьезные неудобства. Появляются серверы разработки, тестирования, «продуктива» — и все это замечательно, но всех их нужно обновлять.
В Oracle есть инструменты казалось бы похожие на рассматриваемый:
Audit
Oracle Streams
Alert
Но все они выполняют другие функции. Одни, обеспечивают аудит изменений, другие синхронизируют данные. А мне бы хотелось действовать более прозрачно, вот например:

connect developer@dev

begin
  UpdateServer(‘prod’);
end;
/

create table a as
select * from dual;

declare
  v_id char:='Y';
  v_cnt number;
begin
  select count(rownum) into v_cnt from a;
  if v_cnt = 1 then
    insert into a values (v_id);
  end if;
end;
/

begin
  CommitUpdate;
end;
/


Теперь все мои действия продублированы на сервере ‘prod’. А может быть, даже так:

begin
    UpdateFilials;
end;
/


И, скажем, семь серверов создали таблицу «A». Здорово? Тогда – поехали.

Подготовка


Выполним соединение с базой данных от имени пользователя имеющего достаточные привилегии для последующих действий:
connect system/***@orcl
Connected.

select banner from v$version;
BANNER                                                                          
--------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


Предполагается что пользователь, выполняющий обновления, не должен иметь доступа к самой системе обновления, впрочем, как и сама система не привязывается к целевой схеме, следовательно, может использоваться универсально. Поэтому создадим нового пользователя:
create user upd identified by pass; 
User created.


Поскольку статья не об ограничении прав новых пользователей:
grant dba to upd;
Grant succeded.

connect upd/pass
Connected.


Опустив рассуждения о проводимых изысканиях, скажу, что самым сложным оказалось получение анонимного PL/SQL блока, который приводился в примере выше. Естественно, одни действия в конечном итоге порождают другие, так например, всё тот же блок из примера, выполнит insert, но на самом деле может быть и не выполнит! Ведь выполняться он будет на другом сервере. Поэтому нас будет интересовать именно анонимный PL/SQL блок, а не последствия. Паблик синоним V$SQL или представление V_$SQL на которое он ссылается, хранит все запросы, выполнявшиеся на сервере. Попробуем найти в нём нашу цель:

set linesize 90

begin
    raise_application_error(-20000, 'Find me');
end;
/

select sql_id from v$sql where sql_text like '%error(-20000, ''Find%';

SQL_ID       
-------------
753c9f808k8hh
1 row selected.


Действительно, именно мой анонимный блок находится там где положено. Конечно же, SQL_ID выполняя мой пример, будет другой, но принадлежит ли он мне? Проверим:
connect system/***
begin
    raise_application_error(-20000, 'Find me');
end;
/

select sql_id from v$sql where sql_text like '%error(-20000, ''Find%';

SQL_ID       
-------------
753c9f808k8hh
1 row selected.


Нет, не принадлежит, оптимизатор видит, что ранее такое выражение уже выполнялось, и возвращает уже зарегистрированный SQL_ID. Пометим на полях свои изыскания, и продолжим изучение:
connect upd/pass
Connected.


Выполнившийся блок, удалось найти, но мне бы хотелось узнать, кем он выполнен, а точнее узнать, что именно я выполнял его в определенный момент времени. Другое представление V_$SESSION, сможет мне в этом помочь:
select sql_id, prev_sql_id from v$session;
Тут нужно пояснить, что синоним v$session предоставляет доступ к VIEW, а доступ для пользователя организуется командой:
grant select on v_$session to upd;
Дело тут в том, что тип представление v_$session является FIXED VIEW, поэтому давать права на его синоним – запрещено. Впрочем, если выдавать права на синоним, скажем таблицы, сами права выдаются на таблицу, а НЕ на синоним.
Так что же там с запросом? Ах да, нужно ограничить выборку текущей сессией:
select sid, sql_id, prev_sql_id from v$session where sid = userenv('sid');
       SID SQL_ID        PREV_SQL_ID
---------- ------------- -------------
        95 54mqd9bcxw8nh 753c9f808k8hh


Как это у Вас, не получается? Ни SQL_ID ни PREV_SQL_ID – не содержат найденного ранее идентификатора 753c9f808k8hh? Естественно! SQL_ID содержит идентификатор только что выполненного запроса, а PREV_SQL_ID скорее всего хранит идентификатор запроса:
select sql_id, prev_sql_id from v$session;

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

CREATE TABLE UPD.UPD$SESSION_TARGETS
(SID NUMBER);
Table created.

CREATE TABLE UPD.UPD$SESSION_DATA
(
   KSUSENUM   NUMBER,
   KSUSEUNM   VARCHAR2 (30 BYTE),
   KSUSEMNM   VARCHAR2 (64 BYTE),
   KSUSESQI   VARCHAR2 (13 BYTE),
   KSUSEPSI   VARCHAR2 (13 BYTE)
);
Table created.


«Что за названия полей второй таблицы?» — спросил бы я. Несмотря на то, что это не имеет веского оправдания, но пытавшись минимизировать нагрузку создаваемую джобом, я добрался до представления более высокого уровня sys.x_$ksuse которое содержит достаточную информацию о целевой сессии. Делая закладку на будущее, в таблицу будут сохраняться еще несколько полезных полей, помимо необходимых: KSUSENUM (SID) и KSUSESQI (SQL_ID). Хорошо будет вынести тело джоба во внешнюю процедуру, и не добавлять ее в пакет, дабы избежать ошибок, если пакет будет не валиден:
CREATE OR REPLACE procedure UPD.UPD$JobTask is
    v_cnt number;
begin
    loop
        select count(rownum)
          into v_cnt
        from upd.upd$session_targets;
        
        if (v_cnt = 0) then
            select count(ksusenum)
              into v_cnt
              from upd.upd$session_data;
            if (v_cnt > 0) then
                execute immediate 'truncate table upd.upd$session_data';
            end if;
            continue;            
        end if;
        
        
        INSERT INTO upd.upd$session_data (KSUSENUM, KSUSEUNM, KSUSEMNM, KSUSESQI, KSUSEPSI)
             SELECT ksusenum, ksuseunm, ksusemnm, ksusesqi, ksusepsi
               FROM sys.x_$ksuse
              WHERE ksusenum IN (SELECT ust.sid FROM upd.upd$session_targets ust)
              MINUS
             SELECT ksusenum, ksuseunm, ksusemnm, ksusesqi, ksusepsi
               FROM upd.upd$session_data;
        commit;      
    end loop;
end UPD$JobTask;
/
Procedure created.


Идея обработки, заключается в том, что бы записывать в историю сессии только тогда, и только то что выполняется пользователем в режиме обновления. Теперь можно создать джоб, прослушать сессию пользователя и проверить результат:
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'begin /*UPD$SESSION_JOB*/  
    UPD$JobTask;
end;'
   ,next_date => SYSDATE
   ,interval  => 'SYSDATE + 1/1444'
   ,no_parse  => FALSE
  );
  COMMIT;
END;
/
PL/SQL procedure successfully completed.

insert into upd.upd$session_targets values (userenv('sid'));
1 row created.

begin
    raise_application_error(-20000, 'Find me');
end;
/

Error at line 3
ORA-20000: Find me
ORA-06512: at line 2

truncate table upd.upd$session_targets;

Table truncated.

select KSUSEPSI from upd.upd$session_data;

KSUSEPSI     
-------------
753c9f808k8hh
1 row selected.

select sql_text from v$sql where sql_id = '753c9f808k8hh';

SQL_TEXT                                                                                  
----------------------------------------------------------------------------
begin     raise_application_error(-20000, 'Find me'); end;                                
1 row selected.

Как видно из результата запроса к V$SQL анонимный блок попал в таблицу лога записанный туда джобом. Для теста, я обращался к столбцу KSUSEPSI лога (предыдущему запросу) ввиду того, что мне приходилось выполнять команды очистки таблицы сессии в момент прослушивания. В дальнейшем, это так же окажется некоторым недостатком, но «обрывание» прослушивания мы исключим из результирующего набора выполняемого на удаленном сервере.
Теперь необходимо собрать DLL команды, которые так же могут выполняться при обновлении. Но здесь происходит противоречие, зачем собирать DDL – если их соберет джоб? К сожалению, он их не соберет, так как DDL не является запросом, а следовательно в v$session не отразится. Для этих целей Oracle предоставляет триггеры уровня СУБД, которыми можно воспользоваться. Выполняемые DDL, запишем в новую таблицу, а по аналогии с джобом, создадим процедуру и триггер выполняющей её:

CREATE GLOBAL TEMPORARY TABLE upd.UPD$BUF
(
   ALIAS_OBJ   VARCHAR2 (500 CHAR),
   SQLTEXT     CLOB,
   OBJNAME     VARCHAR2 (30 BYTE)
)
ON COMMIT PRESERVE ROWS;
Table created.

CREATE OR REPLACE PROCEDURE upd.T_PROC_UPD$DDL AUTHID DEFINER AS
    osuser varchar2(30);
    machine varchar2(64);
    cnt number;
    V_SQL_OUT ORA_NAME_LIST_T;
    V_SQL_STATEMENT CLOB;
    V_NUM NUMBER;
    v_sqlerrm varchar2(2000);
BEGIN    
    
    SELECT count(rownum)
      INTO cnt
      FROM upd$session_targets ust
     WHERE ust.sid = userenv('sid');
     
    if cnt = 0 then
        return;
    end if;
    
    V_NUM := ORA_SQL_TXT(V_SQL_OUT);
    FOR I IN 1 .. V_NUM LOOP
        V_SQL_STATEMENT := V_SQL_STATEMENT || V_SQL_OUT(I);
    END LOOP;
    
    
    INSERT INTO UPD$BUF (ALIAS_OBJ, SQLTEXT, OBJNAME)
         VALUES (NULL, V_SQL_STATEMENT, ora_dict_obj_name); 
    
    EXCEPTION WHEN OTHERS THEN
        raise_application_error(-20000, SQLERRM);
        
END T_PROC_UPD$DDL;
/      
Procedure created.

CREATE OR REPLACE TRIGGER upd.T_UPD$DDL
AFTER DDL
ON DATABASE
BEGIN
    T_PROC_UPD$DDL;
END;
/

Trigger created.


Дополнительная таблица, и её тип (GLOBAL TEMPORARY хранить данные до дисконнекта), выбраны из следующих соображений: джоб собирающий информацию о сессии, работает в сессии отличной от той, которая выполняет скрипты обновления, следовательно запросы записанные в нее стали бы недоступны сессии исполнителя; предоставить Oracle очистку таблицы после обновления; DDL триггер, срабатывает в той же сессии, в которой выполняется DDL, следовательно в этом случае записывать можно сразу в таблицу буфера; сохранение данных таблицы после коммита обусловлено тем, что DDL выполняет молчаливый коммит.
Важно обратить внимание на то, что процедура объявлена с директивой AUTHID DEFINER, которая позволит записывать действия с правами пользователя UPD, которые могут быть большими, чем у вызывающего. Далее производится определение длинны DDL и сохранение буферов в поле CLOB.
Триггер выполняется после (AFTER) DDL, что подразумевает успешное выполнение команды, до записи в буфер.
Подводя итоги изысканий, теперь имеются все возможные типы операций, подлежащие выполнению на обновляемой базе и можно приступить к завершающему этапу – инструменту выполнения обновлений.

Реализация


Мне не нравятся публикации, которые после длительного рассуждения и подготовки заканчиваются чем то вроде: «А теперь, (если не дурак) тебе должно быть ясно как доделать оставшуюся фигню». Конечно же тут дураков – нет, все давно поняли что нужно сделать дальше. Но я приведу свою текущую реализацию, несмотря на то, что её можно считать бета версией. Теперь много кода, а затем пояснения:
CREATE SEQUENCE UPD.UPD$SEQ_LOG
   START WITH 0
   MAXVALUE 9999999999999999999999999999
   MINVALUE 0
   NOCYCLE
   NOCACHE
   NOORDER;

Sequence created.


CREATE SEQUENCE UPD.UPD$SEQ_REV
   START WITH 0
   MAXVALUE 9999999999999999999999999999
   MINVALUE 0
   NOCYCLE
   NOCACHE
   NOORDER;
Sequence created.

CREATE TABLE UPD.UPD$LOG
(
   ID_LOG           NUMBER,
   DAT_LOG          DATE,
   FQDN_UNAME_OBJ   VARCHAR2 (1000 CHAR),
   ALIAS_OBJ        VARCHAR2 (500 CHAR),
   SQL_TEXT         CLOB,
   ID_REV           NUMBER,
   SQLERRM_LOG      VARCHAR2 (2000 CHAR)
);
Table created.

CREATE TABLE UPD.UPD$SERVERS
(
   ALIAS_OBJ             VARCHAR2 (500 CHAR),
   DBLINK_OBJ            VARCHAR2 (500 CHAR),
   USERNAME              VARCHAR2 (64 CHAR),
   CALLBACK_DBLINK_OBJ   VARCHAR2 (500 CHAR)
);
Table created.

CREATE OR REPLACE PACKAGE UPD$ AUTHID CURRENT_USER AS

  procedure BeginUpdateChannel(u_alias varchar2);
  procedure PrepareUpdateChannel;  
  procedure EndUpdateChannel;
  procedure CancelUpdate;

END UPD$;
/

Package created.
CREATE OR REPLACE PACKAGE BODY UPD$ AS
  
  pkg_active_alias varchar2(500);
  pkg_prepared_alias varchar2(500):=null;
  pkg_session number:=null;
  pkg_dblink varchar2(500):=null;
  pkg_callback_dblink varchar2(500):=null;
  
  
  procedure SetSession(u_sid number, u_remove boolean default false) as
    pragma autonomous_transaction;
    l_sid_count number;
  begin
    
    if u_sid is null then
        raise_application_error(-20550, 'Needless to set');    
    end if;
    
    select count(rownum)
      into l_sid_count 
      from upd.upd$session_targets ust
     where ust.sid = u_sid;
     
     if l_sid_count = 0 then
        insert into upd.upd$session_targets (sid) values (u_sid);        
        commit;
        pkg_session:=u_sid;
     elsif u_remove then
        delete from upd.upd$session_targets ust where ust.sid = u_sid; 
        commit;
        pkg_session:=null;
     end if;
  end SetSession;
  
  
  function JobNumber return number as
    l_jobid number;
  begin
    SELECT a.job
      INTO l_jobid
      FROM dba_jobs a
     WHERE a.what like '%/*UPD$SESSION_JOB*/%';
     return l_jobid;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN return 0;
        WHEN OTHERS THEN raise;
  end JobNumber;
  
  
  procedure JobRun as    
    --TODO: run job is it stopped
    --v_cnt number:=0;
    v_job number;
  begin
    v_job:=JobNumber;
    if v_job = 0 then
        raise_application_error(-20560, 'Unable to find updating job');
    end if;
    
    --select count(rownum)
    --  into v_cnt
    --  from dba_jobs_running a where a.job = v_job;
    --if v_cnt = 0 then
    --    dbms_job.run(v_job);
    --    commit;
    --end if;
  end JobRun;
   
  
  procedure SetChannel(u_alias varchar2) as 
  begin
    SELECT dblink_obj, callback_dblink_obj
      INTO pkg_dblink, pkg_callback_dblink
      FROM upd.upd$servers a
     WHERE upper(a.alias_obj) = upper(u_alias)
       AND upper(username) = upper(USER);
    
    exception when no_data_found then
        raise_application_error(-20501, 'Unable set channel. Alias '||u_alias||' not found');
        when others then 
        raise_application_error(-20500, 'Unable set channel for alias '||u_alias||SQLERRM);
  end SetChannel; 
  
  
  procedure CancelUpdate is
  begin    
    pkg_active_alias:=null;
    pkg_prepared_alias:=null;   
    pkg_session:=null;
    pkg_dblink:=null;
    execute immediate 'truncate table upd.upd$buf';
    delete from upd.upd$session_targets ust where ust.sid = userenv('sid');
  end CancelUpdate;
    
  
  procedure BeginUpdateChannel(u_alias varchar2) is    
  begin
    if pkg_active_alias is not null then
        raise_application_error(-20500, 'Unable begin update channel. Alias '||u_alias||' allready active.');
    end if;
    SetChannel(u_alias);        
    execute immediate 'truncate table upd.upd$buf';
    JobRun;
    SetSession(userenv('sid'), false);    
    pkg_active_alias:=u_alias;
    pkg_prepared_alias:=null;
   
  end BeginUpdateChannel;
  
  
  procedure PrepareUpdateChannel is
  begin
    if pkg_prepared_alias is not null then
        raise_application_error(-20500, 'Already prepared');
    end if;

    if pkg_active_alias is null then
        raise_application_error(-20500, 'Needless to prepare');
    end if;        
        
    INSERT INTO upd.upd$buf (ALIAS_OBJ, SQLTEXT)
         SELECT pkg_active_alias, b.sql_fulltext
           FROM (select distinct ksusenum, ksusesqi from upd.upd$session_data) a,
                sys.v_$sql b
          WHERE a.ksusenum = pkg_session
            AND a.ksusesqi = b.sql_id
            AND (trim(upper(sql_text)) not like 'INSERT%' and
                 trim(upper(sql_text)) not like 'UPDATE%' and
                 trim(upper(sql_text)) not like 'DELETE%' and
                 trim(upper(sql_text)) not like 'SELECT%' and
                 trim(upper(sql_text)) not like '%UPD$%' and
                 trim(upper(sql_text)) not like '%AW_TRUNC_PROC%' and
                 trim(upper(sql_text)) not like '%XDB.XDB_PITRIG_PKG%' and
                 sql_text not like '%:B%' and
                 sql_text not like '%:1%'
                );
                
    SetSession(pkg_session, true);                  
    pkg_prepared_alias:=pkg_active_alias;
    pkg_active_alias:=null;                
  end PrepareUpdateChannel;
    
  
  procedure DropObject(object_name varchar2) is
    l_owner varchar2(30);
    l_type varchar2(19);
    l_purge varchar2(6);
  begin
    SELECT OWNER, OBJECT_TYPE, CASE when object_type = 'TABLE' then ' purge' else null end
      INTO l_owner, l_type, l_purge
      FROM all_objects
     WHERE upper(object_name) = upper(DropObject.object_name);
    execute immediate 'drop '||l_type||' '||DropObject.object_name||l_purge;
    exception when no_data_found then null;
              when others then raise;
  end DropObject;
  
 
 procedure ExecRemote(u_sql varchar2) is
    c number;
    r number;
 begin
    execute immediate 'begin :1:=dbms_sql.open_cursor@'||pkg_dblink||'(); end;' using out c;
    execute immediate 'begin dbms_sql.parse@'||pkg_dblink||'(:1, :2, dbms_sql.native); end;' using in c, in u_sql;
    execute immediate 'begin dbms_sql.close_cursor@'||pkg_dblink||'(:1); end;' using in out c;
 end ExecRemote;
 
  
  procedure EndUpdateChannel is    
    l_alias varchar2(5000);
    l_dblink varchar2(500);
    l_sql varchar2(32000);
    l_osuser varchar2(30);
    l_machine varchar2(64);
    l_log_id number:=null;
    l_rev_id number:=null;
    l_error_stack varchar2(30000):=null;
    l_tmp_tab varchar2(500):=DBMS_RANDOM.STRING('', 8);
    l_tmp_proc varchar2(500); 
  begin
    if (pkg_active_alias is null) and (pkg_prepared_alias is null) then
        raise_application_error(-20500, 'Needless to end');
    end if;
    
    if pkg_prepared_alias is null then
        raise_application_error(-20500, 'You must execute PrepareUpdateChannel first');
    end if;
    
    l_tmp_proc:='up_$proc_'||l_tmp_tab;
    l_tmp_tab:='up_$tab_'||l_tmp_tab;
    l_alias:=pkg_prepared_alias;
    pkg_prepared_alias:=null;        
    begin

       execute immediate 'create table '||l_tmp_tab||' as select ub.* from upd.upd$buf ub';
       execute immediate 'grant select on '||l_tmp_tab||' to '||USER;

       l_sql:='create table '||l_tmp_tab||' as select * from upd.'||l_tmp_tab||'@'||pkg_callback_dblink;
       ExecRemote(l_sql);

       DropObject(l_tmp_tab);

       l_sql:='create or replace procedure '||l_tmp_proc||' is
           c number;
           r number;
           l_objname varchar2(30);
           l_sqlforerr varchar2(200);
           l_error_stack varchar2(30000);
       begin
         for c_exec in (select * from '||l_tmp_tab||') loop
           l_objname:=c_exec.objname;
           l_sqlforerr:=dbms_lob.substr(c_exec.sqltext, 200);
           c := dbms_sql.open_cursor();
           dbms_sql.parse(c, c_exec.sqltext, dbms_sql.native);
           r := dbms_sql.execute(c);
           dbms_sql.close_cursor(c);
           l_objname:=null;
         end loop;
         execute immediate ''drop table '||l_tmp_tab||' purge'';
       exception when others then
         execute immediate ''drop table '||l_tmp_tab||' purge'';
           if l_objname is not null then
             select replace(wm_concat(text), '','', chr(10))
               into l_error_stack
               from user_errors
              where name = l_objname;
           end if;                
         raise_application_error(-20000, ''Obj: ''||l_objname||chr(10)||''SQLERRM: ''||SQLERRM||chr(10)||''Show errors: ''||l_error_stack||chr(10)||''Code: ''||l_sqlforerr);
       end;';
       ExecRemote(l_sql);

       begin
         execute immediate 'begin '||l_tmp_proc||'@'||pkg_dblink||'; end;';
         commit;
       exception when others then
           l_error_stack:=SQLERRM;                
       end;

       l_sql:='drop procedure '||l_tmp_proc;
       ExecRemote(l_sql);

       if l_error_stack is not null then
         raise_application_error(-20590, null);
       end if;
     exception          
          when others then
          l_dblink:=pkg_dblink;          
          CancelUpdate;
          DropObject(l_tmp_tab);
          if sqlcode = -20550 then
            raise;          
          elsif sqlcode = -20590 then 
               raise_application_error(-20555, 'Error when executing remote SQL'||chr(10)||
                                               'Compilation errors: ['||l_error_stack||']');
          else
            raise;
          end if;
     end;
    
    SELECT distinct osuser, machine 
      INTO l_osuser,
           l_machine 
      FROM v$session
     WHERE sid = USERENV('sid');    
    
    l_rev_id:=UPD$SEQ_REV.NEXTVAL;
    
    INSERT INTO upd.upd$log (ID_LOG, DAT_LOG, FQDN_UNAME_OBJ, ALIAS_OBJ, SQL_TEXT, ID_REV, SQLERRM_LOG)
         SELECT upd$seq_log.nextval,
                sysdate,
                l_machine||'\'||l_osuser,
                pkg_prepared_alias,
                sqltext,
                l_rev_id,
                null 
           FROM upd.upd$buf ub;
     
    execute immediate 'truncate table upd.upd$buf';

  end EndUpdateChannel;
  
  
  procedure ErrorEnumAccess is
  begin
    null;
  end ErrorEnumAccess;
  

END UPD$;
/

Package body created.


К ранее созданным таблицам, добавились еще две, одна из которых используется для визирования успешно выполненных обновлений, а вторая для настройки соединения с удаленной базой Oracle.
Пакет объявлен с директивой AUTHID CURRENT_USER – что приведет к выполнению процедур пакета, с правами пользователя вызывающего пакет. Теперь, о всех процедурах пакета:
procedure SetSession(u_sid number, u_remove boolean default false) – используя автономную транзакцию, записывает текущий идентификатор сессии в таблицу инициирующую прослушивание.
function JobNumber return number – получает идентификатор джоба прослушивателя.
procedure JobRun – проверяет существование джоба.
procedure SetChannel(u_alias varchar2) – получает настройки удаленного соединения и записывает их в локальные переменные пакета.
procedure CancelUpdate – стирает настройки и очищает временные таблицы.
procedure BeginUpdateChannel(u_alias varchar2) – объединяет вызовы подготовительных процедур и начинает прослушивание.
procedure PrepareUpdateChannel – завершает прослушивание и дописывает в таблицу буфер собранные джобом запросы сессии. Я для собственных нужд, не слишком стараясь, отбрасываю при этом DML, select и встреченные в процессе тестирования служебные команды, а так же вызов процедуры PrepareUpdateChannel который тоже записывается в лог сессии.
procedure DropObject – вспомогательная процедура для очистки.
procedure ExecRemote – выполнение блока на удаленном сервере. Эта процедура реализует один из ключевых моментов механизма. Тут пакет dbms_sql вызывается на удаленном сервере.
procedure EndUpdateChannel – применение обновления. И об этом отдельно.

Оговорюсь, что первый вариант реализации, был несколько проще, того который приведен здесь. Дело в том, что динамический sql не предоставляет возможности выполнять блоки длинной более varchar2 (32767 символов или байт, в зависимости от объявления). Хотя это не совсем так. Локально, dbms_sql позволяет это, но LOB поле невозможно передать на удаленный сервер. Большое спасибо Тому Кайту (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:950029833940), который знает, как пробрасывать LOB между удаленными серверами. Я был приятно удивлен, тем что первый способ, который он приводит, у меня был реализован, через dbms_lob.substr, которым я в цикле обрезал поле CLOB из таблицы UPD$BUF. Второй способ, который он предлагает, для этой задачи выглядит как: создать на текущем хосте таблицу, с правами вызывающего обновление пользователя и выполнить удаленное создание таблицы по обратному соединению с текущей базой данных. Здесь можно указать на несколько недочетов, в приведенной реализации, а именно: допущение возможной ошибки, если пользователь вызывающий обновление, не равен авторизовавшемуся по dblink, ведь он не будет иметь прав на select из временной таблицы; создание и удаление таблиц динамически. Еще одной проблемой, с которой я уже сталкивался, при «перебрасывании» CLOB между серверами — была ошибка «ORA-02046: distributed transaction already begun». По всей видимости, во время тестирования, возникла подвисшая сессия, или идентификатор удаленного соединения остался открытым. Я повторно не смог смоделировать эту ситуацию, но во избежание повторений, нужно подумать о размещении вызова: dbms_session.close_database_link(pkg_dblink);
Для выполнения кода из скопированной таблицы, я пробовал генерировать анонимный блок, содержащий по сути тот же самый код, но это приводило к ошибке выполнения на рекурсивном уровне (номер ошибки я не сохранил, что то вроде Error on SQL level 2), но создание процедуры позволило решить и эту последнюю проблему.

Для конечного пользователя можно создать процедуры обертки, с директивой AUTHID DEFINER и раздать права вызывать их нужным пользователям:
create or replace procedure ChannelUpdate(u_alias varchar2) AUTHID DEFINER is
begin
    upd$.BeginUpdateChannel(u_alias);
end ChannelUpdate;


create or replace procedure ChannelPrepare AUTHID DEFINER is
begin
    upd$.PrepareUpdateChannel;
end ChannelPrepare;

  
create or replace procedure ChannelApply AUTHID DEFINER is
begin
    upd$.EndUpdateChannel;
end ChannelApply;

create or replace procedure ChannelCancel AUTHID DEFINER is
begin
    upd$.CancelUpdate;
end ChannelCancel; 


grant execute on ChannelUpdate to developer;

grant execute on ChannelPrepare to developer;

grant execute on ChannelApply to developer;

grant execute on ChannelCancel to developer;


grant select on upd$log to developer;
Tags:
Hubs:
+3
Comments 9
Comments Comments 9

Articles