Obsessed with Oracle PL/SQL

Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including: High performance querying with BULK COLLECT Super-fast, bulk non-query DML operations with FORALL Table functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases. If you are not already using collections on a regular basis in PL/SQL, you are really missing out. Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs. Documentation The PL/SQL User Guide offers detailed coverage of collection features here . It starts by reviewing the differences between collection...

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  introduction  to table functions, I showed how to build and "query" from a table function that returns a collection of scalars (number, date, string, etc.). If that's all you need to do, well, lucky you! Most of the time, however, you need to pass back a row of data consisting of more than one value, just as you would with "regular" tables when, say, you needed the ID and last name of all employees in department 10, as in: SELECT employee_id, last_name   FROM employees  WHERE department_id = 10 This post explores how you can go about doing that with table functions. So, first, let's explore how not  to do it. That's usually a bit...

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! Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs. So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into. Of course, I am not the first to do so. I encourage to check out the  documentation , as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall . Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part...