It is now time to introduce Varrays and Nested Tables into the discussion. Later, I will compare and contrast the three kinds of collections in more detail (in the sort of way which OCP examinations seem to love). As an introduction here, I will just list some of the ways they are like Associative Arrays, and some of the ways they differ.
Associative Arrays, Varrays, and Nested Tables all
- Require that the object be first defined somewhere in a TYPE statement
- Can be used in PL/SQL anywhere a built-in data type can be used
- Use subscript notation to identify individual items.
- Have the same methods
- Can be used for bulk inserts and selects
They differ in that
- Varrays and Nested Tables can be defined in the database (and thus become SQL types)
- Varrays and Nested Tables can be stored as columns in relational tables
- Varrays and Nested Tables can thus appear in ordinary SQL operations
- Varrays and Nested Tables cannot have a varchar2 index.
- Varrays must have an upper size limit specified in their TYPE statement, and this limit cannot be extended
- Varrays must always be 'dense'; there can be no gaps in their index structure (if v is a varray, you cannot have a situation where v(1), v(2), and v(4) exist, but there is no v(3)).
- Nested Tables must be created and loaded dense, but it is possible after the fact to delete internal elements. Thus, one must insert v(1), v(2), v(3), and v(4) into a nested table, but one can then delete v(3).
They also have one other difference with Associative Arrays, which I will illustrate by changing Listing1 to use a Varray instead of an Associative Array. The obvious way to go is to replace lines 8 and 9 with
8 type amortTableType is varray(362) of amortTableLine;
where all we do is replace the clause 'table' with 'varray(362) and remove the 'index by binary_integer'. When we then try to run thus we get
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 32
Which reminds us that Varrays and Nested Tables have to first be 'initialized'. And we do that by calling the 'constructor function', which is provided to us as a function with the same name as the collection type. So, we add a new line (between lines 31 and 33) as follows:
30 v_monthlyRate := p_InterestRate / 1200;
31
32 amortTable := amortTableType();
33
34 amortTable(1).pmtdate := add_months(p_effectiveDate,1);
and try again:
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 34
For reference, line 34 is the first time we try to access a specific element:
34* amortTable(1).pmtdate := add_months(p_effectiveDate,1);
Let's pause a bit. Oracle refers to an unitialized collection as being "atomically null". To understand the significance of this, let us first ask a few related questions (how they are related will soon be made evident).
Suppose we have 2 varrays (or Nested Tables), A and B. What do the following assertions mean?
1) A = B
2) A < B
3) A IS NULL
Try to decide for yourselves before I continue.
For myself, I would answer (1) as follows: A = B if (and only if) for every element A(i), there is j such that A(i)=B(j). Some others might want to add, " and none of the A(i) or B(i) are null. Others might want to add that the two collections contain the same number of elements. And others might insist that the elements are stored in the exact same order.
I could make a really long list of possibilites around question (2). Some people might insist that all A(i) be <= B(i), with at least one element being greater. Me, I would take another tack, and define it such that for each A(i) there is an identical B(j), and there is at least one B(j) not found in A(i).
For question (3), I would like it to mean that every element of A is null. But Oracle does not adopt any of these. Instead, any attempt to compare two collections results in a PLS-0306 error: wrong number or types of elements in call to '=' (or '<' or '>' or whatever). The only comparison operator one can use against an entire collection is the IS NULL operator. Before a collection X is initialized, 'X IS NULL' evaluates to TRUE. Afterwards, it evaluates to FALSE. And before a collection X is initialized, one cannot refer to its individual elements. Afterwards, one can. So, to be atomicaly null, is to have meaningless elements, and to have the IS NULL operator return TRUE.
To rerturn to our immediate issue, we have not yet allocated any space in the amortTable collection. Let us now allocate some space, and get this block working:
add
32 amortTable := amortTableType();
33 amortTable.extend (p_loanTerm * 12 + 1);
we run it and get:
25-MAY-09 541.67 90.4 99909.6
25-JUN-09 541.18 90.89 99818.71
25-JUL-09 540.68 91.39 99727.32
25-AUG-09 540.19 91.88 99635.44
The trick was line 33, where we actually created 361 slots in the Varray.
The sharp-eyed observer will have noticed that everything has thus far been defined as a record. Since we will eventually want to have these types declared in the database, we need to make sure that this will work there also. So, let's try defining our record in the database:
1 create or replace type amortTableLine is record (
2 pmtDate date,
3 ToInterest number(12,2),
4 ToPrincipal number(12,2),
5 EndingBalance number(12,2)
6 );
7/
Warning: Type created with compilation errors.
SQL> show errors
Errors for TYPE AMORTTABLELINE:
1/24 PLS-00103: Encountered the symbol "RECORD" when expecting one of
the following:
array varray table object fixed varying opaque sparse
The symbol "object was inserted before "RECORD" to continue.
It appears we cannot define a record as a SQL type. Fortunately, there is an alternative; define an object instead:
1 create or replace type amortTableLine is object (
2 pmtDate date,
3 ToInterest number(12,2),
4 ToPrincipal number(12,2),
5 EndingBalance number(12,2)
6 );
7/
Type Created
Much as we would like to remain with anonymous PL/SQL blocks, we cannot, for if we try to change the type of the type from 'record' to 'object', we find
type amortTableLine is object (
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00540: object not supported in this context.
So, let us keep amortTableLine defined as a SQL object, and try our routine. We find that it no longer works:
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 29
SQL> List 1,29
1 DECLARE
2 type amortTableType is table of amortTableLine;
3
4 amortTable amortTabletype;
5
6 p_LoanAmount number(10,2);
7 p_InterestRate number;
8 p_loanTerm number(2,0);
9 p_effectiveDate date;
10 p_payment number(7,2);
11
12 v_pmtNumber number(4,0);
13 v_monthlyRate number;
14 v_totalInt number(13,2);
15 v_totalPmt number(13,2);
16 v_currentPrinc number(8,2);
17 BEGIN
18 p_LoanAmount := 10326.05;
19 p_InterestRate := 2.875;
20 p_loanTerm := 30;
21 p_effectiveDate := to_date('20090425','yyyymmdd');
22 p_payment := 62.22;
23 v_pmtNumber := 2;
24 v_monthlyRate := p_InterestRate / 1200;
25
26 amortTable := amortTableType();
27 amortTable.extend(362);
28
29 amortTable(1).pmtdate := add_months(p_effectiveDate,1);
SQL>
Which comes as a great surprise to someone is familiar with the various writings on the matter. All (well, almost all) suggest that the above should work.
To be fair, the examples usually look something like this:
1 declare
2 type v_type is table of varchar2(10);
3 v_tbl v_type;
4 begin
5 v_tbl := v_type();
6 v_tbl.extend(30);
7 v_tbl(1) := 'aaaaaa';
8 v_tbl(2) := 'bbbbb';
9 dbms_output.put_line(v_tbl(1)||v_tbl(2));
10 end;
11/
which, when run, gives the expected (and desired) results:
SQL> /
aaaaaabbbbb
PL/SQL procedure successfully completed.
The working example's lines 5 and 6 looks similar to our lines 26 and 27. So, what is wrong?
The answer is that the working example's nested table is a table of varchar2, which is something like a one-field record, while our failed example is a table of objects. And objects have to be initialized. We can break our working example by changing the type of the type from varchar2 to object. First, we must create an object that looks like the varchar2 above:
SQL> create type v_obj is object (dodah varchar2(10));
2 /
Type created.
Next, we revise the snippit accordingly:
1 declare
2 type v_type is table of v_obj;
3 v_tbl v_type;
4 begin
5 v_tbl := v_type();
6 v_tbl.extend(30);
7 v_tbl(1).dodah := 'aaaaaa';
8 v_tbl(2).dodah := 'bbbbb';
9 dbms_output.put_line(v_tbl(1).dodah||v_tbl(2).dodah);
10 end;
11/
and we get the ORA-06530 error.
To make the snippit work, we must recognize that v_tbl is a nested table of objects, and initialize those objects. The following works:
1 declare
2 type v_type is table of v_obj;
3 v_tbl v_type;
4 begin
5 v_tbl := v_type();
6 v_tbl.extend(30);
7 v_tbl(1) := v_obj(null);
8 v_tbl(1).dodah := 'aaaaaa';
9 v_tbl(2) := v_obj(null);
10 v_tbl(2).dodah := 'bbbbb';
11 dbms_output.put_line(v_tbl(1).dodah||v_tbl(2).dodah);
12 end;
13/
But it is less than satisfactory, mostly because while we can extend the nested table in a single statement, we are initializing the v_obj object one element at a time. We are able to take care of records in a single statement, can we do the same for objects? The answer is, almost. The solution is to call the object constructor and pass it to the Collection constructor. This creates and initializes one element of the Collection. Then, we can call the EXTEND method, passing it this well-formed first element. A glance at our snippit may help:
1 declare
2 type v_type is table of v_obj;
3 v_tbl v_type;
4 begin
5 v_tbl := v_type(v_obj(null));
6 v_tbl.extend(30,1);
7 v_tbl(1).dodah := 'aaaaaa';
8 v_tbl(2).dodah := 'bbbbbb';
9 dbms_output.put_line(v_tbl(1).dodah||v_tbl(2).dodah);
10 end;
11/
Pay particular attention to lines 5 and 6. Line 5 initializes the nested table and also initializes the first element of it. Then line 6 extends and initializes 30 more elements.
So, let us conclude by applying this to our mortgage amortization routine (where we have modified things a bit by putting the initiallization into the DECLARE section in line 4:
Listing 6
1 DECLARE
2 type amortTableType is table of amortTableLine;
3
4 amortTable amortTableType := amortTableType(amortTableLine(null,null,null,null));
5
6 p_LoanAmount number(10,2);
7 p_InterestRate number;
8 p_loanTerm number(2,0);
9 p_effectiveDate date;
10 p_payment number(7,2);
11
12 v_pmtNumber number(4,0);
13 v_monthlyRate number;
14 v_totalInt number(13,2);
15 v_totalPmt number(13,2);
16 v_currentPrinc number(8,2);
17 BEGIN
18
19 p_LoanAmount := 100000.00;
20 p_InterestRate := 6.5;
21 p_loanTerm := 30;
22 p_effectiveDate := to_date('20090425','yyyymmdd');
23 p_payment := 632.07;
24 v_pmtNumber := 2;
25 v_monthlyRate := p_InterestRate / 1200;
26
27 amortTable.extend(p_loanTerm * 12 + 1,1);
28
29 amortTable(1).pmtdate := add_months(p_effectiveDate,1);
30 amortTable(1).ToInterest := p_LoanAmount * v_monthlyRate;
31 amortTable(1).ToPrincipal := p_payment - amortTable(1).ToInterest;
32 amortTable(1).EndingBalance := p_LoanAmount - amortTable(1).ToPrincipal;
33 v_totalInt := amortTable(1).ToInterest;
34 v_totalPmt := p_payment;
35
36 WHILE (v_pmtNumber < p_loanTerm * 12) LOOP
37 amortTable(v_pmtNumber).pmtdate := add_months(amortTable(v_pmtNumber-1).pmtdate,1);
38 amortTable(v_pmtNumber).ToInterest := amortTable(v_pmtNumber - 1).EndingBalance * v_monthlyRate;
39 amortTable(v_pmtNumber).ToPrincipal := p_payment - amortTable(v_pmtNumber).ToInterest;
40 amortTable(v_pmtNumber).EndingBalance := amortTable(v_pmtNumber - 1).EndingBalance - amortTable(v_pmtNumber).ToPrincipal;
41 v_totalInt := v_totalInt + amortTable(v_pmtNumber).ToInterest;
42 v_totalPmt := v_totalPmt + p_payment;
43
44 v_pmtNumber := v_pmtNumber + 1;
45 END LOOP;
46 amortTable(v_pmtNumber).pmtdate := add_months(amortTable(v_pmtNumber-1).pmtdate,1);
47 amortTable(v_pmtNumber).ToInterest := amortTable(v_pmtNumber - 1).EndingBalance * v_monthlyRate;
48 amortTable(v_pmtNumber).ToPrincipal := amortTable(v_pmtNumber -1).EndingBalance;
49 amortTable(v_pmtNumber).EndingBalance := 0;
50 v_totalInt := v_totalInt + amortTable(v_pmtNumber).ToInterest;
51 v_totalPmt := v_totalPmt + amortTable(v_pmtNumber).ToPrincipal;
52
53 v_pmtNumber := 1;
54 WHILE (v_pmtNumber <= p_loanTerm * 12) LOOP
55 dbms_output.put_line(amortTable(v_pmtNumber).pmtdate||' '||
56 amortTable(v_pmtNumber).ToInterest||' '||
57 amortTable(v_pmtNumber).ToPrincipal||' '||
58 amortTable(v_pmtNumber).EndingBalance);
59 v_pmtNumber := v_pmtNumber + 1;
60 END LOOP;
61
62 END;
63/
So, let's summarize the housekeeping necessary when using Varrays and Nested Tables:
1) The collection must be defined in a TYPE statement somewhere (either in the database, a package definition, or in the PL/SQL block itself). The syntax is as follows:
Varray: TYPE varraytype IS VARRAY(n) OF datatype; where 'n' is the maximum size of the Varray
Nested Table: TYPE nestedtype IS TABLE OF datatype;
The datatype can be a built-in data type, a record type, or an object type.
2) Declare and initialize the collection. The syntax varies slightly, depending on whether the datatype is a built-in datatype, a record (which is just a generalization of a built-in datatype) or an object. The syntax for records:
Records: CollectionName varraytype := varraytype(data);
or CollectionName nestedtype := nestedtype(data);
Objects: CollectionName varraytype := varraytype(datatype(data));
or CollectionName nestedtype := nestedtype(datatype(data));
The data can be default data, or null.
3) If you know at the start how many elements will be needed, construct them at the beginning of your code as follows:
CollectionName.EXTEND(m,1);
where 'm' is one less that the needed number of elements. If the number is unknown, then extend the collection whenever a new element is required.
