This reminded me of a good change in behaviour that I thought I had blogged about but it appears not.

I mentioned this in a comment on Charles’ post above and Mohamed Houri quite rightly questioned my assertion that this behaviour changed as recently as 10.2.0.5.

I can’t find the metalink note that I thought I had referenced before soooo if anyone can verify behaviour on versions before and between those that I currently have access to (need to get that laptop fixed) – 9.2.0.8 and 11.2.0.2 – then I’d be grateful, with particular interest in 7, 8, 8i, 10.2.0.4 and 10.2.0.5.

This is a simple, noddy example but horrendously bad practice, flawed and responsible for a serious duplicate messaging issue recently on a system that I know. But it’s PLSQL code that I reckon there’s still quite a lot of out there.

Forget about the lack of bulk operations, when you look at the results from 9.2.0.8 below, just think about concurrent executions and a SELECT FOR UPDATE driving a LOOP that COMMITS and what that COMMIT does to the supposed protection offered by the SELECT FOR UPDATE on subsequent loops. Thank goodness this is prevented in recent versions.

P.P.P.S. That missing CLOSE reminds me of something else I meant to blog about when someone asked me about whether it was really necessary, as was stipulated in their coding standards, to have all this “IF c1%ISOPEN THEN CLOSE c1;” type code – it’s all about cursor scope…

I wonder if you are remembering Metalink (MOS) Doc ID 14542.1, “COMMITTING OVER A SELECT FOR UPDATE” – Applies to PL/SQL – Version: 10.2.0.5 to 11.2.0.2 – Release: 10.2 to 11.2
That article seems to imply that the advice only applies to versions 10.2.0.5 and above, but it appears that the behavior exists in 10.2.0.2 and 10.2.0.4 also.

A search through MOS finds several related articles, but not much that references 8i or 9i. It appears that there were several patches released to fix false ORA-01002 errors in the 8i and 9o timeframe – I wonder if Oracle also fixed missed ORA-01002 errors at the same time? Reference Doc ID 2361084.8, Bug 2361084, “ORA-1002 possible when not expected after ROLLBACK TO SAVEPOINT” Versions greater than/equal to 8.1.7.3 but less than 10.1.0.2

So, I await with interest some results from Oracle 7 and Oracle 8/8i, maybe even earlier 9i versions.

Like Mohamed, I thought had a vague recollection of this not being allowed in older versions. So when this change in behaviour was revealed by an upgrade, I was surprised. Surprised that it was even allowed in 9i but not having anything older to verify you start to blindly accept that this must have been the way it was, etc, etc.

It’s funny – I hardly believed that this was possible in 9i until I saw what is now clear to be a reversion to the norm in this upgrade.

And pre-9 being such a distant memory now, I convinced myself that it must have always been this way…

What the story is with this phantom metalink note that I seem to remember is, I’m not sure. The only thing that makes sense is that I saw the one mentioned by myself and Charles above, saw the relevance of 10.2.0.5 onwards and bingo 1+1 =3…..

9i is unsupported of course so you’d think this was all irrelevant but just how many 9i systems are still out there and does anyone on these do the above behaviour.

Maybe I was unlucky and found the only 9i system with this coding practice (in 5 or 6 different messaging modules) in existence.

we were using 9i becuase of our ebusinness suite. Last week (may 2012) we upgraded directly from 9i to 11G. And I got the same error as fetch out of sequence. My code was the same as yours. And now in 11G it is not working. I was searching the net for a solution but I ended up. I will change my code.
good luck .