Collections, Part IV (Associative Arrays as Parameters)

The code segments I have presented so far just illustrate the use of Associative Arrays in an anonymous PL/SQL block. Which would be more useful if PL/SQL were useful as a stand alone programming language. Which it is not. Instead, PL/SQL is primarily useful as a database interface. Which means functions, procedures, and packages. Which means, that we need a way for these arrays to get loaded and unloaded other than by being calculated anew each time the program executes. Which (finally) means either passing the array into or out of a routine as a parameter, or storing it in the database. In this posting, I am going to concentrate on the first of these: passing Associative Arrays as parameters of a stored program unit.

But first, I want to clarify some facts which often are glossed over. The first fact is that Oracle SQL and PL/SQL are executed by "different engines". The corollary is that the data types defined for the SQL engine are not identical with PL/SQL datatypes. Confusion arises, because most of the time the only datatypes used are the built-in ones (NUMBER, DATE, VARCHAR2, etc.) and these are available to both engines As a general rule, PL/SQL can use any SQL datatype (built in or user created), but the SQL engine cannot use PL/SQL datatypes.

As it happens, when I first suggest to someone that there is a distinction to be made between SQL data types and PL/SQL data types, their eyes glaze over. And I've had the same reaction to some Oracle employees. So it it probably best to turn to an example, and it makes most sense to me to start with something which works, and then show why making changes will cause it to fail. The example will be simply to convert Listing1 into two separate listings: a calculation routine and a print routine.

Here is the print routine:

        Listing 4a 

1  Create or Replace Package amortTablePkg is
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  procedure amortTablePrt (p_amortTable amortTableType);
11 end amortTablePkg;
12 /

13 Create or Replace Package Body amortTablePkg is

14 procedure amortTablePrt (p_amortTable amortTableType) IS

15  v_pmtNumber      number(4,0);
16 BEGIN
17  v_pmtNumber := 1;
18    WHILE (v_pmtNumber <= p_amortTable.count) LOOP
19      dbms_output.put_line(p_amortTable(v_pmtNumber).pmtdate||'   '||
20                          p_amortTable(v_pmtNumber).ToInterest||'   '||
21                          p_amortTable(v_pmtNumber).ToPrincipal||'   '||
22                          p_amortTable(v_pmtNumber).EndingBalance);
23      v_pmtNumber  := v_pmtNumber + 1;
24    END LOOP;
25  END amortTablePrt;
26 END amortTablePkg;
27 /

My opinion is that package variables are the pons asinorum of PL/SQL, so I will go over this slowly. We have seen the "Type amortTableLine" (lines 2 - 6) and the "type amortTableType" (lines 8 - 9) declarations before. What is different is that they are part of a package specification. By doing this, they are exposed to everyone with privileges on the package. Thus, in the package body (line 14) we don't need to describe what an amortTableType is, because it has already be defined. These package datatypes are known to any piece of PL/SQL because they are defined in the database, however, they still are PL/SQL datatypes and not SQL datatypes.

The other interesting thing about the procedure definition (line 10 or 14) is that the input parameter is typed just as one would type a varchar2 or number parameter. This demonstrates that a PL/SQL type can be used as a PL/SQL parameter, providing that it is exposed as a package variable.

Let's look at a function which can create (but not print) the array:

     Listing4b

  1  create or replace function  amortTableFunc (
  2                           p_LoanAmount     number,
  3                           p_InterestRate   number,
  4                           p_loanTerm       number,
  5                           p_effectiveDate  date,
  6                           p_payment        number
  7                                             )
  8            return amortTablePkg.amortTabletype
  9  IS
 10
 11   amortTable amortTablePkg.amortTabletype;
 12
 13   v_pmtNumber      number(4,0);
 14   v_monthlyRate    number;
 15   v_totalInt       number(13,2);
 16   v_totalPmt       number(13,2);
 17   v_currentPrinc   number(8,2);
 18  BEGIN
 19   v_pmtNumber      := 2;
 20   v_monthlyRate    := p_InterestRate / 1200;
 21
 22   amortTable(1).pmtdate       := add_months(p_effectiveDate,1);
 23   amortTable(1).ToInterest     := p_LoanAmount * v_monthlyRate;
 24   amortTable(1).ToPrincipal    := p_payment - amortTable(1).ToInterest;
 25   amortTable(1).EndingBalance  := p_LoanAmount - amortTable(1).ToPrincipal;
 26   v_totalInt                   := amortTable(1).ToInterest;
 27   v_totalPmt                   := p_payment;
 28
 29   WHILE (v_pmtNumber < p_loanTerm * 12) LOOP
 30     amortTable(v_pmtNumber).pmtdate       := add_months(amortTable(v_pmtNumber-1).pmtdate,1);
 31     amortTable(v_pmtNumber).ToInterest     := amortTable(v_pmtNumber - 1).EndingBalance * v_monthlyRate;
 32     amortTable(v_pmtNumber).ToPrincipal    := p_payment - amortTable(v_pmtNumber).ToInterest;
 33     amortTable(v_pmtNumber).EndingBalance  := amortTable(v_pmtNumber - 1).EndingBalance - amortTable(v_pmtNumber).ToPrincipal;
 34     v_totalInt                   := v_totalInt + amortTable(v_pmtNumber).ToInterest;
 35     v_totalPmt                   := v_totalPmt + p_payment;
 36
 37     v_pmtNumber  := v_pmtNumber + 1;
 38   END LOOP;
 39   amortTable(v_pmtNumber).pmtdate       := add_months(amortTable(v_pmtNumber-1).pmtdate,1);
 40   amortTable(v_pmtNumber).ToInterest     := amortTable(v_pmtNumber - 1).EndingBalance * v_monthlyRate;
 41   amortTable(v_pmtNumber).ToPrincipal    := amortTable(v_pmtNumber -1).EndingBalance;
 42   amortTable(v_pmtNumber).EndingBalance  := 0;
 43   v_totalInt                   := v_totalInt + amortTable(v_pmtNumber).ToInterest;
 44   v_totalPmt                   := v_totalPmt + amortTable(v_pmtNumber).ToPrincipal;
 45
 46  return amortTable;
 47
 48 END;
 49/

