#

| Appendix A |

# A. Appendix: PL/SQL Exercises

Contents:
Exercises
Solutions

The exercises included in this appendix are designed to enhance your ability to write well-structured PL/SQL programs, and also to identify problems with existing code. I recommend that you test out your baseline PL/SQL skills on these exercises before you explore Parts III through V of this book, where you will learn how to apply your skills to building robust and reusable packages.

For solutions to these exercises, see Section A.2, "Solutions" later in this appendix.

## A.1 Exercises

The exercises are arranged by topic:

Conditional logic
Loops
Exception handling
Cursors
Builtin functions
Builtin packages
Modules
Module evaluation

### A.1.1 Conditional Logic

1. Rewrite the following IF statements so that you do not use an IF statement to set the value of `no_revenue`. What is the difference between these two statements? How does that difference affect your answer?

   >     IF total_sales <= 0
   >     THEN
   >        no_revenue := TRUE;
   >     ELSE
   >        no_revenue := FALSE;
   >     END IF;

   >     IF total_sales <= 0
   >     THEN
   >        no_revenue := TRUE;
   >     ELSIF total_sales > 0
   >     THEN
   >        no_revenue := FALSE;
   >     END IF;

2. Rewrite the following IF statement to work as efficiently as possible under all conditions, given the following information: the `calc_totals` numeric function takes three minutes to return its value, while the `overdue_balance` Boolean function returns TRUE/FALSE in less than a second.

   >     IF calc_totals (1994, company_id_in => 1005) AND
   >        NOT overdue_balance (company_id_in => 1005)
   >     THEN
   >        display_sales_figures (1005);
   >     ELSE
   >        contact_vendor;
   >     END IF;

3. Rewrite the following IF statement to get rid of unnecessary nested IFs:

   >     IF salary < 10000
   >     THEN
   >        bonus := 2000;
   >     ELSE
   >        IF salary < 20000
   >        THEN
   >           bonus := 1500;
   >        ELSE
   >           IF salary < 40000
   >           THEN
   >              bonus := 1000;
   >           ELSE
   >              bonus := 500;
   >           END IF;
   >        END IF;
   >     END IF;

4. Which procedure will never be executed in this IF statement?

   >     IF (order_date > SYSDATE) AND order_total >= min_order_total
   >     THEN
   >        fill_order (order_id, 'HIGH PRIORITY');
   >     ELSIF (order_date < SYSDATE) OR
   >           (order_date = SYSDATE)
   >     THEN
   >        fill_order (order_id, 'LOW PRIORITY');
   >     ELSIF order_date <= SYSDATE AND order_total < min_order_total
   >     THEN
   >        queue_order_for_addtl_parts (order_id);
   >     ELSIF order_total = 0
   >     THEN
   >        disp_message (' No items have been placed in this order!');
   >     END IF;

### A.1.2 Loops

1. How many times does the following loop execute?

   >     FOR year_index IN REVERSE 12 .. 1
   >     LOOP
   >        calc_sales (year_index);
   >     END LOOP:

2. Select the type of loop (FOR, WHILE, simple) appropriate to meet each of the following requirements:

   1. Set the status of each company whose company IDs are stored in a PL/SQL table to closed.

   2. For each of twenty years in the loan-processing cycle, calculate the outstanding loan balance for the specified customer. If the customer is a preferred vendor, stop the calculations after twelve years.

   3. Display the name and address of each employee returned by the cursor.

   4. Scan through the list of employees in the PL/SQL table, keeping count of all salaries greater than $50,000. Don't even start the scan, though, if the table is empty or if today is a Saturday or if the first employee in the PL/SQL table is the president of the company.

