My First Quiz

I was pleased to see my PL/SQL quiz question presented yesterday. It was about how PL/SQL variable names and other identifiers whose names conflict with names of tables and columns can still be referenced in SQL within PL/SQL.

It demonstrates how a local variable or parameter of a procedure or function may be referred to unambiguously, by referring to it by the name of the procedure or function, e.g.:

FUNCTION getempid (employee_id IN plch_employees.employee_id%TYPE)
   RETURN plch_employees.employee_id%TYPE
IS
   the_id   plch_employees.employee_id%TYPE;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.the_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;
   RETURN getempid.the_id;
END getempid;

(notice that the INTO and RETURN parts don’t actually need to reference the function name to refer to the local variable, but I’ve done it this way for clarity)

In the case of an anonymous block, it is necessary to supply a label for the block:

<<getempid>>
DECLARE
   employee_id   plch_employees.employee_id%TYPE := 100;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.employee_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;

   DBMS_OUTPUT.put_line (getempid.employee_id);
END getempid;

The quiz answers also explore what happens if a variable name conflicts with a column name, and the identifiers are not referenced; another answer considers the case where the block label happens to conflict with a table name.

I heartily recommend thinking about and writing your own quiz questions and submitting them for Steven to consider for the Challenge. If you do, here are some tips:

  • Focus on one topic – remove any irrelevant details
  • Build a full test case and run it (and re-run it carefully after every single change you make!)
  • Try to remove as much code as possible (without ruining it)
  • Re-read the question the next day, and imagine being a typical Challenger, who nitpicks every single statement 🙂

Multi record select form and record locking

I’m building a very simple set of Oracle Forms for a customer who has very simple requirements. The form will allow one set of users to enter “payments”, which eventually get turned into requests for cheques to be sent out from the organisation. Each payment must go through an approval process – another set of users will open another form which will list all the payments that have been entered, select some or all of them and mark them as “Approved”.

To implement this design, I have one form which allows the users to enter the payment details; this form allows users to both insert new payments and update existing ones. Once a payment has been marked as “Approved”, they can only view but not edit them.

I’ve created a second form for the approvers, which lists all the payments that have not yet been approved, and gives them a simple checkbox. They tick any or all of the payments that they wish to approve, and click the “Approve” button. The button just sends an array of payment IDs to a database procedure which does a bulk update on the payments, setting their status as appropriate. Simple, right?

The one complication here is that this is a multi-user system, and it is quite likely that a users might try to update a payment at the same time as the approver is trying to mark them as approved. My first test of the forms indicated that this would cause a record locking issue:

In session #1, I open the payments form, query an existing payment, and start editing one of the fields. Oracle Forms automatically locks the record for update.

In session #2, I open the approvals form, tick the same payment, and click the “Approve” button. The form complains that it cannot reserve the record for update and the error logged is “FRM-40735 WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-04068.”

To solve this, I go to the checkbox item in the approvals form and add this code to the when-checkbox-changed trigger (the checkbox item is called PAYMENTS.SELECTED):

IF :PAYMENTS.SELECTED = 'Y' THEN
  LOCK_RECORD;
  IF NOT FORM_SUCCESS THEN
    :PAYMENTS.SELECTED := 'N';
    RAISE FORM_TRIGGER_FAILURE;
  END IF;
END IF;

Now, when the user tries to tick a payment that is currently locked, the LOCK_RECORD causes the form to attempt to lock the record. The “unable to reserve record for update” error still appears, after which the trigger un-ticks the record automatically.

If the approve gets to a payment first and ticks it, the record will now be locked until the form commits the status change; this ensures that other users cannot modify the record until the approver either approves the payment or cancels out of the form.