Obsessed with Oracle PL/SQL

If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills. And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ). Here's the list of things to avoid: DON'T set default values of variables to NULL. DON'T select from dual for....just about anything. DON'T declare the FOR loop iterator. DON'T trap NO_DATA_FOUND for inserts, updates and deletes. DON'T execute the same DML statement inside a loop. DON'T hide errors. DON'T re-raise an error without logging first. See below for the gory details. Set default value to NULL Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default v...

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