3. Identify the problems with (or areas for improvement in) the following loops. How would you change the loop to improve it?

   1. >     FOR i IN 1 .. 100
      >     LOOP
      >        calc_totals (i);
      >        IF i > 75
      >        THEN
      >           EXIT;
      >        END IF;
      >     END LOOP;

   2. >     OPEN emp_cur;
      >     FETCH emp_cur INTO emp_rec;
      >     WHILE emp_cur%FOUND
      >     LOOP
      >        calc_totals (emp_rec.salary);
      >        FETCH emp_cur INTO emp_rec;
      >        EXIT WHEN emp_rec.salary > 100000;
      >     END LOOP;
      >     CLOSE emp_cur;

   3. >     FOR a_counter IN lo_val .. hi_val
      >     LOOP
      >        IF a_counter > lo_val * 2
      >        THEN
      >           hi_val := lo_val;
      >        END IF;
      >     END LOOP;

   4. >     DECLARE
      >        CURSOR emp_cur IS SELECT salary FROM emp;
      >        emp_rec emp_cur%ROWTYPE
      >     BEGIN
      >        OPEN emp_cur;
      >        LOOP
      >           FETCH emp_cur INTO emp_rec;
      >           EXIT WHEN emp_cur%NOTFOUND;
      >           calc_totals (emp_rec.salary);
      >        END LOOP;
      >        CLOSE emp_cur;
      >     END;

   5. >     WHILE no_more_data
      >     LOOP
      >        read_next_line (text);
      >        no_more_data := text IS NULL;
      >        EXIT WHEN no_more_data;
      >     END LOOP;

   6. >     FOR month_index IN 1 .. 12
      >     LOOP
      >        UPDATE monthly_sales
      >           SET pct_of_sales = 100
      >         WHERE company_id = 10006
      >           AND month_number = month_index;
      >     END LOOP;

   7. >     DECLARE
      >        CURSOR emp_cur IS SELECT ... ;
      >     BEGIN
      >        FOR emp_rec IN emp_cur
      >        LOOP
      >           calc_totals (emp_rec.salary);
      >        END LOOP;
      >        IF emp_rec.salary < 10000
      >        THEN
      >           DBMS_OUTPUT.PUT_LINE ('Give ''em a raise!');
      >        END IF;
      >        CLOSE emp_cur;
      >     END;

   8. >     DECLARE
      >        CURSOR checked_out_cur IS
      >           SELECT pet_id, name, checkout_date
      >             FROM occupancy
      >            WHERE checkout_date IS NOT NULL;
      >     BEGIN
      >        FOR checked_out_rec IN checked_out_cur
      >        LOOP
      >           INSERT INTO occupancy_history (pet_id, name, checkout_date)
      >              VALUES (checked_out_rec.pet_id,
      >                      checked_out_rec.name,
      >                      checked_out_rec.checkout_date);
      >        END LOOP;
      >     END;

4. How many times does the following WHILE loop execute?

   >     DECLARE
   >        end_of_analysis BOOLEAN := FALSE;
   >        CURSOR analysis_cursor IS SELECT ...;
   >        analysis_rec analysis_cursor%ROWTYPE;
   >        next_analysis_step NUMBER;
   >        PROCEDURE get_next_record (step_out OUT NUMBER) IS
   >        BEGIN
   >           FETCH analysis_cursor INTO analysis_rec;
   >           IF analysis_rec.status = 'FINAL'
   >           THEN
   >              step_out := 1;
   >           ELSE
   >              step_out := 0;
   >           END IF;
   >        END;
   >     BEGIN
   >        OPEN analysis_cursor;
   >        WHILE NOT end_of_analysis
   >        LOOP
   >           get_next_record (next_analysis_step);
   >           IF analysis_cursor%NOTFOUND AND
   >              next_analysis_step IS NULL
   >           THEN
   >              end_of_analysis := TRUE;
   >           ELSE
   >              perform_analysis;
   >           END IF;
   >        END LOOP;
   >     END;

5. Rewrite the following loop so that you do not use a loop at all.

   >     FOR i IN 1 .. 2
   >     LOOP
   >        IF i = 1
   >        THEN
   >           give_bonus (president_id, 2000000);
   >        ELSIF i = 2
   >        THEN
   >           give_bonus (ceo_id, 5000000);
   >        END IF;
   >     END LOOP;

6. What statement would you remove from this block? Why?

   >     DECLARE
   >        CURSOR emp_cur IS
   >           SELECT ename, deptno, empno
   >             FROM emp
   >            WHERE sal < 2500;
   >        emp_rec emp_cur%ROWTYPE;
   >     BEGIN
   >        FOR emp_rec IN emp_cur
   >        LOOP
   >           give_raise (emp_rec.empno, 10000);
   >        END LOOP;
   >     END;

