[Chapter 19] 19.6 PLVfk: Generic Foreign Key Lookups

19.6 PLVfk: Generic Foreign Key Lookups

The PLVfk (PL/Vision Foreign Key) package offers a high-level, easy-to-use interface to looking up foreign key information in your tables.

In any well-normalized relational database, you have many foreign keys. For example, a customer database has companies, contacts within companies, types of companies, types of contacts, etc. The contact table has a foreign key to the company table. The company table has a foreign key to the company type table, and so on. Normalization is a great thing for avoidance of data redundancy, but it can create anguish for developers.

The reality of foreign keys is most succinctly expressed in the following maxim (first encountered in a scribble in the stall of an Oracle headquarters bathroom!):

Where you have foreign keys, you also have foreign key lookups.

In other words, whenever I query/display a contact record, I need to look up the name of the company, the description of the contact type, and so on in order to translate my keys (usually sequentially generated numbers or cryptic string codes) into information users can understand. Furthermore, whenever a user enters a name or description, I will want to look up the corresponding key to make sure the entry is valid.

Foreign key lookups have historically required an awful lot of custom programming. In fact, let's spend a few paragraphs exploring what you would normally have to do to handle foreign keys. Then I will show you how you can use dynamic SQL to create a generic function to handle almost all foreign key lookups.

19.6.1 Traditional Foreign Key Management

We'll stick to the company-contact model. The tables I will use are as follows:

CREATE TABLE company
   (company_id NUMBER,
    company_nm VARCHAR2(100),
    company_type_id NUMBER);

CREATE TABLE contact
   (contact_id NUMBER,
    company_id NUMBER,
    contact_ nm VARCHAR2(100),
    contact_type_id NUMBER);

