Obsessed with Oracle PL/SQL

http://bit.ly/dg-tf A table function is a function that can act like a table inside a SELECT statement. The function returns a collection, and the SQL engine converts that collection into rows and columns that can be manipulated with standard SQL operations (joins, unions, where clauses, etc.). Far and away the most popular post on this blog is an introduction to a series of articles on table functions: Given that level of interest in a very interesting feature of PL/SQL, I thought it would be a good thing to give you even more resources to learn about table functions. So I put together a FREE class at the Oracle Dev Gym  on PL/SQL table functions. It consists of four modules and gives you a solid grounding in table function fundamentals: Each modules consists of a video that covers the basics, followed by a LiveSQL tutorial that dives into more of the details, and gives you an opportunity to run and play with the code. We then finish up the module ...

A left correlation join occurs when you pass as an argument to your table function a column value from a table or view referenced to the left in the table clause. This technique is used with XMLTABLE and JSON_TABLE built-in functions, but also applies to your own table functions. Here's the thing to remember: The table function will be called for each row in the table/view that is providing the column to the function.  Clearly, this could cause some performance issues, so be sure that is what you want and need to do. The following code demonstrates this behavior, for both pipelined and non-pipelined functions. CREATE TABLE things ( thing_id NUMBER, thing_name VARCHAR2 (100) ) / BEGIN INSERT INTO things VALUES (1, 'Thing 1'); INSERT INTO things VALUES (2, 'Thing 2'); COMMIT; END; / CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER) RETURN numbers_t IS l_numbers numb...

I've published several posts on table functions . Here's another one! Why? A few days ago I tweeted the following: Two of my followers immediately asked to hear the story: "those are the fun ones, but pics or it didnt happen ;-)" and "may be interesting to view the step you do to find solutions more then result." Very good points; I especially agree with the latter. As I was going through the revisions to my code, I was thinking (as I have often done before): "It might be helpful to show the process I go through, because it sure feels like a powerful, positive way to build and improve code." The problems I run into when actually sitting down to tell the story are: 1. Time: yes, I know, we are all busy. Plus, isn't it my job to share thoughts on PL/SQL programming?  Yes it is! Well, part of my job, anyway. And I have  been neglecting my blog. But right now, at this moment, I am very focused on finishing the "early adap...

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...

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...

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,                                 c...