Skip to Main Content
  • Questions

  • Oracle Streams - Hold/Intercept changes

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eduardo.

Asked: January 25, 2017 - 4:26 pm UTC

Last updated: February 01, 2017 - 2:57 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi,

We have two databases configured with One-Way Oracle Streams replication.

We need to intercept the data changes before they are applied, and apply these changes somewhere in the future using our business logic (keeping the original order).

To achieve this, we are using a Procedure DML Apply Handler that inserts the LCRs objects in a temporary table (LiveSQL Link attached).
However, Procedure Handlers doesn't support LONG and LONG RAW and we have tables with this data types.
With Statement handlers, we cannot store the LCR in our table since it is not possible to access it.

Is it possible to achieve this requirement while supporting all types of data?

Thank you in advance,
Eduardo



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/ehgkihub9f2tjradghhuewhyu

and Chris said...

First up, note that streams is deprecated in 12c:

Oracle Streams is deprecated in Oracle Database 12c Release 1 (12.1). Use Oracle GoldenGate to replace all replication features of Oracle Streams.

http://docs.oracle.com/database/122/STREP/release-changes.htm#STREP1769

Secondly LONG and LONG RAW are unsupported data types for apply:

Statement DML handlers cannot process LONG, LONG RAW, or nonassembled LOB column data in row LCRs. However, statement DML handlers can process LOB column data in row LCRs that have been constructed by LOB assembly. LOB assembly is enabled by default for statement DML handlers.

Procedure DML handlers and error handlers cannot process LONG or LONG RAW column data in row LCRs. However, procedure DML handlers and error handlers can process both nonassembled and assembled LOB column data in row LCRs, but these handlers cannot modify nonassembled LOB column data.

http://docs.oracle.com/cd/E11882_01/server.112/e17069/ap_restrictions.htm#STRMS1217

So you're probably best of looking at an alternative to streams. GoldenGate is the preferred technology, but you will have to pay for that...

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Eduardo, January 26, 2017 - 4:09 pm UTC

Hi Chris Saxon, thank you for the answer!

We can't use GoldenGate.

Is there any other alternative, either thought Streams features or something else?
Would "Explicit Consumption with Manual Dequeue" help in my scenario?

Thank you,
Eduardo

Connor McDonald
January 30, 2017 - 8:58 pm UTC

I would say your best bet would be to look at refactoring the long/long raws into clob/blob.

Eduardo Lopes, January 31, 2017 - 12:54 pm UTC

I'm trying to use a Procedure Handler to store the LCRs on a table.

After storing them on the table, I'm selecting the LCR and executing the lcr.execute(true) method.

However, if the LCR has a CLOB column I'm getting the following error:
ORA-26688: missing key in LCR

If the LCR is executed within the Procedure Handler, it works correctly.

Any idea why?
Thank you
Chris Saxon
January 31, 2017 - 2:14 pm UTC

What exactly are you doing?

Eduardo, January 31, 2017 - 5:10 pm UTC

Please see this: https://livesql.oracle.com/apex/f?p=590:41:0:::41:SHAREKEY:EHGKIHUB9F2TJRADGHHUEWHYU

1) I'm adding a Procedure Handler to a table.
2) This Procedure Handler just inserts the LCR (and other info extracted from the LCR) into another Table but it doesn't apply it.
3) An async process selects the LCR from the table and executes it.
Chris Saxon
February 01, 2017 - 2:57 pm UTC

The docs have some notes on using LOBs with lcr.execute:

Considerations for LOB Columns

When processing a row LCR with LOB columns with a procedure DML handler or error handler, and the handler is using LOB assembly (the assemble_lobs parameter is set to TRUE for the handler), this member procedure executes the assembled row LCR. An assembled row LCR represents a LOB value with a LOB locator or NULL.

If assemble_lobs is set to FALSE for the handler, then this member procedure executes the nonassembled row LCRs. Nonassembled row LCRs represent LOB values with VARCHAR2 and RAW data types. These nonassembled row LCRs might have been modified by the handler.

An error is raised under the following conditions:

A DML or error handler configured with assemble_lobs set to FALSE attempts to execute a row LCR that contains a LOB locator.

A DML or error handler configured with assemble_lobs set to TRUE attempts to execute a row LCR that contains one or more LOB values represented with VARCHAR2 or RAW data types.

If an error is raised because of one of these conditions, then the transaction that includes the row LCR is moved to the error queue, and the LOB is represented by the original (nonassembled) row LCRs.

http://docs.oracle.com/database/122/ARPLS/Logical-Change-Record-TYPEs.htm#GUID-1462FDB7-E00B-4F7B-8C59-4AEA5FFCF6BD__I997654

Eduardo, February 02, 2017 - 6:44 pm UTC

Please take a look at this script: https://livesql.oracle.com/apex/livesql/s/ehgkihub9f2tjradghhuewhyu

In the statement 1 I'm defining the handler with "assemble_lobs => true"

In the statement 3 I'm performing an insert with a CLOB value.

Does this falls into the "LCR that contains one or more LOB values represented with VARCHAR2 or RAW data types" error condition?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library