Programming with Oracle SQL TYPE constructs, Part 2

In this two-part series I am following up on my UKOUG Annual conference presentation in an attempt to answer a question from Sue Harper on why more people don't program with Oracle's SQL Types. In the previous article I showed how Types still lack some crucial features which expereinced object-oriented programmers would expect. In this article I am going to address the other half of the question: whether PL/SQL programmers don't use Types because they do not understand when they would be appropriate. I hope to show why Types can still be a useful addition to the PL/SQL programmer's arsenal.

TYPE: what is it good for?

Let's look at the places where Types are used:

• Collections in PL/SQL
  
• Message payloads in Advanced Queuing
  
• XML DB
  

These have one thing in common: Types are used to allow programs to handle different kinds of data in a generic fashion. For instance, a collection can be a table of NUMBER or a table of a Complex Data Type. In either case we can manipulate the set of records using standard collection methods such as count(), first(), last(), next(), prior, extend() and delete(). This flexibility has seen programmers to adopt collections in a wide variety of situations: Steven Feuerstein has a whole presentation devoted to nothing but collections. The application of Types in AQ and XML DB similarly provide a framework to handle data as an undifferentiated splodge which we can pass to consuming procedures which understand the data's actual structure.

This gives us a clue as to the kind of application which is suited to a Type application: when we have data with different structures which we want to process in the same general fashion. At the UKOUG conference I discussed my current project. This is a datawarehouse. Source systems provide daily feeds which have to be loaded in to a history schema. Each table has a different structure and so has its own set of DML statements but the whole process is entirely generic. This means that the decision making logic can be stored and executed in a supertype, which calls on the subtypes' overriding implementations to execute specific logic for each individual table. As I discovered at the conference the details of the process are too complex to explain in a presentation. Even in an article like this I don't think it is practical. Instead I am going to use a simpler example which we all understand: customers.

Customers: a worked example

I recently got told off for using contrived examples in my code so I hope the following business scenario doesn't seem too spurious (in real life I would not expect addresses to be stored in quite the way I show here). The business is a mail order company selling something like artists' supplies. It keeps a record of every customer. Some regular customers have accounts; these can either be retail customers (probably professional artists) or wholesalers. The system keeps additional information about its account customers. Wholesale customers may have multiple contact addresses, e.g. for payments and deliveries.

I am not going to build a whole implementation but just enough code to print the shipping address for all the customers. Furthermore, in order to avoid irrelevant complexity I am going to sidestep the whole issue of persistence and just focus on the Types I need to create an API. The first step is to create the Types for addresses and contacts. The ADDRESS Type has a couple of member functions to print the address's components as formatted string.

SQL> create or replace type address as object (
 2      line_1 varchar2(100)
 3      ,  line_2 varchar2(100)
 4      ,  post_town varchar2(50)
 5      ,  post_code varchar2(10)
 6      ,  member function formatted_address return varchar2
 7      ,  member function formatted_address
 8             ( name in varchar2, fao in varchar := null)
 9             return varchar2
10  );
11  /

Type created.

SQL> create or replace type body address as
 2      member function formatted_address
 3          return varchar2
 4      is
 5          return_value varchar2(4000);
 6      begin
 7          return_value := self.line_1;
 8          if self.line_2 is not null then
 9              return_value := return_value
10                  ||chr(10)|| self.line_2;
11          end if;
12          if self.post_town is not null then
13              return_value := return_value
14                  ||chr(10)|| self.post_town;
15          end if;
16          if self.post_code is not null then
17              return_value := return_value
18                  ||chr(10)|| self.post_code;
19          end if;
20          return return_value;
21      end formatted_address;
22      member function formatted_address
23         ( name in varchar2, fao in varchar := null)
24          return varchar2
25      is
26          return_value varchar2(4000);
27      begin
28          return_value := name||chr(10);
29          if fao is not null then
30              return_value := return_value
31                  || 'FAO: '||fao||chr(10);
32          end if;
33          return_value := return_value||self.formatted_address();
34          return return_value;
35      end formatted_address;
36  end;
37  /

Type body created.

SQL>

Wholesale customers may have multiple addresses, which I am representing as a collection. The collection type has methods to retrieve a specific contact address.

SQL> create or replace type contact as object (
 2      cntct_code varchar2(10)
 3      ,  name varchar2(50)
 4      ,  cntct_address address
 5  );
 6  /

Type created.

SQL> create or replace type contact_nt as table of contact
 2  /

Type created.

SQL> create or replace type contacts_list as object (
 2    contacts contact_nt
 3    , member function get_contact_by_code
 4     (cntct_type varchar2) return contact
 5    , member procedure get_contact_by_code
 6        (self in contacts_list, cntct_type in varchar2, addr out address, fao out varchar2)
 7  );
 8  /

Type created.

SQL> create or replace type body contacts_list as
 2    member function get_contact_by_code
 3     (cntct_type varchar2) return contact
 4    is
 5        return_value contact;
 6    begin
 7        if self.contacts.count() > 0 then
 8     for i in self.contacts.first()..self.contacts.last()
 9     loop
