If your application is mainly OLTP, with short, single-action DML statements issued by the client, there really isn’t a compelling reason to use PL/SQL. Just build a nice, data-layer framework in the language you’ve standardized on for the client side. - to issue several SQL statements in succession for each user or event-triggered action - to avoid the latency and overhead of transporting data over the network for processing that could be done in the database - to comply with some architectural directive to keep all the business or data layer logic in PL/SQL For those who have used PL/SQL as a core component of their enterprise applications architecture, you no doubt have run into a few frustrating limitations of PL/SQL. We’ll talk about these limitations, how 8i and 9i new features solve every one of them, and dive into some examples that should help solidify the concepts and give you ideas on where you could use the new features in database and application design. However, I didn’t want to just give you all the answers; it doesn’t tend to stick. Getting your hands dirty, solving a work problem by yourself; this is what develops the real PL/SQL skills and expertise. What’s that old maxim? “If you teach a man to fish…” I didn’t know the exact wording or author of that old proverb, so I looked it up. At first I ran across a few that didn’t quite say what I had in mind:

In that spirit, here is where I learned how to fish.  This is where you should try to find your answers on your own. I’ve listed them in order of their past usefulness to me. There are the two books listed at the back of the paper of course, and there are other sites like orafaq.com, experts exchange, etc. There are some excellent sites I use for DBA and performance tuning work, like Tim Gorman and Steve Adams’ sites, but these are what I use for PL/SQL programming.

There are a number of grievances I’ve had with PL/SQL. These include an incomplete UTL_FILE package (also fixed in 9i), the inability to “introspect” to see what your name is and what line you died on, Oracle’s annoying habit of stripping comments and comment headers from triggers and views, and then the list of items in this slide. The first two items on this slide were fixed in 8i. The remainder were fixed in 9.0.1 and 9.2. Cannot store a list of values related to an individual row.  Alternative: create a separate attributive table to hold the row's list.  These "temporary" child tables clutter the schema, add processing and storage overhead, and make code more complex. Cannot use a collection in SQL statements.  Alternative: create, populate and join to a temporary table. Cannot access list values using character-based keys.  Alternative:  create another kludge table to store the key:value pairs, or roll your own hash tables using a packaged PL/SQL table of record, hash algorithms and functions. Do-able, but not fun.        Cannot insert or update a table using a record.  Alternative: None. Break out a record into its individual columns for insert/update.  This effectively eliminated the benefits of abstraction and de-coupling that records were meant to provide. NDS implementation didn’t support some of the nifty new features. Since NDS is where the real power is at, that was frustrating. Could not model an array in more than one dimension. I haven’t really had a need for this since my C days, but I’m sure other Oracle programmers have really missed the ability. Could not do array-like, or bulk SQL operations on collections.  Alternative: None. Limited to one-by-one row processing.  Shifting from the PL/SQL to SQL engine as each row was processed was a real drag on performance. Cannot use awesome BULK ops to get data directly into collections of record.  Alternative: break every column down into its own collection of scalar. That was really unpleasant working with any more than a three-column table.

I enjoyed that Vincent Price photo to express my frustration, but I was originally going to go with this little Orangutan baby. But there was something unsettling about the picture. I finally realized…

…that it looked a whole lot like my 8-month old baby girl. Then it wasn’t so funny ;-)

You need to see this briefly so that you can more readily read the code examples, as opposed to scratching your head wondering what on earth I’m trying to do. Seeing this pseudo-Hungarian notation makes most professionals who’ve seen it very glad they don’t work on my team. We’ve found it very helpful to always know exactly what we’re dealing with, rather than having to backtrack and hunt down the original variable declaration to see if it’s a string, or a number or collection. Good variable naming can bypass the need for a strict convention like this, but good variable naming often requires more characters than PL/SQL allows. So we think it’s nice for consistency, brevity, and it allows us to be a little lazy with the naming. It’s certainly a far site better than the sorry “p” for parameter “v” for variable convention (started by unschooled Oracle corporation programmers). What does that buy you? Almost nothing . In my mind, it’s worse than no standard at all.

