Oracle Distributed Systems

Procedural Replication

Procedural replication refers to manipulating remote data indirectly by making procedure calls. For example, if you want to make a 5% price hike for all products in the remote PRODUCT_PRICES table, you might make a call like this:

BEGIN
    PriceMaint.PercentageIncrease( pct_in => 5);
END;

This procedure goes to the remote database and executes the statement:

UPDATE PRODUCT_PRICES
SET price = price * 1.05;

Since the stored procedure itself resides in the target database(s), no data travels over the network, just the name of the stored procedure and the passed parameters. Nor does the calling database need to maintain a connection to the remote database while the procedure is executed. Once the calling database delivers the call to the target locations, its work is finished.

Procedural replication is appropriate for operations that manipulate a significant amount of data on a replicated table. For example, the price increase just described changes every row in the PRODUCT_PRICES table. If you were to perform the update locally and let the row-level replication mechanism propagate all of the updated rows, the network traffic could be crippling. In general, you should not attempt to use row-level replication for any transaction that affects more than 20% of a table’s records.

When using procedural replication, you must take care that procedure calls that have transactional dependencies are made in the correct order at the remote sites. For example, a call to HR_APP.HireEmployee ...