start_apply Procedure

The start_apply procedure instructs the Apply process to start applying events. When the Apply process is stopped for any maintenance or disabled for some error situation, the Apply process will have to be started manually. The start_apply procedure is used to achieve this result. The syntax for the procedure is shown below:

Syntax :
DBMS_APPLY_ADM.START_APPLY(
apply_name IN VARCHAR2);

For example, to start the Apply process named LN4_APPLY, the following SQL block can be used:

PROMPT Starting the Apply Process
begin
dbms_apply_adm.start_apply (apply_name => 'LN4_APPLY' ) ;
end ;
/

stop_apply Procedure

The stop_apply procedure stops the Apply process from applying events and rolls back any unfinished transactions being applied.

Syntax :
DBMS_APPLY_ADM.STOP_APPLY(
apply_name IN VARCHAR2,
force IN BOOLEAN DEFAULT false);

If the force parameter is set to TRUE, it stops the Apply process as soon as possible. If it is set to FALSE, it stops the Apply process after ensuring that there are no gaps in the set of applied transactions. The behavior of the Apply process depends on the setting specified for the force parameter and the setting specified for the commit_serialization Apply process parameter.

For example, to start the Apply process named LN4_APPLY, use the following SQL block:

PROMPT Starting the Apply Process
begin
dbms_apply_adm.stop_apply (apply_name => 'LN4_APPLY' ) ;
end ;
/

set_key_columns Procedure

Syntax
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name IN VARCHAR2,
{ column_list IN VARCHAR2, |
column_table IN DBMS_UTILITY.NAME_ARRAY, }
apply_database_link IN VARCHAR2 DEFAULT NULL);

Apply Process Configuration

In this chapter, the extensions to the Apply process will be presented through the creation of suitable user-defined procedures called the Apply handlers. In earlier chapters, the fact that the Apply process is an optional Oracle background process was presented.  The Apply process dequeues logical change records and user messages from a specific queue. 

The following sections also cover some sample procedures that are used as the handler routines.

Apply Process and Apply Handlers

The Apply process dequeues the LCRs and user messages from a queue. When the Apply process dequeues, the events may be applied to database objects directly as transactions or may be passed on as the parameters to user-defined procedures.  These procedures are the user-defined Apply handlers. The database changes may include the DML or DDL changes.

Figure 6.1 Apply Process and Apply Handlers

As shown in Figure 6.1, Apply handlers typically include a DML handler, a DDL handler, a Message handler and Pre-Commit handlers. The concept of handlers has been introduced in previous chapters.
 

Apply Process and Apply Handlers

Apply Process and Apply Handlers

The DDL handler processes the DDL LCR events and executes the DDL operation. The scope of the DDL handler is for a given Apply process. Thus, there is only one DDL handler for an Apply process.

In case of a pre-commit handler, the commit directive for transactions which include the Row LCR(s) or non-LCR user messages is processed. The scope of the pre-commit handler is the entire Apply process.

The Message handler processes the non-LCR user messages and operates at the Apply process level.

The following guidelines are applicable to the Apply handlers:

• DML handlers, DDL Handlers, and message handlers can execute an LCR by calling the LCR's EXECUTE member procedure.
   

• All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, a commit is performed automatically.
   

• When needed, an Apply handler can set a Streams session tag.
   

• An Apply handler may call a Java stored procedure that is published or wrapped in a PL/SQL procedure.
   

• Ensure that the procedure that is specified as the handler is available and is valid. If an Apply process tries to invoke an Apply handler that does not exist or is invalid, the Apply process aborts.
   

• If an Apply handler invokes a procedure or function in an Oracle-supplied package, the user running the Apply handler must have direct EXECUTE privilege on the package. It is not sufficient to grant this privilege through a role.