Listing-1
1 DECLARE
2 type amortTableLine is record (
3 pmtDate date,
4 ToInterest number(12,2),
5 ToPrincipal number(12,2),
6 EndingBalance number(12,2)
7 );
8 type amortTableType is table of amortTableLine
9 index by binary_integer;
10
11 amortTable amortTabletype;
12
13 p_LoanAmount number(10,2);
14 p_InterestRate number;
15 p_loanTerm number(2,0);
16 p_effectiveDate date;
17 p_payment number(7,2);
18
19 v_pmtNumber number(4,0);
20 v_monthlyRate number;
21 v_totalInt number(13,2);
22 v_totalPmt number(13,2);
23 v_currentPrinc number(8,2);
24 BEGIN
25 p_LoanAmount := 100000;
26 p_InterestRate := 6.5;
27 p_loanTerm := 30;
28 p_effectiveDate := to_date('20090315','yyyymmdd');
29 p_payment := 632.07;
30 v_pmtNumber := 2;
31 v_monthlyRate := p_InterestRate / 1200;
32
33 amortTable(1).pmtdate := add_months(p_effectiveDate,1);
34 amortTable(1).ToInterest := p_LoanAmount * v_monthlyRate;
35 amortTable(1).ToPrincipal := p_payment - amortTable(1).ToInterest;
36 amortTable(1).EndingBalance := p_LoanAmount - amortTable(1).ToPrincipal;
37 v_totalInt := amortTable(1).ToInterest;
38 v_totalPmt := p_payment;
39
40 WHILE (v_pmtNumber <>
What Listing-1 does is to put each line of the amortization table into a separate
Associative Array record. Some comments on the program:
Lines 2 - 11: Here we define the array in which we will store each line of the
amortization table. This is done in the standard way, by first (lines 2 - 6)
defining a record type (to define the fields of the line), then an array type
(lines 8-9) and finally (line 11) declaring the array (amortTable) as an instance
of that array type. This could be done in one step if Oracle supplied a suitable
built-in data type, but it doesn't.
Lines 13 - 23: Now we define the variables to be used in the routine. Although
this is going to be run as an anonymous PL/SQL block, we define 5 variables to sort
of be input parameters, and indicate as much by prefixing them with 'p_'
Lines 25 - 331: Here we initialize those variables which require initialization.
Yes, this could have been combined with lines 13 - 23.
Lines 33 - 36: The first line of the amortization table requires special treatment,
so we do that here.
Lines 37 & 38: We are going to calculate the total of payments and the total
interest paid by accumulating the values, so we start the process here. Back when
I was more of a mathematician, I would have skipped this and calculated them at the
end. Since then, I have been perverted by the Finance people, and insist on doing
these calculations the hard way.
Lines 40 - 49: The body of the loop where all but the last payment are calculated.
Lines 50 - 55: Here we calculate the final line in the table.
Lines 57 - 67: And here we print out the results.
Remember the Sherlock Holmes story about the barking dog? The dog didn't bark. And
that was the key to the mystery.
What is most significant about using associative arrays with a dense integer
subscript is what does not have to be done. There is no need to do anything special
to create a row in the array; one just adds it. There is no need to do anything
special to navigate the array, one just increments the index.
The results of running Listing-1 are as follows:
| Month | Interest | Principal | Balance |
| 15-APR-09 | 541.67 | 90.40 | 99909.60 |
| 15-MAY-09 | 541.18 | 90.89 | 99818.71 |
| 15-JUN-09 | 540.68 | 91.39 | 99727.32 |
| 15-JUL-09 | 540.19 | 91.88 | 99635.44 |
| 15-AUG-09 | 539.69 | 92.38 | 99543.06 |
| . | . | . | . |
| . | . | . | . |
| . | . | . | . |
| 15-JAN-39 | 10.15 | 621.92 | 1251.75 |
| 15-FEB-39 | 6.78 | 625.29 | 626.46 |
| 15-MAR-39 | 3.39 | 626.46 | 0 |
Nothing surprising here. So, let's now try something new. We can now index a PL/SQL table with a VARCHAR2 index (which is why they are now called 'associative arrays'). Let us try to do this by using the payment date as the index, while making as few changes as necessary to our program. The result is Listing-2.
Listing-2
1 DECLARE
2 type amortTableLine is record (
3 ToInterest number(12,2), /* No more payment date field in the record */
4 ToPrincipal number(12,2),
5 EndingBalance number(12,2)
6 );
7 type amortTableType is table of amortTableLine
8 index by varchar2(9); /* This will become the payment date, but it */
9 /* cannot be a date type */
10 amortTable amortTabletype;
11
12 p_LoanAmount number(10,2);
13 p_InterestRate number;
14 p_loanTerm number(2,0);
15 p_effectiveDate date;
16 p_payment number(7,2);
17 v_pmtNumber number;
18
19 v_pmtDate date; /* We track the payment date as a date (to use date arithmetic) */
20 v_pmtDateIdx varchar2(9); /* Because we are using it as an index */
21 v_monthlyRate number;
22 v_totalInt number(13,2);
23 v_totalPmt number(13,2);
24 v_currentPrinc number(8,2);
25 BEGIN
26 p_LoanAmount := 100000;
27 p_InterestRate := 6.5;
28 p_loanTerm := 30;
29 p_effectiveDate := to_date('20090315','yyyymmdd');
30 p_payment := 632.07;
31 v_pmtDateIdx := to_char(add_months(p_effectiveDate,1)); /* Deliberately using the session's date mask */
32 v_monthlyRate := p_InterestRate / 1200;
33
34 amortTable(v_pmtDateIdx).ToInterest := p_LoanAmount * v_monthlyRate;
35 amortTable(v_pmtDateIdx).ToPrincipal := p_payment - amortTable(v_pmtDateIdx).ToInterest;
36 amortTable(v_pmtDateIdx).EndingBalance := p_LoanAmount - amortTable(v_pmtDateIdx).ToPrincipal;
37 v_totalInt := amortTable(v_pmtDateIdx).ToInterest;
38 v_totalPmt := p_payment;
39 v_pmtDate := add_months(p_effectiveDate,2);
40 v_pmtDateIdx := to_char(v_pmtDate);
41
42 WHILE (v_pmtDate <>
The first change is at Line 8, where the 'index by' variable becomes a varchar2(9). This induces a change at Line 3, where we take the payment date out of the record (we could have left it in, but the result would have been very confusing.
But this leads to a problem: how do we generate new indexes? Back in the old days, we would have kept the date in a numeric variable, and written routines to increment it as needed (and hoped we didn't have to cross the year 2100 on February 29th!). But this is Oracle, and we have a real Date Type, with Real Date Arithmetic. So, we keep the payment date variable as a date, and use standard date arithmetic on it. Then, we convert the incremented date to the varchar2 variable we use as the actual index (this can be seen in lines 39 - 40 and 48 - 49).
Lines 43 and 45 illustrate the next problem. With an integer subscript, the predecessor of row 'I' was simply 'I - 1'. To do this here would require us to subtract 1 from a varchar2 variable, which is an exercise in frustration. But PL/SQL is a modern, somewhat object-oriented language. So we have a 'prior' method (or function), which returns the previous row, as needed.
So, now when we print out the results we get the following:
| Month | Interest | Principal | Balance |
| 15-APR-09 | 541.67 | 90.4 | 99909.6 |
| 15-MAY-09 | 541.18 | 90.89 | 99818.71 |
| 15-JUN-09 | 541.18 | 90.89 | 99818.71 |
| 15-JUL-09 | 541.18 | 90.89 | 99818.71 |
| 15-AUG-09 | 541.18 | 90.89 | 99818.71 |
| . | . | . | . |
| . | . | . | . |
| . | . | . | . |
| 15-JAN-39 | 524.61 | 107.46 | 96743.03 |
| 15-FEB-39 | 524.61 | 107.46 | 96743.03 |
| 15-MAR-39 | 96957.37 | 0 |
Which is not even close to what we want.
Here is what went wrong: when we calculated the second row, we calculated the index as May 15, 2009. This was converted using the default date mask to '15-MAY-09'. The prior function returned the loan balance as of the only other index present, which was '15-APR-09'.
Next we calculated the third row. Now the index is '15-JUN-09' The prior function grabbed the loan balance for the index which sorts just before '15-JUN-09'. This is not '15-MAY-09', but '15-APR-09' all over again. And the same thing happened with July 15, 2009. Indeed, it is not until we get to August that we again reference '15-MAY-09', by which time we were intending to reference '15-JUL-09'.
The fix is set the default date mask to something which coverts to a varchar2 value which sorts in the order we need:
SQL> alter session set nls_date_format = 'yyyymmdd';
Then, when we run the program, we get what we want:
| Month | Interest | Principal | Balance |
| 15-APR-09 | 541.67 | 90.4 | 99909.6 |
| 15-MAY-09 | 541.18 | 90.89 | 99818.71 |
| 15-JUN-09 | 540.68 | 91.39 | 99727.32 |
| 15-JUL-09 | 540.19 | 91.88 | 99635.44 |
| 15-AUG-09 | 539.69 | 92.38 | 99543.06 |
| . | . | . | . |
| . | . | . | . |
| . | . | . | . |
| 15-JAN-39 | 10.15 | 621.92 | 1251.75 |
| 15-FEB-39 | 6.78 | 625.29 | 626.46 |
| 15-MAR-39 | 3.39 | 626.46 | 0 |
The alternative is to obey Singer's first law of Oracle Date Types:
"Never convert (implicitly or explicitly) an Oracle Date to a number or a character string without wrapping it explicitly in a TO_CHAR function with a format mask you supply"
But this did nicely illustrate what can go wrong with a varchar2 index on an associative array where the sort order is not what one expects.
No comments:
Post a Comment