### A.1.3 Exception Handling

1. In each of the following PL/SQL blocks, a VALUE_ERROR exception is raised (usually by an attempt to place too large a value into a local variable). Identify which exception handler (if any -- the exception could also go unhandled) will handle the exception by writing down the message that will be displayed by the call to PUT_LINE in the exception handler. Explain your choice.

   1. >     DECLARE
      >        string_of_5_chars VARCHAR2(5);
      >     BEGIN
      >        string_of_5_chars := 'Steven';
      >     END;

   2. >     DECLARE
      >        string_of_5_chars VARCHAR2(5);
      >     BEGIN
      >        BEGIN
      >           string_of_5_chars := 'Steven';
      >        EXCEPTION
      >           WHEN VALUE_ERROR
      >           THEN
      >              DBMS_OUTPUT.PUT_LINE ('Inner block');
      >        END;
      >     EXCEPTION
      >        WHEN VALUE_ERROR
      >        THEN
      >           DBMS_OUTPUT.PUT_LINE ('Outer block');
      >     END;

   3. >     DECLARE
      >        string_of_5_chars VARCHAR2(5) := 'Eli';
      >     BEGIN
      >        BEGIN
      >           string_of_5_chars := 'Steven';
      >        EXCEPTION
      >           WHEN VALUE_ERROR
      >           THEN
      >              DBMS_OUTPUT.PUT_LINE ('Inner block');
      >        END;
      >     EXCEPTION
      >        WHEN VALUE_ERROR
      >        THEN DBMS_OUTPUT.PUT_LINE ('Outer block');
      >     END;

   4. >     DECLARE
      >        string_of_5_chars VARCHAR2(5) := 'Eli';
      >     BEGIN
      >        DECLARE
      >           string_of_3_chars VARCHAR2(3) := 'Chris';
      >        BEGIN
      >           string_of_5_chars := 'Veva';
      >        EXCEPTION
      >           WHEN VALUE_ERROR
      >           THEN DBMS_OUTPUT.PUT_LINE ('Inner block');
      >        END;
      >     EXCEPTION
      >        WHEN VALUE_ERROR
      >        THEN DBMS_OUTPUT.PUT_LINE ('Outer block');
      >     END;

   5. >     DECLARE
      >        string_of_5_chars VARCHAR2(5);
      >     BEGIN
      >        BEGIN
      >           string_of_5_chars := 'Steven';
      >        EXCEPTION
      >           WHEN VALUE_ERROR
      >           THEN
      >              RAISE NO_DATA_FOUND;
      >           WHEN NO_DATA_FOUND
      >           THEN
      >              DBMS_OUTPUT.PUT_LINE ('Inner block');
      >        END;
      >     EXCEPTION
      >        WHEN NO_DATA_FOUND
      >        THEN
      >           DBMS_OUTPUT.PUT_LINE ('Outer block');
      >     END;

2. Write a PL/SQL block that allows all of the following SQL DML statements to execute, even if any of the others fail:

   >     UPDATE emp SET empno = 100 WHERE empno > 5000;
   >     DELETE FROM dept WHERE deptno = 10;
   >     DELETE FROM emp WHERE deptno = 10;

3. Write a PL/SQL block that handles by name the following Oracle error:

   >     ORA-1014: ORACLE shutdown in progress.

   The exception handler should, in turn, raise a VALUE_ERROR exception. Hint: use the EXCEPTION INIT pragma.

4. When the following block is executed, which of the two messages shown below are displayed? Explain your choice.

   Message from Exception Handler | Output from Unhandled Exception
   --- | ---
   >     Predefined or
   >     programmer-defined?
   >     Error at line 1:
   >     ORA-1403: no data found
   >     ORA-6512: at line 5

   >     DECLARE
   >        d VARCHAR2(1);
   >        /* Create exception with a predefined name. */
   >        no_data_found EXCEPTION;
   >     BEGIN
   >        SELECT dummy INTO d FROM dual WHERE 1=2;
   >        IF d IS NULL
   >        THEN
   >           /*
   >           || Raise my own exception, not the predefined
   >           || STANDARD exception of the same name.
   >           */
   >           RAISE no_data_found;
   >        END IF;
   >     EXCEPTION
   >        /* This handler only responds to the RAISE statement. */
   >        WHEN no_data_found
   >        THEN
   >           DBMS_OUTPUT.PUT_LINE ('Predefined or programmer-defined?');
   >     END;

