Object Type with Optional Attribute: Extra Constructor Function

When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the bottom of this article.

Sometimes not all attributes of the Object Types are being passed down to the stored procedure, especially when attributes are optional.

Although it appears to be possible to create an Object Type like the following, it will not work:

  SQL> create or replace type test_ot
  2  as object
  3  (name varchar2(20)
  4  ,description varchar2(150) null
  5  );
  6  /

Type created.
   

Notice that the Object Type named TEST_OT has two attributes of which the second one (description) is optional.
When you try to create an instance of that Object Type, you will get an exception.

      SQL> declare
  2   o test_ot;
  3  begin
  4   o := test_ot ('name');
  5  end;
  6  /
   o := test_ot ('name');
        *
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00306: wrong number or types of arguments in call to 'TEST_OT'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
   

Both attributes need to be specified to instantiate the Object.

      SQL> declare
  2   t test_ot;
  3  begin
  4   t := test_ot ('a name','some description');
  5   dbms_output.put_line (t.name||' - '||t.description);
  6  end;
  7  /
a name - some description

PL/SQL procedure successfully completed.
   

But this is not what we wanted, we want to instantiate the Object Type with only one attribute.
To accomplish this, you would need to create a new CONSTRUCTOR function for the Object Type.

      SQL> create or replace type test_ot
  2  as object
  3  (name varchar2(20)
  4  ,description varchar2(150)
  5  ,constructor
  6   function test_ot (name in varchar2)
  7    return self as result
  8  );
  9  /

Type created.
   

Now the Object Type also needs an Object Type Body:

      SQL> create or replace type body test_ot
  2  as
  3   constructor
  4   function test_ot (name in varchar2)
  5      return self as result
  6   is
  7   begin
  8      self.name := name;
  9      self.description := 'created by constructor';
 10      return;
 11   end test_ot;
 12  end;
 13  /

Type body created.
   

This Constructor Function takes one argument, just for the name. In the Constructor Function the description attribute gets a static value. Of course this can also be a NULL.
Now it is possible to instantiate the Object Type with only one argument.

      SQL> declare
  2   t test_ot;
  3  begin
  4   t := test_ot ('a name');
  5   dbms_output.put_line (t.name||' - '||t.description);
  6  end;
  7  /
a name - created by constructor

PL/SQL procedure successfully completed.
   

Links

• Should you expose a Stored Procedure via XMLTYPE?
• Oracle SOA/Java Blog