Went to see Steven Feuerstein talk on pl/sql collections today. Pl/sql collections can be usefull in certain situations, and steven had some interesting examples. He started with a quick overview on pl/sql and showed some examples. He then talked a bit about bulk updates to speed up pl/sql code. By reducing the number of context switches between the pl/sql engine and sql engine you can achieve quite impressive performance enhancements. He said that when doing 5 or more inserts at the same time it already pays to use bulk inserts. Ofcourse, using pl/sql tables means that you’ll be using more memory, so you can’t just do everything in pl/sql tables. Problem is that it’s not easy to determine how many rows you should process in a bulk statement.
Next he showed a more complicated use of pl/sql collections. This example is documented on otn. His example is used to detect problematic overloaded procedures and functions. He reads the all_arguments table and stores the data in four nested pl/sql tables. This makes his code easier to read and maintain.
Finally, steven demonstrated a new tool he’s working on: Swyg. The idea is that current pl/sql ide’s do not do enough to help the programmer improve quality. More code should be reused, so the tool will find everything that you can reuse, and it will generated code based on templates. Nothing to download yet, but expected in about a month.
I think reuse would already greatly improve if it would be easier to find code in your database that you can use. For example, i’m currently programming extensions to an oracle applications database. So quite often i’ll browse through all the packages and tables in toad’s schema browser looking for things to (re)use. Using toad’s schema browser on an oracle applications database is a pain. Very slow to load all the packages. And then, how do you find the relevant code?
Also, the idea of code generation is nice, but i would prefer to be able to do this from a uml tool. For example by using poseidon and andromda, to generate tables, packages and unit tests.
Steven’s presentations and code can be downloade from the minmaxplsql website.