5. I create the `getval` package as shown below. I then call DBMS_OUTPUT.PUT_LINE to display the value returned by the `getval.get` function. What is displayed on the screen?

   >     CREATE OR REPLACE PACKAGE getval
   >     IS
   >        FUNCTION get RETURN VARCHAR2;
   >     END getval;
   >     /
   >     CREATE OR REPLACE PACKAGE BODY getval
   >     IS
   >        v VARCHAR2(1) := 'abc';
   >        FUNCTION get RETURN VARCHAR2 IS
   >        BEGIN
   >           RETURN v;
   >        END;
   >     BEGIN
   >        NULL;
   >     EXCEPTION
   >       WHEN OTHERS THEN
   >         DBMS_OUTPUT.PUT_LINE ('Trapped!');
   >     END getval;
   >     /

### A.1.4 Cursors

1. What cursor-related statements are missing from the following block?

   >     DECLARE
   >        CURSOR emp_cur IS SELECT * FROM emp;
   >     BEGIN
   >        OPEN emp_cur;
   >        FETCH emp_cur INTO emp_rec;
   >     END;

2. What statement should be removed from the following block?

   >     DECLARE
   >        CURSOR emp_cur IS SELECT * FROM emp;
   >        emp_rec emp_cur%ROWTYPE;
   >     BEGIN
   >        FOR emp_rec IN emp_cur
   >        LOOP
   >           give_raise (emp_rec.empno);
   >        END LOOP;
   >     END;

3. Name the cursor attribute (along with the cursor name) you would use (if any) for each of the following requirements:

   1. If the FETCH did not return any records from the `company_cur` cursor, exit the loop.

   2. If the number of rows deleted exceeded 100, notify the manager.

   3. If the `emp_cur` cursor is already open, fetch the next record. Otherwise, open the cursor.

   4. If the FETCH returns a row from the `sales_cur` cursor, display the total sales information.

   5. I use an implicit cursor SELECT statement to obtain the latest date of sales for my store number 45067. If no data is fetched or returned by the SELECT, display a warning.