A record is much like a row in a relational database table, composed of one or more individual fields, or columns.  The structure of the record’s type can be explicitly defined by the programmer, or implicitly defined by the %ROWTYPE attribute.  %ROWTYPE abstracts the details of the underlying object.  As columns are added, deleted and modified, your code doesn’t have to change as often.  Records allow you to assign, access, refer to and pass entire rows and tables as a single unit, as opposed to scores of individual variables or parameters. We choose PL/SQL when we need to 1) batch up multiple SQL operations, or 2) improve speed by performing logic within the database, rather than moving the data over the network.  Since both goals involve operations on rows of data that is/will be stored in relational tables, you should be using records most of the time!  This is a common best practice in PL/SQL circles.  Leaning heavily on records leaves your code simpler, cleaner, easier to read and maintain, and more robust.

· In practice, most varrays (per row) total less than 4000 bytes.  This data will be stored RAW inline and makes varrays your best choice for speed and efficiency.  If the data surpasses the 4000 byte mark, or if you specify a LOB storage clause for the varray, Oracle will create a LOB segment and move the data out of line. · Varrays cannot be extended beyond their initially defined maxsize.  You will need to drop and recreate the type and table if you miscalculated the maxsize during modeling. · Varray columns can't be modified using SQL; you are reduced to using PL/SQL to insert, update or delete varrays.  You can't just replace an element inside the varray either; you must replace the whole array. · Physically, nested tables are implemented by Oracle as a child table, with a hidden column (nested_table_id), which is foreign-keyed to a surrogate, hidden column in the parent table (one for every nested table column).  The child table may not be queried or modify independently of the parent. · There is no inherent order to how data in nested tables is stored or returned, and foreign keys are not supported.  If you want to impose order, you must add an attribute to the nested table for indexing or sorting. · Unlike regular DML, when unnesting Oracle doesn't allow the subquery to return zero or multiple collections.   The subquery to get the row's nested table must bring back only one nested table. · If you do create columns using nested tables, index the nested_table_id pseudocolumn.  Since the child table created by the server to store your nested table has the tendency to de-cluster the ordered data you've put in there, it is also a good idea to define the nested tables as index-organized (less physical IO). . Oracle docs speak of compressing the leading column in the UK you add to the nested table. In the docs, they show the nested_table_id as the letters A,B,C etc. In reality it is a chock-full RAW(16) column which can really save some space if compressed. . So both the parent and child end up with a rather large extra column, even though the parent probably already has a UK/PK. There is also the overhead of the extra UK on the parent table.

Systems grow. Business strategy and requirements change course. Marketing invents new promotions. Management requests new metrics. The only real constant is change. Our data models, development processes and testing methods must accommodate rapid change. Unfortunately, the majority of us do not have such a setup. Especially with regard to the foundation of everything else, the data model. The data model is especially fragile. It needs to be designed correctly and flexibly from the first draft. If it is not, what occurs is something I call “mudballing.” The DBA or developer either cannot or does not dare alter the data model in significant ways. Instead, they just tack on yet another column to an already large, poorly modeled table, thereby adding another layer to their precious mudball they don’t dare peel or crack open. With time, these mudball tables take on a life of their own. Everything depends on them. No one dares remove anything from them “just in case.” Within 10 years, only 10% of the columns will actually be used, but there are so many layers, so many years of bad decisions that you just have to go with the flow or hope the data center burns down so you can start from scratch. On the RMOUG listserver in January, in fact, a poster mentioned a gigantic mudball table he had inherited that had 350 columns in it. Mudballing metaphor best represented by the fiddler crab, but I love the image of this little scarab carefully gathering and packing down his favorite stuff into another layer on his prized ball. When I hear of a mudball table or system, where the management isn’t willing to risk doing it right, cleaning up or starting over, but would rather live with all the poor decisions of the past, I like to close my eyes and think of this hard-working little beetle, carefully building and jealously guarding his ball. It makes me chuckle and helps me deal with the pain of working with such awful data structures. Records are designed to minimize the impacts of change. If you have mudball tables and dung beetle managers, records could be especially beneficial to you.

Use CAST to tell Oracle "again" that it is really what it says it is: a nested table

I was going to provide lots of screenshots showing how TOAD, SQL Navigator, Rapid SQL, SQL-Station and PL/SQL Developer all handle LOBs, objects and collections. But time was running short. I’ll have to leave the comparisons up to you. If your PL/SQL involves records and collections, you owe it to yourself to find a tool that smoothly handles complex columns, types, attributes, parameters and return types. This is especially true for generating test code, insert/update statements and anonymous blocks. Such features could save you 50-60% of your coding time. If your tool does not do these things, demand enhancements from your tool vendor.