3 Replies Latest reply: Jan 17, 2013 1:27 AM by 985390 RSS

    Complex Oracle Streams issue - Update conflicts

    668512
      This is for Oracle streams replication on 11g r2.

      I am facing update conflicts in a table. The conflict arise due to technical and business logic issue. The business logic will pass through the replication/apply process successfully but we want to arrest and resolve it before replication for our requirements. These are typically a bit complex cases and we are exploring the possibility of having both DML handlers and Error handlers. The DML handlers will take care of business logic conflicts and Error handler for technical issues before pushing it to Error queue by Streams. Based on our understanding and verification, we found a limitation to configure both procedure DML handler and Error handler together for the same table operation.

      Statement handlers can not be used for our conflict scenarios.

      Following are my questions:
      1. Have anyone implemented or faced such a scenario in their real time application? If yes, can you please share some insights or inputs?
      2. Is there a custom way to handle this complex problem of configuring both DML and Error handler?
      3. Is there any alternative possible way to resolve this situation at Oracle streams environment with other handlers?
        • 1. Re: Complex Oracle Streams issue - Update conflicts
          pmcelroy-Oracle
          A DML handler is used to replace the normal Streams apply processing with a customized procedure or a SQL statement (in the case of Statement handlers). If you configure a DML handler, you should handle any errors that might occur within the DML handler.
          Error handlers can coexist with Statement handlers, but can not coexist with procedural DML handlers.


          Typically Error handlers are used to handle errors that occur using the normal Streams apply processing.
          • 2. Re: Complex Oracle Streams issue - Update conflicts
            668512
            Thanks and whatever you have replied has helped me contextually.

            But if you can share some examples on complex statement handlers and procedural error mechanism, that will be great.
            • 3. Re: Complex Oracle Streams issue - Update conflicts
              985390
              Dear All

              I too have a similar requirement. Could anyone help with one?

              We can handle the error-ing transactions via Error Handler procedures.
              But we can not configure the DML handler procedure for transactions that are successfully replicated. STreams does not allow us to configure a handler for this. Is there any other handler / procedures / hooks in streams where we can implement the desired functionality - which includes changing the values in the LCR before invoking lcr.execute() and we should be able to discard the LCR also if required.

              Regards
              Velmurugan

              Edited by: 982387 on Jan 16, 2013 11:25 PM

              Edited by: 982387 on Jan 16, 2013 11:27 PM