4. What message is displayed in the following block if the SELECT statement does not return a row?

   >     PROCEDURE display_dname (emp_in IN INTEGER) IS
   >        department# dept.deptno%TYPE := NULL;
   >     BEGIN
   >        SELECT deptno INTO department#
   >          FROM emp
   >         WHERE empno = emp_in;
   >        IF department# IS NULL
   >        THEN
   >           DBMS_OUTPUT.PUT_LINE ('Dept is not found!');
   >        ELSE
   >           DBMS_OUTPUT.PUT_LINE ('Dept is ' || TO_CHAR (department#));
   >        END IF;
   >     EXCEPTION
   >        WHEN NO_DATA_FOUND
   >        THEN
   >           DBMS_OUTPUT.PUT_LINE ('No data found');
   >     END;

5. What message is displayed in the following block if there are no employees in department 15?

   >     PROCEDURE display_dept_count
   >     IS
   >        total_count INTEGER := 0;
   >     BEGIN
   >        SELECT COUNT(*) INTO total_count
   >          FROM emp
   >         WHERE deptno = 15;
   >        IF total_count = 0
   >        THEN
   >           DBMS_OUTPUT.PUT_LINE ('No employees in department!');
   >        ELSE
   >           DBMS_OUTPUT.PUT_LINE
   >              ('Count of employees in dept 15 = ' || TO_CHAR (total_count));
   >        END IF;
   >     EXCEPTION
   >        WHEN NO_DATA_FOUND
   >        THEN
   >           DBMS_OUTPUT.PUT_LINE ('No data found');
   >     END;

6. If you fetch past the last record in a cursor's result set, what will happen?

7. How would you change the SELECT statement in the following block's cursor so that the block can display the sum of salaries in each department?

   >     DECLARE
   >        CURSOR tot_cur IS
   >           SELECT deptno, SUM (sal)
   >             FROM emp
   >            GROUP BY deptno;
   >     BEGIN
   >        FOR tot_rec IN tot_cur
   >        LOOP
   >           DBMS_OUTPUT.PUT_LINE
   >              ('Total is: ' || tot_rec.total_sales);
   >        END LOOP;
   >     END;

8. Rewrite the following block to use a cursor parameter. Then rewrite to use a local module, as well as a cursor parameter.

   >     DECLARE
   >        CURSOR dept10_cur IS
   >           SELECT dname, SUM (sal) total_sales
   >             FROM emp
   >            WHERE deptno = 10;
   >        dept10_rec dept10_cur%ROWTYPE;
   >        CURSOR dept20_cur IS
   >           SELECT dname, SUM (sal)
   >             FROM emp
   >            WHERE deptno = 20;
   >        dept20_rec dept20_cur%ROWTYPE;
   >     BEGIN
   >        OPEN dept10_cur;
   >        FETCH dept10_cur INTO dept10_rec;
   >        DBMS_OUTPUT.PUT_LINE
   >           ('Total for department 10 is: ' || tot_rec.total_sales);
   >        CLOSE dept10_cur;
   >        OPEN dept20_cur;
   >        FETCH dept20_cur INTO dept20_rec;
   >        DBMS_OUTPUT.PUT_LINE
   >           ('Total for department 20 is: ' || tot_rec.total_sales);
   >        CLOSE dept20_cur;
   >     END;

9. Place the following cursor inside a package, declaring the cursor as a public element (in the specification). The SELECT statement contains all of the columns in the `emp` table, in the same order.

   >     CURSOR emp_cur (dept_in IN INTEGER) IS
   >        SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
   >          FROM emp
   >         WHERE deptno = dept_in;

### A.1.5 Builtin Functions

1. Identify the appropriate builtin to use for each of the following requirements:

   Requirement | Builtin
   --- | ---
   > Calculate the number of days until the end of the month. |
   > Capitalize the first character in a word and lowercase the rest of the word. |
   > Convert a date to a string. |
   > Convert a number to a string. |
   > Convert a string to a date. |
   > Convert a string to lower case. |
   > Determine the length of a string. |
   > Determine the place of a character in the collating sequence of the character set used by the database. |
   > Extract the last four characters in a string. |
   > Extract the word found between the first and second _ delimiters in a string. |
   > Fill out a number in a string with leading zeroes. |
   > Find the last blank in a string. |
   > Find the Saturday nearest to the last day in March 1992. |
   > Find the third S in a string |
   > Get the first day in the month for a specified date. |
   > How many months are between `date1` and `date2`? |
   > I store all my names in uppercase in the database, but want to display them in reports in upper and lowercase. |
   > If it is High Noon in New York, what time is it in Calcutta? |
   > Remove a certain prefix from a string (for example, change `std_company_id` to `company_id`). |
   > Replace all instances of _ with a #. |
   > Return the error message associated with a SQL error code. |
   > Return the largest integer less than a specified value. |
   > Review all new hires on the first Wednesday after they'd been working for three months. |
   > Strip all leading numeric digits from a string. |
   > What is the current date and time? |
   > What is the date of the last day in the month? |

2. What portion of the string "Curious George deserves what he gets!" (assigned to variable `curious_george`) is returned by each of the following calls to SUBSTR:

   >     1234567890123456789012345678901234567
   >     Curious George deserves what he gets!

   SUBSTR Usage | Returns
   --- | ---
   >     SUBSTR (curious_george, -1)
   >     SUBSTR (curious_george, 1, 7)
   >     SUBSTR (curious_george, 9, 6)
   >     SUBSTR (curious_george, -8, 2)
   >     SUBSTR (curious_george,
   >             INSTR (curious_george, -1, ' ') + 1)
   >     SUBSTR (curious_george,
   >             INSTR (curious_george, ' ', -1, 3) + 1,
   >             LENGTH ('cute'))
   >     SUBSTR (curious_george, -1 * LENGTH (curious_george))

### A.1.6 Builtin Packages

1. What program would you use to calculate the elapsed time of your PL/SQL code execution? To what degree of accuracy can you obtain these timings?

2. What would you call to make your PL/SQL program pause for a specified number of seconds? What other techniques can you think of which would have this same effect?

3. What package can you use to determine if the current session has issued a COMMIT? How would you go about obtaining this information?

4. What do you see when you execute the following statements in SQL*Plus (assuming that you have already called SET SERVEROUTPUT ON):

   >     SQL> execute DBMS_OUTPUT.PUT_LINE (100);
   >     SQL> execute DBMS_OUTPUT.PUT_LINE ('     Five spaces in');
   >     SQL> execute DBMS_OUTPUT.PUT_LINE (NULL);
   >     SQL> execute DBMS_OUTPUT.PUT_LINE (SYSDATE < SYSDATE - 5);
   >     SQL> execute DBMS_OUTPUT.PUT_LINE (TRANSLATE ('abc', NULL));
   >     SQL> execute DBMS_OUTPUT.PUT_LINE (RPAD ('abc', 500, 'def'));

5. When an error occurs in your program, you want to be able to see which program is currently executing. What builtin packaged function would you call to get this information? If the current program is a procedure named `calc_totals` in the analysis package, what would you see when you call the builtin function?

6. You want to build a utility for DBAs that would allow them to create an index from within a PL/SQL program. Which package would you use? Which programs inside that package would be needed?

7. You need to run a stored procedure named `update_data` every Sunday at 4 AM to perform a set of batch processes. Which builtin package would you use to perform this task? You will need to pass a string to the `submit` program to tell it how often to run `update_data`. What would that string be?

### A.1.7 Modules

1. In each of the following modules, identify changes you would make to improve their structure, performance, or functionality.

   1. >     FUNCTION status_desc (status_cd_in IN VARCHAR2) RETURN VARCHAR2
      >     IS
      >     BEGIN
      >        IF    status_cd_in = 'C' THEN RETURN 'CLOSED';
      >        ELSIF status_cd_in = 'O' THEN RETURN 'OPEN';
      >        ELSIF status_cd_in = 'A' THEN RETURN 'ACTIVE';
      >        ELSIF status_cd_in = 'I' THEN RETURN 'INACTIVE';
      >        END IF;
      >     END;

   2. >     FUNCTION status_desc
      >                 (status_cd_in IN VARCHAR2, status_dt_out OUT DATE)
      >     RETURN VARCHAR2
      >     IS
      >     BEGIN
      >        ... /* same function as above */
      >     END;

   3. >     FUNCTION company_name (company_id_in IN company.company_id%TYPE)
      >        RETURN VARCHAR2
      >     IS
      >        cname company.company_id%TYPE;
      >        found_it EXCEPTION;
      >     BEGIN
      >        SELECT name INTO cname FROM company
      >         WHERE company_id = company_id_in;
      >        RAISE found_it;
      >     EXCEPTION
      >        WHEN NO_DATA_FOUND
      >        THEN
      >            RETURN NULL;
      >        WHEN found_it
      >         THEN
      >            RETURN cname;
      >     END;

   4. >     PROCEDURE compute_net (company_id IN INTEGER)
      >     IS
      >        balance_remaining NUMBER := annual_sales (company_id);
      >     BEGIN
      >        FOR month_index IN 1 .. 12
      >        LOOP
      >           IF balance_remaining <= 0
      >           THEN
      >              RETURN 0;
      >           ELSE
      >              balance_remaining := debt (company_id, month_index);
      >           END IF;
      >        END LOOP;
      >     END;

2. Given the header for `calc_profit` below, which of the following calls to `calc_profit` are valid:

   >     PROCEDURE calc_profit
   >        (company_id_in IN NUMBER,
   >         profit_out OUT NUMBER
   >         fiscal_year_in IN NUMBER,
   >         profit_type_in IN VARCHAR2 := 'NET_PROFITS',
   >         division_in IN VARCHAR2 := 'ALL_DIVISIONS')

   Call to calc_profit | Good/Bad? Why?
   --- | ---
   >     calc_profit
   >        (1005, profit_level, 1995, 'ALL', 'FINANCE');
   >     calc_profit
   >        (new_company, profit_level);
   >     calc_profit
   >        (company_id_in => 32, fiscal_year_in => 1995,
   >         profit_out => big_number);
   >     calc_profit
   >        (company_id_in => 32, fiscal_year_in => 1995,
   >         profit_out => 1000);

3. Suppose that I have a general utility that displays the contents of a PL/SQL table of dates. The header for this procedure is:

   >     PROCEDURE dispdates
   >        (table_in IN PLVtab.date_table,
   >         num_rows_in IN INTEGER,
   >         header_in IN VARCHAR2 := NULL);

   where `PLVtab.date_table` is a predefined table TYPE stored in the PLVtab package. Notice that the default value for the header is NULL, which means that no header is displayed with the table contents.

   Here is an example of a call to this program:

   >     dispdates (birthdays, bday_count, 'List of Birthdays');

   Now suppose that you had to use `dispdates` to satisfy the following requirement: "Display the list of company start dates stored in the date table without any header." I can think of two ways do this:

   >     dispdates (company_list, num_companies);

   and:

   >     dispdates (company_list, num_companies, NULL);

   Which of these implementations would you choose and why? Is there any reason to choose one over the other?

### A.1.8 Module Evaluation: Foreign Key Lookup

I have found that there are two ways to improve your skills in module construction:

1. Write lots of procedures and functions.

2. Critique someone else's efforts.

Certainly, there is no substitute for doing the work yourself. I find, on the other hand, that when I have the opportunity to look at another developer's work, a different kind of dynamic sets in. I am not sure that it speaks well of my personality, but I find it a whole lot easier to find the weaknesses in someone else's programs than in my own.

So assuming that everyone in the world in the same as me (a scary thought, but one I must entertain as a possibility), I offer a function for you to evaluate that I built myself long ago that does foreign-key lookups. No holds barred. No one to insult. See just how many problems you can find in the `getkey_clrtyp`. You might even try to rewrite the program to suit your tastes -- and then evaluate that!

We spend way too much of our time writing software to perform foreign key lookups. And in many situations, the interface we offer to our users to support easy access to foreign key information is inadequate. The approach I like to take is to hide the foreign keys themselves (users rarely need to know, after all, that the ID number for Acme Flooring, Inc. is 2765). Instead, I let the user type in as much of the name as she wants. I then see if there if there are any matches for that string. If there are no matches, I prompt for another entry. If there is just one match, I return the full name and the ID to the host application. If there are more than one match, I display a list.

The `getkey_clrtyp` function encapsulates this logic. The function itself returns a numeric code as follows:

0 = No match | 1 = Unique | 2 = Duplicate

It also returns through the parameter list the full name of the caller type and the numeric foreign key value. This function has a number of weaknesses in its design. See how many you can identify.

>     FUNCTION GETKEY_CLRTYP
>     (NAME_INOUT IN OUT VARCHAR2, NU_INOUT OUT NUMBER)
>     RETURN NUMBER IS
>        CURSOR CLRTYP_CUR IS
>           SELECT TYP_NU, TYPE_DS
>             FROM CALLER_TYPE
>            WHERE TYPE_DS LIKE NAME_INOUT || '%';
>        CLRTYP_REC CLRTYP_CUR%ROWTYPE;
>        NEXT_REC CLRTYP_CUR%ROWTYPE;
>        TYP_NU VARCHAR2(10) := NULL;
>        RETVAL NUMBER := NULL;
>     BEGIN
>     IF NAME_INOUT IS NOT NULL
>     THEN
>     OPEN CLRTYP_CUR;
>        FETCH CLRTYP_CUR INTO CLRTYP_REC;
>        IF CLRTYP_CUR%NOTFOUND
>        THEN RETURN 0; ELSE
>        FETCH CLRTYP_CUR INTO NEXT_REC;
>        IF CLRTYP_CUR%NOTFOUND
>        THEN RETVAL := 1;
>        ELSE RETVAL := 2;
>        END IF;
>        NU_INOUT := CLRTYP_REC.TYP_NU;
>        NAME_INOUT := CLRTYP_REC.TYP_DS;
>        END IF;
>     CLOSE CLRTYP_CUR;
>     RETURN RETVAL;
>     END IF;
>     END GETKEY_CLRTYP;

VI. Testing Your Knowledge |  | A.2 Solutions

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