Working with Oracle SQL object types

Introduction
Sometimes, it could be convenient to represent real business entities using Oracle object types. For example, address, contact information or some payment instructions - all theses entities have standard set of attributes and, may be, have some internal business logic. It would be handy to define an object type, encapsulating all necessary attributes, and work with it in the same way if it were usual scalar type. In this post I'll show how to work with a simple Oracle object type at the business model level and how to create a declarative component working with this model at the view level.

Use Case
I need to store in my database information about payments. It should contain payment's date, currency, amount and contact information of the person connected with the payment. I'm going to use object type for contact info with the following structure:

create or replace type Tcontact_info as object
(Name Varchar2(100),
 PhoneNumber Varchar2(100),
 CellPhoneNumber Varchar2(100),
 email Varchar2(100)
 )

And the table for payments looks like this:

create table Payment
(ID Number,
 PaymentDate date,
 Currency Varchar2(3),
 Amount Number,
 Contact Tcontact_info
 )


Let's do it

In JDeveloper in the model project I created entity for Payment table using "Create Business Components from Tables" wizard.

The wizard has created entity object with the following structure:

Have a look at the Contact's type. And the wizard has created business domain object TcontactInfo representing Tcontact_info SQL object type:

After that I created entity based view object VPayment and Application Module containing an instance of this view object. If we now explore the Data Controls panel, we will see very interesting picture:

Contact attribute is defined like a child iterator with its own set of attributes and operations. And if we drag-n-drop VPayment from the Data Controls panel to the page, we will see interesting structure in the generated PageDef file:

  <executables>
    <variableIterator id="variables"/>
    <iterator Binds="VPayment" RangeSize="25" DataControl="AppModuleDataControl"
              id="VPaymentIterator"/>
    <accessorIterator MasterBinding="VPaymentIterator" Binds="Contact"
                      RangeSize="25" DataControl="AppModuleDataControl"
                      BeanClass="com.cs.blog.sqlobjectexample.model.common.TcontactInfo"
                      ObjectType="true" id="ContactIterator"/>
  </executables>
  <bindings>
    <!--General payment's attributes: -->   
    <attributeValues IterBinding="VPaymentIterator" id="Amount">
      <AttrNames>
        <Item Value="Amount"/>
      </AttrNames>
    </attributeValues>
    ... 

    <!--Contact's attributes: -->    
    <attributeValues IterBinding="ContactIterator" id="Name">
      <AttrNames>
        <Item Value="Name"/>
      </AttrNames>
    </attributeValues>
    ... 
  </bindings>
  

Access to Contact's attributes is provided via special "accessIterator"! And it works fine as we can see here:

At this point we could say "We are done!", but we are not looking for easy ways! I'm going to create declarative component for contact info in order to  use it widely in my applications.

In my previous post you can see how to create, deploy and use declarative components in details. In "Create JSF Declarative Component Wizard" we need to add attribute "value" with type of business domain TcontactInfo:

          

In jspx file for our declarative component ContactInfo I have added the following content:

<af:panelBox text="Contact Info" id="dc_pb1" showDisclosure="false"
              inlineStyle="width:350.0px;">
  <af:panelFormLayout id="dc_pfl1">
    <af:inputText value="#{attrs.value.name}" label="Person's name"
                  id="dc_it1"></af:inputText>
    <af:inputText value="#{attrs.value.phonenumber}" label="Phone number"
                  id="it6"></af:inputText>
    <af:inputText value="#{attrs.value.cellphonenumber}"
                  label="Cell phone number" id="it5"></af:inputText>
    <af:inputText value="#{attrs.value.email}" label="Email" id="dc_it2"></af:inputText>
    <f:facet name="footer"/>
  </af:panelFormLayout>
 </af:panelBox>

Note, that access to the Contact's attributes is provided using keyword "attrs.value", which is referring to an object of TcontactInfo class.

In my target jspx page I can use new declarative component like this:

<compLib:ContactInfo id="ci1" value="#{bindings.Contact.inputValue}"/> 

And my PageDef looks like this:

  <executables>
    <variableIterator id="variables"/>
    <iterator Binds="VPayment" RangeSize="25" DataControl="AppModuleDataControl"
              id="VPaymentIterator"/>
  </executables>
  <bindings>
    <!--General payment's attributes: --> 
    <attributeValues IterBinding="VPaymentIterator" id="Amount">
      <AttrNames>
        <Item Value="Amount"/>
      </AttrNames>
    </attributeValues>
    ...
    <attributeValues IterBinding="VPaymentIterator" id="Contact">
      <AttrNames>
        <Item Value="Contact"/>
      </AttrNames>
    </attributeValues>
 </bindings>

So, I don't need any "accessorIterator" and define "Contact" as usual scalar attribute. The following screen is presenting the result of our work. Sorry for design:

Are we done? Almost. We can get and show information from the database SQL object type. And we need to be able to put this information back, to save it to the database. Next time.