rem rem $Header: dbmsdefr.sql 09-apr-96.13:26:20 sbalaram Exp $ rem rem Rem Copyright (c) 1993, 1996 by Oracle Corporation Rem Copyright (c) 1992,1993, 1996 by Oracle Corporation Rem ***** Oracle Proprietary ***** Rem ***** This file contains the embodiment of proprietary technology. ***** Rem ***** It is for the sole use of Oracle employees and Oracle ***** Rem ***** customers who have executed non-disclosure agreements. ***** Rem ***** The contents of this file may not be disclosed to persons ***** Rem ***** or organization who have not executed a non-disclosure ***** Rem ***** agreement. ***** Rem NAME Rem dbmsdefr.sql - replicated deferred remote procedure calls Rem DESCRIPTION Rem The external interfaces and bodies of two replication packages are Rem also included, as are some sequences used by the packages. Rem All objects are created in the schema 'SYS'. Rem Packages Rem dbms_defer Rem dbms_defer_query Rem dbms_defer_sys Rem Rem NOTES Rem The algorithms used here were originally conceived by Sandeep Rem Jain, and are described in a forthcoming memo by Sandeep Jain Rem and Dean Daniels Titled "A Method for Deferring Remote Procedure Calls Rem Utilizing a Relational Database System." Rem Rem The structures implemented by this package should probably be a Rem cluster. The create table statements can be modified to include Rem storage information as appropriate for a particular installation. Rem Rem DEPENDENCIES Rem These packages use calls from the DBMS_SQL, DBMS_ASYNCRPC, and Rem DBMS_DEFER_PACK packages Rem Rem USAGE Rem This script is to be run by user connected as INTERNAL. Rem Rem SECURITY Rem Tables and sequences created by this script are kept private Rem The dbms_defer package is granted to public, but it is reasonable to Rem restrict access Rem to users creating replicated applications. Rem The dbms_defer_query package can be executed by users who need to Rem monitor deferred rpc queues, for example dba correcting conflicts. Rem The dbms_defer_sys package is granted to DBAs (by default) Rem The dbms_defer_internal_sys packages is Rem kept private. Rem The dbms_defer_pack package is kept private, as is dbms_asyncrpc. Rem Rem COMPATIBILITY Rem MODIFIED (MM/DD/YY) Rem sbalaram 04/08/96 - Remove internal procedure Rem dbms_defer_sys.create_error from here Rem and move it to prvtdefr.sql Rem sjain 11/20/95 - Add toms Rem jstamos 08/17/95 - code review changes Rem boki 07/07/95 - new function to disable queue propagation Rem jstamos 06/29/95 - merge changes from branch 1.9.720.6 Rem hasun 03/20/95 - Reset batch size to 0 Rem hasun 01/24/95 - Add gname parameter to call() Rem dsdaniel 01/17/95 - eliminate grant to public Rem dsdaniel 12/05/94 - eliminate def_trandest table Rem boki 12/02/94 - modified execute(), adding new argument Rem hasun 11/17/94 - add Exception NOREPOPTION for factoring Rem dsdaniel 10/26/94 - coverage, interface changes Rem dsdaniel 09/12/94 - comment in dbms_defer_query Rem dsdaniel 07/11/94 - dbms_sys_error upgrade Rem adowning 04/29/94 - merge latest revisions from repint Rem dsdaniel 03/29/94 - error message change Rem dsdaniel 02/17/94 - repcat integration Rem adowning 02/02/94 - split file into public / private binary files Rem dsdaniel 01/20/94 - dbms_sys_error error range change Rem rjenkins 01/13/94 - giving RESET a default Rem dsdaniel 12/27/93 - dbms_sys_sql change Rem rjenkins 12/22/93 - oops Rem rjenkins 12/17/93 - creating job queue Rem dsdaniel 11/02/93 - dbms_sql.parse changes Rem dsdaniel 10/28/93 - deferred rpc dblink security Rem dsdaniel 10/26/93 - merge changes from branch 1.1.400.3 Rem dsdaniel 10/10/93 - break out queue tables Rem rjenkins 10/07/93 - adding deferrcount Rem dsdaniel 08/30/93 - package for installation by system Rem dsdaniel 08/04/93 - Creation by renaming dbmsrrpc.sql Rem dsdaniel 05/17/93 - upgrade to 7.0/7.1 version Rem dsdaniel 02/22/93 - Creation Rem ******************************************************************* Rem Rem Rem REM ******************************************************************** REM THIS PACKAGE MUST NOT BE MODIFIED BY THE CUSTOMER. DOING SO REM COULD CAUSE INTERNAL ERRORS AND SECURITY VIOLATIONS IN THE REM RDBMS. SPECIFICALLY, THE PSD* ROUTINES MUST NOT BE CALLED REM DIRECTLY BY ANY CLIENT AND MUST REMAIN PRIVATE TO THE PACKAGE BODY. REM ******************************************************************** CREATE OR REPLACE PACKAGE dbms_defer AS ------------------- -- OVERVIEW -- -- This package is the user interface to a replicated transactional -- deferred remote -- procedure call facility. Replicated applications use the calls -- in this interface -- to queue procedure call for later transactional execution at remote nodes. -- These routines are typically called from either after row triggers -- or application -- specified update procedures. ------------ -- SECURITY -- -- This package is not granted to public because a user can -- potentially steal the rights of the user pushing the deferred rpcs. -- Be careful when granting execute privileges on dbms_defer. ------------- -- CONSTANTS -- -- constants used in the arg_type column of the def$_args table -- definitions copied from dtydef.h -- arg_type_num CONSTANT NUMBER := 2; -- DTYNUM arg_type_char CONSTANT NUMBER := 96; -- DTYAFC arg_type_varchar2 CONSTANT NUMBER := 1; -- DTYCHAR arg_type_date CONSTANT NUMBER := 12; -- DTYDAT arg_type_rowid CONSTANT NUMBER := 11; -- DTYRID arg_type_raw CONSTANT NUMBER := 23; -- DTYBIN -- definition same as dbms_repcat_mas.repcat_status_normal -- (don't want to require repcat to be loaded) repcat_status_normal CONSTANT NUMBER := 0.0; -- --------- -- TYPES -- -- node list type used for the defer_txn call -- representation is an array (table) indexed from 1 up to a NULL -- entry or NO_DATA_FOUND TYPE node_list_t IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; -- ----------------- -- EXCEPTIONS -- -- Parameter type does not match actual type. bad_param_type EXCEPTION; PRAGMA exception_init(bad_param_type, -23325); bad_param_num NUMBER := -23325; -- The database is being quiesced. deferred_rpc_quiesce EXCEPTION; PRAGMA exception_init(deferred_rpc_quiesce, -23326); quiesce_num NUMBER := -23326; quiesce_msg VARCHAR(76) := 'the system is being quiesced.'; -- Generic errors that are not important enough for specific exceptions -- string text will explain them further. These are internal errors. -- message varies. dbms_defererror EXCEPTION; PRAGMA exception_init(dbms_defererror, -23305); deferror_num NUMBER := -23305; -- -- dbms_defer package detects mal-formed call (e.g. argument count -- miss-match). Message varies. malformedcall EXCEPTION; PRAGMA exception_init(malformedcall, -23304); malformed_num NUMBER := -23304; -- generic exceptions that (user-written) deferred procedures -- can raise to indicate -- that the remote update has failed because of data updates by concurrent -- transactions. A deferror table record will be created by the deferred -- rpc executor updateconflict EXCEPTION; PRAGMA exception_init(updateconflict, -23303); conflict_num NUMBER := -23303; conflict_msg VARCHAR(76) := 'Remote update failed due to conflict.'; -- generic exceptions that (user-written) deferred procedures -- can raise to indicate -- that the remote update has failed because communications failures -- so that a a deferror table record will not be created by the -- deferred rpc -- executor. commfailure EXCEPTION; PRAGMA exception_init(commfailure, -23302); commfail_num NUMBER := -23302; commfail_msg VARCHAR(76) := 'Remote update failed due to communication failure'; -- mixed use repcat determined destinations and non-repcat destinations -- in one transaction mixeddest EXCEPTION; PRAGMA exception_init(mixeddest, -23301); mixeddest_num NUMBER := -23301; mixeddest_msg VARCHAR(76) := 'Destinations for transaction not consistently specified'; -- parameter length exceed deferred rpc limits (2000 char/varchar2, -- 255 raw) in one transaction parameterlength EXCEPTION; PRAGMA exception_init(parameterlength, -23323); paramlen_num NUMBER := -23323; paramlen_msg VARCHAR(76) := 'parameter length exceeds deferred rpc limits'; -- deferred rpc execution is disabled executiondisabled EXCEPTION; PRAGMA exception_init(executiondisabled, -23354); executiondisabled_num NUMBER := -23354; paramlen_msg VARCHAR(76) := 'parameter length exceeds deferred rpc limits'; ---------------------- -- PROCEDURES -- PROCEDURE commit_work(commit_work_comment IN VARCHAR2); -- Perform a transaction commit after checking for well-formed -- deferred RPCs. -- Must be used instead of the commit work sql call for -- transactions deferring RPCS. -- Updates the comment_comment and commit_scn fields in -- the def$_txn table. -- Input parameters: -- commit_work_comment -- Up to fifty characters to describe the transaction -- in the def$_txns -- table and system two-phase commit tables (this latter -- once we figure out -- how to get it in.) Comment is truncated to fifty characters. -- Exceptions -- ORA-23304 (malformedcall) if there is an defer_rpc_arg -- call missing or defer_txn -- was not called for this transaction. -- -- -- -- Transaction and call deferral procedures -- A deferred transaction consist of the following: -- Call to dbms_defer.transaction (this is optional, the first call to -- dbms_defer.call will call transaction) -- one or more complete calls, each of which consists of -- Call to dbms_defer.call -- zero of more calls (depending on arg_count in -- dbms_defer.call) to dbms_defer.arg_* -- commit or call to commit_work -- -- DESTINATION SPECIFICATION -- Destinations can be specified in several ways -- A) All deferred procedures are in repcat and the default list is -- NOT specified in the transaction call. -- OR -- B) destinations are specified without repcat using the following order -- of precedence -- 1) list specified in the nodes parameter to dbms_defer.call -- 2) list specified in the nodes parameter to dbms_defer.transaction -- 3) list specified in defdefaultdest table. -- The mixeddest exception is raised if an attempt to mix destinations -- modes is detected. -- PROCEDURE transaction; PROCEDURE transaction(nodes IN node_list_t); -- Mark a transaction as deferred (as containing deferred RPCs ) -- This call is optional. The first call to dbms_defer.call -- in a transaction will call -- deftxn (with no arguments) if it has not been previously called. -- Input parameters are optional, and if they are not -- specified the destination -- list is taken from the system defaults stored in the -- def$_defaultdest table and -- maintained by the dbms_defer_sys.add_default_node and -- dbms_defer_sys.delete_default_node calls -- Input parameters: -- nodes -- Table containing a list of nodes (dblink) to propagate the -- deferred calls of the -- transaction to. Indexed from 1 until a NULL entry is -- found or NO_DATA_FOUND is raised. -- Case insensitive comparison -- used for node lists. -- Use of this parameter overrides distribution lists as -- specified in repcat. -- Exceptions -- ORA-23304 (malformedcall) if the previous transaction -- not correctly formed -- or terminated -- ORA-23319 Parameter value is not appropriate -- ORA-23352 Raised by dbms_defer.call if the node -- list contains duplicates ---- PROCEDURE call( schema_name IN VARCHAR2, package_name IN VARCHAR2, proc_name IN VARCHAR2, arg_count IN NATURAL, group_name IN VARCHAR2 := ''); PROCEDURE call( schema_name IN VARCHAR2, package_name IN VARCHAR2, proc_name IN VARCHAR2, arg_count IN NATURAL, nodes IN node_list_t); -- Defer a remote procedure call. Automatically call -- deftxn if this is the first -- call call of a transaction. -- Input parameters: -- schema_name -- Name of the schema containing the remote procedure. For -- compatibility with future compile-time checking only string -- constants should be used. -- package_name -- Name of the package containing the remote procedure. For -- compatibility with future compile-time checking only string -- constants should be used. -- proc_name -- Name of the remote procedure to call. -- For compatibility with -- future syntactic integration -- and compile-time checking only string constants should be used. -- arg_count -- Number of parameters to the procedure. This must -- exactly match the number of -- defrpcarg_* calls immediately following the dbms_defer.call call. -- group_name -- Reserved for internal use -- nodes -- Optional table containing a list of nodes to propagate the -- deferred call to. -- Indexed from 1 until a NULL entry is -- found or NO_DATA_FOUND is raised. -- Case insensitive comparison -- used for node lists. -- If not specified, the destination list is determined by the -- list passed to the transaction procedure, or the system defaults, -- Use of this parameter in any deferred call invalidate the use of -- the use of repcat to determine distribution lists in any -- calls for a transaction. -- Exceptions -- -- Exceptions -- ORA-23304 (malformedcall) if the previous call not -- correctly formed (number of -- defrpcarg_* call not matched to arg_count). -- ORA-23319 Parameter value is not appropriate -- ORA-23352 If the destination list (specified by nodes or by a previous -- dbms_defer.transaction call contains a duplicate. ---- PROCEDURE number_arg(arg IN nUMBER); -- Queue a number parameter value for a deferred call. -- Input parameter: -- arg -- The number value of the parameter to the call -- previously deferred with a -- dbms_defer.call call. -- Exceptions: none. -------- PROCEDURE date_arg(arg IN DATE); -- Queue a date parameter value for a deferred call. -- Input parameter: -- arg -- The date value of the parameter to the call previously -- deferred with a -- dbms_defer.call call. -- Exceptions: none. -------- PROCEDURE varchar2_arg(arg IN VARCHAR2); -- Queue a varchar2 parameter value for a deferred call. -- Input parameter: -- arg -- The varchar2 value of the parameter to the call -- previously deferred with a -- dbms_defer.call call. The length of arg is limited to 2000. -- Exceptions: -- whatever error sql gives if arg exceeds 2000 characters. PROCEDURE char_arg(arg IN CHAR); -- Queue a char parameter value for a deferred call. -- Input parameter: -- arg -- The char value of the parameter to the call previously -- deferred with a -- dbms_defer.call call. The length of arg is limited to 2000. -- Exceptions: -- whatever error sql gives if arg exceeds 2000 characters. --------------------- -- rowids can not be -- used on different nodes. It might be reasonable to use a -- rid in a deferred call -- to a local node, but be careful. PROCEDURE rowid_arg(arg IN ROWID); -- Queue a rowid parameter value for a deferred call. -- Input parameter: -- arg -- The rowid value of the parameter to the call -- previously deferred with a -- dbms_defer.call call. -- Exceptions: -- dbms_deferError -------- -- The following calls will not be supported until dbms_sql -- supports -- PROCEDURE raw_arg(arg IN raw); -- Queue a rowid parameter value for a deferred call. -- Input parameter: -- arg -- The raw value of the parameter to the call -- previously deferred with a -- dbms_defer.call call. -- Exceptions: -- dbms_deferError -------- -------- END dbms_defer; / DROP PUBLIC SYNONYM dbms_defer / CREATE PUBLIC SYNONYM dbms_defer FOR dbms_defer / CREATE OR REPLACE PACKAGE dbms_defer_query AS ------------------- -- OVERVIEW -- -- This package permits querying the deferred RPC queue data that -- is not exposed through views. -- The following type declaration are used by get_call_args call. TYPE type_ary is table of number index by binary_integer; TYPE val_ary is table of varchar2(2000) index by binary_integer; FUNCTION get_arg_type(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2) RETURN NUMBER; -- Return type of a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id ---- -- Result -- The type of the deferred rpc parameter. ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. ------ FUNCTION get_number_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN NUMBER; -- Return a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a number. ------- FUNCTION get_varchar2_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; -- Return a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a VARCHAR2. ------- FUNCTION get_char_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN CHAR; -- Return type of a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a char. ------- FUNCTION get_date_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN DATE; -- Return a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a date. ------- FUNCTION get_raw_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN RAW; -- Return a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a raw. ------- FUNCTION get_rowid_arg(callno IN NUMBER, deferred_tran_db IN VARCHAR2, arg_no IN NUMBER, deferred_tran_id IN VARCHAR2 DEFAULT NULL) RETURN ROWID; -- Return a deferred call parameter. -- Input parameters -- callno -- call identifier from the defCall view -- deferred_tran_db -- database deferring call from the defCall view -- arg_no -- position of desired parameter in calls argument list -- parameter positions are 1..number of parameters in call -- deferred_tran_id -- deferred transaction id, defaults to one passed in get_arg_type ---- -- Result -- The value of the parameter . ------ -- EXCEPTIONS -- NO_DATA_FOUND desired parameter value not found in the deferred rpc -- queue tables. -- WRONG_TYPE if the desired parameter is not a rowid. ------- PROCEDURE get_call_args ( callno IN NUMBER, -- deferred call number startarg IN NUMBER := 1, -- starting argument to fetch argcnt IN NUMBER, -- number of arguments in the call argsize IN NUMBER, -- maximum size of returned argument tran_db IN VARCHAR2, -- origin database tran_id IN VARCHAR2, -- transaction id date_fmt IN VARCHAR2, -- date format types OUT TYPE_ARY, -- output array for types -- of the arguments vals OUT VAL_ARY -- output array of the values ); -- This procedure returns the text version of the various arguments for the -- given call. The exceptions returned by this function are the same ones -- as returned by get_arg_type and get _xxx_arg. END dbms_defer_query; / DROP PUBLIC SYNONYM dbms_defer_query / CREATE PUBLIC SYNONYM dbms_defer_query FOR dbms_defer_query / GRANT EXECUTE on dbms_defer_query TO DBA / CREATE OR REPLACE PACKAGE dbms_defer_sys AS ------------------- -- OVERVIEW -- -- This package is the system administrator interface to a replicated -- transactional deferred remote -- procedure call facility. Administrators and replication -- deamons can execute -- transactions queued for remote nodes using this facility -- and administrators -- can control the nodes to which remote calls are destined. ------------ -- SECURITY -- -- By default, this package is owned by user SYS and -- execution should be granted -- only to administrators and deamons that perform -- replication administration and -- execute deferred transactions. See the security considerations for -- the dbms_defer package for related considerations. ------------- -- EXCEPTIONS -- -- Parameter type does not match actual type. Message varies. crt_err_err EXCEPTION; PRAGMA exception_init(crt_err_err, -23324); crt_err_num NUMBER := -23324; crt_err_msg VARCHAR(76) := 'Error creating deferror entry: '; -- Replication is not linked as an option norepoption EXCEPTION; PRAGMA exception_init(norepoption, -2094); norepoption_num NUMBER := -2094; ------------- -- CONSTANTS -- size of long buffer used for packing parameters parm_buffer_size CONSTANT NUMBER := 4096; default_alert_name CONSTANT VARCHAR2(30) := 'ORA$DEFER_ALERT'; -- PROCEDURES -- manage default replication node lists PROCEDURE add_default_dest(dblink IN VARCHAR2); -- Add a node to the default list for replication targets. -- Input parameters -- dblink -- name of the node (dblink) to add tRo the default list. -- Exceptions -- ORA-23352 -- dblink is already in the default list. ---------- PROCEDURE delete_default_dest(dblink IN VARCHAR2); -- Delete a node from the default list for replication targets -- Input parameters -- dblink -- name of the node (dblink) to delete from the default list. -- Operation is a no-op if dblink is not in the list. -- Exceptions -- none. ----------------- PROCEDURE execute(destination IN VARCHAR2, stop_on_error IN BOOLEAN := FALSE, transaction_count IN BINARY_INTEGER := 0, execution_seconds IN BINARY_INTEGER := 0, execute_as_user IN BOOLEAN := FALSE, delay_seconds IN NATURAL := 0, batch_size IN NATURAL := 0); -- Execute transactions queued for destination_node. stop_on_error -- determines whether processing of subsequent transaction continues -- after an error is detected. -- deftrandest (and defcalldest if appropriate) entries -- for the successfully executed transactions are deleted and if there -- are no other references, the defcall and deftran entries are deleted. -- -- Input Parameters: -- destination -- node (dblink) at which to execute -- deferred transaction. Case -- insensitive comparisons used. -- stop_on_error -- If TRUE, execution of queued transactions will -- alway stop when an error is -- encountered, leaving unattempted transaction in -- the queue. If FALSE, -- execution continues except when errors that appear -- to mean that node is -- unavailable are encountered, it which case execution -- always stops, leaving -- unattempted transactions queued. -- transaction_count -- If positive, at most this many transactions will be executed. -- executions_seconds -- If positive, execution will stop after completions of the -- last transaction after this many seconds of executions. -- execute_as_user -- IF TRUE the execution of deferred RPCs is authenticated at the remote -- system using the authentication context of the session user. If -- FALSE the execution of deferred RPCs is authenticated at the remote -- system using the authentication contexts of the users that originally -- queued the deferred RPCs (indicated in the origin_user column of -- the deftran table). -- delay_seconds -- If positive, the routine will sleep for this many seconds before -- returning when it finds no deferred RPCs queued for the destination -- Non-zero values can redue execution overhead compared to calling -- dbms_defer_sys.execute from a tight loop. -- batch_size -- The number of deferred rpc calls should be executed before -- committing deferred transactions. If batch_size is 0 a commit will -- occur after each deferred transaction. If batch_size is greater than -- zero a commit will occur when the total number of deferred calls -- executed exceeds batch_size and a complete transaction has been -- executed. -- -- Exceptions -- Raises the last exception encountered before execution -- stops because of -- an exception. ---------------- PROCEDURE execute_error(deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2); -- (Re)Execute transactions that previously encountered conflicts. -- Execution stops when any error is encountered. If some input is null, -- then each transaction is committed as it completes. If exactly one -- transaction is specified, then the transactions is not committed. -- Upon successful execution, transactions are removed for deferror, and if -- there are no other references, entries are deleted from -- defcall and deftran. -- Input Parameters: -- deferred_tran_db -- node (global_name) originating or copying transaction that -- encountered an error. If null, then -- deferred_transaction_id must be null and all -- transactions from all destinations matching destination -- (as specified) are re-executed. -- -- deferred_tran_id -- The identifier of the transaction to be reexecuted. -- If null then if all transactions in deferror matching -- deferred transaction id and destination (as specified) -- are re-executed. -- destination -- dblink that transaction was originally destined to. -- Must not be null. -- Exceptions -- Raises an exception if destination is null or if -- deferred_tran_db is null and deferred_tran_id is not null. -- Raises the last exception encountered before execution -- stops because of an exception. ---------------- PROCEDURE delete_tran(deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2); -- Delete transactions from queues. Deletes deftrandest (and defcalldest -- entries if appropriate. If there are not other references, -- deftran and defcall entries are deleted. -- Input Parameters: -- destination -- dblink for which transaction(s) are to be removed from queues. -- If null, the transaction specified by the other parameters are -- deleted from queues for all destinations. -- deferred_tran_id -- The identifier of the transaction to be deleted -- If null then all transactions matching destination and -- deferred_tran_db are deleted. -- deferred_tran_db -- The identifier of the origin/copying node for the transaction to be -- deleted. If null then all transactions matching destination and -- deferred_tran_id are deleted. -- Exceptions -- tid and/or node not found. --------------- PROCEDURE delete_error(deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2); -- Delete transactions from deferror table. If there are -- not other references, -- deftran and defcall entries are deleted. -- Input Parameters: -- destination -- destination for which transaction(s) are to be removed from -- deferror. -- If null, the transaction specified by the other parameters are -- deleted from deferror for all destinations. -- deferred_tran_id -- The identifier of the transaction to be deleted -- If null then all transactions matching destination and -- deferred_tran_db are deleted. -- deferred_tran_db -- The identifier of the origin/copying node for the transaction to be -- deleted. If null then all transaction matching destination and -- deferred_tran_id are deleted. -- Exceptions -- tid and/or node not found. --------------- PROCEDURE copy(deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination_list IN dbms_defer.node_list_t, destination_count IN BINARY_INTEGER); -- Copy as deferred transaction assign it and new -- deferre_tranid , deferred_tran_db, -- executions_order, setting destination_list to 'D' -- and retaining the other fields from the source -- transaction). The new transaction has destinations as -- specified by destination_list and destination_count. -- destination_node -- the Origin_ -- Case insensitive comparisons used. -- tid -- The identifier of the transaction to be added to the node's queue. -- Exceptions -- tid not found. --------------- PROCEDURE schedule_execution(dblink IN VARCHAR2, interval IN VARCHAR2, next_date IN DATE, reset IN BOOLEAN default FALSE, stop_on_error IN BOOLEAN := NULL, transaction_count IN BINARY_INTEGER := NULL, execution_seconds IN BINARY_INTEGER := NULL, execute_as_user IN BOOLEAN := NULL, delay_seconds IN NATURAL := NULL, batch_size IN NATURAL := NULL); -- Insert or update a defschedule entry and signal the background process. -- this procedure does a commit; -- Input Parameters: -- dblink -- Queue name to schedule execution for; -- interval -- If non-null then DefSchedule.interval for dblink is set to this -- value. If null and the DefSchedule entry for dblink exists, -- the value of DefSchedule.interval is not modified. If -- null and the DefSchedule entry -- for dblink does not exist, then the DefSchedule entry for -- dblink is created with a null interval value. -- next_date -- If non-null then DefSchedule.next_date for dblink is set to this -- value. If null and the DefSchedule entry for dblink exists, the value -- of DefSchedule.next_date is not modified. If null and -- the DefSchedule entry -- for dblink does not exist, then the DefSchedule entry -- for dblink is created with a null next_date value. -- reset -- If TRUE then last_txn_count, last_error, and last_msg are nulled. -- stop_on_error -- transaction_count -- execution_seconds -- execute_as_user -- delay_seconds -- batch_size -- If non-null, these parameters are passed to the -- dbms_defer_sys.execute call that is scheduled for execution by -- this call. --------- PROCEDURE unschedule_execution(dblink IN VARCHAR2); -- Delete a defschedule entry. Signal to background process to stop -- servicing this queue. -- Input Parameters: -- dblink -- Queue name to stop automatic execution of. -- Exceptions: -- NO_DATA_FOUND -- no entry for dblink in DefSchedule. --------- FUNCTION disabled(destination IN VARCHAR2) RETURN BOOLEAN; -- Effects: Returns TRUE if the deferred RPC queue is disabled for -- the given destination. Else returns FALSE. -- Raises the following exceptions: -- no_data_found: -- if the "destination" does not appear in defschedule. -- --------- PROCEDURE set_disabled(destination IN VARCHAR2, disabled IN BOOLEAN := TRUE); -- Effects: If "disabled" is TRUE, disable propagation to the given -- "destination." All future invocations of dbms_defer_sys.execute -- will not be able to push the deferred RPC queue to this -- destination until it is enabled. This function has no effect -- on a session already pushing the queue to the given -- destination. This function has no effect on sessions appending -- to the queue with dbms_defer. -- If "disabled" is FALSE, enable propagation to the given "destination." -- Although this does not push the queue, it permits future invocations -- of dbms_defer_sys.execute to push the queue to the given destination. -- In either case, a COMMIT is required for this to take effect in other -- sessions. -- -- Raises the following exceptions: -- no_data_found: -- if the "destination" does not appear in defschedule. -- END dbms_defer_sys; / DROP PUBLIC SYNONYM dbms_defer_sys / CREATE PUBLIC SYNONYM dbms_defer_sys FOR dbms_defer_sys / GRANT EXECUTE ON dbms_defer_sys TO DBA /