#

| Chapter 19 PLVdyn and PLVfk: Dynamic SQL and PL/SQL |

## 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`.

1. 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');

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

1. Assume that table contact has `contact_id` and `contact_nm` columns.

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

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

3. Use alternative suffixes for a code table.

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

4. 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''');

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

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

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.