Santosh Benjamin's Weblog

Adventures with AppFabric, BizTalk & Clouds

The Adapter Diaries – Oracle Adapter – 4

with one comment

“Pragma” killed the Post-Poll

Bembeng Arifin commented on my second post in this series calling out something he had found when working with polling and i decided to follow up on this and in my opinion, the technique is so simple, its absolutely brilliant!!

Basically, to avoid some of the problems with duplicate data pickup when using NativeSQL with Poll and PostPoll, Bembeng had used a function in the select statement which automatically did the postpoll statement inside an autonomous transaction (PRAGMA AUTONOMOUS_TRANSACTION – hence the title of this post 🙂 ).

Now I had taken an instant dislike to the NativeSQL when i used it earlier , particularly because i didnt fancy hardcoding a map with index functoids. (I think it may be possible to write a nifty little generic parser for this and maybe will try my hand at it sometime, but dont have enough time for that now). I was happy with the schema provided by the TableChangeEvent so i needed a compromise. I decided to use a view that brought back all the data from the main table where the timestamp was greater than the value in the poll_control table. My aim was that as soon as i got the data successfully i would call an update statement on the poll_control table.

My data comes in through a port map and then gets debatched by an orch (calling a pipeline ) and then the normalized ‘event’ documents get processed by another set of orchs. With this disconnected model it was rather difficult to spot where to update the poll control table because we dont want too much time to lapse such that we lose records (that arrived just after the poll). I tried to create a send port subscribing to the MessageType of the event message, but calling the UPDATE statement didnt work. I got some “object reference not set” type of errors. I guess i havent really understood how to use the UPDATE statement yet.

So, the dilemma was , should i go back to nativeSQL and use Bembengs approach or should i stick it out with the UPDATE? And then I had an epiphany : Why not both together?   (:-) terribly dramatic aren’t I?). The solution then was to use the function inside the view.  The view code is something like


“GetLastPollTime” being the function of course. This works very well. Everytime the tablechangedevent is executed, the view is called which in turn executes the function. Bembeng provides a sample of the function code in the article.

I’m really chuffed with this discovery (and i learned a fancy new thing in Oracle too – PRAGMA directives – woohoo!!). Thank you Bembeng!

A Rant about embedded ‘connection strings’

Whilst the Poll and Post-Poll are a nice feature of the Oracle adapter over the SQL adapter (except of course for the NativeSQL and the frustrations with the oddly named TableChangeEvent that i moaned about earlier), one thing i absolutely detest about the adapter is the way it embeds the connection data in the targetNamespace. (OracleDB://SERVICE_OR_DSN_NAME/SCHEMA_OWNER/TABLES/TABLE_NAME).

Bah! Humbug!. What if you have a different DSN /SERVICE (as most people do) for development and production (and other environments in between such as TEST, UAT and so on). Do you have different schemas? Perish the thought! Maintaining them would be a nightmare.

The approach which I have currently taken is to create a fairly generic service name like ‘CRMSERVICE’ in my Oracle TNSNAMES. That internally maps to the development instance. I also created a System DSN with the same name ‘CRMSERVICE’. Fortunately, the schema owner is the same in Dev and Live, otherwise i would have had to ask the DBA to create the same schema owner name in all environments. Now when i generate the schemas it uses CRMSERVICE /SCHEMAOWNER so when i move to a new environment i can just change the TNSNAMES.ORA file. (Thats the theory anyway – i shall know in a couple of days if that works out or not – currently my local connections and the DEV connections point to the same oracle instance, but not so when we move to TEST).

I really appreciated the SQL adapters approach where it let you create any namespace so you could be creative and just change the connection parameters in the binding file.

Anyway, I guess using SSO (instead of embedded credentials) in conjunction with this generic service name approach would give equal flexibility.

So, tell me, how do you handle this issue with the adapter? does the approach i am using make sense? any suggestions to make it better?


Written by santoshbenjamin

May 27, 2008 at 11:14 PM

One Response

Subscribe to comments with RSS.

  1. Hi Benjy,

    Glad the work around useful for you, and btw i also just found out the PRAGMA directives while trying to find this work around 😉

    I also agree that having DSN name in the schema namespace is not a nice thing indeed, It’s troublesome if later we found out that we need to use other DSN name after the development has been completed.

    Bembeng Arifin

    May 30, 2008 at 6:07 AM

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: