6.13.1 Validating Foreign Key Entry with Cursors

A hefty percentage of our code can be taken up with validating the entry or selection of foreign keys. Consider the example of an application which maintains companies and employees of that company. On the employee maintenance screen, I want to let my user enter the name or partial name of the company that employs a person. If the user has identified a unique company, the form displays that name and stores the company ID on the null canvas. If the user's entry finds more than one match, a message is displayed. If no matches are found, the entry is rejected.

How should I implement this requirement? Well, the first thing that comes to the minds of many programmers is the following:

Use a cursor which, with a single fetch, employs the COUNT built-in to compute the total number of companies that match the enemy.

This is, perhaps, the most obvious and direct solution to the requirement -- when it is phrased as follows:

To find out if the user's entry has more than one match, count up just how many matches there are.

6.13.2 Managing a Work Queue with SELECT FOR UPDATE

As discussed earlier, a cursor with a SELECT...FOR UPDATE syntax issues a row-level lock on each row identified by the query. I encountered a very interesting application of this feature while helping a client resolve a problem.

The client offers a distribution package which tracks warehouse inventory. The work queue screen assigns warehouse floor packers their next tasks. The packer opens the screen and requests a task. The screen finds the next unassigned task and assigns it to the packer. A task might involve collecting various products together for shipment or returning products to the shelf. Completion of this task can take anywhere between one and ten minutes. When the task is completed, the packer will commit the changes or close the screen, performing an implicit commit.

For the amount of time it takes a packer to finish the task, that record must be tagged as "assigned" so that no other packer is given the same job to do. The first attempt at implementing this feature involved the use of a status flag. Whenever a packer was assigned a task, the flag on that task was set to ASSIGNED and the task record committed. The screen then excludes that task from the work queue. The problem with this approach is that the status had to be committed to the database so that other users could see the new status. This commit not only interrupted the actual transaction in the screen, but also created a number of headaches:

• What if the user never completes the task and exits the screen? The form would have to detect this scenario (and there are generally many ways to cancel/exit) and update the status flag to AVAILABLE, which involves yet another commit.

• Worse yet, what if the database goes down while the user is performing the task? That task will disappear from the work queue until manual intervention resets the status.

My client needed a mechanism by which the task could be flagged as UNAVAILABLE without having to perform commits, build complex checks into the form, and develop crash-recovery guidelines. They needed a program that would step through each of the open tasks in priority until it found a task that was unassigned. The SELECT...FOR UPDATE construct proved to be the perfect answer, in combination with two queries against the task table -- an explicit cursor and an implicit cursor using a FOR UPDATE clause.

The function in the following example returns the primary key of the next unassigned task using a cursor against the task table to look through all open tasks in priority order. The tasks returned by this first cursor include those which are assigned but "in process" (and should therefore not be assigned again). For each task retrieved from this cursor, the function then tries to obtain a lock on that record using the FOR UPDATE...NOWAIT clause. If the SELECT statement cannot obtain a lock, it means that task is being handled by another packer. So the function fetches the next task and tries, once again, to obtain a lock, continuing on in this fashion until a free task is found or the last task is fetched.

Notice that the next_task function does not perform any commits, so it doesn't have to do any kind of complicated clean-up. It simply requests the lock and returns the primary key for that task. The calling program can then offer this task to the packer who will issue the commit, freeing the lock, when she or he is done with the task:

/* Filename on companion disk: selupdt.sf */
FUNCTION next_task RETURN task.task_id%TYPE
IS
   /* Cursor of all open tasks, assigned and unassigned */
   CURSOR task_cur IS
      SELECT task_id
        FROM task
       WHERE task_status = 'OPEN'
       ORDER BY task_priority, date_entered DESC;

   /* The record for the above cursor */
   task_rec task_cur%ROWTYPE;
   /*
   || An exception for error ORA-00054:
   || "resource busy and acquire with NOWAIT specified"
   */
   record_locked EXCEPTION
   PRAGMA EXCEPTION_INIT (record_locked, -54);
   /*
   || Variables which determine whether function should continue
   || to loop through the cursor's records.
   */
   found_unassigned_task BOOLEAN := FALSE;
   more_tasks BOOLEAN := TRUE;

   /* The primary key of the unassigned task to be returned */
   return_value task.task_id%TYPE := NULL;
BEGIN
   /* Open the cursor and start up the loop through its records */
   OPEN task_cur;
   WHILE NOT found_unassigned_task AND more_tasks
   LOOP
      /* Fetch the next record. If nothing found, we are done */
      FETCH task_cur INTO task_rec;
      more_tasks := task_cur%FOUND;
      IF more_tasks
      THEN
         /*
         || A record was fetched. Create an anonymous block within
         || the function so that I can trap the record_locked
         || exception and still stay inside the cursor loop.
         */
         BEGIN
            /* Try to get a lock on the current task */
            SELECT task_id INTO return_value
              FROM task
              WHERE task_id = task_rec.task_id
             FOR UPDATE OF task_id NOWAIT;
            /*
            || If I get to this line then I was able to get a lock
            || on this particular task. Notice that the SELECT INTO
            || has therefore already set the function's return value.
            || Now set the Boolean to stop the loop.
            */
            found_unassigned_task := TRUE;
         EXCEPTION
            WHEN record_locked
            THEN
               /* Record was already locked, so just keep on going */
               NULL;
         END;
      END IF;
   END LOOP;
   /*
   || Return the task id. Notice that if an unassigned task was NOT
   || found, I will simply return NULL per declaration default.
   */
   CLOSE task_cur;
   RETURN return_value;
EXCEPTION
   /*
   || General exception handler for the function: if an error occurred,
   || then close the cursor and return NULL for the task ID.
   */
   WHEN OTHERS
   THEN
      CLOSE task_cur;
      RETURN NULL;
END;