Obsessed with Oracle PL/SQL

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

I recently got an email from an Oracle Database developer who was trying to get the MULTISET operator to work in his code. He'd created nested tables of records and found that MULTISET UNION would work but MULTISET EXCEPT would not. When he ran his code he got this error: PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL" I will confess that it took me longer than I'd like to admit (but I just did!) to get to the heart of his problem, so I figure others might get similarly befuddled. Time for a blog post! Let's explore some of the nuances behind musing MULTISET, centered around this important statement from the documentation : Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP methods defined on them to be used in equality

Just getting started with PL/SQL? You will find PL/SQL to be a very readable and accessible programming language. You'll be productive in a very short amount of time! I offer this post as a quick way to access a number of resources that will provide a nicely-paced introduction to this powerful database programming language. Of course, it helps a lot  to know SQL, too, so check out the Other Useful Links at the bottom of the post. I wrote a series of "PL/SQL 101" articles for Oracle Magazine several years ago. Here's a convenient index to all those articles: 1. Building with Blocks  - an overview of PL/SQL, followed by coverage of some fundamentals 2.  Controlling the Flow of Execution  - conditional statements and loops 3.  Working with Strings 4.  Working with Numbers 5.  Working with Dates 6.  Error Management 7.  Working with Records 8.  Working with Collections 9.  Bulk Processing with BULK COLLECT and FORALL 10.  The Data Dictionary: Make Views W