Do you use forms based on procedures in APEX? If not, why not?
Do you use records in the procedures you use for forms in APEX? If not, why not?

I like procedures and records and with APEX I like them even more. Why you say? Should you not just point forms against tables and let the APEX magic take care of it all? No, I do not think you should.

The reason is that I love that feature for prototyping, but not for production quality code. The reason is that I think the classic design pattern of separating presentation logic from business logic and data layer is as important with APEX as it is with any other technology.

That is, APEX applications should contain as much logic for presenting data as possible, and as little data about the data layer as possible. Thus, I think any table should be able to be renamed without making any change to the APEX application.

One key to that is to use procedures to hide what tables are updated or read. That allows the tables to be changed with just updating the procedure. No change to the APEX application would be needed and thus no new deploy of it would be required.

But what if a column is added or removed from the table, does that not require a change to the signature of the procedure? Sure it does, if every column in the table is reflected in the signature as individual fields. This is why using records and procedures together is such a powerful combination.

Let’s take a look at a basic implementation using a form based on a procedure that uses a record.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);
end tb_access;

create or replace package body tb_access as
  procedure read_emp(p_in_empno    in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
       into p_out_emp_rec.empno
           ,p_out_emp_rec.ename
       from emp
      where empno = p_in_empno;
  end read_emp;
end tb_access;

This is a procedure that just returns a row of a certain emp_no from the emp table. The columns emp_no and ename are returned in the record.

To create a page in APEX using this procedure, follow these steps.

1. Create a new page
2. Select Form
3. Select Form on Procedure
4. Select the procedure (tb_access-read_emp) + next
5. Change page and region names if you want to. + next
6. Next (Do not use tabs)
7. Next (No invoking page)
8. Select a page to branch to on cancel and submit. + next
9. Select to include only the columns that are in the record (empno and ename).
10. Select display only as the display type for empno. +next
11. Create

That was pretty easy, pretty much the same amount of work as to create it based on a table. However, there is one more thing we need to do. The reason for this is a bug. Hopefully this step is not needed in future versions. You know you have encountered this if the page items on the new page has names such as p4_pno and p4_ame. That is the first two letters in the name of the fields in the record were lost when the page items were created. This does not happen for fields in the signature, only for fields in a record that is in the signature.

It is easy enough to change the names of the page items. It is of course not needed technically speaking, but you will save yourself a lot of grief by having page items you know what they are used for.

The next step is to update the call to the procedure. In the “page processing” section there is a “run stored procedure”. The code will look something like this.

#OWNER#.TB_ACCESS.READ_EMP(
EMPNO => :p4_PNO,
ENAME => :p4_AME);

Yes, the bug affects the name of the page items used to provide values to the call. Not only is that wrong, the call is not using a record so the call will not work. A third problem is that the call is in the page processing section, but we want it to read data to be displayed.

As we will not update any data in this post, just comment out this code and copy it. Set the condition to never to avoid the call to even be attempted. Now create a process in “before regions” in page rendering. Paste the code you copied and update it to look like this.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

This code fetches a row from the emp table based on an empno that the page item p4_empno has been set to. This is of course assumed to be done by a page that branches to this page.

This code is of course something that is very much a template that once you have it set up the way you want in one place it is just a matter of adjusting it for the needs of different pages. It really does not take much more time than widget based page creation using a table. This however gives much more flexibility in changing the underlying data model as well as having some people focus on developing the presentation logic and others focused on writing data access logic.

This form is now just a read-only form, but it takes very little work to make it a full fledged crud by supporting insert, update, and delete in the same form. I may build on this form in a future post to show one way of doing that.

As a last thing, let’s just show that this page is now immune to changes to the record. Let’s change the package to add another field to the record and populate it in the SQL.

create or replace package tb_access as
  type t_emp_rec is record ( 
        empno  emp.empno%type
       ,ename  emp.ename%type
       ,sal    emp.ename%type);

    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec);
end tb_access;

create or replace package body tb_access as
    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec)  is
      begin
         select empno
               ,ename
               ,sal
           into p_out_emp_rec.emp_no
               ,p_out_emp_rec.ename
               ,p_out_emp_rec.sal
           from emp
          where empno = p_in_empno;
      end read_emp;
end tb_access;

This change is now available to any user of the procedure, while the existing page just keeps working. It does not even need a resave/compile. The signature of the procedure is the same and so all callers will keep working. This allows a change to be made without being forced to also change code or recompile all places where a call to the procedure is made.