Obsessed with Oracle PL/SQL

Write code for a living? Feeling kind of lazy? Then maybe you should find a way to generate  some code. That's the focus of this blog post. The bulk processing feature of PL/SQL was introduced a long, long time ago. It lets us, among other things, do this: Lovely, concise syntax. One downside, however, is that the target collection in the BULK COLLECT INTO clause must be indexed by integer (which means all nested tables and varrays, but only INDEX BY - associative array - collections that are index by PLS_INTEGER or variations therein). This means that if you do want to use a string-indexed collection, you need to first "dump" it into an integer-indexed array, and then move it over to a string-indexed array. I've done this, and my code usually looks like this: DECLARE CURSOR c IS select last_name, first_name, employee_id from employees where department_id = 30; TYPE t IS TABLE OF c%ROWTYPE; c_limit CONSTANT PLS_INTEGER := 10...

I recently received this question in my In Box: Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation  says : "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post  claims  Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick Answer There is no significant difference I can see in the performance based on different collection types (with the limited tests I have run). Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2). Use the documentation as a starting, not ending, point of your exploration. Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read fur...

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