Obsessed with Oracle PL/SQL

At Oracle Open World 2014, I was a member of a panel on SQL and PL/SQL Performance. It was an awful lot of fun, but as I had expected, most of the questions focused on SQL performance, and most of them were answered by Tom Kyte (no complaints from me!). I came away with a strong feeling that we should offer separate panels on SQL and PL/SQL, so that each could get the proper amount of attention (or as "proper" as you can get in 45 minutes. Sigh...). Well, my wish has come true! There will be a panel on SQL for Functionality, Performance and Maintainability, and its future , and another on PL/SQL: Optimizing PL/SQL for Performance and Maintainability ( CON8416 ) Moscone South - 306, Wednesday, October 28th, 13:45 - 14:30 Notice that we are  suggesting  that when it comes to "optimization," we should all be thinking about more than performance. Performance is, justifiably, a primary concern for any  application developer (and DBA), but that doesn...

Please do feel encouraged 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! From Oracle Help Center (a.k.a., documentation), we read : Data is said to be pipelined if it is consumed by a consumer (transformation) as soon as the producer (transformation) produces it, without being staged in tables or a cache before being input to the next transformation.   Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.   A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on demand. This makes it possible to use a table function like a virtual table. In a nutshell, this means that the ca...

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! Last week, I published my first post in the Table Function series on pipelined table functions. I included this advice at the top of the post: Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature: You are unlikely to ever need a pipelined table function, but when you do, it is going to be  amazing . I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what they are doing with those functions, it becomes clear that they are not pumped up about the  pipelining . They are not, in fact,  benef...

After a one week extension for the nominations period, we have now closed nominations for the first-ever round of Oracle Database Developer Choice Awards . We have a total of 75 nominations across five categories (SQL, PL/SQL, Application Express, Database Design and ORDS). That's quite a healthy number and we look forward to the judges now winnowing that list down to a set of finalists in each group. Judges? What judges? Isn't Oracle simply going to decide who the finalists - and then winners - will be? NO WAY. These are developer choice  awards. By this we mean that our users  decide the finalists and then vote among the finalists to determine the winners of the awards. We've set up a panel of judges for each category. Each panel consists of four ACEs and/or ACE Directors, plus one Oracle employee. For example, I am on the PL/SQL panel (surprise!). And now you are probably wondering: so who are the other PL/SQL judges? And the judges on the other panels? H...

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! [Gee, that was embarrassing. I start a series on table functions , and then it falters and stops. My apologies; I got so busy eating and drinking and breathing and sleeping and....anyway, back to the series !] In this post I introduce PL/SQL developers to the  pipelined table function . Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature: You are unlikely to ever need a pipelined table function, but when you do, it is going to be amazing . I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what...