CREATE TABLE company_type
   (company_type_id NUMBER, company_type_nm VARCHAR2(100);

CREATE TABLE contact_type
   (comntact_type_id NUMBER, contact_type_nm VARCHAR2(100);

The traditional (pre-DBMS_SQL) approach to foreign key lookups entails building a function for each separate entity that serves as a foreign key in a table. This function would take the foreign key and return the name or description. The specifications for such functions would look like:

FUNCTION contact_name (contact_id_in IN contact.contact_id%TYPE) 
   RETURN VARCHAR2;

FUNCTION company_name (company_id_in IN company.company_id%TYPE) 
   RETURN VARCHAR2;

FUNCTION company_type (company_type_id_in IN company.company_type_id%TYPE) 
   RETURN VARCHAR2;

and so on, for as many foreign keys as you've got. And every time a new foreign key is added to the mix, you must write a new function. In addition, you would have a set of functions that take a name and return an ID. Lots of different program elements.

19.6.1.1 Typical foreign key lookup code

Here is an example of what only one of these functions would contain:

FUNCTION contact_name (contact_id_in IN contact.contact_id%TYPE) 
   RETURN VARCHAR2
IS
   CURSOR con_cur (id_in IN NUMBER)
   IS
      SELECT contact_nm
        FROM contact
       WHERE contact_id = id_in;
   con_rec con_cur%ROWTYPE;

   return_value contact.name%TYPE := NULL;
BEGIN
   OPEN con_cur (contact_id_in);
   FETCH con_cur INTO con_rec;
   IF con_cur%FOUND
   THEN
      return_value := con_rec.contact_nm;
   END IF;
   CLOSE con_cur;
   RETURN return_value;
END contact_name;

Not a terribly complicated function, but when you repeat those steps over and over again, you end up with a significant volume of code to construct, debug, and maintain.

19.6.1.2 A better mousetrap

Wouldn't it be just fabulous if you could construct a single, generic function using dynamic SQL that would work for all foreign keys? The PLVfk package offers that single function. Actually, it provides two different general-purpose functions: one that accepts a key or ID and returns the associated name (the name procedure), and another that accepts a name and returns the associated key (the id procedure).

By using PLVfk, you can avoid writing functions like the contact_name program shown above. Instead, you simply execute this kind of command:

v_cname := PLVfk.name
              (v_contact_id, 'contact', 'contact_id', 'contact_nm');

and maybe even nothing more than this:

v_cname := PLVfk.name (v_contact_id, 'contact');

The elements of the PLVfk package are explained in the following sections. To get more information about the implementation behind PLVfk, you can review the code on the companion disk or read Chapter 15 of Oracle PL/SQL Programming.

19.6.2 Configuring the PLVfk Package

I have found that in many Oracle shops there are clear, consistent guidelines for naming tables and their columns. If you work in this kind of environment, you can leverage the predictability of these conventions into a user interface that both reflects and takes advantage of these standards.

In the PLVfk package, this means that you can inform PLVfk through the "set" programs how the column names for primary keys and their descriptors are constructed in relation to the entity or table name. You can tell it, for example, that the string "_ID" is always attached to the table name as a suffix to form the primary key column name. Or you can tell it that the descriptor column is always formed as the string "NAME$" attached as a prefix to the table name.

Once you have informed PLVfk of your standards, you do not have to constantly type in the names of your columns. Instead, you just pass in the table name and let PLVfk do the rest. And if you run into exceptions to your rule, you always can override the default conventions with full names or alternative conventions. All of these variations are explored in the following sections.

PLVfk provides three different "set" programs to provide override values to default elements of the PLVfk configuration: set_id_default, set_nm_default, and set_vclen.

19.6.2.1 Setting column name defaults

The set_id_default procedure sets the default string to be used as a suffix or prefix to the specified table name. The other two set programs determine how PLVfk constructs column names to be used in the dynamic SQL that retrieves the requested data. The header for set_id_default is:

PROCEDURE set_id_default
   (string_in IN VARCHAR2 := c_no_change, 
    type_in IN VARCHAR2 := c_no_change);

where string_in is the string to be used as suffix or prefix and type_in is the type of concatenation action. The constant, c_no_change, can be used to indicate that you do not want to change one of these settings. The following examples illustrate the different ways to call set_id_default.

Change the prefix from the default of _ID to _KEY. Don't provide any value for the type, since the default is prefix and you are sure that hasn't been modified.
```
SQL> exec PLVfk.set_id_default ('_KEY');
```
Rather than use prefixes, my site uses standard suffixes of ID_ and NM_. So in my call to set_id_default, I only change the value of the type and leave the string itself the same (since they match the default, starting values).
```
SQL> exec PLVfk.set_id_default (type_in=>PLVfk.c_suffix);
```
The set_nm_default procedure performs the same kind of action, except that it applies to the name column and not the ID column. The header for this procedure is:
```
PROCEDURE set_nm_default
   (string_in IN VARCHAR2 := c_no_change, 
    type_in IN VARCHAR2 := c_no_change);
```

See the examples for set_id_default to get an idea of what you can do with set_nm_default.

19.6.2.2 Setting the default string length

The set_vclen procedure can be used to set the maximum size of a value returned by DBMS_SQL with a call to COLUMN_VALUE. The header for set_vclen is:

PROCEDURE set_vclen (length_in IN INTEGER);

The default value for this maximum length is 100. The following call to set_vclen notifies PLVfk that it may encounter descriptors of up to 255 bytes in length:

SQL> exec PLVfk.set_vclen (255);

Once you have set the PLVfk package to reflect as closely as possible your server environment and standards, you can use the id and name functions of PLVfk to perform effortless foreign key lookups.

19.6.3 Looking Up the Name

You can use the name function to retrieve the name for a specific key. The header for name is:

FUNCTION name
   (id_in IN INTEGER,
    table_in IN VARCHAR2, 
    id_col_in IN VARCHAR2 := c_no_change, 
    nm_col_in IN VARCHAR2 := c_no_change, 
    max_length_in IN INTEGER := c_int_no_change, 
    where_clause_in IN VARCHAR2 := NULL)
RETURN VARCHAR2;

where id_in is the specific ID value used in the lookup and table_in is the name of the table to be searched. These are the only required arguments. The next four arguments allow you to tweak the SQL statement constructed by PLVfk, as described below:

id_col_in: The name of the ID column in the table. If you do not supply a value, the default column suffix or prefix is applied to the table name. If you do supply a value and it starts or ends with an underscore, that string is used as a suffix or prefix. Otherwise, the string you supply is used as the column name itself.
nm_col_in: The name of the name or descriptor column in the table. If you do not supply a value, the default column suffix or prefix is applied to the table name. If you do supply a value and it starts or ends with an underscore, that string is used as a suffix or prefix. Otherwise, the string you supply is used as the column name itself.
max_length_in: The maximum length of the string value returned by the query. If no value is provided, the current value (initially 100 and set by the set_vclen procedure) is used in the call to the DBMS_SQL.DEFINE_COLUMN builtin.
where_clause_in: Optional WHERE clause to attach to the SELECT statement. You can use this argument to add additional AND clauses, such as a restriction to look only at active records.

The following series of examples demonstrate how to use the name function.

Assume that table contact has contact_id and contact_nm columns.
```
v_name := PLVfk.name (v_contact_id, 'contact');
```
Assume that contact_type table has contact_type_id and contact_type_nm columns.
```
v__type_ds := PLVfk.name (v_type_id, 'contact_type');
```
Of course, in the real world, conventions do not hold up so consistently. In fact, I have found that database administrators and data analysts often treat an entity like contact, with its contact ID number and contact name, differently from the way they would a contact type, with its type code and description. The columns for the contact type table are more likely to be: contact_typ_cd and contact_typ_ds. Fortunately, PLVfk.name still handles this situation as shown in number 3.

Use alternative suffixes for a code table.

v_type_ds := PLVfk.name (v_type_id, 'contact_type', '_cd', '_ds');

Only retrieve the description of the call type if that record is still flagged as active. Notice that I must stick several single quotes together to get the right number of quotes in the evaluated argument passed to name.
```
v_contact_type_ds := 
  PLVfk.name 
    (v_contact_type_id, 
     'contact_type', '_cd', '_ds', 25,
     'AND row_active_flag = ''Y''');
```
In examples 3 and 4, I could avoid specifying the column suffixes by making a prior call to set_id_default and set_nm_default, as shown below:
```
PLVfk.set_id_default ('_cd');
PLVfk.set_nm_default ('_ds');
```
and now my calls to PLVfk.name are made simpler:
```
v_type_ds := PLVfk.name (v_type_id, 'contact_type');

v_contact_type_ds := 
  PLVfk.name 
    (v_contact_type_id, 
     'contact_type', 25, 'AND row_active_flag = ''Y''');
```
Retrieve the name of the store that is kept in the record for the current year. I use named notation to skip over all intermediate arguments for which I want to use the default values and specify my WHERE clause. Notice that I must use two contiguous single quotes inside my string so that it evaluates to a valid string for a SQL statement.
```
/* Only the record for this year should be used */
year_number := TO_CHAR (SYSDATE, 'YYYY');
/* Pass check for year to WHERE clause. */
v_description :=
  name 
    (v_store_id, 'store_history', 
     where_clause_in =>
        'AND TO_CHAR (eff_date, ''YYYY'') = ''' ||
        year_number || '''');
```

The following table shows how various arguments for the ID and name column strings are converted into the column names concatenated into the dynamic SQL SELECT statement.

Formal Parameter	Argument Supplied to PLVfk.name	Converted Value
ID column	`c_no_change` or simply skipped	`contact_id`
Name column	`c_no_change` or simply skipped	`contact_nm`
ID column	`contact_number`	`contact_number`
Name column	`contact_name`	`contact_name`
ID column	_#	contact_#
Name column	_fullname	`contact_fullname`

19.6.4 Looking up the ID

Sometimes you want to get the key or ID number from a name. In this case, you use the id function, whose header is shown below:

FUNCTION id
   (nm_in IN VARCHAR2,
    table_in IN VARCHAR2, 
    id_col_in IN VARCHAR2 := c_no_change, 
    nm_col_in IN VARCHAR2 := c_no_change, 
    max_length_in IN INTEGER := c_int_no_change, 
    where_clause_in IN VARCHAR2 := NULL)
RETURN INTEGER;

where nm_in is the specific name used in the lookup and table_in is the name of the table to be searched. These are the only required arguments. The next four arguments allow you to tweak the SQL statement constructed by PLVfk, as described below:

id_col_in: The name of the ID column in the table. If you do not supply a value, the default column suffix or prefix is applied to the table name. If you do supply a value and it starts or ends with an underscore, that string is used as a suffix or prefix. Otherwise, the string you supply is used as the column name itself.
nm_col_in: The name of the name or descriptor column in the table. If you do not supply a value, the default column suffix or prefix is applied to the table name. If you do supply a value and it starts or ends with an underscore, that string is used as a suffix or prefix. Otherwise, the string you supply is used as the column name itself.
max_length_in: The maximum length of the string value returned by the query. If no value is provided, the current value (initially 100 and set by the set_vclen procedure) is used in the call to the DBMS_SQL.DEFINE_COLUMN builtin.
where_clause_in: Optional WHERE clause to attach to the SELECT statement. You can use this argument to add additional AND clauses, such as a restriction to only look at active records.

As you can see, the id function is very similar to the name function; they are mirrors of each other. Rather than repeat another set of examples, see those supplied for the name function to get a feeling for how to use the PLVfk.id function.

Here are some factors to consider when working with PLVfk:

You cannot call either of the PLVfk functions from within a SQL statement. Yes, it is a PL/SQL function and you can call PL/SQL functions from within SELECTs, INSERTs, and so on. You cannot, on the other hand, call a PL/SQL function which, in turn, executes any programs from a builtin package. Since PLVfk relies heavily on DBMS_SQL, it is not callable from within SQL. I received more messages (complaints?) about this problem (usually phrased more like "your code doesn't work!") from my first book than on any other topic.
David Thompson, ace technical reviewer of this book and author of PLVddd, offered an interesting idea for enhancing PLVfk further. Why depend on all those complicated column name prefixes and suffixes to determine the primary key? Why not read the data dictionary for this information instead? You could even store all primary key information in a PL/SQL table to avoid repetitive data dictionary access. An excellent idea to explore, although I would be concerned about adding more overhead to PLVfk processing.


19.5 DML Operations		20. PLVcmt and PLVrb: Commit and Rollback Processing