So, we now have a procedure (amortTablePkg.amortTablePrt) which can print out the contents of an amortTablePkg.amortTableType array, and a function, amortTableFunc, which can return such an array. It would seem logical that if we put them together, we ought to end up where we were in back at the start of this Let's see if this actually works.

A SQL*Plus guru might try:

exec amortTablePkg.amortTablePrt(amortTableFunc( 100000, 6.5, 30, to_date('20090315','yyyymmdd'), 632.07))

With results (only the first few lines shown):

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

But, this is ugly. A better guru might try instead:

exec  amortTablePkg.amortTablePrt(                            -
                           amortTableFunc(                    -
                           p_LoanAmount    => 100000,         -
                           p_InterestRate  => 6.5,            -
                           p_loanTerm      => 30,             -
                           p_effectiveDate => to_date('20090315','yyyymmdd'), -
                           p_payment       => 632.07          -
                                          )                   -
                           )

Which also works. But what are those dashes at the end of each line? And where is the semicolon?

The answer of course, is that 'exec' is not a SQL command, but a SQL*Plus command. So, the end-of-line character starts execution, not a semicolon. And the dashes are needed as line continuation characters. In fact, 'exec' is just a way for SQL*Plus to generate the following bit of PL/SQL:

     Listing 4c
  1  begin
  2  amortTablePkg.amortTablePrt(
  3                              amortTableFunc(
  4                              p_LoanAmount    => 100000,
  5                              p_InterestRate  => 6.5,
  6                              p_loanTerm      => 30,
  7                              p_effectiveDate => to_date('20090315','yyyymmdd'),
  8                              p_payment       => 632.07
  9                                             )
 10                             );
 11* end;

So, we actually have not executed any SQL at all. We can always try to execute the function by selecting from DUAL:

SQL> select amortTableFunc(
                    p_LoanAmount    => 100000,
                    p_InterestRate  => 6.5,
                    p_loanTerm      => 30,
                    p_effectiveDate => to_date('20090315','yyyymmdd'),
                    p_payment       => 632.07
         )
     from dual
/

and if we try this we find:

SQL>  select amortTableFunc(
       *
ERROR at line 1:
ORA-00902: invalid datatype

Which proves that PL/SQL datatypes cannot be used where a SQL datatype is required.

Let's create a new function, which returns a SQL datatype. At least, then it has a chance of being used in a SQL query:

create or replace function amorttableDemo (p_tbl amortTablePkg.amorttabletype)
    return number
is
begin
  return p_tbl(5).toInterest;
end;
/

What this function lacks in usefulness, it makes up by returning a SQL datatype. Let's prove that it actually works:

Declare
  v_result  number;
begin
  v_result := amortTableDemo(amortTableFunc( 100000, 6.5, 30, to_date('20090315','yyyymmdd'), 632.07));
  dbms_output.put_line(v_result);
end;
/

SQL>539.69

PL/SQL procedure successfully completed.

Indeed, it returns the number we expect. But, when we try to call it from SQL:

select amortTableDemo(amortTableFunc( 100000, 6.5, 30, to_date('20090315','yyyymmdd'), 632.07))
                         *
ERROR at line 1:
ORA-00902: invalid datatype

We still have a problem.

Clearly, we need something else, and those somethings are Varrays and Nested Tables.