BizTalk 2006 Oracle Adapter (ODBC) - An alternative for Poll Statement with Function (Not Procedure)

In Oracle Adapter, there are several ways to poll the data from the database, in this post, I will be discussing on the polling data without triggers, just by using Poll SQL Statement and Post Poll SQL Statement.

Part 1 - The First Project
We have a table of data which will be polled by several orchestrations based on a status field in the table to process, see below screen for the details.

By using Poll SQL Statement to get the rows and Post SQL Statement to update the rows to other status is actually working fine for most of the cases, and it is important to note that they are both executed within a transaction of serializable isolation level.

In my first project, it is working fine, because the frequency is quite low, poll 1 row of data / 60 seconds. Life is good ;)

Part 2 - The Second Project
Ok, now in which kind of scenarios where it is not working as expected?

We have a new application which needs to poll rows of data in short interval, because the expected data to process will be huge, so it is not acceptable for us to poll 1 row / 60 seconds.
In short, we need to process 3.000 rows of data in 1 hour, so that leaves us with 50 rows / 60 seconds.

So what's the issue with that? just change the query to select top 50 and off you go? hhhmm... not as smooth as we thought :P

Reasons : As the processing got huge, it seems that the server and/or the adapter are affected as well. Because of the latency, it seems that the Post Poll SQL Statement is not executed in the expected time, this cause the subsequent polls may poll the same data as the previous one, because the Update the row status in the Post Poll SQL Statement hasn't been executed yet. This eventually lead to double processing of the data.

Part 3 - The Search for Alternatives
What is the ideal way for this? I would say that call a procedure to poll the data, because we can get the rows and update them in the same time. And btw, I don't really like the way Poll SQL Statement and Post SQL Statement work, we need to make sure that the conditions / where statements in the Poll SQL Statement and Post Poll SQL Statement are the same or we'll get nasty update results :P

Unfortunately, I have been trying for calling a procedure without any good results for quite a while now, and i have posted some questions about this in the web and they have been replicated to many places now :P links

Well, several people have mentioned that they are able to use oracle procedure to poll the data, however I haven't received more details about it until now.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2527819&SiteID=17
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.biztalk.general&tid=97564c53-15ee-4a8e-997c-72f8c2520241

The main problem with oracle procedure, it will need an output parameter to return a resultset, and I was not able to think a way to work with this parameter in the Poll SQL Statement.

So several days ago, i stumbled on a post (I forgot where from) but someone from the post mentioned about using function in the select statement like this below :

Select packagename.functionname from dual;

This gave me an idea, because it will just like a normal select query which returns row(s).

So the next thing to do is to have the DML statement to update the selected rows, I found a way to allow DML Statement in the function, is to use PRAGMA AUTONOMOUS_TRANSACTION which will summon its own transaction so it will not affect the caller transaction.

Part 4 - The Solutions

Solution 1 - Get one row
1. Create an oracle function to select and update the row then return the unique key

CREATE OR REPLACE FUNCTION pollonejob RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
JOBID NUMBER;
BEGIN
SELECT JOB_ID
INTO JOBID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM = 1 FOR UPDATE;

-- This is to test the lock only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID = JOBID;

COMMIT;

RETURN JOBID;
END;
/

2. Setup the Poll SQL Statement :
SELECT pollonejob AS JOBID FROM dual

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status FROM jobs WHERE job_id = pollonejob

Solution 2 - Get more rows
1. Create an oracle type as the container for the unique keys
CREATE OR REPLACE TYPE number_key_t is TABLE OF NUMBER

2. Create an oracle function returning the previously created type with a parameter to speficy how many rows to retrieve
CREATE OR REPLACE FUNCTION pollmultiplejob(noOfRows NUMBER) RETURN NUMBER_KEY_T IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_data NUMBER_KEY_T := NUMBER_KEY_T();
BEGIN
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM <= noOfRows FOR UPDATE;

-- This is to test the locking only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID IN (SELECT column_value FROM TABLE(CAST(l_data AS NUMBER_KEY_T)));

COMMIT;

RETURN l_data;
END;
/

3. Setup the Poll SQL Statement :
SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T))

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status
FROM jobs WHERE job_id IN (SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T)))

Notes :
1. You do not need the Post Poll SQL Statement anymore, since when the Poll SQL Statement is executed, the rows will be updated in the same time it polls
2. Avoid setting the poll interval less than the time to execute the polling query, for example if the polling takes 30 seconds to complete, it's better to set the poll interval 40 seconds or more, consider the future data growth
3. As the function will update the data when being called in the select query, please do handle that more carefully by giving more meaningful function name, so everyone will know that the function will not only retrieve the data but also update the rows in the same time.

I have tested this with one receive location for several days now and it seems that they are working fine as expected :)

