Skip to Main Content
Oracle Distributed Systems
book

Oracle Distributed Systems

by Charles Dye
April 1999
Intermediate to advanced content levelIntermediate to advanced
552 pages
15h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Expert Oracle RAC 12c

Expert Oracle RAC 12c

Syed Jaffar Hussain, Tariq Farooq, Riyaj Shamsudeen, Kai Yu
Expert Oracle Exadata, Second Edition

Expert Oracle Exadata, Second Edition

Martin Bach, Karl Arao, Andy Colvin, Frits Hoogland, Randy Johnson, Kerry Osborne, Tanel Poder

Publisher Resources

ISBN: 1565924320Supplemental ContentCatalog PageErrata