10.5.1 Automatic Conversion of Row Number Expressions

PL/SQL will go to great lengths to convert an expression or string to BINARY_INTEGER for use as a row number. Here are some examples:

• Store the string in row 16:

  requests_table (15.566) := 'Totals by Category';

• Store the string in row 255:

  company_keys_table ('25'||'5') := 1000;

• The expression will be evaluated and used as the row number:

  keyword_list_table (last_row + 15) := 'ELSE';

10.5.2 Referencing an Undefined Row

As I've mentioned, a key difference between arrays and PL/SQL tables is that a row in a PL/SQL table does not exist until you assign a value to that row. This makes sense, given the fact that PL/SQL tables are unconstrained in size. In a 3GL program like C or FORTRAN, you specify a maximum size or dimension to an array when you declare it. Once an array is declared, the memory is set aside for all the cells in the array, and you can read from or place a value in any cell within the bounds of the array.

PL/SQL tables, on the other hand, have no predefined number of values, so PL/SQL does not create the memory structure for the rows in the table until you need them. Instead, whenever you assign a value to a row in a PL/SQL table, PL/SQL creates that row.

If, however, you reference a row which does not yet exist, PL/SQL raises the NO_DATA_FOUND exception. (The exception is also raised when an implicit cursor SELECT statement does not return any rows, or when you attempt to read past the end of a file with the UTL_FILE package.)

The following PL/SQL block will cause the NO_DATA_FOUND exception to be raised (and go unhandled) because row 15 has not yet been assigned a value:

DECLARE
   new_request NUMBER;
   TYPE request_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   request_table request_tabtype;
BEGIN
   new_request := request_table (15);
END;

If you want to trap the NO_DATA_FOUND exception, then you will need to add the following exception handler to the exception section of the block:

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      ... take appropriate action ...
END;

You pay a stiff penalty for referencing a row in a table that has not been assigned a value. To avoid this exception, you need to keep track of the minimum and maximum row numbers used in a PL/SQL table.

10.5.3 Nonsequential Use of PL/SQL Table

Of course, the idea of using a minimum and maximum row assumes that the rows in the table are used sequentially. That is, you fill row one, then row two, etc. This is a perfectly reasonable way to fill a table's rows; to do this you absolutely must know the value of the row last filled. You are not, however, required to fill rows in this way. You can place a value in any row of the table you wish, regardless of the primary key value of the last row you filled.

The following example illustrates filling a PL/SQL table's rows randomly rather than sequentially:

DECLARE
   TYPE countdown_tests_tabtype IS TABLE OF VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
   countdown_test_list countdown_tests_tabtype;
BEGIN
   countdown_test_list (1) := 'All systems go';
   countdown_test_list (43) := 'Internal pressure';
   countdown_test_list (255) := 'Engine inflow';
END;

In this situation, the minimum and maximum values do not have much significance.

The ability to randomly place values in a table can come in very handy when the primary key value for the table's row is actually not sequentially derived, but is instead based on data in your application. This use of "intelligent" primary key values is explored in more detail in Section 10.9.2, "Data-Smart Row Numbers in PL/SQL Tables" later in this chapter.

10.5.4 Passing PL/SQL Tables as Parameters

You can also pass a PL/SQL table as a parameter in a procedure or function; with this approach you can, in a single call, pass all the values in a table into the module. In the following package specification I define two modules that pass PL/SQL tables as parameters. The send_promos procedure sends a promotional mailing to all the companies in my table. The companies_overdue function returns a table filled with the names of companies that have overdue bills.

PACKAGE company_pkg
IS
   TYPE primary_keys_tabtype IS TABLE OF company.company_id%TYPE NOT NULL
      INDEX BY BINARY_INTEGER;
   company_keys_tab primary_keys_tabtype;
   emp_keys_tab primary_keys_tabtype;

   /* Table type and table for company names */
   TYPE company_names_tabtype IS TABLE OF company.name%TYPE
      INDEX BY BINARY_INTEGER;
   company_names_tab company_names_tabtype;

   /* Parameter is a table of company primary keys */
   PROCEDURE send_promos (company_table_in IN primary_keys_tabtype);

   /* Function returns a table of company names */
   FUNCTION companies_overdue (overdue_date_in IN DATE)
      RETURN company_names_tabtype;

   /* Returns company ID for name. */
   FUNCTION id (name in IN company.name%TYPE)
      RETURN company.company id%TYPE

END company_pkg;

Now that I have a package containing both the table type and the programs referencing those types, I can call these programs. The only tricky part to remember here is that you must declare a PL/SQL table based on the type before you can use any of the programs. Here is an example of returning a PL/SQL table as a function's return value:

CREATE OR REPLACE PROCEDURE send_promos_for_overdue_companies
   (date_in IN DATE := SYSDATE)
IS
   v_row PLS_INTEGER;

   /* Declare a PL/SQL table based on the packaged type. */
   cnames company_pkg.company_names_tabtype;
BEGIN
   cnames := company_pkg.companies_overdue (date_in);

   /*
   || In PL/SQL 2.3 I can use navigation methods to traverse.
   || Notice that I do not assume that rows are filled sequentially.
   */
   v_row := cnames.FIRST;
   LOOP
      EXIT WHEN v_row IS NULL;
      DBMS_OUTPUT.PUT_LINE (cnames(v_row));
      v_row := cnames.NEXT (v_row);
   END LOOP;
END;
/

Notice that I could also have avoided declaring my own PL/SQL table, cnames, by using the predefined table in the package:

   company_pkg.company_names_tab :=
   company_pkg.companies_overdue (date_in);

If I had taken this approach, however, any rows already defined in this "global" table would have been erased.

Here is an example of calling a procedure, passing a PL/SQL table as an argument in the parameter list:

DECLARE
   v_row PLS_INTEGER;

   /* Table of primary keys for company */
   company_ids company_pkg.primary_keys_tabtype;
BEGIN
   /* Populate the table with names of overdue companies. */
   company_pkg.company_names_tab :=
      company_pkg.companies_overdue (date_in);

   /* For each company name, look up the ID and pass it to the
   || PL/SQL table of company IDs.
   */
   v_row := company_pkg.company_names_tab.FIRST;
   LOOP
      EXIT WHEN v_row IS NULL;
      company_ids (NVL (company_ids.LAST, 0) + 1) :=
         company_pkg.id(company_pkg.company_name_tab(v_row));
      v_row := company_pkg.company_names_tab.NEXT (v_row);
   END LOOP;

   /* Now send out promotional flyers to those companies. */
   company_pkg.send_promos (company_ids);

   /* Delete all the rows from the company names table. */
   company_pkg.company_names_tab.DELETE;

END company_pkg;