I'm just glad that I have this workaround or you might say that this is a bit hack :P but it did solve my problem and work for Oracle Adapter with ODBC, and i hope it will be useful for you guys as well.

**Update** I have tried creating 5 receive locations calling the same function and there were no duplicate poll :D
However, there is a flaw, the FOR UPDATE statement may not work as expected when trying to poll the data sequentially where in oracle you will need to have a sub query like below :
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_ID IN (
SELECT JOB_ID
FROM (
SELECT JOB_ID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
ORDER BY JOB_ID
) A
WHERE ROWNUM <= 1
)
FOR UPDATE;
I experienced duplicate polls with this, so make sure you test your query first for any duplicate issue.

Feel free to drop any comments or questions ;)

Microsoft.ServiceModel.Channels.Common.ConnectionException: ORA-1017: invalid username/password; logon denied

This is the first problem I had with the new oracle adapter pack.
From the error message, it is quite obvious right. But I was quite sure that I have provided the right user name and password.

I found the answer here, the user name / password may be case sensitive. After providing the user name and password with the appropriate case sensitivity, it is working now. hopla ;)

Installation and setup files for BizTalk 2006 R2 with Oracle Adapter Pack 3 (Evaluation Edition)

I'm currently trying BizTalk 2006 R2 with the new BizTalk Oracle Adapter Pack 3.0 (ODP.Net) to resolve the locking issue we had with the previous Oracle Adapter version with ODBC.

You can grab the installation files from here :

1. Oracle Data Access Component (ODAC)
   
2. BizTalk Server 2006 R2 Evaluation Edition (Note: You will not be able to perform upgrade with the evaluation edition, you will need to uninstall the current BizTalk development edition first and then install the evaluation edition)
3. WCF LOB Adapter SDK
4. BizTalk Oracle Adapter Pack 3.0 (Oracle adapter included inside)
5. BizTalk Adapter Pack: Oracle Database Adapter Samples
   

FOR UPDATE statement may not work with BizTalk Server 2006 Oracle Adapter ODBC

I created a simple test console application today for testing FOR UPDATE statement using different types of component.

1. Using ODP.Net - Working
Note : The second execution will wait until the first execution to finish first.

2. .Net Oracle Client - Working
Note : A slight different between odp.net and .net oracle client is that the second will wait until the first program ends, so eventhough the first program has finished the execution, it was still waiting until i close the first program, i suspect there is a different transaction handling between them.

3. ODBC - Not working
Note : Quite a surprising result, but as we expected. They seems to ignore the FOR UPDATE statement.

Then I found these information at MSDN and oracle sites for ODBC :
http://msdn2.microsoft.com/en-us/library/ms711792(VS.85).aspx
http://msdn2.microsoft.com/en-us/library/ms713566(VS.85).aspx
http://forums.oracle.com/forums/thread.jspa?threadID=607056&tstart=60
http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/ODBCFAQ.pdf

It says that before the statement is passed to the driver, the FOR UPDATE clause will be removed first and this seems to apply not only for Oracle ODBC, but as a standard ODBC Programming reference.

My conclusion : As long as we're using Oracle Adapter with ODBC, we'll stuck with this unless we use the new Oracle WCF LOB Adapter with ODP.Net which requires BizTalk Server 2006 R2 and .Net Framework 3.0.

Update : Below is the query result which displays the executed queries in the oracle, see that the query for TestOracleLock.exe (2nd row) didn't have the FOR UPDATE statement which I specified in the code earlier.

You can use this query below to get the result above :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
Order By last_load_time DESC;

My Podcast List, Shared RSS Feeds, and Twitter

Podcast List
If you noticed in the right section after my shared feeds, I have compiled the list of podcasts (Audio only & w\Video) into a shared Google Notebook, I have put it there a week ago but I didn't have anytime yet to blog it until now.

This is the podcast list which I'm currently subscribing and listening to them mostly in my daily commute to and from the office.

Shared RSS Feed
I also have a Google Shared feeds at the right section or you can open it up from here.

These are the tools which I have been using in my windows mobile 6 :
1. Viigo - RSS Feed
One great feature of Viigo is I can manage my subscription online through My Viigo website
2. BeyondPod - RSS Feed & Podcatcher
I only use this to subscribe my podcasts since I already have Viigo for RSS Feed

On the desktop / laptop, I'm using Google Reader for my feed subscriptions and yes I need to sync up manually of what i have read in Viigo and Google Reader, I do this because I don't have unlimited data subscription plan for my mobile. I'll probably wait until Google Gears for Mobile can be used for Google Reader Mobile ;)

Twitter
If you haven't used this, you might want to read Scott Hanselman's post and Robert Scoble's post to see whether it's worth for you to use.
Please add me if you already have a twitter id or you have just joined ;)