Collections, Part I

By "Collections", Oracle means the use of Varrays, Nested Tables, or Associative Arrays (formerly Index-By Tables (formerly PL/SQL Tables)) in PL/SQL. There are many examples extant, most of which use an (almost) meaningless code fragment to illustrate one, or perhaps two, points. My goal here is to illustrate the use of Collections through a meaningful example. Which is more complicated than one might assume at first glance.

For the first question one must ask when considering the appropriateness of an array in a PL/SQL program, is, "why is a relational table not being used instead"? Many of the examples I have come across in existing programs appear to have been written because the programmer was afraid to write a join. Indeed, when I first was introduced to PL/SQL, my immediate thought was "now I won't have to become a SQL expert." Today, I argue that any proper use of collections should be to do something which cannot be done with SQL.

In 8i and 9i, one standard use was for bulk fetches or inserts. Today, Oracle will automatically optimize DML to use bulk fetches or inserts (the problem is to prevent it when one-row-at-a-time is more efficient.) So, for this series of posts, I want to illustrate other uses for them.

I am going to develop a case study, specifically, that of a mortgage amortization schedule. As an illustration I am going to use what Lending Tree was displaying at the time I went looking. The exact numbers are not important at the moment; here I just want to show the basics of the calculations (only the first 5 and the last two lines are shown):

Amortization Schedule

Month	Interest	Principal	Balance
Mar, 2009	$541.67	$90.40	$99,909.60
Apr, 2009	$541.18	$90.89	$99,818.71
May, 2009	$540.68	$91.38	$99,727.32
Jun, 2009	$540.19	$91.88	$99,635.45
Jul, 2009	$539.69	$92.38	$99,543.07
Jan, 2039	$6.79	$625.28	$628.66
Feb, 2039	$3.41	$628.66	$0.00

Elsewhere, we are told that the annual interest rate is 6.5%, the loan amount is $100,000, and the monthly payment is $632.07. The table then gives us the date of each payment, the amount of the payment allocated to interest, the amount allocated to principal reduction, and the loan balance after the payment is made.

If we were given this as a programming assignment, these would be the proverbial "napkin specs." Before we can proceed, we must bring them up to at least "one-pager" status. We can start by looking at how each column gets calculated:

Month: [initial] = Loan Origination Date + one month
[subsequent] = Previous value + one month

Balance [initial] = Loan Amount - Current Principal
[subsequent] = Previous value - Current Principal

Principal = Payment - Current Interest

Interest: = Previous Balance * I
where I = (Annual Interest Rate) / 1200

To make sure we really understand what is happening, let's look at a few lines. The monthly interest rate is 6.5/1200, or 0.005416667. Multiply this by the original loan balance, and we get the interest for the first month: $541.67. Subtracting this from the payment ($632.07) we find that the principal reduction is $90.40. Subtract $90.40 from $100,000 and we get the balance after 1 month, $99,909.60.

Just to make sure that this isn't a fluke, let us repeat this with the third payment. The balance after two payments is $99,818.71. Multiplying this by 0.005416667 we get the interest for this payment, $540.68. Subtracting this from $632.07 we get the amount devoted to principal, $91.39. Subtract this from last month's balance, and we get the new loan balance, $99.727.32. And the published chart has $91.38 for this month's principal reduction. Payment 3 and we are already out of synch.

The reader who has stayed with me this far with the numbers will have already noted a number of other places where the published chart is wrong. What is happening is that the program computing the numbers is not rounding at each step. The actual payment used is $632.0680238 (which we see rounded as $632.07). The actual interest charge for the first payment is $541.666667 and the balance after one payment is actually $99,909.59868, the balance after two payments $99,818.70765, and the balance after three $99,727.32429. Since the unrounded interest is 540.68467 and the unrounded principal reduction is $91.38335 we see that things really are in balance.

It is, of course, illegal for a financial institution to actually use the example numbers. This gives us another specification for our routine: make sure all intermediate results are properly rounded.

And there is yet another item to be added. Because no intermediate results were rounded, the last line happens to balance perfectly. In general this will not be the case, instead, the last payment will be a little larger or smaller than the others. So, the finished routine will have to adjust the last payment.

Before any specification can be considered complete, the boundary conditions must be examined. We have already examined the case where the final payment must be examined. What happens if the specified payment is less than the interest due? At first glance, all seems well, the principal reduction will become negative and get added to the outstanding principal. But there is a problem here: if we are not careful, we could end up breaking the law.

The "law" allows for two approaches to this problem, where the payment is less than the interest due. One approach, known as the "actuarial method" (because it is more mathematically elegant) allows for the interest to be added to the principal, and, in the next period, for interest to be charged on this interest. The other, termed the U.S. Rule, has the interest added to the principal, but no interest is then collected on this interest. Which rule applies depends on the loan documents. For simplicity, I am going to be applying the actuarial rule, but this complication must be mentioned at least in passing.

Already at the end of the first post, and not a line of PL/SQL has yet been written. Expect quite a bit of it in the next post.