Defining a Constant PL/SQL Lookup Table the Hard Way

The situation: we have a Large Company, with an American and a European division. Each division is divided into multiple regions. In the U.S., each region is assigned a state code. In the European division this code should probably be called something else, but to promote code reusability, it still is called a state code.

The problem: Given two regions, to determine if they have been assigned the same state code.

The bigger problem: We don't have a proper database lookup table. I won't go into why in detail (I grade the reason as "defensible" but not "conclusive"). So, the problem becomes, how to put such a lookup table into PL/SQL code, but not into a real database table.

Now, an associative array with a varchar2 index is a very nice object to use in place of a lookup table. Suppose we have a list of sales regions, and we want to know their state code. We could define an array as follows:

TYPE lookup_tbl_type IS TABLE OF VARCHAR2(2) INDEX BY VARCHAR2(4);
lookup_tbl lookup_tbl_type;

And then in the code section, assign some values:

lookup_tbl('REG1') := 'MI';
lookup_tbl('REG2') := 'MI';
lookup_tbl('REG3') := 'OH';
lookup_tbl('REG4') := 'KY';
lookup_tbl('REG5') := 'MI';

Given this, if we have a variable, l_region say, and want to do something if this region is in Michigan, but something else if it is in Ohio, we could then code:

IF (lookup_tbl(l_region) = 'MI')
THEN do something
ELSIF (lookup_tbl(l_region) = 'OH')
THEN do something else
END IF;

All one has to do is to get the values loaded into such an array. Which
is normally done from a database table. Which if we could have one, we wouldn't be having this discussion.

As it happens, I already have a package containing global types and constants. This is the natural place to put the lookup table. Unfortunately, one cannot define a constant PL/SQL table. As the PL/SQL User's Guide states (regarding associative arrays):

An initialization clause is not allowed. There is no constructor notation for associative arrays.

Include the 'CONSTANT' clause without initialization, and you get a PLS-00322 error. Try some kind of initialization and you get a PLS-00320. Leave off the CONSTANT -- and the table is no longer constant.

Well, I really want to have this "table" defined entirely in the package heading. But, there doesn't seem to be any way to do it using PL/SQL tables. Finally, the Dumb Bell rang, and I realized the solution was to use a PL/SQL Nested Table instead.

My first thought here was to define a record type, then a table type, and then a table:

TYPE look_me_up IS RECORD (
region varchar2(4),
state varchar2(2)
);
TYPE lookup_tbl_type IS TABLE OF look_me_up;
lookup_tbl lookup_tbl_type;

But this puts me back where I started: there is no constructor method for records, so one cannot assign them in their definition, so one cannot define them as constant (I suspect that the issue with PL/SQL tables is related).

The solution is to use a table of tables:

create or replace package testtypes as
TYPE lookup_tbl_type IS TABLE OF varchar2(4);
TYPE look_me_up_type IS TABLE OF lookup_tbl_type;

Then we can define as constant some nested tables. The plan is to put all the regions in one state in the same table:

lookup_tblMI constant lookup_tbl_type := lookup_tbl_type (
'REG1', 'REG2', 'REG5');
lookup_tblOH constant lookup_tbl_type := lookup_tbl_type
('REG3');
lookup_tblKY constant lookup_tbl_type := lookup_tbl_type
('REG4');

Then we can define our final "lookup table" as a constant table of these tables:

look_me_up constant look_me_up_type
:= look_me_up_type (lookup_tblMI, lookup_tblOH,
lookup_tblKY);

Now, if my primary question was to determine the state code for an arbitrary region, I would still be up a creek, and would have to create yet another table to indicate which index of look_me_up to use. Fortunately, the precise question I have is to determine if two arbitrary regions have the same state code. And this is a somewhat
easier question to answer. First, I do need yet another table:

tester_tbl lookup_tbl_type := lookup_tbl_type(null,null);

And then assign the two regions I wish to test:

tester_tbl(1) := 'REG1';
tester_tbl(2) := 'REG5';

Finally, I can test whether they are both in the same state by using the SUBMULTISET operator:

IF (tester_tbl SUBMULTISET OF look_me_up(1) )
THEN Do Something;
END IF:

And yes, fighting the Corporate reasons for not creating the natural lookup table would be more trouble than the above coding.

For reference, here is a package defining the nested lookup table, and a demonstration stub using it:


create or replace package testtypes as
TYPE lookup_tbl_type IS TABLE OF varchar2(4);
TYPE look_me_up_type IS TABLE OF lookup_tbl_type;
lookup_tbl1 constant lookup_tbl_type := lookup_tbl_type (
'REG1', 'REG2', 'REG5');
lookup_tbl2 constant lookup_tbl_type := lookup_tbl_type
('REG3');
lookup_tbl3 constant lookup_tbl_type := lookup_tbl_type
('REG4');
look_me_up constant look_me_up_type
:= look_me_up_type (lookup_tbl1, lookup_tbl2,
lookup_tbl3);
end;


declare
tester_tbl testtypes.lookup_tbl_type
:= testtypes.lookup_tbl_type(null,null);
begin
tester_tbl(1) := 'REG1';
tester_tbl(2) := 'REG2';
FOR i in 1 .. testtypes.look_me_up.count LOOP
IF (tester_tbl SUBMULTISET OF testtypes.look_me_up(i) )
THEN dbms_output.put_line('Found a Match at '||i );
END IF;
END LOOP;
end;