Nice trick to create custom sql statements in polling DbAdapters with OSB 12c

October 29, 2015

Reading time ~1 minute

Now we will learn how to change the sql statement for Database pollers using OSB 12c. There we go:


OSB allows us to create DbAdapters and select a polling functionality, but there are some restrictions if we want to write a custom sql statement.
There is a nice way to set a pure sql statement for polling database rows and after reading operation. I will show you how to handle it.

Create poller

First, we have to create a dbAdapter on proxy services and select “Poll for New or Changed Records in a Table”.

In the “After Read” step select “Delete the Row(s) that were Read” option like the image below:

If you are using clusters then select Distributed Polling (FOR UPDATE SKIP LOCKED) . This will help to avoid record locking issues.

Edit poller

Then it will create 6 files and one of them finishes in -or-mapping.xml, this file is protected, so you have to open in another text editor. Inside of this file you can set the pure sql for the poller and after reading operations.

To set poller sql statement:

		 <query name="TestPoller" xsi:type="read-all-query">
			<call xsi:type="sql-call">
				<sql>Pure SQL statement</sql>

To set pure sql after read

	<delete-query xsi:type="delete-object-query">
		<call xsi:type="sql-call">
			<sql>After read SQL statement</sql>


Basicaly, we can execute any pure sql statement selecting this option in the DbAdapter.
When we select this option we will not delete some row if we add these codes to overwrite the delete function with another sql statement.

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.

Customizing service calls with multiple levels of security in OSB 12c

This will be a part two of my previous post which you can read [here](… Continue reading