10         if self.contacts(i).cntct_code = upper(trim(cntct_type)) then
11      return_value := self.contacts(i);
12      exit;
13          end if;
14     end loop;
15        end if;
16        return return_value;
17    end get_contact_by_code;
18    member procedure get_contact_by_code
19        (self in contacts_list, cntct_type in varchar2, addr out address, fao out varchar2)
20    is
21        l_cntct contact;
22    begin
23        l_cntct := self.get_contact_by_code(cntct_type);
24        addr := l_cntct.cntct_address;
25        fao := l_cntct.name;
26    end get_contact_by_code;
27  end ;
28  /

Type body created.

SQL>

Now I create the base type of Customer. This Type is used to represent the non-account customers. I have overloaded the get_mailing_address() function to provide a hook for extending the functionality in the subtypes.

SQL> create or replace type customer as object (
 2    id number
 3    , name varchar2(50)
 4    , legal_address address
 5    ,  final member function get_mailing_address return varchar2
 6    ,  member function get_mailing_address
 7          (addr_code in varchar2) return varchar2
 8  ) not final;
 9  /

Type created.

SQL> create or replace type body customer as
 2    final member function get_mailing_address
 3        return varchar2
 4    is
 5    begin
 6        return self.legal_address.formatted_address(self.name);
 7    end get_mailing_address;
 8    member function get_mailing_address
 9        (addr_code in varchar2) return varchar2
10    is
11    begin
12        return self.get_mailing_address();
13    end get_mailing_address;
14  end;
15  /

Type body created.

SQL>

To model the account customers I have an Account_Customer Type. It has member functions to get the payment and delivery addresses. This Type is not instantiable, which means that I will have to implement subtypes derived from it.

SQL> create or replace type account_customer under customer (
 2    acct_no varchar2(20)
 3    , credit_limit number
 4    ,  member function get_billing_address return varchar2
 5    ,  member function get_shipping_address return varchar2
 6    ,  overriding member function get_mailing_address
 7        (addr_code in varchar2) return varchar2
 8  ) not final not instantiable;
 9  /

Type created.

SQL> create or replace type body account_customer as
 2    member function get_billing_address return varchar2
 3    is
 4    begin
 5        return null;
 6    end get_billing_address;
 7    member function get_shipping_address return varchar2
 8    is
 9    begin
10        return null;
11    end get_shipping_address;
12    overriding member function get_mailing_address
13        (addr_code in varchar2) return varchar2
14    is
15    begin
16        case addr_code
17     when 'SHIPPING' then
18         return self.get_shipping_address();
19     when 'BILLING' then
20         return self.get_billing_address();
21     else
22         return self.get_mailing_address();
23        end case;
24    end get_mailing_address;
25  end;
26  /

Type body created.

SQL>

Now I will create two subtypes of Account_Customer to model retail and wholesale customers. These need to have code to implement the member functions specified in the parent Type.

SQL> create or replace type retail_customer under account_customer(
 2   overriding member function get_billing_address return varchar2
 3    , overriding member function get_shipping_address return varchar2
 4  );
 5  /

Type created.

SQL> create or replace type body retail_customer as
 2    overriding member function get_billing_address return varchar2
 3    is
 4    begin
 5        return self.get_mailing_address();
 6    end get_billing_address;
 7    overriding member function get_shipping_address return varchar2
 8    is
 9    begin
10        return self.get_mailing_address();
11    end get_shipping_address;
12  end;
13  /

Type body created.

SQL> create or replace type wholesale_customer under account_customer (
 2    contact_addresses contacts
 3    , overriding member function get_billing_address return varchar2
 4    , overriding member function get_shipping_address return varchar2
 5    , member procedure find_address
 6        (self in wholesale_customer, cntct_type in varchar2
     , addr out address, fao out varchar2)
 7  );
 8  /

Type created.

SQL> create or replace type body wholesale_customer
 2  as
 3    overriding member function get_billing_address
 4        return varchar2
 5    is
 6        l_fao varchar2(50);
 7        l_addr address;
 8    begin
 9        find_address('BILLING', l_addr, l_fao);
10        return l_addr.formatted_address(self.name, l_fao);
11    end get_billing_address;
12    overriding member function get_shipping_address
13        return varchar2
14    is
15        l_fao varchar2(50);
16        l_addr address;
17    begin
18        find_address('SHIPPING', l_addr, l_fao);
19        return l_addr.formatted_address(self.name, l_fao);
20    end get_shipping_address;
21    member procedure find_address
22        (self in wholesale_customer, cntct_type in varchar2, addr out address, fao out varchar2)
23    is
24    begin
25        if self.contact_addresses.contacts.count() = 0 then
26     addr := self.legal_address;
27     fao := null;
28        else
29     contact_addresses.get_contact_by_code(cntct_type, addr, fao);
30        end if;
31    end find_address;
32  end;
33  /

Type body created.

SQL>

In order to avoid having duplicated code in the get_billing_address() and get_shipping_address() functions I have introduced the find_address() procedure to hold the shared code. As discussed in Part 1 I must include this procedure in the Type specification, which clutters the interface but that is just the way it is.

Finally I need to create a nested table type which I can use for holding a collecton of customers.

SQL> create or replace type customer_nt as table of customer
 2  /

Type created.

SQL>

The actual procedure to print the delivery addresses is very simple, with a straightforward interface. It takes a list of Customer objects and calls the get_mailing_address() member function to display the address. Because this function was overloaded in the Customer Type the procedure does not need to distinguish betweeen account and non-account customers.

SQL> create or replace procedure print_delivery_addresses (p_list in customer_nt)
 2  is
 3  begin
 4    for k in p_list.first()..p_list.last()
 5    loop
 6        dbms_output.put_line(p_list(k).get_mailing_address('SHIPPING'));
 7    end loop;
 8  end print_delivery_addresses;
 9  /

Procedure created.

SQL>

To use this procedure I instantiate four customers of three different Types. I pass them as a collection of Customer objects to my procedure.

SQL> set serveroutput on size 1000000
SQL> declare
 2    --  sample address objects
 3    a1 address :=
 4        new address('1 Lister Road', 'Balham', 'London', 'SW12 8MM');
 5    a2 address :=
 6        new address('34 William St', 'Tooting', 'London', 'SW17 8YY');
 7    a3 address :=
 8        new address('124 Legal Road', null, 'London', 'NE4 7AA');
 9    a4 address :=
10        new address('The Old Warehouse', '23 Museum Street', 'London', 'WC1 8UU');
11    a5 address :=
12        new address('Piggybank House', '86 Bill Row', 'London', 'WC2 8CC');
13    a6 address :=
14        new address('Stapler Mansions', '124 Monet Road', 'London', 'SW4 7GG');
15    c1 contacts_list := new contacts_list(contact_nt());
16    c2 contacts_list := new contacts_list(
17       contact_nt(contact('SHIPPING', 'Reg', a4)
18           , contact('BILLING', 'Raj Pasanda', a5)));
19    --  sample customer objects
20    cus1 customer :=
21        new customer(1111, 'Mr O Knox', a1);
22    rcus1 retail_customer :=
23        new retail_customer(2222, 'Ms Cecilia Foxx', a2, 'A/C 1234P', 1000);
24    wcus1 wholesale_customer :=
25        new wholesale_customer(3333, 'Cornelian '||chr(38)||' Sons', a3
                                                , 'A/C 7890C',10000, c1);
26    wcus2 wholesale_customer :=
27        new wholesale_customer(4444, 'Brushes R Us', a6, 'A/C 9876C',200000, c2);
28    mailing_list customer_nt := new customer_nt (cus1, rcus1, wcus1, wcus2);
29  begin
30    print_delivery_addresses(mailing_list);
31  end;
32  /
Mr O Knox
1 Lister Road
Balham
London
SW12 8MM                                 

Ms Cecilia Foxx
34 William St
Tooting
London
SW17 8YY                          

Cornelian & Sons
124 Legal Road
London
NE4 7AA                                 

Brushes R Us
FAO: Raj Pasanda
Piggybank House
86 Bill Row
London
WC2 8CC       

PL/SQL procedure successfully completed.

SQL>

Note that I edited the DBMS_OUTPUT output for clarity.

How it works as a Type implementation

The procedure print_delivery_addresses issues a call on the method Customer.get_mailing_address(): that is encapsulation. This method is implemented in different fashions in the Customer and Account_Customer Types. The version defined in Account_Customer is the one used by the Reatil_Customer anmd Wholesale_Customer subtypes: that is inheritance. The procedure takes a list of Customer Types but executes the code appropriate for the actual subtype: that is polymorphism.

Conclusion

Assembling this example was an interesting exercise. The starting idea was simple enough but modelling it in objects proved tricky. For instance I am not altogether happy about overloading the get_mailing_address() function in the Customer Type. But the only alternative seemed to be putting an IS OF test in the print_delivery_addresses() procedure to check each Customer object to see whether it is an account customer.

This may seem like a lot of complexity to do something so simple. What needs to be borne in mind is that this complexity is largely overhead. Writing a procedure to print out the billing addresses is another single call. We can submit a list of any or all kinds of Customer objects and be confident that the correct address will be printed. We can extend Customer or AccountCustomer with different implementations of get_mailing_address() and the original procedure will consume them quite happily.

Whether these benefits are sufficient to justify using Types is debatable. Modelling in Types requires a different approach from modelling with packages. Moreover it requires perseverence and flexibility. It is very easy to go off down one route and then find oneself derailed by some limitation of Oracle's Type implementation, or just by some unanticipated aspect of object behaviour. But it is worth having a play around with Types. Because some day you might find yourself designing an application with some complicated generic logic and lots of repetitious specific behaviours and Types may just fit the scenario.