tag:blogger.com,1999:blog-3729574054547172121.post8414099928591499066..comments2024-03-12T04:17:25.995+08:00Comments on Technical Issues & Solutions: BizTalk 2006 Oracle Adapter (ODBC) - An alternative for Poll Statement with Function (Not Procedure)Bembeng Arifinhttp://www.blogger.com/profile/02124310640716335418noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-3729574054547172121.post-87063896192837806542013-06-10T15:58:42.824+08:002013-06-10T15:58:42.824+08:00wonderful information, I had come to know about yo...wonderful information, I had come to know about your blog from my friend nagesh, delhi,i have read atleast 6 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, <a href="//oracletechniques89.blogspot.com/2013/04/single-row-function-in-sql.html" rel="nofollow"> Single Row Function in sql</a><br /><br /><br /><br /><br />Anonymoushttps://www.blogger.com/profile/17313661190126567875noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-85125328686584924382011-11-14T17:06:16.601+08:002011-11-14T17:06:16.601+08:00There is one problem with this approach, and that ...There is one problem with this approach, and that is the PRAGMA AUTONOMOUS_TRANSACTION directive. Normally, when BizTalk polls the database and inserts the message into the messagebox, that will be executed as one distributed transaction. When you use the PRAGMA AUTONOMOUS_TRANSACTION, the update in the polling-table will be done even though BizTalk is not able to insert the message into the messagebox, so BizTalk is not able to do a rollback if the insert intoi msgbox is unsuccessful. I used this approach earlier also, but as exaplained above, it is not perfect, and I am now working on a new and improved pollingmechanism.bluephnxhttps://www.blogger.com/profile/13522501991887083398noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-23876917556672304592011-06-30T02:29:01.557+08:002011-06-30T02:29:01.557+08:00Hi, thanks for your article. We ran into the same...Hi, thanks for your article. We ran into the same situation with our oracle poller for one of our mission critical apps. The post poll was updating rows that were inserted between the poll statement and the post poll statement. We implemented your approach and have had huge success. However, at least once a day we encounter a situation where the function queried the db, found a row, updated the status as being sent, but biztalk did not get the key value returned. we are polling every 3 seconds and return a max of 6 rows. becasue of the frequency we really never get 6 rows. And what i find really weird is the missed polled row is usually from a time where we have a lower load on the system. Has anyone ever encountered this situation where the function updated the row as sent but never hit biztalk be processed.<br /><br />Pieter...Pieternoreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-77075661231555477612011-06-23T13:32:31.814+08:002011-06-23T13:32:31.814+08:00Thanks for sharing this it help me a lot in my Web...Thanks for sharing this it help me a lot in my Web development career.men's dress shirtshttp://www.mensusa.com/tools.aspx?id=234noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-48473974036150370162009-11-17T18:17:48.824+08:002009-11-17T18:17:48.824+08:00Wonderful Article. I had gained lot of things such...Wonderful Article. I had gained lot of things such as how the data is retrieved using the Oracle Connection. The Example taken is this article is really good.stainless steel barometerhttp://www.bitsfortheboat.comnoreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-83747958454050562042008-07-21T16:05:00.000+08:002008-07-21T16:05:00.000+08:00Hi rlg,From what I know, FOR UPDATE statement is s...Hi rlg,<BR/><BR/>From what I know, FOR UPDATE statement is similar to ROWLOCK in MS. SQL Server, it will block other requests (reads) for the returned rows until the transaction is completed.<BR/>As for the incorrect result, I can say that we need to be careful when using ROWNUM, since it may not returned the rows in the correct sequence, you can read it more at http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html. If you are worry about incorrect result regarding to the concurrent requests to the database, I suggest you read more on the isolation level when retrieving the rows.<BR/>From the purpose description, it surely looks like ThreadPool is a good way to go since it works like a queue process. You can find out more at http://www.albahari.com/threading/part3.html#_Thread_Pooling and see whether it suits to your needs.<BR/>Good Luck ;)Bembeng Arifinhttps://www.blogger.com/profile/02124310640716335418noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-70989312067332759642008-07-21T06:55:00.000+08:002008-07-21T06:55:00.000+08:00Hi Bembeng Arfin,The function is within oracle pac...Hi Bembeng Arfin,<BR/><BR/>The function is within oracle package and it is too big to post. My only concern is if 'FOR UPDATE' will cause any issue or not. I have never used this befor. Also, rownum <= 500, along with other WHERE and AND clause has created some concerns too. Someone said if you are using ROWNUM <= 500 along with other WHERE clause, you might not get correct result. I think if I am not using order by clause, I should be fine. <BR/>The purpose of this is to use all thread 100% of time. In normal environment, if you have 1000 records and 5 threads, you would divide them and have each thread work no 200 records. There is strong possibility that one or two thread will be done in 1/4 of time and go to end. My goal is to give each thread say, 100 records first then which ever gets done first, assign it with another 50 so on and so forth.<BR/><BR/>Thanks for your comment.rlghttps://www.blogger.com/profile/05256290059431158565noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-18726304556414750692008-07-20T22:08:00.000+08:002008-07-20T22:08:00.000+08:00Hi rlg,Are you using the cursor in the function? I...Hi rlg,<BR/><BR/>Are you using the cursor in the function? If you are, can you please post the whole function at here?<BR/>The FOR UPDATE statement should work for multithread environment, if you need to test it out, you can try create some .net code to call the function and use my concurrency testing tool to execute that code in multithread execution. The details at http://bembengarifin-tech.blogspot.com/2008/06/testing-concurrency-using-threadpool.html<BR/>Hope that helpsBembeng Arifinhttps://www.blogger.com/profile/02124310640716335418noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-21612679989112215252008-07-20T21:24:00.000+08:002008-07-20T21:24:00.000+08:00I have similar concern:Please see the cursor below...I have similar concern:<BR/><BR/>Please see the cursor below, I want to update col1 to 'P', this will be used in multithreading environment in oracle.<BR/>Do you see any issue?<BR/>I don't need any order clause.<BR/><BR/>cursor c1 is <BR/>SELECT rowid<BR/>FROM table tbl<BR/>WHERE tbl.col1 = 'U'<BR/>AND tbl.col1 = 'abc'<BR/>AND rownum <= 500<BR/>FOR UPDATE;rlghttps://www.blogger.com/profile/05256290059431158565noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-45407216173467924752008-06-19T09:25:00.000+08:002008-06-19T09:25:00.000+08:00Hi Arnold,I looked at the function and it seems to...Hi Arnold,<BR/><BR/>I looked at the function and it seems to be fine.<BR/><BR/>There are 2 things you can try :<BR/><BR/>1. Try to invoke this in your sql Plus and see how many rows are returned and how many rows were updated.<BR/><BR/>Put this in the function after update statement and before the line commit, to print out how many rows were updated :<BR/>Dbms_Output.put_line(SQL%ROWCOUNT);<BR/><BR/>Execute :<BR/>SELECT column_value from table(cast(SHAMAN.BT_POLL_MULTIPLE_PT_CREATE(1)<BR/>AS SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE))<BR/><BR/>2. Change the condition below for temporary, to make sure that you only get 1 row then test again the result :<BR/>AND ROWNUM <= noOfRows FOR UPDATE;<BR/>to <BR/>AND ROWNUM <= 1 FOR UPDATE;Bembeng Arifinhttps://www.blogger.com/profile/02124310640716335418noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-88061305834845881372008-06-18T23:12:00.000+08:002008-06-18T23:12:00.000+08:00HI Bembeng Arifin,select *from BTCTL_PASR_NAMES wh...HI Bembeng Arifin,<BR/><BR/>select *<BR/>from BTCTL_PASR_NAMES where seq_id IN<BR/>(SELECT column_value from table(cast(SHAMAN.BT_POLL_MULTIPLE_PT_CREATE(1)<BR/>AS SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE)))<BR/><BR/>Thanks for your time,<BR/>We try this query result is the same.<BR/>That why we to change the "seq_id IN" to "seq_id =" to return single row. This the function we created. thanks again for time and help.<BR/><BR/>CREATE OR REPLACE FUNCTION BT_POLL_MULTIPLE_PT_CREATE(noOfRows NUMBER) RETURN SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE IS<BR/> PRAGMA AUTONOMOUS_TRANSACTION;<BR/> l_data SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE := SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE();<BR/> BEGIN<BR/> SELECT SEQ_ID BULK COLLECT <BR/> INTO l_data<BR/> FROM SHAMAN.BTCTL_PASR_NAMES<BR/> WHERE POLL_STATUS = 'NEW'<BR/> AND ROWNUM <= noOfRows FOR UPDATE;<BR/><BR/>-- This is to test the locking only<BR/>--DBMS_LOCK.SLEEP(3);<BR/> <BR/> UPDATE SHAMAN.BTCTL_PASR_NAMES<BR/> SET POLL_STATUS = 'POLLED'<BR/> WHERE SEQ_ID IN (SELECT column_value FROM TABLE(CAST(l_data AS SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE)));<BR/> <BR/> COMMIT;<BR/> RETURN l_data;<BR/>END;lastikmanhttps://www.blogger.com/profile/06437054642491493751noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-79064201939524608172008-06-18T20:51:00.000+08:002008-06-18T20:51:00.000+08:00Hi Arnold,Based on the query, it seems that you wo...Hi Arnold,<BR/>Based on the query, it seems that you would return the only one row since you're using "seqid =", otherwise you would get the error ORA-01427: single-row subquery returns more than one row.<BR/>Can you please check the query in the BT_POLL_MULTIPLE_PT_CREATE function that you are using to get the rows and make sure that you use the parameter (1) in rownum = varNoInstance.<BR/>Then check that when you update the query, you are using the temporary BTCTL_SHAMAN_ICARUS_TYPE table.<BR/>Hope you can find the problem :)Bembeng Arifinhttps://www.blogger.com/profile/02124310640716335418noreply@blogger.comtag:blogger.com,1999:blog-3729574054547172121.post-89146507287282549592008-06-18T18:34:00.000+08:002008-06-18T18:34:00.000+08:00Hi Good Day,select *from BTCTL_PASR_NAMES where se...Hi Good Day,<BR/><BR/>select *<BR/>from BTCTL_PASR_NAMES where seq_id =<BR/>(SELECT column_value from table(cast(SHAMAN.BT_POLL_MULTIPLE_PT_CREATE(1) <BR/>AS SHAMAN.BTCTL_SHAMAN_ICARUS_TYPE)))<BR/><BR/><BR/>we used this solutions its work find to the test database, but when we try to ship to live database.<BR/>the query that we used polled all status of the table. please help us thanks you very much.<BR/><BR/>ARNOLD<BR/>TMClastikmanhttps://www.blogger.com/profile/06437054642491493751noreply@blogger.com