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

Conditional Compilation and Static Boolean

One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.

With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where issue #103 is implemented). The dependency lies in the fact that the custom template is stored in the LOGGER_PREFS table and before issue #103 was resolved there was no way to add data to the LOGGER_PREFS table (or at least not a supported way).

Conditinal Compilation is just what the doctor ordered. With a Conditional Compilation directive you can check if Logger is at least version 3, so we can have a supported way of writing into the LOGGER_PREFS table. Sounds easy enough.

And this is where I made some discoveries about Conditional Compilation.

Let's begin with a package specification with only CONSTANTS in there.

      create or replace package constants_pkg
      is
         version   constant varchar2(10) := '1.2.3';
         major_num constant number := 1;
         major_int constant pls_integer := 1;
         major_vc  constant varchar2(1) := 'a';
      end constants_pkg;
   

There are a few variations in there, starting with the current method that Logger has implemented the version number (the constant called VERSION).
Second there is a NUMBER constant.
Third is an PLS_INTEGER constant.
Fourth a variation to the first constant, just one character.

Following is a procedure, called conditional (how appropriate):

      create or replace
      procedure conditional
      is
      begin
         $if constants_pkg.version like '1%'
         $then
            dbms_output.put_line ('string, with LIKE comparison');
         $end
         dbms_output.put_line ('This will always be displayed');
      end conditional;
   

The $IF, $THEN, $END are part of the syntax used for Conditional Compilation.
On line 5 the packaged constant is checked if the string start with a 1. When it does, line 7 is included in the compiled code. If the packaged constant doesn't start with a 1 then line 7 is not included in the compiled code.
You might say: "Should you do a comparison like this"

      $if to_number (substr (constants_pkg.version, 1, 1)) > 1
   

and you would be right, but... for this example it doesn't matter as both don't work. When you try to compile the code, you will see the following error:

Errors for PROCEDURE CONDITIONAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8  PLS-00174: a static boolean expression must be used
   

So my next attempt at getting this to work, was using the full version constant:

      $if constants_pkg.version = '1.2.3'
   

With the same results, the same compilation error.

What about just a single character string?

      $if constants_pkg.major_vc = '1'
   

...Nope, again the same compilation error.

Next up, try a NUMBER constant instead:

      $if constants_pkg.major_num = 1.0
   

I thought the ".0" at the end could make a difference, but alas.. same compilation error.

Last attempt: the PLS_INTEGER:

      $if constants_pkg.major_int = 1
   

This may not come as a surprise now, but this works. :D
This is similar to the way that Oracle does it itself.

When you want to know which release of the Oracle database you are on, you can check DBMS_DB_VERSION. There are constants defined in DBMS_DB_VERSION which you can use with Conditional Compilation.

So Martin, if you are still reading: Can I have the version as a PLS_INTEGER, please?

Links to related articles

1. Speed Up Development with Logger
2. Create Custom Template with LoggerUtil
3. DBMS_DB_VERSION