PL / SQL via dblink

On metalink,
Oracle can't do that ...

Did you have to implement non-standard solutions? And in Oracle? I would like to consider the use of techniques that allow you to better understand the principles of the DBMS, and in the aggregate they 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 across the system's landscape in the background, automatically registering the changes made.

Sample update on the development server
')

Prologue

Have you encountered harmful DBA? Did you work with such? In fact, both sides (Developer vs. DBA) achieve the same result, system performance, but from different sides. However, when the system is expanded, decentralized, but preserves the integrity of the implementation, then maintaining the consistent state of the software tool can begin to cause serious inconvenience. Development, testing, “productive” servers appear - and all this is wonderful, but all of them need to be updated.
In Oracle, there are tools seemingly similar to the one under consideration:
• Audit
• Oracle Streams
• Alert
But they all perform other functions. Some provide 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. And maybe even so:

 begin UpdateFilials; end; / 

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

Training

Perform a connection to the database on behalf of a user who has sufficient privileges for subsequent 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. 

Having omitted the reasoning about the ongoing research, I will say that the most difficult was obtaining an anonymous PL / SQL block, which was cited in the example above. Naturally, some actions ultimately spawn others, for example, the same block from the example will perform insert, but in fact it may not be! After all, it will be executed on another server. Therefore, we will be interested in the anonymous PL / SQL block, and not the consequences. A public synonym for V $ SQL or the V_ $ SQL view to which it refers, stores all queries 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 where it should be. Of course, the SQL_ID doing my example 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 does not belong, the optimizer sees that such an expression has already been executed, and returns the already registered SQL_ID. Mark our surveys in the fields, and continue to explore:

 connect upd/pass Connected. 

I managed to find the completed unit, but I would like to know who made it, or rather find out that it was I who performed 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 type of the representation v_ $ session is FIXED VIEW, therefore it is prohibited to give rights to its synonym. However, if you issue rights to a synonym, say the tables, the rights themselves are issued on the table, and NOT on the synonym.
So what's up with the request? Oh yeah, 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 you can not? Neither SQL_ID nor PREV_SQL_ID - do not contain the previously found 753c9f808k8hh identifier? Naturally! The SQL_ID contains the identifier of the query just executed, and the 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 consistently fulfilled 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 the anonymous block and the request to the presentation. Anyway, I think that another stage of research has been completed. Now we have the source code of the anonymous block, and we know that it was executed by us.
Unfortunately, I don’t like the solution that automates the binding, because after a certain moment, it is necessary to remember all the possible anonymous blocks that are executed by the user, but does the view of the user’s history session store exist? Or does it exist? But I did not find at the moment, I propose the following approach. Create a table that will store the session ID, which is interested in listening, and the job query 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 second table fields?” I would ask. Although this does not have a valid 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. When bookmarking for the future, several useful fields will be saved to the table, in addition to the required ones: KSUSENUM (SID) and KSUSESQI (SQL_ID). It will be good to take out the body of the job to the external procedure, and not to 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 history of the session only if and only what is done 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 can be seen from the result of the query to V $ SQL, an anonymous block got into the log table written there by a job. For the test, I turned to the KSUSEPSI log column (the previous query) 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 a certain disadvantage, but we will exclude the “interruption” of listening from the result set performed on the remote server.
Now you need to collect DLL commands that can also be executed during the upgrade. But there is a contradiction here, why collect DDL - if a job collects them? Unfortunately, it will not collect them, since the DDL is not a query, and therefore will not be reflected in v $ session. For these purposes, Oracle provides database-level triggers that you can use. Executed DDL, write to the new table, and by analogy with the job, create a procedure and trigger performing 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. 

The additional table, and its type (GLOBAL TEMPORARY to store data until disconnect), is selected from the following considerations: the job collects information about the session, works in a session different from the one that runs the update scripts, hence the requests recorded in it would become inaccessible for the session; provide Oracle with table cleaning after the update; The DDL trigger is triggered in the same session in which the DDL is executed, therefore in this case you can write to the buffer table immediately; storing the table data after the commit is due to the fact that the DDL performs silent commit.
It is important to note that the procedure is declared with the AUTHID DEFINER directive, which allows you to record actions with UPD user rights, which may be greater than that of the caller. Next, determine the length of the DDL and save the buffers in the CLOB field.
The trigger is executed after (AFTER) DDL, which implies successful execution of the command, before writing to the buffer.
Summing up the results of the research, now there are all possible types of operations to be performed on the updated database and you can proceed to the final stage - the tool for performing updates.

Implementation

I don’t like publications that, after lengthy reasoning and preparation, end up with something like: “And now, (if not a fool) it should be clear to you how to finish the remaining stuff.” 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. 

Two more tables were added to the previously created tables, one of which is used to validate successfully completed 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 user rights of the calling package. Now, about all the package procedures:
procedure SetSession(u_sid number, u_remove boolean default false) - using a stand-alone transaction, writes the current session identifier to the table that initiates the listen.
function JobNumber return number - gets the ID of the listener.
procedure JobRun - checks for the existence of a job.
procedure SetChannel(u_alias varchar2) - receives remote connection settings and writes them to local variables of the package.
procedure CancelUpdate - clears settings and clears temporary tables.
procedure BeginUpdateChannel(u_alias varchar2) - combines preparatory procedure calls and starts listening.
procedure PrepareUpdateChannel - finishes listening and appends session requests collected by a job to the table buffer. For my own needs, I don’t try too hard, discarding DML, select and service commands encountered during the test, as well as a call to the PrepareUpdateChannel procedure, which is also recorded in the session log.
procedure DropObject is an auxiliary procedure for cleaning.
procedure ExecRemote - block execution on a remote server. This procedure implements one of the key moments of the mechanism. Here the dbms_sql package is called on the remote server.
procedure EndUpdateChannel — apply the update. And about this separately.

I will make a reservation that the first version of the implementation 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 it is not quite so. Locally, dbms_sql allows this, but the LOB field cannot be transferred to a remote server. Many thanks to Tom Kite (https://asktom.oracle.com/pls/apex/f?p=100:11): ):::::P11_QUESTION_ID:950029833940), who knows how to forward LOB between remote servers. I was pleasantly surprised by the fact that the first method, which he gives, was implemented through dbms_lob.substr, with which I cut the CLOB field from the UPD $ BUF table in a loop. The second method he suggests for this task looks like: create a table on the current host, with the rights of the caller updating the user, and create a remote table over the connection with the current database. Here you can point out a few shortcomings in the above implementation, namely: the assumption of a possible error, if the user calling the update is not equal to logged in by dblink, because he will not have rights to select from the temporary table; creating and deleting tables dynamically. Another problem I already encountered when “rolling over” CLOB between servers was the error “ORA-02046: distributed transaction already already”. Apparently, during testing, a suspended session occurred, or the remote connection identifier remained open. I couldn’t simulate this situation again, but in order to avoid repetitions, I need to think about placing a call: 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;