Using custom SQL queries for Distributed polling in OSB 12C

November 15, 2016

Reading time ~1 minute

In this post, we will learn how to use customized queries for Distributed polling in OSB 12C, there we go:

SOLUTION

First, we need to know how to create a Db poller in OSB 12C using JDeveloper. Actually, I made a previous post about how to acomplish this step in the following link: create custom sql statements in polling DbAdapters with OSB 12c

So basically, after we created a Database poller with the option of physical delete (first option) then we will modify the xxx-mappings.xml file with a text editor because this file is protected in Jdeveloper.

  <querying>
            <queries>
               <query name="customerPollerSelect" xsi:type="read-all-query">
                 <!--QUERY FOR POLLING-->
                  <call xsi:type="sql-call">
                        <sql>SELECT * FROM CLIENTS WHERE POLLER_READ=0 AND TIMESTAMP &lt;= SYSDATE FOR UPDATE SKIP LOCKED</sql>
                  </call>
                  <reference-class>customerPoller.Clients</reference-class>
                  <lock-mode>lock-no-wait</lock-mode>
                  <container xsi:type="list-container-policy">
                     <collection-type>java.util.Vector</collection-type>
                  </container>
               </query>
            </queries>
            <delete-query xsi:type="delete-object-query">
		<call xsi:type="sql-call">
      <!--QUERY FOR UPDATING AFTER POLLING-->
			<sql>UPDATE CLIENTS SET POLLER_READ=1 WHERE ID=#ID</sql>
		</call>
	</delete-query>
  </querying>

In this piece of code, I’m specifying the query for polling and query for updating after polling. Here there is one important thing when we are handling distributed polling, I added “FOR UPDATE SKIP LOCKED”. This means that one managed server will process a database row and it won’t be processed by another manager server that is polling the same table.

Christopher is part of middleware Sysco. He is CTO of his own startup in South America. He was working with differents open source solutions. Also He was participating in several programming tournaments related to algorithms of Computer Science. Actually these last years, he gained experience with Oracle MAF, ADF ,OSB and other Oracle solutions.

Service call with multiple levels of security in OSB 12c

![Client Certificate security implementation](/images/2017-09-27-Service-call-with-multiple-levels-of-security-in-OSB-12c/HTTP_GET_Securi...… Continue reading

Apache Kafka workshop

Published on September 26, 2017