PL / SQL via dblink

    On metalink, every one said there's no solution ...
    Oracle can't do that ...


    Have you ever implemented custom solutions? What about Oracle? I would like to consider the use of techniques that allow you to better learn the principles of the DBMS, and together provide convenience for the developer.


    It is much more convenient to carry out the development of database applications in a single space, and transfer the results over the landscape of the system in the background, automatically recording the changes made.

    Example of updating on the development server

    Prologue

    Have you met harmful DBAs? Have you worked with such people? In fact, both sides (Developer vs. DBA), achieve the same result, the health of the system, but from different sides. However, when the system expands, is decentralized, but maintains integrity in the implementation, then maintaining the consistent state of the software can begin to cause serious inconvenience. There are development, testing, "productive" servers - and all this is wonderful, but they all need to be updated.
    Oracle has tools that seem to be similar to the one in question:
    Audit
    Oracle Streams
    Alert
    But they all perform other functions. Some provide an audit of changes, others synchronize data. And I would like to act more transparently, for example:

    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;
    /
    


    Now all my actions are duplicated on the 'prod' server. Or maybe even like this:

    begin
        UpdateFilials;
    end;
    /
    


    And, say, seven servers created table “A”. Is it great? Then - let's go.

    Training


    Connect to the database on behalf of a user who has sufficient privileges for the following actions:
    connect system/***@orcl
    Connected.
    select banner from v$version;
    BANNER                                                                          
    --------------------------------------------------------------------------
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    


    It is assumed that the user performing the updates should not have access to the update system itself, however, just like the system itself is not tied to the target scheme, therefore, it can be used universally. Therefore, create a new user:
    create user upd identified by pass; 
    User created.
    


    Since the article is not about restricting the rights of new users:
    grant dba to upd;
    Grant succeded.
    connect upd/pass
    Connected.
    


    Omitting the discussion about the research, I’ll say that the most difficult thing was to get the anonymous PL / SQL block, which was given in the example above. Naturally, some actions ultimately give rise to others, for example, all the same block from the example will execute insert, but in reality it may not! After all, it will be executed on another server. Therefore, it will be the anonymous PL / SQL block that interests us, not the consequences. The public synonym V $ SQL or the view V_ $ SQL to which it refers, stores all the queries that were executed on the server. Let's try to find our goal in it:

    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.
    


    Indeed, it is my anonymous block that is located where it should be. Of course, executing my example, SQL_ID will be different, but does it belong to me? Check:
    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.
    


    No, it doesn’t belong, the optimizer sees that such an expression has already been executed, and returns the already registered SQL_ID. We mark our research in the fields and continue the study:
    connect upd/pass
    Connected.
    


    I managed to find the completed block, but I would like to know who performed it, or rather, to find out exactly what I was doing it at a certain point in time. Another view of V_ $ SESSION can help me with this:
    select sql_id, prev_sql_id from v $ session;
    Here it is necessary to clarify that the synonym v $ session provides access to VIEW, and access for the user is organized by the command:
    grant select on v_ $ session to upd;
    The point here is that the view type v_ $ session is FIXED VIEW, so giving rights to its synonym is prohibited. However, if you give out rights to a synonym, say tables, the rights themselves are issued to a table, and NOT to a synonym.
    So what is there with the request? Oh yes, you need to limit the selection to the current session:
    select sid, sql_id, prev_sql_id from v$session where sid = userenv('sid');
           SID SQL_ID        PREV_SQL_ID
    ---------- ------------- -------------
            95 54mqd9bcxw8nh 753c9f808k8hh
    


    How is it with you, it doesn’t work? Neither SQL_ID nor PREV_SQL_ID contain the previously found identifier 753c9f808k8hh? Naturally! SQL_ID contains the identifier of the newly executed query, and PREV_SQL_ID most likely stores the identifier of the query:
    select sql_id, prev_sql_id from v$session;
    

    I hope that the reader sequentially executed the requests as I quoted them and therefore did not immediately find what was expected. In order to make sure that the result will be as indicated, it is necessary to execute an anonymous block sequentially and a request for a presentation. Be that as it may, I believe that another stage of the research has been completed. Now we have the source text of the anonymous block, and we know that it was executed by us.
    Unfortunately, I don’t like the solution that automates the linking, because after a certain moment, is it necessary to remember all possible anonymous blocks executed by the user, and there is no representation of the user storing the session history? Or does it exist? However, I have not found it at the moment, I propose the following approach. Let's create a table that will store the identifier of the session that is interested in listening and a job that polls this table and saves the session history.

    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.
    


    “What are the names of the fields in the second table?” I would ask. Despite the fact that this does not have a good excuse, but trying to minimize the load created by the job, I got to the presentation of a higher level sys.x_ $ ksuse which contains sufficient information about the target session. Making a bookmark for the future, a few more useful fields will be saved in the table, in addition to the necessary: ​​KSUSENUM (SID) and KSUSESQI (SQL_ID). It will be good to put the body of the job in an external procedure, and not add it to the package in order to avoid errors if the package is not valid:
    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.
    


    The idea of ​​processing is to write to the session history only then, and only what is performed by the user in update mode. Now you can create a job, listen to the user session and check the result:
    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.
    

    As you can see from the result of the query to V $ SQL, the anonymous block got into the log table written there with a job. For the test, I turned to the KSUSEPSI column of the log (previous request) due to the fact that I had to execute commands to clear the session table at the time of listening. In the future, this will also turn out to be some drawback, but we will exclude the “interruption" of listening from the result set executed on the remote server.
    Now you need to assemble the DLL commands that can also be executed during the upgrade. But here there is a contradiction, why collect DDL - if the job collects them? Unfortunately, he will not collect them, since DDL is not a request, and therefore will not be reflected in v $ session. For these purposes, Oracle provides DBMS level triggers that you can use. We write the DDLs to be executed in a new table, and by analogy with the job, create a procedure and a trigger that executes it:

    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.
    


    An additional table, and its type (GLOBAL TEMPORARY to store data until disconnected), are selected from the following considerations: a job that collects session information works in a session different from the one that runs update scripts, therefore, requests written to it would become inaccessible to the session of the performer; Provide Oracle to clean the table after the upgrade. DDL trigger, fires in the same session in which DDL is executed, therefore, in this case, you can write directly to the buffer table; saving table data after a commit is due to the fact that DDL performs a silent commit.
    It is important to pay attention to the fact that the procedure is declared with the AUTHID DEFINER directive, which allows recording actions with UPD user rights, which may be larger than those of the caller. Next, the DDL length is determined and the buffers are stored in the CLOB field.
    The trigger is executed after the (AFTER) DDL, which implies the successful execution of the command, before writing to the buffer.
    Summing up the research, now there are all possible types of operations to be performed on the updated base and you can proceed to the final stage - the update tool.

    Implementation


    I don’t like publications that after a long discussion and preparation end with something like: “And now, (if not a fool) you should be clear how to complete the remaining garbage.” Of course, there are fools here - no, everyone has long understood what needs to be done next. But I will give my current implementation, despite the fact that it can be considered a beta version. Now a lot of code, and then an explanation:
    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.
    


    To the previously created tables, two more were added, one of which is used to sight successful updates, and the second to set up a connection to a remote Oracle database.
    The package is declared with the AUTHID CURRENT_USER directive - which will lead to the execution of the package procedures with the rights of the user calling the package. Now, about all the procedures of the package:
    procedure SetSession(u_sid number, u_remove boolean default false)- using an autonomous transaction, writes the current session identifier to the initiating listening table.
    function JobNumber return number- Gets the listener id
    procedure JobRun- checks for the existence of a job.
    procedure SetChannel(u_alias varchar2)- gets the settings of the remote connection and writes them to the local variables of the package.
    procedure CancelUpdate- erases settings and clears temporary tables.
    procedure BeginUpdateChannel(u_alias varchar2)- combines the calls of preparatory procedures and starts listening.
    procedure PrepareUpdateChannel- finishes listening and appends the session requests collected by the job to the buffer in the table. For my own needs, not trying too hard, I discard the DML, select, and service commands encountered during testing, as well as the call to the PrepareUpdateChannel procedure, which is also recorded in the session log.
    procedure DropObject- an auxiliary procedure for cleaning.
    procedure ExecRemote- block execution on a remote server. This procedure implements one of the key points of the mechanism. Here the dbms_sql package is called on the remote server.
    procedure EndUpdateChannel- application updates. And about this separately.

    I will make a reservation that the first implementation option was somewhat simpler than the one given here. The fact is that dynamic sql does not provide the ability to execute blocks longer than varchar2 (32767 characters or bytes, depending on the declaration). Although this is not entirely true. Locally, dbms_sql allows this, but the LOB field cannot be transferred to the remote server. Many thanks to Tom Kite (https://asktom.oracle.com/pls/apex/f?p=100:11:59::::P11_QUESTION_ID:950029833940), who knows how to forward LOB between remote servers. I was pleasantly surprised by the fact that the first method that he brings to me was implemented through dbms_lob.substr, which I looped off the CLOB field from the UPD $ BUF table. The second method that he offers for this task looks like this: create a table on the current host, with the rights of the caller to update the user and perform remote table creation on the reverse connection to the current database. Here you can point out several shortcomings in the given implementation, namely: making a possible error if the user causing the update is not equal to the dblink authorizing one, because he will not have select rights from the temporary table; Create and delete tables dynamically. Another problem that I have already encountered when “flipping” CLOB between servers was the error “ORA-02046: distributed transaction already begun”. Apparently, during testing, a suspended session occurred, or the identifier of the remote connection remained open. I could not simulate this situation again, but in order to avoid repetitions, you need to think about placing the call: dbms_session.
    To execute the code from the copied table, I tried to generate an anonymous block containing essentially the same code, but this led to an execution error at the recursive level (I did not save the error number, something like Error on SQL level 2), but the procedure was created allowed to solve this last problem.

    For the end user, you can create wrapping procedures with the AUTHID DEFINER directive and give the right to call them to the right users:
    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;
    

    Also popular now: