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!):
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.
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.
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.
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 .
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 .
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 .
See the examples for set_id_default to get an idea of what you can do with set_nm_default .
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.
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:
The following series of examples demonstrate how to use the name function.
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.
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.