JDBCs setMaxRows and the SELECT … FOR UPDATE clause

Today following question came up: Would the JDBC driver of Oracle regonize it, when you set the setMaxRows and perform a SELECT using the FOR UPDATE clause. Example: You’ve a table with 40 rows. Now you call setMaxRows(20) so that you only would get the first 20 rows and perform the select without any where clause (I know, not really a pretty solution: Instead of using rownum, you would select all rows and just stop fetching at the 21th row, but however). Would you now lock all 40 rows or just 20. Well, the answer is (I’ve expected it): You get a row lock on the entry table, not only the first 20 lines. With rownum in the where clause it is now problem. You just lock the selected 20 rows and the other 20 are available for other transactions. So developers: Use the rownum pseudo column instead of the setMaxRows function specially if you perform a select… for update!

But the nicest thing on this little test: I wrote my first Java code since 4 years! 🙂

Possibility of object-oriented programming in PL/SQL

Oh damn, I just get through a presentation of Steven Feuerstein and found out, that PL/SQL supports object-oriented programming. I’m sure, now you think: Why does this stupid guy mean “oh damn…”. Because this “feature” is available since 8i and got strong on 9i. And now they already released 11g! So I didn’t know this possibility several years ago! Well but now I know it and I find it pretty cool, because PL/SQL gets more and more powerful (there are also supported functions for file I/O, HTTP, XML and many many more). Also external or java stored procedures were a big step forward. I just can recur me: Pretty cool!

New Oracle 11g features

Oracle 11g is already available and I just finished reading the “New features guide” of Oracle 11g. There are some really nice features in it: Flashback Transactions, CONTINUE-Clause in PL/SQL, Data Revovery Advisor, SQL Test Case Builder, Automatic Health Monitor, SQL Performance Analyzer just to name some few. I’m really excited if all this features work as Oracle promises, but I’ll finally see, when I’ve installed the database on my test system.