Obsessed with Oracle PL/SQL

Years ago, Oracle Corporation gave out Excellence Awards for developers. For example, I was honored to be chosen as "PL/SQL Developer of the Year" in both 2002 and 2006. But it's been a while since developers received those awards. The focus seems to have shifted more to C-level awards. Well, hey, C-level folks need to feel appreciated, too. But these days, application developers rule the roost (or, to be less playful word-wise, build the apps) and it's critical that we at Oracle both celebrate and help support more effectively the communities of developers building apps on top of Oracle Database. That's why I put together the Oracle Developer Advocates team - and that's why Andy Mendehlson, Executive Vice President of Server Technologies at Oracle, authorized the establishment of a new awards program, under the sponsorship of the Oracle Technology Network:  Oracle Database Developer Choice Awards   For 2015, awards will be given out in the foll

I encourage everyone attending Kscope15 to take a vow: I will not drink water from a disposable plastic bottle. It won't be hard to find them. I imagine the conference organizers will provide them by the bushel. But remember: every piece of plastic you consume will haunt the planet for hundreds, probably thousands of years. The worst thing to use plastic for is something disposable. And bottled water? Much of it is not even cleaner or "better" than tap water (though it will almost certainly have filtered out the nasty chlorine taste). Much of it is actually nothing more than filtered tap water (for example, Dasani-Coke). Instead, bring a steel water bottle (best not to drink or eat out of plastic altogether - there are many inexpensive such bottles available via Amazon and so forth). Refill it as needed. If lots of us foreswore plastic water bottles at Kscope15, we could reduce landfill and waste by hundreds of bottles. ODTUG would likely even save some mo

I've built (or helped build) two unit testing frameworks for PL/SQL over the years: utPLSQL (open source) and Code Tester for Oracle (Dell). I pushed long and hard with my audiences to follow test driven development, to build automated regression tests, etc. The peak of my TDD/unit testing drive was represented by my "Six Simple Steps to Unit Testing Happiness." I had fun doing these presentations, and my audiences laughed at how I made fun of we all tested (or rather did not  test). And I am sure that I did have some impact on behavior....but.... While I am still convinced of their value, I spend far less time promoting these ideas these days, mostly for the following three reasons: 1. I hate hypocrites (who doesn't?). And the hypocrite I detest more than any other? Me. Bottom line is that I did not / do not use either of those tools (nor SQL Developer's integrated unit testing ) when I write code. Why? All the usual excuses I pooh-poohed in my talks: &qu

I am very pleased to announce that as of 15 June 2015, Connor McDonald, ACE Director extraordinaire, recognized worldwide for both his depth of knowledge on Oracle Database and his excellent presentations, is now an  Oracle Developer Advocate for SQL. Welcome, Connor! Connor McDonald has, over the past 18 years, worked with Oracle Database systems in Australia, the UK, Southeast Asia, Europe, and the United States.  He has co-authored three books, and has been a popular speaker at Oracle conferences around the world, specializing in topics on the database engine, in particular SQL and PL/SQL. He has twice won the Inspirational Speaker award by the UK Oracle User Group in 2009 and 2011.  Whether speaking in a full day seminar to a room of developers, or just a "Hey, check out this cool feature" to the person sitting next to him, Connor has always loved sharing information about technology with others.   While working for clients over the years,  Connor  real

When I (re)joined Oracle in March 2014, the PL/SQL Challenge website was also acquired by Oracle. I'd been thinking that in a few months or so, we'd have it up and running on an Oracle server, re-branded with lots of red. But then, well, I got kind of busy with all sorts of other stuff. My bad. But I am very happy to announce that over the coming weekend   (13-14 June) ,  PL/SQL Challenge  will go offline for hopefully no more than a few days and then resurface as an Oracle website. And that's why for the first time in five years , we will not offer new, competitive quizzes on SQL or PL/SQL or anything else this coming week (we will still put up some of our "deja vu" quizzes). I don't want to set up quizzes and then not give you sufficient time to take them (and you never quite know what's going to happen so....). Now, those of you who've been to the website know that we use lots of orange (why? Because it's a pleasant color and also is th

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! In my last post on table functions, I showed how I was able to reduce many Application Express  interactive reports  into one by pushing all the complexity into a table function, thereby reducing the query in the report to nothing more than a "parameterized view": SELECT *   FROM TABLE (      qdb_rankings.ir_other_ranking_tf (category_in  => :p443_category,                                 period_type_in      => :p443_period_type,                                 competition_id_in   => :p443_competition_id,                                 period_in           => :p443_period)) pr I hope you agree that this is a nice trade-off: keep the code in Applicat

So your users make changes to tables (with great care and security, through your PL/SQL API), your app calls this procedure and that function. At some point along the way, a COMMIT statement is executed successfully in a user's session. Yet that same user's session still has uncommitted changes! Huh? How is this possible? Think you know? Comment below!

I recently tweeted   this link  to an article you published in the November/December 2004 edition of Oracle Magazine. Bryn Llewellyn, PL/SQL Product Manager, followed up with a message I thought we might all benefit from reading, so here goes: A side box in the article ends with: " Dynamic PL/SQL statements must end in a semicolon; dynamic SQL statements may not end in a semicolon." That's like saying << when wearing a red pullover, you must make subject and verb in a sentence agree in number; you must not write "myself" when you mean "I">>. Taken literally, it's not untrue. But the implication is that things are different when you wear a differently colored pullover. But, of course, they are not different. It's just a property of SQL that it has no larger building block than the single statement. There is no need for a terminator character. And the use of one, whatever might take your fancy, causes a syntax error. This a unive